Tuesday, September 4, 2012

HR EMPLOYEE SALARY UPDATE API



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

Best Blogger TipsGet Flower Effect