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_group_name VARCHAR2 (200);
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 (200);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (200);
l_gsp_post_process_warning VARCHAR2 (200);
l_job_id VARCHAR2 (30);
l_position_id VARCHAR2 (30);
l_grade_id VARCHAR2 (30);
l_org_id VARCHAR2 (30);
l_loc_id VARCHAR2 (30);
l_assg_cat VARCHAR2 (100);
l_assg_start_date DATE;
l_pf_flag CHAR (1);
l_segment1 VARCHAR2 (100);
l_segment2 VARCHAR2 (100);
l_segment3 VARCHAR2 (100);
l_person_id NUMBER;
--
v_emp_num VARCHAR2 (50);
l_assign_date DATE;
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);
CURSOR c1
IS
/* Formatted on 13-Mar-18 8:41:02 PM (QP5 v5.114.809.3010) */
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,
ORGANIZATION,
(SELECT organization_id
FROM hr_all_organization_units
WHERE UPPER (NAME) LIKE UPPER (ORGANIZATION)
AND business_group_id = 82)
org_id,
JOB,
(SELECT pj.job_id
FROM per_jobs pj, per_job_definitions pjd
WHERE pj.job_definition_id = pjd.job_definition_id
AND BUSINESS_GROUP_ID = 82
AND pj.NAME = JOB)
job_id,
POSITION,
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'HR_POSITION_NAME'
AND language = 'US'
AND MEANING = POSITION)
POSITION_CODE,
GRADE,
SALARY_BASIS,
PAYROLL_NAME,
(SELECT payroll_id
FROM pay_all_payrolls_f
WHERE 1 = 1 AND UPPER (payroll_name) LIKE UPPER (t.PAYROLL_NAME)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
payroll_id,
PROBATION_PERIOD,
TO_CHAR (TO_DATE (PROBATION_END_DATE, 'mm/dd/yyyy'), 'DD-MON-YYYY')
PROBATION_END_DATE,
SUPERVISOR_LINE_MANAGER_NAME,
SUPERVISOR_EMPLOYEE_NUMBER,
TAX_UNIT_ENTITY_NAME,
ASS_PROCESS_FLAG,
ASS_PROCESS_MESSAGE
FROM XXHRL_MY_ASSIGNMENTS t;
-- WHERE NVL (ASS_PROCESS_MESSAGE, '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_people_group_id := NULL;
l_soft_coding_keyflex_id := NULL;
l_group_name := 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_assg_cat := NULL;
l_job_id := NULL;
l_position_id := NULL;
l_grade_id := NULL;
l_org_id := NULL;
l_assign_date := NULL;
l_sup_hire_date := NULL;
l_emp_hire_date := NULL;
l_soft_coding_keyflex_id := 61;
-- AssignmentID
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_assg_id := NULL;
l_error_msg := 'Assignment error';
l_error := 1;
END;
-- Employee Hire Date Effective 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 hire error';
l_error := 1;
END;
-- Position ID
BEGIN
SELECT pap.position_id
INTO l_position_id
FROM HR_ALL_POSITIONS_F pap, per_position_definitions ppd
WHERE pap.position_definition_id = ppd.position_definition_id
AND BUSINESS_GROUP_ID = 82
AND ORGANIZATION_ID = i.org_id
AND JOB_ID = I.JOB_ID
AND SUBSTR (pap.NAME, 7, LENGTH (NAME)) = i.POSITION;
EXCEPTION
WHEN OTHERS
THEN
-- l_position_id := NULL;
l_error_msg := 'Position error';
l_error := 1;
END;
-- Grade ID
BEGIN
SELECT pg.grade_id
INTO l_grade_id
FROM per_grades pg
WHERE BUSINESS_GROUP_ID = 82 AND name = i.grade;
EXCEPTION
WHEN OTHERS
THEN
l_grade_id := NULL;
END;
-- Location ID
BEGIN
SELECT location_id
INTO l_loc_id
FROM hr_locations_all
WHERE UPPER (location_code) LIKE UPPER ('XXX');
--i.LOCATION);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Location ID error';
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;
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := 'Object Version error';
l_error := 1;
END;
BEGIN
hr_assignment_api.update_emp_asg_criteria (
p_validate => FALSE,
p_effective_date => TO_DATE (i.effective_date, 'DD-MON-RRRR'),
p_datetrack_update_mode => 'CORRECTION',
p_assignment_id => l_assg_id,
p_position_id => l_position_id,
p_job_id => i.job_id,
p_payroll_id => i.payroll_id,
p_grade_id => l_grade_id,
p_location_id => l_loc_id,
p_organization_id => i.org_id,
p_pay_basis_id => l_paybasis_id,
--Out parameters
p_people_group_id => l_people_group_id,
p_object_version_number => l_object_version_number,
p_special_ceiling_step_id => l_special_ceiling_step_id,
p_group_name => l_group_name,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_other_manager_warning => l_other_manager_warning,
p_spp_delete_warning => l_spp_delete_warning,
p_entries_changed_warning => l_entries_changed_warning,
p_tax_district_changed_warning => l_tax_district_changed_warning,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id,
p_concatenated_segments => l_concatenated_segments
);
-- l_count := l_count + 1;
DBMS_OUTPUT.put_line ('l_count: ' || l_count);
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Assignmen Exception ' || SQLERRM;
DBMS_OUTPUT.put_line (l_error_msg);
END;
BEGIN
-- Update Employee Assignment only for probation details
hr_assignment_api.update_emp_asg (
p_effective_date => TO_DATE (i.effective_date, 'DD-MON-RRRR'),
p_datetrack_update_mode => 'CORRECTION', --// 'UPDATE',
p_assignment_id => l_assg_id,
p_change_reason => NULL,
p_manager_flag => 'Y',
-- Output parameters
p_probation_period => i.PROBATION_PERIOD,
p_probation_unit => 'M',
p_date_probation_end => TO_DATE (i.PROBATION_END_DATE, 'DD-MON-RRRR'),
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_MY_ASSIGNMENTS
SET ASS_PROCESS_FLAG = 'Y', ASS_PROCESS_MESSAGE = 'Loaded'
WHERE employee_id = i.employee_id;
EXCEPTION
WHEN OTHERS
THEN
l_error := 1;
l_error_msg := 'Probation API ' || SQLERRM;
DBMS_OUTPUT.put_line (SQLERRM);
END;
IF l_error = 1
THEN
UPDATE XXHRL_MY_ASSIGNMENTS
SET ASS_PROCESS_FLAG = 'N', ASS_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
DBMS_OUTPUT.put_line (SQLERRM);
END;
