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 p="">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