Extra Person Information (EIT) Upload API — Procedure

Following Procedure used for uploading data in Extra Person Information.  

1) Create a EIT using System Administrator Responsibility.

Application>>Flexfield>>Descriptive>>Segments

Create as per following snapshot.

 

2)  Create Following Staging Table and upload data as required. 

CREATE TABLE APPS.XX_Passport_TBL
(
EMP_NUMBER VARCHAR2(200 BYTE),
PASSPORT_NUMBER VARCHAR2(200 BYTE),
PROFESSION VARCHAR2(200 BYTE),
ISSUE_DATE_G VARCHAR2(200 BYTE),
EXP_DATE_G VARCHAR2(200 BYTE),
PLACE_OF_ISSUE VARCHAR2(200 BYTE),
ISSUE_DATE_H VARCHAR2(200 BYTE),
EXP_DATE_H VARCHAR2(200 BYTE),
LOAD_STATUS VARCHAR2(200 BYTE),
ERROR_MSG VARCHAR2(400 BYTE)
)

3) Create following Procedure and Execute 

Create or replace PROCEDURE xx_emp_paasport_conv_proc (errorbuf VARCHAR2, retcode VARCHAR2)
AS
totalrecords_sucess NUMBER := 0;
totalrecords_fail NUMBER := 0;
totalrecords_tobeupload NUMBER := 0;
ln_person_id NUMBER;
lc_passport_number per_people_extra_info.pei_information1%TYPE;
lc_profession per_people_extra_info.pei_information2%TYPE;
lc_issue_date_g per_people_extra_info.pei_information3%TYPE;
lc_exp_date_g per_people_extra_info.pei_information4%TYPE;
lc_place_of_issue per_people_extra_info.pei_information5%TYPE;
lc_issue_date_h per_people_extra_info.pei_information7%TYPE;
lc_exp_date_h per_people_extra_info.pei_information8%TYPE;
lc_information_type per_people_extra_info.information_type%TYPE
:= ‘EIT_PASSPORT’;
lc_pei_information_category per_people_extra_info.pei_information_category%TYPE
:= ‘EIT_PASSPORT’;
ln_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE
:= NULL;
ln_object_version_number per_people_extra_info.object_version_number%TYPE
:= NULL;
v_error VARCHAR2 (3000);

CURSOR cur_hafidet
IS
SELECT emp_number, passport_number, profession,
TO_CHAR (TO_DATE (issue_date_g, ‘RRRR/MM/DD’),
‘RRRR/MM/DD HH24:MI:SS’
) issue_date_g,
TO_CHAR (TO_DATE (exp_date_g, ‘RRRR/MM/DD’),
‘RRRR/MM/DD HH24:MI:SS’
) exp_date_g,
place_of_issue,
to_char(TO_DATE (issue_date_g, ‘RRRR/MM/DD’),’dd/mm/yyyy’,’NLS_CALENDAR=”Arabic Hijrah”’) issue_date_h,
to_char(TO_DATE (exp_date_g, ‘RRRR/MM/DD’),’dd/mm/yyyy’,’NLS_CALENDAR=”Arabic Hijrah”’) exp_date_h
FROM XX_Passport_TBL
where nvl(LOAD_STATUS,’E’) <> ‘S’
and issue_date_g is not null;

BEGIN
FOR i IN cur_hafidet
LOOP

DBMS_OUTPUT.put_line (‘After Cursor’);
lc_passport_number := i.passport_number;
lc_profession := i.profession;
lc_issue_date_g := i.issue_date_g;
lc_exp_date_g := i.exp_date_g;
lc_place_of_issue := i.place_of_issue;
lc_issue_date_h := i.issue_date_h;
lc_exp_date_h := i.exp_date_h;

BEGIN
SELECT papf.person_id
INTO ln_person_id
FROM per_all_people_f papf
WHERE papf.employee_number = i.emp_number
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.business_group_id = 81;
EXCEPTION
WHEN OTHERS
THEN
v_error := ‘Loc1:’ || i.emp_number || SQLERRM;
— UPDATE XX_Passport_TBL
— SET load_status = ‘E’,
— error_msg = v_error
— WHERE emp_number = i.emp_number;
ln_person_id := NULL;
END;

IF ln_person_id IS NOT NULL
THEN

BEGIN
DBMS_OUTPUT.put_line (‘Before API’);
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => ln_person_id,
p_information_type => lc_information_type,
p_pei_information_category => lc_pei_information_category,
p_pei_information1 => lc_passport_number,
p_pei_information2 => lc_profession,
p_pei_information3 => lc_issue_date_g,
p_pei_information4 => lc_exp_date_g,
p_pei_information5 => lc_place_of_issue,
p_pei_information7 => lc_issue_date_h,
p_pei_information8 => lc_exp_date_h,
p_person_extra_info_id => ln_person_extra_info_id,
p_object_version_number => ln_object_version_number
);
COMMIT;
DBMS_OUTPUT.put_line (‘After API’);

UPDATE XX_Passport_TBL
SET load_status = ‘S’,
error_msg = ‘Successfullly Loaded’
WHERE emp_number = i.emp_number;

totalrecords_sucess := totalrecords_sucess + 1;
fnd_file.put_line (fnd_file.output,
‘Success: For Employee Number:=’
|| i.emp_number
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_error :=
v_error
|| ‘Loc API :’
|| i.emp_number
|| ln_person_id
|| SQLERRM;

UPDATE XX_Passport_TBL
SET load_status = ‘E’,
error_msg = v_error
WHERE emp_number = i.emp_number;

totalrecords_fail := totalrecords_fail + 1;
fnd_file.put_line (fnd_file.LOG,
‘Failure: For Employee Number:=’
|| i.emp_number
|| ‘Error Message –>’
|| v_error
);
END;
ELSE
UPDATE XX_Passport_TBL
SET load_status = ‘E’,
error_msg = i.emp_number || ‘=>Person Does not exist’
WHERE emp_number = i.emp_number;

totalrecords_fail := totalrecords_fail + 1;
fnd_file.put_line (fnd_file.LOG,
‘Failure: For Employee Number:=’
|| i.emp_number
|| ‘Error Message –>’
|| v_error
);
END IF;

totalrecords_tobeupload := totalrecords_tobeupload + 1;
END LOOP;

fnd_file.put_line (fnd_file.output,
‘TOTAL RECORDS TO BE UPLOAD–>’
|| totalrecords_tobeupload
);
fnd_file.put_line (fnd_file.output,
‘TOTAL SUCCESS RECORDS –>’ || totalrecords_sucess
);
fnd_file.put_line (fnd_file.output,
‘TOTAL FAILURE RECORDS–>’ || totalrecords_fail
);
END xx_emp_paasport_conv_proc;