PROCEDURE xxxx_create_passport_p
IS
CURSOR get_details
IS
SELECT *
FROM xxxx_passport_sit_t
WHERE process_flag = 'N';
l_err_msg VARCHAR2 (500) := NULL;
l_id_flex_num NUMBER (9) := NULL;
l_analysis_criteria_id NUMBER (9) := NULL;
l_person_analysis_id NUMBER (9) := NULL;
l_pea_object_version_number NUMBER (9) := NULL;
BEGIN
--Initialize
Session
fnd_global.
apps_initialize (user_id => 2234, -- XXXX_MIGRATION
resp_id => 50637, -- XXXX
HRMS Manager
resp_appl_id => 800 --
Human Resouces
);
SELECT fifs.id_flex_num
INTO l_id_flex_num
FROM fnd_id_flex_structures fifs
WHERE id_flex_structure_code = 'XXXX_PASSPORT_DETAILS';
FOR fetch_details IN get_details
LOOP
l_err_msg := NULL;
l_analysis_criteria_id := NULL;
l_person_analysis_id := NULL;
l_pea_object_version_number := NULL;
BEGIN
hr_sit_api.
create_sit (
p_validate => FALSE,
p_person_id => fetch_details.person_id,
p_business_group_id => l_business_group_id_gl,
p_id_flex_num => l_id_flex_num,
p_effective_date => SYSDATE,
p_comments => NULL,
p_date_from => fetch_details.start_date,
p_date_to => fetch_details.end_date,
p_segment1 => fetch_details.passport_number,
p_segment2 => NULL,
p_segment3 => fetch_details.place_of_issue,
p_segment4 => fetch_details.issue_date,
p_segment5 => fetch_details.expiry_date,
p_analysis_criteria_id => l_analysis_criteria_id,
p_person_analysis_id => l_person_analysis_id,
p_pea_object_version_number => l_pea_object_version_number);
UPDATE xxxx_passport_sit_t dps
SET dps.process_flag = 'Y', dps.err_msg = NULL
WHERE dps.emp_no = fetch_details.emp_no;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
UPDATE xxxx_passport_sit_t dps
SET dps.process_flag = 'N', dps.err_msg = l_err_msg
WHERE dps.emp_no = fetch_details.emp_no;
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
DBMS_OUTPUT.put_line ('Main
Exception: ' || l_err_msg);
END xxxx_create_passport_p;
No comments:
Post a Comment