Today we will explain by example how to create person extra information in ORACLE EBS HRMS by using standard API (hr_person_extra_info_api.create_person_extra_info), but first, we will highlight what is EIT in the oracle e-business suite.
EIT in Oracle HRMS.EITs are basically Descriptive Flexfields (DFF). Standard product has EIT DFFs defined in forms such as Person, Assignment, Job, etc. We can add segments to the defined DFF based on our requirements so that they will appear on the corresponding screen.
and here is an example to show you how to insert an employee’s passport details into extra information tables using API.
procedure create_pp_details (
p_pp_country in varchar2
,p_pp_number in varchar2
,p_pp_issue_date in date
,p_pp_expiry_date in date
,p_pp_issue_place in varchar2
,p_person_id in number)
is
l_person_extra_info_id number;
l_object_version_number number;
begin
hr_person_extra_info_api.create_person_extra_info (
p_validate => false
,p_person_id => p_person_id
,p_information_type => 'PASSPORT_DETAILS'
,p_pei_attribute_category => null
,p_pei_information_category => 'PASSPORT_DETAILS'
,p_pei_information1 => p_pp_country
,p_pei_information2 => p_pp_number
,p_pei_information3 => to_char (p_pp_issue_date, 'RRRR/MM/DD HH:MI:SS')
,p_pei_information4 => to_char (p_pp_expiry_date, 'RRRR/MM/DD HH:MI:SS')
,p_pei_information5 => p_pp_issue_place
,p_person_extra_info_id => l_person_extra_info_id
,p_object_version_number => l_object_version_number);
commit;
end;And here, you can find another example about how to use API to create person extra info
declare
LB_VALIDATE BOOLEAN;
LN_PERSON_ID NUMBER;
LV_INFORMATION_TYPE VARCHAR2(150);
LV_PEI_ATTRIBUTE_CATEGORY VARCHAR2(150);
LV_PEI_ATTRIBUTE1 VARCHAR2(150);
LV_PEI_ATTRIBUTE2 VARCHAR2(150);
LV_PEI_ATTRIBUTE3 VARCHAR2(150);
LV_PEI_ATTRIBUTE4 VARCHAR2(150);
LV_PEI_ATTRIBUTE5 VARCHAR2(150);
LV_PEI_ATTRIBUTE6 VARCHAR2(150);
LV_PEI_ATTRIBUTE7 VARCHAR2(150);
LV_PEI_ATTRIBUTE8 VARCHAR2(150);
LV_PEI_ATTRIBUTE9 VARCHAR2(150);
LV_PEI_ATTRIBUTE10 VARCHAR2(150);
LV_PEI_ATTRIBUTE11 VARCHAR2(150);
LV_PEI_ATTRIBUTE12 VARCHAR2(150);
LV_PEI_ATTRIBUTE13 VARCHAR2(150);
LV_PEI_ATTRIBUTE14 VARCHAR2(150);
LV_PEI_ATTRIBUTE15 VARCHAR2(150);
LV_PEI_ATTRIBUTE16 VARCHAR2(150);
LV_PEI_ATTRIBUTE17 VARCHAR2(150);
LV_PEI_ATTRIBUTE18 VARCHAR2(150);
LV_PEI_ATTRIBUTE19 VARCHAR2(150);
LV_PEI_ATTRIBUTE20 VARCHAR2(150);
LV_PEI_INFORMATION_CATEGORY VARCHAR2(150);
LV_PEI_INFORMATION1 VARCHAR2(150);
LV_PEI_INFORMATION2 VARCHAR2(150);
LV_PEI_INFORMATION3 VARCHAR2(150);
LV_PEI_INFORMATION4 VARCHAR2(150);
LV_PEI_INFORMATION5 VARCHAR2(150);
LV_PEI_INFORMATION6 VARCHAR2(150);
LV_PEI_INFORMATION7 VARCHAR2(150);
LV_PEI_INFORMATION8 VARCHAR2(150);
LV_PEI_INFORMATION9 VARCHAR2(150);
LV_PEI_INFORMATION10 VARCHAR2(150);
LV_PEI_INFORMATION11 VARCHAR2(150);
LV_PEI_INFORMATION12 VARCHAR2(150);
LV_PEI_INFORMATION13 VARCHAR2(150);
LV_PEI_INFORMATION14 VARCHAR2(150);
LV_PEI_INFORMATION15 VARCHAR2(150);
LV_PEI_INFORMATION16 VARCHAR2(150);
LV_PEI_INFORMATION17 VARCHAR2(150);
LV_PEI_INFORMATION18 VARCHAR2(150);
LV_PEI_INFORMATION19 VARCHAR2(150);
LV_PEI_INFORMATION20 VARCHAR2(150);
LV_PEI_INFORMATION21 VARCHAR2(150);
LV_PEI_INFORMATION22 VARCHAR2(150);
LV_PEI_INFORMATION23 VARCHAR2(150);
LV_PEI_INFORMATION24 VARCHAR2(150);
LV_PEI_INFORMATION25 VARCHAR2(150);
LV_PEI_INFORMATION26 VARCHAR2(150);
LV_PEI_INFORMATION27 VARCHAR2(150);
LV_PEI_INFORMATION28 VARCHAR2(150);
LV_PEI_INFORMATION29 VARCHAR2(150);
LV_PEI_INFORMATION30 VARCHAR2(150);
LN_PERSON_EXTRA_INFO_ID NUMBER;
LN_OBJECT_VERSION_NUMBER NUMBER;
begin
HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO(
P_VALIDATE => FALSE
,P_PERSON_ID => 68567
,P_INFORMATION_TYPE => 'XXALB_OTHER_DETAILS'
,P_PEI_ATTRIBUTE_CATEGORY => LV_PEI_ATTRIBUTE_CATEGORY
,P_PEI_ATTRIBUTE1 => LV_PEI_ATTRIBUTE1
,P_PEI_ATTRIBUTE2 => LV_PEI_ATTRIBUTE2
,P_PEI_ATTRIBUTE3 => LV_PEI_ATTRIBUTE3
,P_PEI_ATTRIBUTE4 => LV_PEI_ATTRIBUTE4
,P_PEI_ATTRIBUTE5 => LV_PEI_ATTRIBUTE5
,P_PEI_ATTRIBUTE6 => LV_PEI_ATTRIBUTE6
,P_PEI_ATTRIBUTE7 => LV_PEI_ATTRIBUTE7
,P_PEI_ATTRIBUTE8 => LV_PEI_ATTRIBUTE8
,P_PEI_ATTRIBUTE9 => LV_PEI_ATTRIBUTE9
,P_PEI_ATTRIBUTE10 => LV_PEI_ATTRIBUTE10
,P_PEI_ATTRIBUTE11 => LV_PEI_ATTRIBUTE11
,P_PEI_ATTRIBUTE12 => LV_PEI_ATTRIBUTE12
,P_PEI_ATTRIBUTE13 => LV_PEI_ATTRIBUTE13
,P_PEI_ATTRIBUTE14 => LV_PEI_ATTRIBUTE14
,P_PEI_ATTRIBUTE15 => LV_PEI_ATTRIBUTE15
,P_PEI_ATTRIBUTE16 => LV_PEI_ATTRIBUTE16
,P_PEI_ATTRIBUTE17 => LV_PEI_ATTRIBUTE17
,P_PEI_ATTRIBUTE18 => LV_PEI_ATTRIBUTE18
,P_PEI_ATTRIBUTE19 => LV_PEI_ATTRIBUTE19
,P_PEI_ATTRIBUTE20 => LV_PEI_ATTRIBUTE20
,P_PEI_INFORMATION_CATEGORY => 'XXALB_OTHER_DETAILS'
,P_PEI_INFORMATION1 => LV_PEI_INFORMATION1
,P_PEI_INFORMATION2 => LV_PEI_INFORMATION2
,P_PEI_INFORMATION3 => 'Y'
,P_PEI_INFORMATION4 => LV_PEI_INFORMATION4
,P_PEI_INFORMATION5 => 'This is Sample'
,P_PEI_INFORMATION6 => 'For EIT Creation API'
,P_PEI_INFORMATION7 => LV_PEI_INFORMATION7
,P_PEI_INFORMATION8 => LV_PEI_INFORMATION8
,P_PEI_INFORMATION9 => LV_PEI_INFORMATION9
,P_PEI_INFORMATION10 => LV_PEI_INFORMATION10
,P_PEI_INFORMATION11 => LV_PEI_INFORMATION11
,P_PEI_INFORMATION12 => LV_PEI_INFORMATION12
,P_PEI_INFORMATION13 => LV_PEI_INFORMATION13
,P_PEI_INFORMATION14 => LV_PEI_INFORMATION14
,P_PEI_INFORMATION15 => LV_PEI_INFORMATION15
,P_PEI_INFORMATION16 => LV_PEI_INFORMATION16
,P_PEI_INFORMATION17 => LV_PEI_INFORMATION17
,P_PEI_INFORMATION18 => LV_PEI_INFORMATION18
,P_PEI_INFORMATION19 => LV_PEI_INFORMATION19
,P_PEI_INFORMATION20 => LV_PEI_INFORMATION20
,P_PEI_INFORMATION21 => LV_PEI_INFORMATION21
,P_PEI_INFORMATION22 => LV_PEI_INFORMATION22
,P_PEI_INFORMATION23 => LV_PEI_INFORMATION23
,P_PEI_INFORMATION24 => LV_PEI_INFORMATION24
,P_PEI_INFORMATION25 => LV_PEI_INFORMATION25
,P_PEI_INFORMATION26 => LV_PEI_INFORMATION26
,P_PEI_INFORMATION27 => LV_PEI_INFORMATION27
,P_PEI_INFORMATION28 => LV_PEI_INFORMATION28
,P_PEI_INFORMATION29 => LV_PEI_INFORMATION29
,P_PEI_INFORMATION30 => LV_PEI_INFORMATION30
,P_PERSON_EXTRA_INFO_ID => LN_PERSON_EXTRA_INFO_ID
,P_OBJECT_VERSION_NUMBER => LN_OBJECT_VERSION_NUMBER
);
dbms_output.put_line('EIT Create Successfully . LN_PERSON_EXTRA_INFO_ID = '||LN_PERSON_EXTRA_INFO_ID);
exception when others then
dbms_output.put_line('Exception '||SQLERRM);
end;and the last example you can take as a reference for you when you want to use this API or any API to execute it from a concurrent program.
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;Note: for the last example, there are two parameters in the procedure for a concurrent program.
If you have any questions, please don’t hesitate to ask them in the comments section below.
Hopefully, it was clear and concise. Please share it to spread the knowledge
If you have an addition to this guide that will add extra value for the readers, kindly let me know.
