This is used to migrate Employee Dependent (Contact) Details.
DECLARE
l_business_group_id per_business_groups.business_group_id%TYPE;
l_contact_rel_id per_contact_relationships.contact_relationship_id%TYPE;
l_ctr_object_ver_num per_contact_relationships.object_version_number%TYPE;
l_contact_person per_all_people_f.person_id%TYPE;
l_object_version_number per_contact_relationships.object_version_number%TYPE;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name per_all_people_f.full_name%TYPE;
l_per_comment_id per_all_people_f.comment_id%TYPE;
l_name_comb_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_contact_exp EXCEPTION;
l_person_id per_all_people_f.person_id%TYPE;
l_contact_type hr_lookups.lookup_code%TYPE;
l_contact_gender hr_lookups.lookup_code%TYPE;
l_contact_titile_cnt NUMBER;
l_effective_date DATE;
l_date date;
l_contact_effective_date DATE;
l_process_status VARCHAR2(1);
l_process_message VARCHAR2(4000);
l_user_id fnd_user.user_id%TYPE;
l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
l_resp_appl_id fnd_responsibility_tl.application_id%TYPE;
CURSOR validate_emp_contacts
IS
SELECT *
FROM xx_emp_contact_stg
WHERE NVL(process_status, 'N') IN ('N', 'E')
AND employee_number NOT IN ('1234567890')
--AND sl_no = 1
;
CURSOR emp_contacts
IS
SELECT *
FROM xx_emp_contact_stg
WHERE process_status IN ('V')
--AND sl_no = 1
-- AND 1=2
;
BEGIN
l_user_id := NULL;
l_resp_id := NULL;
l_resp_appl_id := NULL;
l_business_group_id := NULL;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'XX_USER';
EXCEPTION
WHEN OTHERS THEN
l_user_id := NULL;
END;
BEGIN
SELECT responsibility_id, application_id
INTO l_resp_id, l_resp_appl_id
FROM fnd_responsibility_tl
WHERE responsibility_name = 'Global HRMS Manager'
AND language = USERENV('LANG')
;
EXCEPTION
WHEN OTHERS THEN
l_resp_id := NULL;
l_resp_appl_id := NULL;
END;
-- Initialize apps session
fnd_global.apps_initialize(user_id => l_user_id
,resp_id => l_resp_id
,resp_appl_id => l_resp_appl_id
);
BEGIN
SELECT fnd_profile.value('PER_BUSINESS_GROUP_ID')
INTO l_business_group_id
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_business_group_id := NULL;
END;
IF l_business_group_id IS NOT NULL THEN
FOR rec_validate_emp_contacts IN validate_emp_contacts
LOOP
l_person_id := NULL;
l_contact_type := NULL;
l_contact_gender := NULL;
l_contact_titile_cnt := 0;
l_effective_date := NULL;
l_contact_effective_date := NULL;
IF rec_validate_emp_contacts.employee_number IS NOT NULL THEN
BEGIN
SELECT person_id, original_date_of_hire--, start_date
INTO l_person_id, l_effective_date
FROM per_all_people_f
WHERE TRIM(employee_number) = TRIM(rec_validate_emp_contacts.employee_number)
AND business_group_id = l_business_group_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_person_id := NULL;
l_process_status := 'E';
l_process_message := 'Employee Number '||rec_validate_emp_contacts.employee_number||' does not exists or end dated';
WHEN OTHERS THEN
l_person_id := NULL;
l_process_status := 'E';
l_process_message := 'Error while validating Employee Number '||rec_validate_emp_contacts.employee_number;
END;
ELSE
l_process_status := 'E';
l_process_message := 'Employee Number Cannot be Null.';
END IF;
IF rec_validate_emp_contacts.contact_relationship_type IS NOT NULL THEN
BEGIN
SELECT hl.lookup_code
INTO l_contact_type
FROM hr_lookups hl
WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type))
AND hl.lookup_type = 'CONTACT';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_process_status := 'E';
l_process_message := l_process_message||' Contact Relationship Type '||rec_validate_emp_contacts.contact_relationship_type||' does not exists in Lookup Type CONTACT.';
WHEN OTHERS THEN
l_process_status := 'E';
l_process_message := l_process_message||' Invalid Contact Relationship Type '||rec_validate_emp_contacts.contact_relationship_type||'.';
END;
ELSE
l_process_status := 'E';
l_process_message := 'Contact Relationship Type Cannot be Null.';
END IF;
IF rec_validate_emp_contacts.contact_gender IS NOT NULL THEN
BEGIN
SELECT hl.lookup_code
INTO l_contact_gender
FROM hr_lookups hl
WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_gender))
AND hl.lookup_type = 'SEX';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_process_status := 'E';
l_process_message := l_process_message||' Contact Gender '||rec_validate_emp_contacts.contact_gender||' does not exists in Lookup Type SEX.';
WHEN OTHERS THEN
l_process_status := 'E';
l_process_message := l_process_message||' Invalid Contact Gender '||rec_validate_emp_contacts.contact_gender||'.';
END;
ELSE
l_process_status := 'E';
l_process_message := 'Contact Gender Cannot be Null.';
END IF;
IF rec_validate_emp_contacts.contact_name_title IS NOT NULL THEN
SELECT COUNT(1)
INTO l_contact_titile_cnt
FROM hr_lookups hl
WHERE UPPER(TRIM(hl.meaning))=UPPER(TRIM(rec_validate_emp_contacts.contact_name_title))
AND hl.lookup_type = 'TITLE';
IF l_contact_titile_cnt = 0 THEN
l_process_status := 'E';
l_process_message := l_process_message||' Contact Name Title '||rec_validate_emp_contacts.contact_name_title||' does not exists in Lookup Type TITLE.';
END IF;
ELSE
l_process_status := 'E';
l_process_message := l_process_message||' Contact Name Title Cannot be Null.';
END IF;
IF UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type)) <> 'SPOUSE' THEN
IF l_effective_date > rec_validate_emp_contacts.contact_dob THEN
l_contact_effective_date := l_effective_date;
ELSE
l_contact_effective_date := NULL;
END IF;
END IF;
UPDATE xx_emp_contact_stg
SET business_group_id = l_business_group_id
,person_id = l_person_id
,contact_gender_code = l_contact_gender
,contact_relationship_type_code = l_contact_type
,spouse_effective_date = DECODE(UPPER(TRIM(rec_validate_emp_contacts.contact_relationship_type)), 'SPOUSE', l_effective_date, NULL)
,contact_effective_date = l_contact_effective_date
,process_status = 'V'
,process_message = NULL
WHERE sl_no = rec_validate_emp_contacts.sl_no;
END LOOP;
ELSE
UPDATE xx_emp_contact_stg
SET business_group_id = NULL
,process_status = 'E'
,process_message = 'Error while fetching business_group_id '
WHERE process_status IN ('N','E');
END IF;
FOR load_emp_contacts IN emp_contacts
LOOP
l_contact_rel_id := null;
l_ctr_object_ver_num := null;
l_date := NULL;
dbms_output.put_line('Employee Number: '||load_emp_contacts.employee_number);
dbms_output.put_line('Person ID: '||load_emp_contacts.person_id);
IF UPPER(TRIM(load_emp_contacts.contact_relationship_type)) = 'SPOUSE' THEN
SELECT load_emp_contacts.spouse_effective_date
INTO l_date
FROM dual;
ELSE
SELECT NVL(load_emp_contacts.contact_effective_date, load_emp_contacts.contact_dob)
INTO l_date
FROM dual;
END IF;
-- Create Employee Contact
-- -------------------------------------
hr_contact_rel_api.create_contact
( -- Input data elements
-- -----------------------------
p_validate => FALSE
,p_start_date => /*DECODE(load_emp_contacts.contact_relationship_type, 'SPOUSE', load_emp_contacts.spouse_effective_date
, load_emp_contacts.contact_dob)*/
l_date
,p_business_group_id => load_emp_contacts.business_group_id
,p_person_id => load_emp_contacts.person_id
,p_contact_type => load_emp_contacts.contact_relationship_type_code
,p_date_start => l_date
,p_title => UPPER(TRIM(load_emp_contacts.contact_name_title))-- 'MR.'
,p_last_name => load_emp_contacts.contact_last_name
,p_middle_names => load_emp_contacts.contact_middle_name
,p_first_name => load_emp_contacts.contact_first_name
,p_date_of_birth => load_emp_contacts.contact_dob
,p_sex => load_emp_contacts.contact_gender_code --'M'
,p_primary_contact_flag => load_emp_contacts.contact_primary_contact
,p_personal_flag => load_emp_contacts.contact_personal_relationship --'Y'
,p_beneficiary_flag => load_emp_contacts.contact_beneficiary
,p_dependent_flag => load_emp_contacts.contact_dependent
--,p_effective_date => l_date
-- Output data elements
-- --------------------------------
,p_contact_relationship_id => l_contact_rel_id
,p_ctr_object_version_number => l_ctr_object_ver_num
,p_per_person_id => l_contact_person
,p_per_object_version_number => l_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_per_comment_id => l_per_comment_id
,p_name_combination_warning => l_name_comb_warning
,p_orig_hire_warning => l_orig_hire_warning
);
IF l_contact_rel_id is null then
dbms_output.put_line('Create Contact Relationship failed '||sqlerrm);
UPDATE xx_emp_contact_stg
SET process_status = 'E'
,process_message = 'Create Contact Relationship failed '
WHERE sl_no = load_emp_contacts.sl_no;
--raise l_contact_exp;
ELSE
dbms_output.put_line('Contact Relationship Created');
UPDATE xx_emp_contact_stg
SET contact_relationship_id = l_contact_rel_id
,ctr_object_version_number = l_ctr_object_ver_num
,per_person_id = l_contact_person
,per_object_version_number = l_object_version_number
,per_effective_start_date = l_per_effective_start_date
,per_effective_end_date = l_per_effective_end_date
,full_name = l_full_name
,per_comment_id = l_per_comment_id
--,name_combination_warning = l_name_comb_warning
--,orig_hire_warning = l_orig_hire_warning
,process_status = 'S'
,process_message = 'Success'
WHERE sl_no = load_emp_contacts.sl_no;
END IF;
END LOOP;
--COMMIT;
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
dbms_output.put_line('Inside Exception: '||SQLERRM);
END;
/
