Monday, April 14, 2025

How do you Delete Loaded Assigned Payroll Data ?

 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

No comments:

Post a Comment