Tuesday, September 4, 2012

HR EMPLOYEE ORGANIZATION UPDATE API



PROCEDURE PRC_EMP_ORG_UPDATE(RET_CODE IN VARCHAR2,RET_BUF IN VARCHAR2) IS
              
L_EFFECTIVE_DATE             DATE:=    NULL;
L_ASSIGNMENT_ID              NUMBER:=  NULL;
L_CORRECTION              BOOLEAN;
L_UPDATE                  BOOLEAN;
L_UPDATE_OVERRIDE            BOOLEAN;
L_UPDATE_CHANGE_INSERT       BOOLEAN;
LC_DT_UD_MODE                VARCHAR2(100):= NULL;
L_LOCATION_ID                NUMBER:=  NULL;
L_GRADE_ID                   NUMBER:=  NULL;
L_JOB_ID                  NUMBER:=  NULL;
L_PAYROLL_ID              NUMBER:=  NULL;
L_ORGANIZATION_ID            NUMBER:=  NULL;
L_EMPLOYEE_CATEGORY          VARCHAR2(100):= NULL;
L_POSITION_ID                NUMBER:=  NULL;
L_PEOPLE_GROUP_ID            NUMBER:=  NULL;
L_OBJ_VERSION_NUM            NUMBER:=  NULL;
L_SPECIAL_CEILING_STEP_ID    PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
L_GROUP_NAME              VARCHAR2(30);
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_ORG_NOW_NO_MANAGER_WARNING BOOLEAN;
L_OTHER_MANAGER_WARNING         BOOLEAN;
L_SPP_DELETE_WARNING            BOOLEAN;
L_ENTRIES_CHANGED_WARNING       VARCHAR2(30);
L_TAX_DISTRICT_CHANGED_WARN     BOOLEAN;
L_PERSON_ID                  NUMBER:=  NULL;
L_EFFECTIVE_DATE_VALID       NUMBER:=  NULL;
ERROR_MESSAGE1               VARCHAR2(4000):=NULL;
TOTAL_RECORDS                NUMBER;
CURRENT_RECORDS              NUMBER;
ERROR_RECORDS                NUMBER;
CURSOR STAGING_RECORDS IS
SELECT A.*,ROWID FROM EMP_ORG_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
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID);
--INITIATION OF VARIABLES
L_PEOPLE_GROUP_ID         := NULL;
L_OBJ_VERSION_NUM         := NULL;
L_SPECIAL_CEILING_STEP_ID := NULL;
ERROR_MESSAGE1            := NULL;
-- TO GET PERSON_ID BASED ON EMPLOYEE_NUMBER FROM STAGING TABLE
BEGIN
SELECT PERSON_ID INTO L_PERSON_ID
   FROM PER_ALL_PEOPLE_F
      WHERE EMPLOYEE_NUMBER =I.EMPLOYEE_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
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;

