/* Formatted on 08-Mar-18 6:02:18 PM (QP5 v5.114.809.3010) */
DECLARE
ln_phone_id PER_PHONES.PHONE_ID%TYPE;
ln_object_version_number PER_PHONES.OBJECT_VERSION_NUMBER%TYPE;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
CURSOR c1
IS
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,
PHONE_TYPE,
PHONE_NUMBER,
PROCESS_FLAG,
PROCESS_MESSAGE
FROM XXHRL_MY_PHONES t
WHERE NVL (process_flag, 'N') = 'N';
BEGIN
FOR i IN c1
LOOP
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
hr_phone_api.create_phone (
p_date_from => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
p_phone_type => 'M',
p_phone_number => TO_CHAR (i.PHONE_NUMBER),
p_parent_id => i.PERSON_ID,
p_parent_table => 'PER_ALL_PEOPLE_F',
p_effective_date => TO_CHAR (i.effective_date, 'DD-MON-RRRR'),
-- Output parameters
p_phone_id => ln_phone_id,
p_object_version_number => ln_object_version_number
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('l_count: '|| l_count) ;
DBMS_OUTPUT.put_line ('Phone ID: '|| ln_phone_id) ;
UPDATE XXHRL_MY_PHONES
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_PHONES
SET process_flag = 'N', PROCESS_MESSAGE = l_error_msg
--SUBSTR (l_error_msg, 3999)
WHERE employee_id = i.employee_id;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments