Oracle Apps HRMS R12 API to update the supervisor_id.

1.
Creating Custom Interface table:

CREATE TABLE APPS.EMP_SUPERVISOR_UPDATE_STG
(
  EMPLOYEE_ID     VARCHAR2(30 BYTE),
  EFFECTIVE_DATE  DATE,
  UPDATE_STATUS   VARCHAR2(30 BYTE),
  NEW_MANAGER_ID  VARCHAR2(30 BYTE),
  OTHERCOL        VARCHAR2(30 BYTE),
  OTHERCOL2       VARCHAR2(30 BYTE),
  OTHERCOL3       VARCHAR2(30 BYTE),
  ERROR_MESSAGE   VARCHAR2(2000 BYTE)
)

2.
Upload data/ Insert data into above custom table using below script:


INSERT INTO  EMP_SUPERVISOR_UPDATE_STG
SELECT PAPF.PERSON_ID,TO_DATE('01-JAN-2016'),'',18504,NULL,NULL,NULL,NULL
--,SUPERVISOR_ID
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAAF.ORGANIZATION_ID IN (1421)
AND PRIMARY_FLAG = 'Y'

3. pl/sql block api:


DECLARE
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')
--AND EMPLOYEE_ID = 2809
;

CURSOR C2 (P_PERSON_ID NUMBER, P_DATE DATE) IS
 SELECT OBJECT_VERSION_NUMBER, EFFECTIVE_START_DATE ,ASSIGNMENT_ID
  FROM PER_ALL_ASSIGNMENTS_F ASG
  WHERE ASG.ASSIGNMENT_TYPE='E'
  AND ASG.PERSON_iD = P_PERSON_ID
  AND (ASG.EFFECTIVE_START_DATE >= P_DATE
    --AND (ASG.EFFECTIVE_START_DATE >= trunc(sysdate)
  OR  P_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
  ) ;

BEGIN

FOR I IN STAGING_RECORDS LOOP

L_SOFT_CODING_KEYFLEX_ID := NULL;
L_OBJ_VERSION_NUM:= NULL;
-- TO FETCH THE PERSON_ID BASED ON EMPLOYEE_NUMBER
   
    FOR J IN C2(I.EMPLOYEE_ID,I.EFFECTIVE_DATE)  LOOP
BEGIN
dt_api.find_dt_upd_modes
   (    p_effective_date                  => J.EFFECTIVE_START_DATE ,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => J.ASSIGNMENT_ID,--ln_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  );


EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('HI UPDATE MODES');
END;

 IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;

BEGIN
HR_ASSIGNMENT_API.UPDATE_EMP_ASG
(
-- INPUT DATA ELEMENTS
P_EFFECTIVE_DATE => J.EFFECTIVE_START_DATE,
P_DATETRACK_UPDATE_MODE => LC_DT_UD_MODE,
P_ASSIGNMENT_ID => J.ASSIGNMENT_ID,
P_SUPERVISOR_ID => I.NEW_MANAGER_ID, -- ENTER SUPERVISOR_ID HERE TO CHANGE THE EXISTING SUPERVISOR
P_CHANGE_REASON => NULL,
--IN OUT ELEMENT
P_OBJECT_VERSION_NUMBER => J.OBJECT_VERSION_NUMBER,
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
);
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;
COMMIT;

EXCEPTION WHEN OTHERS THEN
ERROR_MESSAGE1:= ERROR_MESSAGE1||SQLERRM||' *';
UPDATE EMP_SUPERVISOR_UPDATE_STG SET UPDATE_STATUS = 'E', ERROR_MESSAGE = ERROR_MESSAGE1 WHERE EMPLOYEE_ID =
I.EMPLOYEE_ID;
COMMIT;
---------------------------------------------------------------
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line('HI');
END;