HDL – Query to DELETE Assigned Payroll data from Fusion

In case, you need to delete assigned payroll data for masking purpose in lower environment or for some other reason like correction etc, use below query to pull the data in HDL format:

SELECT 'METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner' HEADER, 1 DATA_ORDER
  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 DATA_ORDER
  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 trunc(papd.creation_date) < trunc(sysdate)-1
   --AND papf.person_number = '11111'
 ORDER BY 2

Sample HDL File:

METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner
DELETE|AssignedPayroll|300000012343876|2022/01/19|300000046430123|300000004188123|300000014605111|300000046430342|ASGN_PAYROLL_12345|EBS-HR