-- TO GET OBJECT_VERSION_NUMBER BASED ON PERSON_ID AND ACTIVE PERSON
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 NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('L_OBJ_VERSION_NUM:'||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,LOCATION_ID,GRADE_ID,JOB_ID,PAYROLL_ID,EMPLOYMENT_CATEGORY,POSITION_ID, PEOPLE_GROUP_ID
 INTO L_ASSIGNMENT_ID, L_EFFECTIVE_DATE, L_LOCATION_ID, L_GRADE_ID, L_JOB_ID, L_PAYROLL_ID, L_EMPLOYEE_CATEGORY, L_POSITION_ID, L_PEOPLE_GROUP_ID
   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:'||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 VALIDATE THE ORGANIZATION WHICH IS IN STAGING TABLE
BEGIN
SELECT ORGANIZATION_ID INTO L_ORGANIZATION_ID
   FROM HR_ALL_ORGANIZATION_UNITS
      WHERE NAME = I.NEW_ORGANIZATION;
DBMS_OUTPUT.PUT_LINE('L_ORGANIZATION_ID:'||L_ORGANIZATION_ID);
EXCEPTION
       WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||' '||SQLERRM||' *';
END;

--       AND BUSINESS_GROUP_ID = 202;
IF L_PERSON_ID IS NOT NULL AND L_OBJ_VERSION_NUM IS NOT NULL AND L_ASSIGNMENT_ID IS NOT NULL AND L_EFFECTIVE_DATE_VALID IS NOT NULL AND L_ORGANIZATION_ID 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                     => L_CORRECTION,
         P_UPDATE                         => L_UPDATE,
         P_UPDATE_OVERRIDE                => L_UPDATE_OVERRIDE,
         P_UPDATE_CHANGE_INSERT           => L_UPDATE_CHANGE_INSERT
     );

   IF ( L_UPDATE_OVERRIDE = TRUE OR L_UPDATE_CHANGE_INSERT = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       LC_DT_UD_MODE := 'UPDATE_OVERRIDE';
   END IF;
  IF ( L_CORRECTION = TRUE )
  THEN
      -- CORRECTION
     LC_DT_UD_MODE := 'CORRECTION';
  END IF;
  IF ( L_UPDATE = TRUE )
  THEN
      -- UPDATE
      LC_DT_UD_MODE := 'UPDATE';
   END IF;
  DBMS_OUTPUT.PUT_LINE('MODE: '||LC_DT_UD_MODE);
--WE NEED TO PROVIDE COMBINATIONS OF POSITION VALUES, JOB VALUES AND ORGANIZATION VALUES WHICH ARE LOCATED IN PER_POSITIONS.
HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
 (
 -- INPUT DATA ELEMENTS
  P_EFFECTIVE_DATE                              => I.EFFECTIVE_DATE,
  P_DATETRACK_UPDATE_MODE                    => LC_DT_UD_MODE,
  P_ASSIGNMENT_ID                               => L_ASSIGNMENT_ID,
  P_LOCATION_ID                                 => L_LOCATION_ID,
  P_GRADE_ID                                    => L_GRADE_ID,
  P_JOB_ID                                      => L_JOB_ID,
  P_PAYROLL_ID                                  => L_PAYROLL_ID,
  P_ORGANIZATION_ID                             => L_ORGANIZATION_ID,   --NEW ORG_ID
  P_EMPLOYMENT_CATEGORY                         => L_EMPLOYEE_CATEGORY,
  P_POSITION_ID                           => L_POSITION_ID,
  -- OUTPUT DATA ELEMENTS
  P_PEOPLE_GROUP_ID                             => L_PEOPLE_GROUP_ID,
  P_OBJECT_VERSION_NUMBER                   => L_OBJ_VERSION_NUM,
  P_SPECIAL_CEILING_STEP_ID                     => L_SPECIAL_CEILING_STEP_ID,
  P_GROUP_NAME                                  => L_GROUP_NAME,
  P_EFFECTIVE_START_DATE                        => L_EFFECTIVE_START_DATE,
  P_EFFECTIVE_END_DATE                          => L_EFFECTIVE_END_DATE,
  P_ORG_NOW_NO_MANAGER_WARNING               => L_ORG_NOW_NO_MANAGER_WARNING,
  P_OTHER_MANAGER_WARNING                       => L_OTHER_MANAGER_WARNING,
  P_SPP_DELETE_WARNING                          => L_SPP_DELETE_WARNING,
  P_ENTRIES_CHANGED_WARNING                  => L_ENTRIES_CHANGED_WARNING,
  P_TAX_DISTRICT_CHANGED_WARNING             => L_TAX_DISTRICT_CHANGED_WARN
 );

 DBMS_OUTPUT.PUT_LINE(L_EFFECTIVE_START_DATE);
 IF L_EFFECTIVE_START_DATE IS NOT NULL THEN
 UPDATE EMP_ORG_UPDATE_STG   SET UPDATE_STATUS = 'S' WHERE ROWID = I.ROWID;
 COMMIT;
 ELSE
 UPDATE EMP_ORG_UPDATE_STG   SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1||' UNKNOWN ERROR FROM API *' WHERE ROWID = I.ROWID;
 COMMIT;
 END IF;

EXCEPTION WHEN OTHERS THEN                                                               
ERROR_MESSAGE1:=   ERROR_MESSAGE1||SQLERRM||' *';
UPDATE EMP_ORG_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_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH EMPLOYEE EXISTS *';
   END IF;
   IF L_ASSIGNMENT_ID IS NULL THEN
   ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO SUCH ASSIGNMENT EXISTS *';
   END IF;
   IF I.EFFECTIVE_DATE IS NULL OR L_EFFECTIVE_DATE_VALID IS NULL THEN
   ERROR_MESSAGE1:= ERROR_MESSAGE1||'PLEASE PROVIDE CORRECT EFFECTIVE DATE *';
   END IF;
   IF L_OBJ_VERSION_NUM IS NULL THEN
   ERROR_MESSAGE1:= ERROR_MESSAGE1||'NO ACTIVE ASSIGNMENTS AVAILABLE FOR THIS EMPLOYEE *';
   END IF;
UPDATE EMP_ORG_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1 WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
SELECT COUNT(1) INTO TOTAL_RECORDS FROM EMP_ORG_UPDATE_STG;
SELECT COUNT(1) INTO ERROR_RECORDS FROM EMP_ORG_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 PRC_EMP_ORG_UPDATE;
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect