/* Formatted on 17-Apr-18 4:49:09 PM (QP5 v5.114.809.3010) */
DECLARE
lc_employee_number PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
ln_person_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
ln_object_ver_number PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%
TYPE;
ld_per_effective_start_date PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
ld_per_effective_end_date PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
lc_full_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
ln_per_comment_id PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
ln_assignment_sequence PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%
TYPE;
lc_assignment_number PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
l_assg_id NUMBER;
CURSOR c1
IS
SELECT t.EMPLOYEE_NUMBER,
t.employee_id,
(SELECT DISTINCT PERSON_ID
FROM PER_ALL_PEOPLE_F
WHERE EMPLOYEE_NUMBER = t.EMPLOYEE_NUMBER
AND BUSINESS_GROUP_ID = 81)
PERSON_ID,
(SELECT paf.EFFECTIVE_START_DATE
FROM PER_ALL_PEOPLE_F paf
WHERE paf.employee_number = t.EMPLOYEE_NUMBER
AND BUSINESS_GROUP_ID = 81
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date)
effective_date,
(SELECT object_version_number
FROM per_all_people_f
WHERE employee_number = t.EMPLOYEE_NUMBER
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date
AND business_group_id = 81)
object_version_number,
email_id,
marital_status,
process_flag,
error_message
FROM XXHRL_SG_EMPLOYEE_MASTER t
WHERE EMAIL_ID is not null;
-- update XXHRL_SG_EMPLOYEE_MASTER set PROCESS_FLAG = null, ERROR_MESSAGE = null;
BEGIN
FOR i IN c1
LOOP
ln_object_ver_number := i.object_version_number;
lc_employee_number := i.EMPLOYEE_NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('In Loop');
l_error := 0;
hr_sg_person_api.update_sg_person (
\ -- ------------------------------
p_effective_date => TO_DATE ('01-Apr-2018', 'DD-MON-RRRR'),
p_datetrack_update_mode => 'UPDATE',
p_person_id => i.person_id,
p_email_address => i.new_email,
-- Output variables
-- --------------------------------
p_employee_number => lc_employee_number,
p_object_version_number => ln_object_ver_number,
p_effective_start_date => ld_per_effective_start_date,
p_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_comment_id => ln_per_comment_id,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('Employee loaded # ' || l_count);
UPDATE XXHRL_SG_EMPLOYEE_MASTER
SET process_flag = 'Y', error_message = 'Loaded'
WHERE EMPLOYEE_NUMBER = i.EMPLOYEE_NUMBER;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Api Exception ' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_SG_EMPLOYEE_MASTER
SET process_flag = 'N', error_message = l_error_msg
WHERE EMPLOYEE_NUMBER = i.EMPLOYEE_NUMBER;
END IF;
l_error := 0;
l_error_msg := NULL;
END LOOP;
--COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments