DECLARE
l_effective_date DATE := SYSDATE;
l_assignment_id NUMBER;
l_obj NUMBER;
l_assignment_number VARCHAR2 (40);
l_supervisor_id NUMBER;
lo_soft_coding_keyflex_id NUMBER;
o_concatenated_segments VARCHAR2 (240);
o_comment_id NUMBER;
o_effective_start_date DATE;
o_effective_end_date DATE;
o_no_managers_warning BOOLEAN;
o_other_manager_warning BOOLEAN;
CURSOR CUR
IS
SELECT distinct paaf.assignment_number l_assignment_number, paaf.object_version_number l_obj,assignment_id l_assignment_id, --, papf1.person_id,
(SELECT person_id FROM per_all_people_f WHERE employee_number = ges.supervisor_num
AND TRUNC(SYSDATE) BETWEEN TRUNC(effective_start_date) AND TRUNC(effective_end_date)) l_supervisor_id
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
per_all_people_f papf1,
jg_emp_stg_tbl ges
WHERE 1=1
AND papf1.employee_number = ges.employee_number
AND ges.employee_number <> '555'
AND TRUNC(SYSDATE) BETWEEN TRUNC(paaf.effective_start_date)
AND TRUNC(paaf.effective_end_date)
AND UPPER(past.user_status) = 'ACTIVE ASSIGNMENT'
AND papf1.person_id = paaf.person_id
AND ges.CREATION_DATE is not null
AND past.assignment_status_type_id = paaf.assignment_status_type_id;
BEGIN
FOR C1 IN CUR
LOOP
DBMS_OUTPUT.PUT_LINE(C1.l_assignment_number || 'Employee Supervisor is :'||C1.l_supervisor_id);
hr_assignment_api.update_emp_asg
(p_effective_date => l_effective_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => C1.i_assignment_id --i_assignment_id
,p_assignment_number => C1.l_assignment_number
,p_object_version_number => C1.l_obj
,p_supervisor_id => C1.l_supervisor_id
-- OUT Variables
,p_soft_coding_keyflex_id => lo_soft_coding_keyflex_id
,p_concatenated_segments => o_concatenated_segments
,p_comment_id => o_comment_id
,p_effective_start_date => o_effective_start_date
,p_effective_end_date => o_effective_end_date
,p_no_managers_warning => o_no_managers_warning
,p_other_manager_warning => o_other_manager_warning
);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee Supervisor is updated Successful !!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error updating Employee Supervisor : '||SQLERRM);
END;
/