Oracle Apps Technical: Oracle Apps HRMS - Employee Contacts/Relationships Extra Information

Oracle Apps HRMS - Employee Contacts/Relationships Extra Information

------------------------------------------------------------------------------------

Flat File Format
----------------

EMP_NUM	GENDER	RELATION	FULL_NAME	CATEGORY_NAME	MEDGULF_PIN	MEDICAL_EXP_DATE	PROCESS_FLAG	ERROR_MSG
3	F	S	xxxxxx	CLASS VIP1	12345	10-Oct-14	N

Staging Table Format
----------------------

CREATE TABLE xxat_empdep_extra_info
(emp_num NUMBER,
gender VARCHAR2(1),
relation VARCHAR2(1),
full_name VARCHAR2(1000),
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_depend_extrainfo_pkg
AS

--Employee contact extra information
----------------------------------------------
PROCEDURE xxat_emp_depend_extra_info ;

--Employee contact Creation
----------------------------------------------

PROCEDURE xxat_emp_contact_creation ;

END xxat_emp_depend_extrainfo_pkg;
/

Package Body
----------------

CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_depend_extrainfo_pkg
AS

--Employee contact extra information
----------------------------------------------
PROCEDURE xxat_emp_depend_extra_info
IS
l_infor_type_ins VARCHAR2 (100)
:= 'EATC_MED_INSURANCE_DETAILS';
l_infor_type_iq VARCHAR2 (100) := 'EATC_IQAMA';
l_person_id NUMBER;
l_error_msg VARCHAR2 (3000);
v_emp_num VARCHAR2 (100);
v_iqama NUMBER := 0;
v_cat_id NUMBER;
l_error NUMBER;
v_contact_extra_info_id NUMBER;
v_object_version_number NUMBER;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_cat NUMBER := 0;
v_medgulf NUMBER := 0;
v_medexp NUMBER := 0;
v_crel_id NUMBER;
v_per_id NUMBER;
v_datetrack_delete_mode VARCHAR2 (2000) := 'UPDATE';

-- v_contype varchar2(1);
-- v_sex varchar2(1);
-- v_emp_id number;
CURSOR c1
IS
SELECT *
FROM xxat_empdep_extra_info
WHERE process_flag = 'N'
ORDER BY 1;
BEGIN
-- fnd_global.apps_initialize (fnd_global.user_id,
-- fnd_global.resp_id,
-- fnd_global.resp_appl_id
-- );
-- fnd_file.put_line (fnd_file.LOG, ' ****** Process Validation Start ******');
DBMS_OUTPUT.put_line (' ****** Process Validation Start ******');

FOR i IN c1
LOOP
l_error := 0;
v_crel_id := 0;
v_per_id := 0;
v_cat := 0;
v_medgulf := 0;
v_medexp := 0;

--v_contype := null;
--v_sex := null;
--v_emp_id := null;
IF i.emp_num IS NULL
OR i.gender IS NULL
OR i.relation IS NULL
OR i.full_name 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 COUNT (cei_information1)
INTO v_iqama
FROM per_contact_extra_info_f
WHERE contact_relationship_id = i.relationship_id
AND information_type = 'EATC_IQAMA'
AND cei_information_category = 'EATC_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;
DBMS_OUTPUT.put_line (i.relship_person_id || '-' || l_error_msg);
END IF;
*/
BEGIN
SELECT COUNT (person_id)
INTO v_per_id
FROM per_all_people_f
WHERE employee_number = i.emp_num
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_per_id := 0;
END;

IF v_per_id > 0
THEN
BEGIN
SELECT a.contact_relationship_id --,
-- a.contact_type,
-- b.sex,
-- c.person_id
INTO v_crel_id --,
--v_contype,
--v_sex,
--v_emp_id
FROM per_contact_relationships a,
per_all_people_f b,
per_all_people_f c
WHERE 1 = 1
AND REPLACE (TRIM (UPPER (b.full_name)), ' ', ' ') =
REPLACE (TRIM (UPPER (i.full_name)), ' ', ' ')
AND a.contact_person_id = b.person_id
AND TRUNC (SYSDATE) BETWEEN b.effective_start_date
AND b.effective_end_date
AND a.person_id = c.person_id
AND c.employee_number = i.emp_num
AND TRUNC (SYSDATE) BETWEEN c.effective_start_date
AND c.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg :=

-- i.emp_num
-- || '-'
-- ||
' Relationship not found/defined';
-- || INITCAP (i.full_name);
l_error := 1;
v_crel_id := 0;
DBMS_OUTPUT.put_line (l_error_msg);
--DBMS_OUTPUT.put_line (i.emp_num || '-' || l_error_msg);
END;

IF v_crel_id > 0
THEN
--AND v_contype IS NOT NULL v_sex IS NOT NULL AND v_emp_id IS NOT NULL
BEGIN
BEGIN
SELECT COUNT (cei_information2),
COUNT (cei_information7),
COUNT (cei_information8)
INTO v_cat,
v_medgulf,
v_medexp
FROM per_contact_extra_info_f
WHERE contact_relationship_id = v_crel_id
AND information_type = 'EATC_MED_INSURANCE_DETAILS'
AND cei_information_category =
'EATC_MED_INSURANCE_DETAILS';
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
/* hr_contact_extra_info_api.delete_contact_extra_info
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_datetrack_delete_mode => v_datetrack_delete_mode,
p_contact_extra_info_id => v_crel_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
);

v_cat := 0;
v_medgulf := 0;
v_medexp := 0;
*/
l_error_msg :=
'Insurance Details already exists';
-- || INITCAP (i.full_name);
l_error := 1;
DBMS_OUTPUT.put_line
( -- INITCAP (i.full_name) || '-' ||
l_error_msg);
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 with 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 (v_crel_id || '-' || l_error_msg);
-- p_errbuf := l_error_msg;
END IF;
END IF;
ELSE
l_error_msg :=

--i.emp_num || ' - ' ||
'Employee & Dependents Not Defined';
l_error := 1;
END IF;

IF l_error = 1
THEN
DBMS_OUTPUT.put_line (v_crel_id || '-' || l_error);

UPDATE xxat_empdep_extra_info
SET process_flag = 'E',
error_msg = l_error_msg
WHERE UPPER (full_name) = UPPER (i.full_name);
ELSIF l_error = 0
THEN
DBMS_OUTPUT.put_line (v_crel_id || '-' || l_error);

UPDATE xxat_empdep_extra_info
SET process_flag = 'Y'
WHERE UPPER (full_name) = UPPER (i.full_name);

/*

IF v_iqama = 0
AND l_infor_type_iq = 'EATC_IQAMA'
THEN
hr_contact_extra_info_api.create_contact_extra_info
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_contact_relationship_id => i.relationship_id,
p_information_type => l_infor_type_iq,
p_cei_information_category => l_infor_type_iq,
p_cei_information1 => i.iqama_num,
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
);
END IF;

*/
IF v_cat = 0
AND v_medgulf = 0
AND v_medexp = 0
AND l_infor_type_ins = 'EATC_MED_INSURANCE_DETAILS'
THEN
hr_contact_extra_info_api.create_contact_extra_info
(p_validate => FALSE,
p_effective_date => TRUNC (SYSDATE),
p_contact_relationship_id => v_crel_id,
p_information_type => l_infor_type_ins,
p_cei_information_category => l_infor_type_ins,
p_cei_information2 => v_cat_id,
p_cei_information7 => i.medgulf_pin,
p_cei_information8 => '2014/10/10 00:00:00',
-- TO_CHAR
-- (i.medical_exp_date,'DD-MON-RRRR'
-- ),
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
);
END IF;
END IF;
END IF;
END LOOP;

--fnd_file.put_line (fnd_file.LOG, '****** Process Validation End ******');
DBMS_OUTPUT.put_line ('****** Process Validation End ******');
END xxat_emp_depend_extra_info;

--Employee contact Creation
----------------------------------------------

PROCEDURE xxat_emp_contact_creation
IS
v_contact_relationship_id NUMBER;
v_ctr_object_version_number NUMBER;
v_per_person_id NUMBER;
v_per_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_full_name VARCHAR2 (1000);
v_per_comment_id NUMBER;
v_name_combination_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_per_id NUMBER;
l_error_msg VARCHAR2 (3000);
l_error NUMBER;
v_title VARCHAR2 (100);

CURSOR c1
IS
SELECT *
FROM xxat_empdep_create
WHERE process_flag = 'N'
ORDER BY 1;
BEGIN
FOR i IN c1
LOOP
l_error := 0;

IF i.emp_num IS NULL
OR i.gender IS NULL
OR i.relation IS NULL
OR i.first_name IS NULL
OR i.last_name IS NULL
OR i.father_name IS NULL
OR i.full_name IS NULL
OR i.dob IS NULL
THEN
l_error_msg :=
'All Fields are mandatory & should not be null';
l_error := 1;
ELSE
BEGIN
SELECT person_id
INTO v_per_id
FROM per_all_people_f
WHERE employee_number = i.emp_num
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
v_per_id := 0;
l_error_msg := 'Employee Not Defined';
l_error := 1;
END;

IF i.gender = 'M'
AND i.relation = 'C'
THEN
v_title := 'MR.';
ELSIF i.gender = 'F'
AND i.relation = 'C'
THEN
v_title := 'MISS';
ELSIF i.gender = 'F'
AND i.relation = 'S'
THEN
v_title := 'MRS.';
END IF;

IF l_error = 0
THEN
BEGIN
hr_contact_rel_api.create_contact
(p_validate => FALSE,
p_start_date => TRUNC (SYSDATE),
p_business_group_id => 81,
p_person_id => v_per_id,
p_contact_person_id => NULL,
p_contact_type => i.relation,
p_date_start => TO_DATE
(TO_CHAR
(i.dob,
'RRRR/MM/DD HH24:MI:SS'
),
'RRRR/MM/DD HH24:MI:SS'
),
p_personal_flag => 'Y',
p_last_name => INITCAP (i.last_name),
p_sex => i.gender,
p_date_of_birth => TO_DATE
(TO_CHAR
(i.dob,
'RRRR/MM/DD HH24:MI:SS'
),
'RRRR/MM/DD HH24:MI:SS'
),
p_first_name => INITCAP
(i.first_name),
p_title => v_title,
p_per_information_category => 'SA',
p_per_information1 => INITCAP
(i.father_name),
p_contact_relationship_id => v_contact_relationship_id,
p_ctr_object_version_number => v_ctr_object_version_number,
p_per_person_id => v_per_person_id,
p_per_object_version_number => v_per_object_version_number,
p_per_effective_start_date => v_per_effective_start_date,
p_per_effective_end_date => v_per_effective_end_date,
p_full_name => v_full_name,
p_per_comment_id => v_per_comment_id,
p_name_combination_warning => v_name_combination_warning,
p_orig_hire_warning => v_orig_hire_warning
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
l_error_msg :=
'Exception in API ' || SQLERRM;
l_error := 1;
END;
END IF;

IF l_error = 1
THEN
UPDATE xxat_empdep_create
SET process_flag = 'E',
error_msg = l_error_msg,
rel_id = v_contact_relationship_id
WHERE UPPER (full_name) = UPPER (i.full_name);
ELSIF l_error = 0
THEN
UPDATE xxat_empdep_create
SET process_flag = 'Y',
rel_id = v_contact_relationship_id
WHERE UPPER (full_name) = UPPER (i.full_name);
END IF;

COMMIT;
END IF;
END LOOP;
END xxat_emp_contact_creation;
END xxat_emp_depend_extrainfo_pkg;
/

Oracle Apps Technical

Thursday 13 March 2014

Oracle Apps HRMS - Employee Contacts/Relationships Extra Information - Contact/Relationship Creation

No comments:

Post a Comment