SELECT 'METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner' HEADER, 1 DELETE_ASGPAYROLL
FROM DUAL
UNION ALL
SELECT 'DELETE|AssignedPayroll'
||'|'||
papd.ASSIGNED_PAYROLL_ID
||'|'||
to_char(papd.start_date,'RRRR/MM/DD')
||'|'||
paam.assignment_id
||'|'||
papd.LEGISLATIVE_DATA_GROUP_ID
||'|'||
papd.Payroll_Id
||'|'||
papd.Payroll_Term_Id
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner HEADER, 2 DELETE_ASGPAYROLL
FROM pay_assigned_payrolls_dn papd
,pay_payroll_terms ppt
,pay_pay_relationships_dn prrd
,pay_all_payrolls_f papf_pay
,pay_rel_groups_dn prgd
,pay_rel_groups_f prgf
,per_all_people_f papf
,per_person_names_f ppnf
,per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE papd.payroll_term_id = ppt.payroll_term_id
AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
AND papd.payroll_id = papf_pay.payroll_id
AND prrd.person_id = papf.person_id
AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
AND prgd.relationship_group_id = prgf.relationship_group_id
AND prgd.assignment_id = paam.assignment_id
AND paam.effective_latest_change = 'Y'
AND prgd.group_type = 'A'
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND hikm.surrogate_id = papd.ASSIGNED_PAYROLL_ID
AND papf.person_number = NVL(:P_PERSON_NUMBER,papf.person_number)
ORDER BY 2
FROM DUAL
UNION ALL
SELECT 'DELETE|AssignedPayroll'
||'|'||
papd.ASSIGNED_PAYROLL_ID
||'|'||
to_char(papd.start_date,'RRRR/MM/DD')
||'|'||
paam.assignment_id
||'|'||
papd.LEGISLATIVE_DATA_GROUP_ID
||'|'||
papd.Payroll_Id
||'|'||
papd.Payroll_Term_Id
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner HEADER, 2 DELETE_ASGPAYROLL
FROM pay_assigned_payrolls_dn papd
,pay_payroll_terms ppt
,pay_pay_relationships_dn prrd
,pay_all_payrolls_f papf_pay
,pay_rel_groups_dn prgd
,pay_rel_groups_f prgf
,per_all_people_f papf
,per_person_names_f ppnf
,per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE papd.payroll_term_id = ppt.payroll_term_id
AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
AND papd.payroll_id = papf_pay.payroll_id
AND prrd.person_id = papf.person_id
AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
AND prgd.relationship_group_id = prgf.relationship_group_id
AND prgd.assignment_id = paam.assignment_id
AND paam.effective_latest_change = 'Y'
AND prgd.group_type = 'A'
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND hikm.surrogate_id = papd.ASSIGNED_PAYROLL_ID
AND papf.person_number = NVL(:P_PERSON_NUMBER,papf.person_number)
ORDER BY 2
No comments:
Post a Comment