Employee Contact EIT API Script
DECLARE
o_object_version_number NUMBER;
o_person_extra_info_id NUMBER;
l_person_id NUMBER;
l_error VARCHAR2 (4000 BYTE);
v_contact_extra_info_id NUMBER;
v_object_version_number NUMBER;
v_effective_start_date DATE;
v_effective_end_date DATE;
CURSOR c1
IS
SELECT *
FROM xx_act_emp_con_passport
WHERE status = 'Y' AND sno <= 50000;
BEGIN
FOR c1_rec IN c1
LOOP
v_object_version_number := NULL;
v_contact_extra_info_id := NULL;
l_person_id := NULL;
l_error := NULL;
IF c1_rec.contact_relationship_id IS NOT NULL
THEN
BEGIN
hr_contact_extra_info_api.create_contact_extra_info
(p_validate => FALSE,
p_effective_date => c1_rec.contact_start_date,
p_contact_relationship_id => c1_rec.contact_relationship_id,
p_information_type => 'XX_HR_CONTACT_PASSPORT',
p_cei_information_category => 'XX_HR_CONTACT_PASSPORT',
p_cei_information1 => c1_rec.passport_number,
p_cei_information2 => c1_rec.passport_issue_location,
p_cei_information3 => TO_CHAR(TO_DATE(c1_rec.passport_issue_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD'),
p_cei_information4 => TO_CHAR(TO_DATE(c1_rec.passport_expire_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD'),
p_cei_information5 => TRIM (c1_rec.contact_name),
p_contact_extra_info_id => v_contact_extra_info_id,
p_object_version_number => v_object_version_number,
p_effective_start_date => v_effective_start_date,
p_effective_end_date => v_effective_end_date
);
UPDATE xx_act_emp_con_passport
SET status = 'S'
WHERE sno = c1_rec.sno;
EXCEPTION
WHEN OTHERS
THEN
l_error := l_error || SQLERRM;
UPDATE xx_act_emp_con_passport
SET status = 'E',
remarks = l_error
WHERE sno = c1_rec.sno;
END;
END IF;
END LOOP;
COMMIT;
END;
