DECLARE
ln_contact_rel_id PER_CONTACT_RELATIONSHIPS.CONTACT_RELATIONSHIP_ID%TYPE;
ln_ctr_object_ver_num PER_CONTACT_RELATIONSHIPS.OBJECT_VERSION_NUMBER%TYPE;
ln_contact_person PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
ln_object_version_number PER_CONTACT_RELATIONSHIPS.OBJECT_VERSION_NUMBER%TYPE;
ld_per_effective_start_date DATE;
ld_per_effective_end_date DATE;
lc_full_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
ln_per_comment_id PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
lb_name_comb_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
CURSOR c1
IS
/* Formatted on 13-Mar-18 7:17:05 PM (QP5 v5.114.809.3010) */
SELECT EMPLOYEE_ID,
(SELECT DISTINCT PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER = t.EMPLOYEE_ID AND BUSINESS_GROUP_ID = 82)
PERSON_ID,
(SELECT paf.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F paf
WHERE paf.employee_number = t.EMPLOYEE_ID
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date)
effective_date,
CONTACT_FULL_NAME,
LAST_NAME,
FIRST_NAME,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CONTACT'
AND LANGUAGE = 'US'
AND MEANING = RELATIONSHIP_TYPE)
RELATIONSHIP_TYPE,
DATE_OF_BIRTH,
(CASE
WHEN gender = 'Male' THEN 'M'
WHEN gender = 'Female' THEN 'F'
END)
gender,
(CASE
WHEN tittle = 'Mr.' THEN 'MR.'
WHEN tittle = 'Ms.' THEN 'MS.'
END)
title,
(SELECT CONTACT_RELATIONSHIP_ID
FROM PER_CONTACT_RELATIONSHIPS
WHERE BUSINESS_GROUP_ID = 82
AND PERSON_ID =
(SELECT DISTINCT PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER = t.EMPLOYEE_ID
AND BUSINESS_GROUP_ID = 82))
CONTACT_RELATIONSHIP_ID,
PROCESS_FLAG,
PROCESS_MESSAGE
FROM XXHRL_MY_CONTACTS t;
--WHERE NVL (process_flag, 'N') = 'N'
BEGIN
FOR i IN c1
LOOP
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
hr_contact_rel_api.create_contact (
p_start_date => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
p_business_group_id => 82,
p_person_id => i.PERSON_ID,
p_contact_type => i.RELATIONSHIP_TYPE, --'JP_FT',
p_date_start => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
p_last_name => i.LAST_NAME,
p_first_name => i.FIRST_NAME,
p_title => i.title,
p_personal_flag => 'Y',
p_sex => i.gender,
p_date_of_birth => TO_CHAR (i.DATE_OF_BIRTH, 'DD-MON-RRRR'),
-- Output parameters
p_contact_relationship_id => ln_contact_rel_id,
p_ctr_object_version_number => ln_ctr_object_ver_num,
p_per_person_id => ln_contact_person,
p_per_object_version_number => ln_object_version_number,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_name_combination_warning => lb_name_comb_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('l_count: ' || l_count);
UPDATE XXHRL_MY_CONTACTS
SET process_flag = 'Y', PROCESS_MESSAGE = 'Loaded'
WHERE employee_id = i.employee_id;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Exception ' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_MY_CONTACTS
SET process_flag = 'N', PROCESS_MESSAGE = l_error_msg
WHERE employee_id = i.employee_id;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments