PROCEDURE
PRC_EMPSAL_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
LB_INV_NEXT_SAL_DATE_WARNING BOOLEAN;
LB_PROPOSED_SALARY_WARNING BOOLEAN;
LB_APPROVED_WARNING BOOLEAN;
LB_PAYROLL_WARNING BOOLEAN;
LN_PAY_PROPOSAL_ID NUMBER;
LN_OBJECT_VERSION_NUMBER NUMBER;
L_ASSIGNMENT_NUMBER
NUMBER;
L_PROPOSAL_REASON
VARCHAR2(30);
L_APPROVED VARCHAR2(10);
L_DATE_TO DATE:= NULL;
TOTAL_RECORDS
NUMBER;
ERROR_RECORDS
NUMBER;
CURRENT_RECORDS
NUMBER;
L_ERROR_MESSAGE
VARCHAR2(4000);
L_PERSON_ID NUMBER;
L_EFFECTIVE_DATE_VALID
NUMBER;
CURSOR STAGING_RECORDS
IS
SELECT A.*,ROWID FROM HR_EMP_UPDATE_STAGING A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
BEGIN
SELECT COUNT(*)INTO
CURRENT_RECORDS FROM HR_EMP_UPDATE_STAGING A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';
FOR I IN STAGING_RECORDS
LOOP
--INITIAL VALUES
L_ERROR_MESSAGE := NULL;
L_APPROVED := NULL;
LN_PAY_PROPOSAL_ID := NULL;
LN_OBJECT_VERSION_NUMBER := NULL;
--STATUS VALIDATIONS
IF I.STATUS = 'Approved' THEN
L_APPROVED := 'Y';
ELSIF I.STATUS = 'Proposed' THEN
L_APPROVED := 'N';
ELSE
L_APPROVED := NULL;
END IF;
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
FROM
PER_ALL_PEOPLE_F
WHERE
EMPLOYEE_NUMBER =I.EMPLOYEE_ID --
ENTER EMPLOYEE NUMBER HERE TO FETCH PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE('L_PERSON_ID: '||L_PERSON_ID);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
BEGIN
SELECT ASSIGNMENT_ID INTO
L_ASSIGNMENT_NUMBER -- TO FETCH ASSIGNMENT_ID BASED ON PERSON_ID WHICH IS
GENERATED FROM ABOVE QUERY
FROM
PER_ALL_ASSIGNMENTS_F
WHERE PERSON_ID = L_PERSON_ID
AND EFFECTIVE_START_DATE >=SYSDATE
AND ROWNUM<2;
DBMS_OUTPUT.PUT_LINE('L_ASSIGNMENT_NUMBER: '||L_ASSIGNMENT_NUMBER);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
--TO GET PROPOSAL REASONS
BEGIN
SELECT LOOKUP_CODE INTO
L_PROPOSAL_REASON FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PROPOSAL_REASON' AND MEANING = I.PROPOSAL_REASON;
DBMS_OUTPUT.PUT_LINE('L_PROPOSAL_REASON: '||L_PROPOSAL_REASON);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
BEGIN
SELECT 1 INTO
L_EFFECTIVE_DATE_VALID -- EFFECTIVE DATE VALIDATION
FROM PER_PAY_PROPOSALS
WHERE ASSIGNMENT_ID =
L_ASSIGNMENT_NUMBER
HAVING I.EFFECTIVE_DATE > MAX(CHANGE_DATE);
DBMS_OUTPUT.PUT_LINE('L_EFFECTIVE_DATE_VALID: '||L_EFFECTIVE_DATE_VALID);
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
END;
IF
L_ASSIGNMENT_NUMBER IS NOT NULL AND I.NEW_SALARY IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL AND L_APPROVED IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' NEW SALARY: '||I.NEW_SALARY);
BEGIN
HR_MAINTAIN_PROPOSAL_API.CRE_OR_UPD_SALARY_PROPOSAL
(
-- INPUT
P_BUSINESS_GROUP_ID => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
P_ASSIGNMENT_ID =>
L_ASSIGNMENT_NUMBER,
P_CHANGE_DATE => I.EFFECTIVE_DATE, -- EFFECTIVE FROM DATE
P_PROPOSAL_REASON =>
L_PROPOSAL_REASON, --
PROPOSAL REASON
P_PROPOSED_SALARY_N => I.NEW_SALARY, -- PROPOSED SALARY
P_APPROVED => L_APPROVED,
P_DATE_TO => L_DATE_TO, -- EFFECTIVE TO DATE
-- OUTPUT
P_PAY_PROPOSAL_ID =>
LN_PAY_PROPOSAL_ID,
P_OBJECT_VERSION_NUMBER
=> LN_OBJECT_VERSION_NUMBER,
P_INV_NEXT_SAL_DATE_WARNING
=> LB_INV_NEXT_SAL_DATE_WARNING,
P_PROPOSED_SALARY_WARNING
=> LB_PROPOSED_SALARY_WARNING,
P_APPROVED_WARNING =>
LB_APPROVED_WARNING,
P_PAYROLL_WARNING =>
LB_PAYROLL_WARNING
);
IF
LN_PAY_PROPOSAL_ID IS NOT NULL THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(LN_PAY_PROPOSAL_ID||' FOR :'||L_ASSIGNMENT_NUMBER||' PERSON_ID');
UPDATE
HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
ELSE
UPDATE
HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E', ERROR_MESSAGE = 'ERROR OUT FROM API EXECUTION' WHERE ROWID = I.ROWID;
END IF;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= L_ERROR_MESSAGE||' '||SQLERRM||' *';
UPDATE
HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E', ERROR_MESSAGE =
L_ERROR_MESSAGE WHERE ROWID = I.ROWID;
END;
ELSE
IF
L_ASSIGNMENT_NUMBER IS NULL THEN
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE||'NO SUCH EMPLOYEE EXISTS *';
END IF;
IF L_EFFECTIVE_DATE_VALID
IS NULL THEN
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT EFFECTIVE DATE *';
END IF;
IF I.NEW_SALARY IS NULL THEN
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT SALARY *';
END IF;
IF L_APPROVED IS NULL THEN
L_ERROR_MESSAGE :=
L_ERROR_MESSAGE||'PLEASE PROVIDE CORRECT APPROVED STATUS *';
END IF;
UPDATE
HR_EMP_UPDATE_STAGING SET UPDATE_STATUS = 'E',ERROR_MESSAGE =
L_ERROR_MESSAGE WHERE ROWID = I.ROWID;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR RECORD FOR EMPLOYEE ID : '||I.EMPLOYEE_ID||' '||L_ERROR_MESSAGE);
COMMIT;
END IF;
END LOOP;
SELECT COUNT(*) INTO TOTAL_RECORDS FROM HR_EMP_UPDATE_STAGING;
SELECT COUNT(*) INTO ERROR_RECORDS FROM HR_EMP_UPDATE_STAGING WHERE UPDATE_STATUS = 'E';
--VIEW LOG FOR THIS ERROR DATA
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------RECORD
VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CURRENT NO. OF RECORDS TO INSERT : '||CURRENT_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.LOG,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.LOG, '---------------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UN-SUCCESSFUL');
--VIEW OUTPUT FILE FOR THIS DATA
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------RECORD
VALIDATION STATS-------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TOTAL NO. OF RECORDS : '||TOTAL_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'CURRENT NO. OF RECORDS TO INSERT : '||(TOTAL_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS INSERTED : '||(CURRENT_RECORDS-ERROR_RECORDS));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO. OF RECORDS FAILED TO INSERT : '||ERROR_RECORDS);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '---------------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'SUCCESSFUL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END
PRC_EMPSAL_UPDATE;
/
No comments:
Post a Comment