Tuesday, September 4, 2012

HR EMPLOYEE SUPERVISOR UPDATE API



PROCEDURE PRC_EMPSUPERVISOR_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
--DECLARATION
L_PERSON_ID                  NUMBER;
L_ASSIGNMENT_ID              NUMBER;
L_EFFECTIVE_DATE            DATE:= NULL;
L_SUPERVISOR_ID              NUMBER;
LB_CORRECTION                BOOLEAN;
LB_UPDATE                 BOOLEAN;
LB_UPDATE_OVERRIDE           BOOLEAN;
LB_UPDATE_CHANGE_INSERT         BOOLEAN;
LC_DT_UD_MODE                VARCHAR2(100):= NULL;
L_OBJ_VERSION_NUM            NUMBER;
L_SOFT_CODING_KEYFLEX_ID     HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
L_CONCATENATED_SEGMENTS         VARCHAR2(2000);
L_COMMENT_ID              PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
L_EFFECTIVE_START_DATE       PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
L_EFFECTIVE_END_DATE         PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
L_NO_MANAGERS_WARNING        BOOLEAN;
L_OTHER_MANAGER_WARNING         BOOLEAN;
ERROR_MESSAGE                VARCHAR2(4000):= NULL;
CURRENT_RECORDS              NUMBER;
TOTAL_RECORDS                NUMBER;
ERROR_RECORDS                NUMBER;
L_EFFECTIVE_DATE_VALID       NUMBER;
ERROR_MESSAGE1               VARCHAR2(4000):= NULL;

CURSOR STAGING_RECORDS IS
SELECT A.*,ROWID FROM EMP_SUPERVISOR_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';

BEGIN
SELECT COUNT(*) INTO CURRENT_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG A WHERE UPDATE_STATUS IS NULL OR UPDATE_STATUS = 'E';

FOR I IN STAGING_RECORDS LOOP
--INITIATING VARIABLES TO NULL
L_SOFT_CODING_KEYFLEX_ID := NULL;
L_OBJ_VERSION_NUM:= NULL;
-- TO FETCH THE PERSON_ID BASED ON EMPLOYEE_NUMBER
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);
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
END;
BEGIN
SELECT MAX(OBJECT_VERSION_NUMBER) INTO L_OBJ_VERSION_NUM
   FROM PER_ALL_ASSIGNMENTS_F
      WHERE PERSON_ID = L_PERSON_ID
        AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
IF L_OBJ_VERSION_NUM IS NULL THEN
DBMS_OUTPUT.PUT_LINE(L_OBJ_VERSION_NUM);
END IF;
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
END;
-- TO FETCH THE ASSIGNMENT_ID BASED ON PERSON_ID
BEGIN
SELECT ASSIGNMENT_ID,EFFECTIVE_START_DATE
 INTO L_ASSIGNMENT_ID, L_EFFECTIVE_DATE                   -- 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 SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
        AND OBJECT_VERSION_NUMBER = L_OBJ_VERSION_NUM;
DBMS_OUTPUT.PUT_LINE(L_ASSIGNMENT_ID);
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
END;
-- TO VALIDATE EFFECTIVE_DATE
IF I.EFFECTIVE_DATE >= L_EFFECTIVE_DATE THEN
L_EFFECTIVE_DATE_VALID := 1;
ELSE
L_EFFECTIVE_DATE_VALID := NULL;
END IF;
-- TO FETCH SUPERVISOR_ID THROUGH SUPERVISOR_NAME FROM STAGING TABLE
BEGIN
SELECT PERSON_ID INTO L_SUPERVISOR_ID           -- TO GET SUPERVISOR_ID FROM EMPLOYEE_NUMBER OF SUPERVISOR FROM STAGING TABLE
   FROM PER_ALL_PEOPLE_F
      WHERE EMPLOYEE_NUMBER = I.NEW_MANAGER_ID
       AND SYSDATE BETWEEN EFFECTIVE_START_DATE
                     AND EFFECTIVE_END_DATE;
