/* Formatted on 10/13/2020 12:25:24 PM (QP5 v5.277) */
DECLARE
l_datetrack_update_mod VARCHAR2 (100);
l_assignment_id NUMBER;
l_grade_id NUMBER := NULL;
l_position_id NUMBER;
l_job_id NUMBER;
l_payroll_id NUMBER := NULL;
l_location_id NUMBER;
l_organization_id NUMBER;
l_pay_basis_id NUMBER := NULL;
l_segment1 VARCHAR2 (100) := HR_API.G_VARCHAR2;
l_segment2 VARCHAR2 (100) := hr_api.G_VARCHAR2;
l_segment3 VARCHAR2 (100) := hr_api.G_VARCHAR2;
l_old_obj_ver_number NUMBER;
l_special_ceiling_step_id NUMBER := NULL;
l_people_group_id NUMBER := hr_api.g_number;
l_soft_coding_keyflex_id NUMBER := hr_api.g_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_spp_delete_warning BOOLEAN;
l_entries_changed VARCHAR2 (100);
l_tax_district_changed_warning BOOLEAN;
l_concatenated_segments VARCHAR2 (100);
l_gsp_post_process_warning VARCHAR2 (100);
l_cagr_grade_def_id NUMBER := hr_api.g_number; -- bug 2359997
l_cagr_concatenated_segments VARCHAR2 (30);
l_comment_id NUMBER := hr_api.g_number;
l_no_managers_warning BOOLEAN;
l_ass_attribute9 VARCHAR2(500);
l_hourly_salaried_warning BOOLEAN;
p_segments APPS.FND_FLEX_EXT.SEGMENTARRAY;
n_segment1 VARCHAR (100) := NULL;
n_segment2 VARCHAR2 (100) := NULL;
n_segment3 VARCHAR2 (100) := NULL;
n_segment4 VARCHAR2 (100) := NULL;
n_segment5 VARCHAR2 (100) := NULL;
n_segment6 VARCHAR2 (100) := NULL;
n_segment7 VARCHAR2 (100) := NULL;
p_code_combination_id NUMBER;
l_code_combination_id NUMBER;
l_object_version_number NUMBER := hr_api.g_number;
l_supervisor_assignment_id NUMBER ;
l_effective_date DATE := HR_API.G_DATE;
l_mode VARCHAR2 (100);
z_segment3 VARCHAR2 (100);
l_employee_number NUMBER;
-- l_batch_run_number hr_api_batch_message_lines.batch_run_number%TYPE;
-- l_dummy_line_id hr_api_batch_message_lines.line_id%TYPE;
CURSOR l_data
IS
SELECT xpa.assignment_id,
xpc.new_assignment_id new_assignment_id,
(SELECT effective_start_date
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = xpc.new_assignment_id and effective_end_date>SYSDATE)
effective_start_date2,
xpa.eFFECTIVE_START_DATE,
location_id,
location_code,
job_id,
position_id,
employee_category,
NVL (ASS_ATTRIBUTE6,'No') ASS_ATTRIBUTE6,
NVL (ASS_ATTRIBUTE7, '0') ASS_ATTRIBUTE7,
ASS_ATTRIBUTE8,
ASS_ATTRIBUTE9,
ASS_ATTRIBUTE10,
ASS_ATTRIBUTE11,
ASS_ATTRIBUTE12,
ASS_ATTRIBUTE13,
(SELECT object_version_number
FROM per_all_assignments_f
WHERE assignment_id = xpc.new_assignment_id and effective_end_date>SYSDATE )
object_version_number2,
(SELECT papf.person_id
FROM per_all_people_f papf
WHERE papf.effective_end_date > SYSDATE
AND papf.person_id= (select new_person_id from xx.XX_PERSON_CREATE_TMP xpct where xpct.person_id = xpa.supervisor_id)) new_supervisor_id,
(SELECT papf.start_date
FROM per_all_people_f papf
WHERE papf.effective_end_date > SYSDATE
AND papf.person_id= (select new_person_id from xx.XX_PERSON_CREATE_TMP xpct where xpct.person_id = xpa.supervisor_id)) new_supervisor_start_date,
supervisor_id supervisor_id_old,
(SELECT papf.assignment_id
FROM per_all_assignments_f papf
WHERE papf.effective_end_date > SYSDATE
AND papf.person_id =
(SELECT new_person_id
FROM xx.XX_PERSON_CREATE_TMP xpct
WHERE xpct.person_id = xpa.supervisor_id)) supervisor_assgn_id,
xpc.person_id,
xpc.new_person_id,
(SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id = xpc.new_assignment_id and effective_end_date>SYSDATE)
assignment_number,
employee_number,
DEFAULT_CODE_COMB_ID,
full_name,
segment1,
segment2,
segment3,
DECODE(segment4,'00000000','0') segment4,
segment5,
segment6,
segment7,
xpa.status
FROM XX.XX_PERSON_ASSIGNMENT_TMP xpa,
xx.XX_PERSON_CREATE_TMP xpc
WHERE xpa.person_id = xpc.person_id ;
BEGIN
fnd_global.apps_initialize(
user_id=>1110,
resp_id=>20536,
resp_appl_id=>800);
FOR n_data IN l_data
LOOP
l_object_version_number := n_data.object_version_number2;--
-- DBMS_OUTPUT.PUT_LINE (n_data.effective_start_date);
--l_effective_date := n_data.effective_start_date;
z_segment3:= xxntc_get_cc_segment3(n_data.employee_number);
DBMS_OUTPUT.PUT_LINE('z_segment3= ' || z_segment3);
p_segments (1) := n_data.segment1;
p_segments (2) := n_data.segment2;
p_segments (3) := NVL(z_segment3,n_data.segment3);
p_segments (4) := n_data.segment4;
p_segments (5) := n_data.segment5;
p_segments (6) := n_data.segment6;
p_segments (7) := n_data.segment7;
p_segments (8) := '00000';
p_segments (9) := '00000';
p_segments (10) := '00000';
p_segments (11) := '0';
p_segments (12) := '0';
p_code_combination_id :=
xxntc_get_ccid (50388,
SYSDATE,
12,
p_segments);
dbms_output.put_line(p_code_combination_id || ' - ' || fnd_flex_ext.get_message);
DBMS_OUTPUT.put_line ('ccid : '||p_code_combination_id || ' assignment_id = ' || n_data.assignment_id || ' supervisor_assgn_id= ' || n_data.supervisor_assgn_id);
--
l_mode := 'CORRECTION';
l_effective_date := n_data.effective_start_date2;
if(n_data.new_supervisor_start_date > n_data.effective_start_date2 ) then
l_effective_date := n_data.new_supervisor_start_date;
l_mode := 'UPDATE';
dbms_output.put_line('--------- '||to_char(n_data.person_id));
end if;
IF n_data.ass_attribute9 is null THEN
l_ass_attribute9 :=NULL;
ELSE
l_ass_attribute9 :=z_segment3;
END IF;
BEGIN
hr_assignment_api.update_emp_asg (
p_validate => FALSE,
p_effective_date => l_effective_date,--n_data.effective_start_date2, -- TO_DATE (SYSDATE, 'DD-MM-YYYY'), --sel.effective_start_date
p_datetrack_update_mode => l_mode,--'CORRECTION', -- superviser fix @28.07.2018
p_assignment_id => n_data.new_assignment_id,
p_object_version_number => l_object_version_number, --n_data.,
p_default_code_comb_id => p_code_combination_id,
p_assignment_number => n_data.assignment_number, --,p_change_reason => sel.CHANGE_REASON
p_cagr_grade_def_id => l_cagr_grade_def_id, -- bug 2359997
p_cagr_concatenated_segments => l_cagr_concatenated_segments,
p_concatenated_segments => l_concatenated_segments,
p_soft_coding_keyflex_id => l_soft_coding_keyflex_id, -- bug 2359997
p_comment_id => l_comment_id, --,p_employee_category => sel.employee_category
p_effective_start_date => n_data.effective_start_date,
p_effective_end_date => l_effective_end_date, --,p_effective_start_date => SEL.BASLANGIC
p_no_managers_warning => l_no_managers_warning,
p_other_manager_warning => l_other_manager_warning,
p_set_of_books_id => 2021,
p_supervisor_id => n_data.new_supervisor_id, -- new added
p_supervisor_assignment_id => n_data.supervisor_assgn_id, -- new added
p_employee_category => n_data.EMPLOYEE_CATEGORY, -- new added
p_ass_attribute6 => n_data.ASS_ATTRIBUTE6, -- new added
p_ass_attribute7 => n_data.ASS_ATTRIBUTE7, -- new added
p_ass_attribute8 => n_data.ass_attribute8 ,-- P_ASS_ATTRIBUTE10 => L_UNV
p_ass_attribute9 => l_ass_attribute9,--ASS_ATTRIBUTE9
p_ass_attribute10 => n_data.ass_attribute10,
p_ass_attribute11 => n_data.ass_attribute11,
p_ass_attribute12 => n_data.ass_attribute12,
p_ass_attribute13 => n_data.ass_attribute13 -- => L_UNV
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('1');
DBMS_OUTPUT.put_line (SQLERRM);
END;
COMMIT;
BEGIN
hr_assignment_api.update_emp_asg_criteria (
p_effective_date => l_effective_date,--n_data.effective_start_date2, --TO_DATE (SYSDATE,'DD-MM-YYYY'),
p_datetrack_update_mode => l_mode,--'CORRECTION',
p_assignment_id => n_data.new_assignment_id,
p_validate => FALSE,
p_called_from_mass_update => TRUE,
p_grade_id => l_grade_id,
p_position_id => n_data.new_position_id,
p_job_id => n_data.new_job_id,
p_payroll_id => l_payroll_id,
p_location_id => n_data.new_location_id,
p_organization_id => 0,
p_pay_basis_id => l_pay_basis_id,
p_segment1 => l_segment1,
p_segment2 => l_segment2,
p_segment3 => l_segment3,
p_concat_segments => NULL,
p_grade_ladder_pgm_id => NULL,
p_object_version_number => l_object_version_number,
p_special_ceiling_step_id => l_special_ceiling_step_id, --
p_people_group_id => l_people_group_id, --
--p_soft_coding_keyflex_id => l_soft_coding_keyflex_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,
p_tax_district_changed_warning => l_tax_district_changed_warning
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('2');
DBMS_OUTPUT.put_line (SQLERRM);
END;
l_people_group_id := NULL;
l_old_obj_ver_number := NULL;
l_special_ceiling_step_id := NULL;
l_soft_coding_keyflex_id := NULL;
UPDATE XX.XX_PERSON_ASSIGNMENT_TMP
SET status = 'UPDATE'
WHERE assignment_id = n_data.assignment_id;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
No comments:
Post a Comment