/* Formatted on 13-Mar-18 4:51:27 PM (QP5 v5.114.809.3010) */
DECLARE
l_object_version_number NUMBER;
l_special_ceiling_step_id NUMBER;
l_people_group_id NUMBER;
l_soft_coding_keyflex_id NUMBER;
l_supervisor_id NUMBER;
l_assg_id NUMBER;
l_sup_assg_id NUMBER;
l_group_name VARCHAR2 (100);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_org_now_no_manager_warning BOOLEAN;
l_other_manager_warning BOOLEAN;
l_no_managers_warning BOOLEAN;
l_spp_delete_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (100);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (100);
l_gsp_post_process_warning VARCHAR2 (100);
l_job_id VARCHAR2 (20);
l_position_id VARCHAR2 (20);
l_grade_id VARCHAR2 (20);
l_org_id VARCHAR2 (20);
l_loc_id VARCHAR2 (20);
l_assg_cat VARCHAR2 (50);
l_assg_start_date DATE;
l_pf_flag CHAR (1);
l_segment1 VARCHAR2 (50);
l_segment2 VARCHAR2 (50);
l_segment3 VARCHAR2 (50);
l_person_id NUMBER;
--
v_emp_num VARCHAR2 (100);
l_assign_date VARCHAR2 (100);
l_sup_hire_date DATE;
l_emp_hire_date DATE;
l_payroll_id NUMBER;
l_paybasis_id NUMBER;
l_comment_id NUMBER;
l_count NUMBER := 0;
l_error NUMBER;
l_error_msg VARCHAR2 (4000);
l_update_mode varchar2 (30);
CURSOR c1
IS
SELECT EMPLOYEE_ID,
HIRE_DATE,
(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,
SUPERVISOR_LINE_MANAGER_NAME,
SUPERVISOR_EMPLOYEE_NUMBER,
SUP_ERROR_MESSAGE,
sup_process_flag
FROM XXHRL_MY_ASSIGNMENTS t
WHERE SUPERVISOR_EMPLOYEE_NUMBER IS NOT NULL
AND NVL (sup_process_flag, 'N') = 'N';
BEGIN
FOR i IN c1
LOOP
DBMS_OUTPUT.put_line ('In Loop');
l_object_version_number := NULL;
l_special_ceiling_step_id := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
l_org_now_no_manager_warning := FALSE;
l_other_manager_warning := FALSE;
l_spp_delete_warning := FALSE;
l_entries_changed_warning := NULL;
l_tax_district_changed_warning := FALSE;
l_concatenated_segments := NULL;
l_gsp_post_process_warning := NULL;
l_assign_date := NULL;
l_sup_hire_date := NULL;
l_emp_hire_date := NULL;
l_soft_coding_keyflex_id := 61;
--Employee Assignment ID
BEGIN
SELECT paf.assignment_id
INTO l_assg_id
FROM per_assignments_f paf
WHERE paf.assignment_number = i.employee_id
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Assignment not found';
l_error := 1;
END;
-- Suppervisor Assignment ID
BEGIN
SELECT paf.assignment_id
INTO l_sup_assg_id
FROM per_assignments_f paf
WHERE paf.assignment_number = i.SUPERVISOR_EMPLOYEE_NUMBER
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
-- l_assg_id := NULL;
l_error_msg := 'Suppervisor Assignment ID not exist';
l_error := 1;
END;
-- Employee Hire Date / Effective Start Date
BEGIN
SELECT paf.start_date
INTO l_emp_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.employee_id
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT TO_DATE (TO_CHAR (l_assign_date, 'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS')
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Employee start date not found';
l_error := 1;
END;
-- Supervisor Hire Date / Effective Start Date
BEGIN
SELECT paf.start_date
INTO l_sup_hire_date
FROM per_all_people_f paf
WHERE paf.employee_number = i.SUPERVISOR_EMPLOYEE_NUMBER
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT TO_DATE (TO_CHAR (l_assign_date, 'RRRR/MM/DD HH24:MI:SS'),
'RRRR/MM/DD HH24:MI:SS')
INTO l_assign_date
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Employee start date not found';
l_error := 1;
END;
IF l_emp_hire_date < l_sup_hire_date
THEN
l_assign_date := l_sup_hire_date;
l_update_mode := 'UPDATE';
ELSE
l_assign_date := l_emp_hire_date;
l_update_mode := 'CORRECTION';
END IF;
--l_update_mode := 'CORRECTION';
DBMS_OUTPUT.put_line( 'emp date'
|| l_emp_hire_date
|| 'super date'
|| l_sup_hire_date
|| 'final'
|| l_assign_date);
-- Supervisor ID
BEGIN
SELECT person_id
INTO l_supervisor_id
FROM per_all_people_f ppf
WHERE employee_number = i.SUPERVISOR_EMPLOYEE_NUMBER
AND BUSINESS_GROUP_ID = 82
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Supervisor ID not found';
l_error := 1;
END;
-- Object Version Number
BEGIN
SELECT paaf.object_version_number
INTO l_object_version_number
FROM per_all_assignments_f paaf
WHERE SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.assignment_id = l_assg_id;
-- DBMS_OUTPUT.put_line ( 'object version no.'
-- || l_object_version_number
-- );
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Object Version not found';
l_error := 1;
END;
-- dbms_output.put_line(TO_DATE (l_assign_date,'DD-MON-RRRR' ));
BEGIN
hr_assignment_api.update_emp_asg (
p_effective_date => TO_DATE (l_assign_date, 'DD-MON-RRRR'),
p_datetrack_update_mode => l_update_mode,
p_assignment_id => l_assg_id,
p_supervisor_id => l_supervisor_id,
p_change_reason => NULL,
p_manager_flag => 'Y',
p_supervisor_assignment_id => l_sup_assg_id,
p_object_version_number => l_object_version_number,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments,
p_comment_id => l_comment_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning
);
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('l_count: ' || l_count);
UPDATE XXHRL_ASSIGNMENTS
SET sup_PROCESS_FLAG = 'Y', sup_error_message = 'Loaded'
WHERE employee_id = i.employee_id;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Error In Supervisors API ' || SQLERRM;
END;
IF l_error = 1
THEN
UPDATE XXHRL_ASSIGNMENTS
SET sup_PROCESS_FLAG = 'N', sup_error_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
DBMS_OUTPUT.put_line (SQLERRM);
END;
0 Comments