Oracle Apps Technical

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 HRMS API – Insert/Create Employee Extra Information
-----------------------------------------------------------
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

PROCEDURE xxat_emp_info_upload;

END xxat_emp_extrainfo_pkg;

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

CREATE OR REPLACE PACKAGE BODY APPS.xxat_emp_extrainfo_pkg

PROCEDURE xxat_emp_info_upload

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

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

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

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 LOOP;

DBMS_OUTPUT.put_line ('****** Process Validation End ******');

COMMIT;

END xxat_emp_info_upload;

END xxat_emp_extrainfo_pkg;

Oracle Apps Technical

Thursday, 13 March 2014

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

Oracle HRMS API – Insert/Create Employee Extra Information