Oracle HRMS API – Insert/Create Employee Extra Information
-----------------------------------------------------------
Data File Format
----------------------
Staging Table Format
--------------------------
-----------------------------------------------------------
Data File Format
----------------------
| Employee Num | Nationality | IQAMA Num | Categoy | Medgulf PIN | Medical Expiry Date | Process Flag | Error Msg |
| 3 | 1000206001 | CLASS VIP1 | 13651132 | 10-Oct-14 | N |
Staging Table Format
--------------------------
CREATE TABLE XXAT_EMP_EXTRA_INFO
(emp_num NUMBER,
natiionality varchar2(1000),
iqama_num NUMBER,
category_name VARCHAR2(100),
medgulf_pin VARCHAR2(100),
medical_exp_date DATE,
process_flag VARCHAR2(1),
error_msg VARCHAR2(4000)
);
Package Specification
--------------------------
CREATE OR REPLACE PACKAGE APPS.xxat_emp_extrainfo_pkg
AS
PROCEDURE xxat_emp_info_upload;
END xxat_emp_extrainfo_pkg;
Package Body
-----------------
-----------------
CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_extrainfo_pkg
AS
PROCEDURE xxat_emp_info_upload
IS
l_infor_type_ins VARCHAR2 (100) := 'EATC_MEDICAL_INSURANCE';
l_infor_type_iq VARCHAR2 (100) := 'SA_IQAMA';
l_person_id NUMBER;
l_error NUMBER;
l_error_msg VARCHAR2 (3000);
l_person_extra_info_id NUMBER;
l_object_version_number NUMBER;
v_emp_num VARCHAR2 (100);
v_iqama NUMBER := 0;
v_cat_id NUMBER;
v_cat NUMBER := 0;
v_medgulf NUMBER := 0;
v_medexp NUMBER := 0;
CURSOR c1
IS
SELECT *
FROM xxat_emp_extra_info
WHERE process_flag = 'N';
BEGIN
DBMS_OUTPUT.put_line (' ****** Process Validation Start ******');
FOR i IN c1
LOOP
l_error := 0;
l_person_id := NULL;
IF i.emp_num IS NULL
OR i.iqama_num IS NULL
OR i.category_name IS NULL
OR i.medgulf_pin IS NULL
OR i.medical_exp_date IS NULL
THEN
l_error_msg :=
'All Fields are mandatory & should not be null';
l_error := 1;
-- p_retcode := 2;
--fnd_file.put_line (fnd_file.LOG,i.relationship_id || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
ELSE
BEGIN
SELECT person_id
INTO l_person_id
FROM per_all_people_f
WHERE employee_number = i.emp_num
-- AND business_group_id = fnd_global.per_business_group_id
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := SQLERRM || ' - ' || SQLCODE;
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
END;
IF I.NATIIONALITY IS NOT NULL THEN
BEGIN
BEGIN
SELECT COUNT (pei_information1)
INTO v_iqama
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SA_IQAMA'
AND pei_information_category = 'SA_IQAMA';
EXCEPTION
WHEN OTHERS
THEN
v_iqama := 0;
END;
IF v_iqama > 0
THEN
l_error_msg :=
'IQAMA Already exists for this employee';
l_error := 1;
-- p_retcode := 2;
-- fnd_file.put_line (fnd_file.LOG,i.emp_num || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf
END IF;
END;
END IF;
BEGIN
BEGIN
SELECT COUNT (pei_information2),
COUNT (pei_information7),
COUNT (pei_information8)
INTO v_cat,
v_medgulf,
v_medexp
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'EATC_MEDICAL_INSURANCE'
AND pei_information_category = 'EATC_MEDICAL_INSURANCE';
EXCEPTION
WHEN OTHERS
THEN
v_cat := 0;
v_medgulf := 0;
v_medexp := 0;
END;
IF v_cat > 0
AND v_medgulf > 0
AND v_medexp > 0
THEN
l_error_msg :=
'Insurance Details already exists for this employee';
l_error := 1;
-- p_retcode := 2;
-- fnd_file.put_line (fnd_file.LOG,i.emp_num || '-' || l_error_msg);
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf
END IF;
END;
BEGIN
SELECT lookup_code
INTO v_cat_id
FROM hr_lookups
WHERE lookup_type = 'EATC_MED_INSURANCE_CATEGORIES'
AND meaning = i.category_name;
EXCEPTION
WHEN OTHERS
THEN
v_cat_id := 0;
END;
IF v_cat_id = 0
THEN
l_error_msg :=
'Category Type Mismatching in EATC_MED_INSURANCE_CATEGORIES Lookup';
l_error := 1;
-- p_retcode := 2;
-- fnd_file.put_line (fnd_file.LOG,
-- i.relationship_id || '-' || l_error_msg
-- );
DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
END IF;
IF l_error = 1
THEN
UPDATE xxat_emp_extra_info
SET process_flag = 'E',
error_msg = l_error_msg
WHERE emp_num = i.emp_num;
ELSIF l_error = 0
THEN
UPDATE xxat_emp_extra_info
SET process_flag = 'Y'
WHERE emp_num = i.emp_num;
IF v_iqama = 0
AND l_infor_type_iq = 'SA_IQAMA' and i.NATIIONALITY is not null
THEN
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => l_person_id,
p_information_type => l_infor_type_iq,
p_pei_information_category => l_infor_type_iq,
p_pei_information1 => i.iqama_num,
p_person_extra_info_id => l_person_extra_info_id,
p_object_version_number => l_object_version_number
);
END IF;
IF v_cat = 0
AND v_medgulf = 0
AND v_medexp = 0
AND l_infor_type_ins = 'EATC_MEDICAL_INSURANCE'
THEN
hr_person_extra_info_api.create_person_extra_info
(p_validate => FALSE,
p_person_id => l_person_id,
p_information_type => l_infor_type_ins,
p_pei_information_category => l_infor_type_ins,
p_pei_information2 => v_cat_id,
p_pei_information7 => i.medgulf_pin,
p_pei_information8 => '2014/10/10 00:00:00',
-- TO_CHAR
-- (i.medical_exp_date,'DD-MON-RRRR'
-- ),
p_person_extra_info_id => l_person_extra_info_id,
p_object_version_number => l_object_version_number
);
END IF;
--fnd_file.put_line (fnd_file.LOG, '****** Process Validation End ******');
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('****** Process Validation End ******');
COMMIT;
END xxat_emp_info_upload;
END xxat_emp_extrainfo_pkg;
/
No comments:
Post a Comment