DBMS_OUTPUT.PUT_LINE(L_SUPERVISOR_ID);      
EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
END;
IF L_PERSON_ID IS NOT NULL AND L_ASSIGNMENT_ID IS NOT NULL AND I.EFFECTIVE_DATE IS NOT NULL AND L_SUPERVISOR_ID IS NOT NULL AND L_OBJ_VERSION_NUM IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL THEN
BEGIN
DT_API.FIND_DT_UPD_MODES
   (    P_EFFECTIVE_DATE                  => (I.EFFECTIVE_DATE),
        P_BASE_TABLE_NAME                 => 'PER_ALL_ASSIGNMENTS_F',
        P_BASE_KEY_COLUMN                 => 'ASSIGNMENT_ID',
        P_BASE_KEY_VALUE                  => L_ASSIGNMENT_ID,
         -- OUTPUT DATA ELEMENTS
        P_CORRECTION                      => LB_CORRECTION,
        P_UPDATE                          => LB_UPDATE,
        P_UPDATE_OVERRIDE                 => LB_UPDATE_OVERRIDE,
        P_UPDATE_CHANGE_INSERT            => LB_UPDATE_CHANGE_INSERT
     );

   IF ( LB_UPDATE_OVERRIDE = TRUE OR LB_UPDATE_CHANGE_INSERT = TRUE )
   THEN
      -- UPDATE_OVERRIDE
       LC_DT_UD_MODE := 'UPDATE_OVERRIDE';
   END IF;
  IF ( LB_CORRECTION = TRUE )
  THEN
      -- CORRECTION
     LC_DT_UD_MODE := 'CORRECTION';
  END IF;
  IF ( LB_UPDATE = TRUE )
  THEN
      -- UPDATE
      LC_DT_UD_MODE := 'UPDATE';
   END IF;
 HR_ASSIGNMENT_API.UPDATE_EMP_ASG
 (
 -- INPUT DATA ELEMENTS
  P_EFFECTIVE_DATE                              => I.EFFECTIVE_DATE,
  P_DATETRACK_UPDATE_MODE                    => LC_DT_UD_MODE,
  P_ASSIGNMENT_ID                               => L_ASSIGNMENT_ID,
  P_SUPERVISOR_ID                               => L_SUPERVISOR_ID,          -- ENTER SUPERVISOR_ID HERE TO CHANGE THE EXISTING SUPERVISOR
  P_CHANGE_REASON                           => NULL,
  --IN OUT ELEMENT
  P_OBJECT_VERSION_NUMBER                    => L_OBJ_VERSION_NUM,
  P_SOFT_CODING_KEYFLEX_ID                   => L_SOFT_CODING_KEYFLEX_ID,
  --OUTPUT DATA ELEMENTS
  P_CONCATENATED_SEGMENTS                    => L_CONCATENATED_SEGMENTS,
  P_COMMENT_ID                                  => L_COMMENT_ID,
  P_EFFECTIVE_START_DATE                        => L_EFFECTIVE_START_DATE,
  P_EFFECTIVE_END_DATE                          => L_EFFECTIVE_END_DATE,
  P_NO_MANAGERS_WARNING                      => L_NO_MANAGERS_WARNING,
  P_OTHER_MANAGER_WARNING                    => L_OTHER_MANAGER_WARNING
 );
IF L_EFFECTIVE_START_DATE IS NOT NULL THEN
UPDATE EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||I.EMPLOYEE_ID||' START DATE'||L_EFFECTIVE_START_DATE);
COMMIT;
ELSE
UPDATE EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = 'RECORD NOT INSERTED DUE TO API ISSUE *' WHERE ROWID = I.ROWID;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||I.EMPLOYEE_ID||' HAS FAILED TO UPDATE');
END IF;

EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
UPDATE EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1 WHERE ROWID = I.ROWID;
COMMIT;
END;

ELSE
   IF L_PERSON_ID IS NULL THEN
   ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH EMPLOYEE EXISTS *';
   END IF;
   IF L_ASSIGNMENT_ID IS NULL THEN
   ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH ASSIGNMENT EXISTS *';
   END IF;
   IF I.EFFECTIVE_DATE IS NULL THEN
   ERROR_MESSAGE:= ERROR_MESSAGE||'PLEASE PROVIDE CORRECT EFFECTIVE DATE *';
   END IF;
   IF L_SUPERVISOR_ID IS NULL THEN
   ERROR_MESSAGE:= ERROR_MESSAGE||'NO SUCH SUPERVISOR EXISTS *';
   END IF;
UPDATE EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;

SELECT COUNT(1) INTO TOTAL_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG;
SELECT COUNT(1) INTO ERROR_RECORDS FROM EMP_SUPERVISOR_UPDATE_STG 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, '---------------------------------------------------------');

--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.LOG,'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, '---------------------------------------------------------');
EXCEPTION WHEN OTHERS THEN
         dbms_output.put_line(SQLERRM);

END;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect