In this article we will look into how to load/delete the AssignedPayroll information.
We need the associate employees to Payroll definitions using AssignedPayroll in order to process the payroll for the employees. Sometimes, we will have to modify the payroll name for the employees and for achieving it, we will have to delete the existing AssignedPayroll and add the new AssignedPayroll information.
Table of Contents
Loading AssignedPayroll using HDL:
METADATA|AssignedPayroll|SourceSystemOwner|SourceSystemId|EffectiveStartDate|AssignmentNumber|PayrollDefinitionCode|LegislativeDataGroupName|StartDate
MERGE|AssignedPayroll|HDL_LOAD|E12345_US Biweekly B|2022/09/01|E12345|US Biweekly|US Legislative Data Group|2022/09/01
MERGE|AssignedPayroll|HDL_LOAD|E23456_US Biweekly B|2022/09/01|E23456|US Weekly|US Legislative Data Group|2022/09/01Deleting AssignedPayroll using Source Keys:
METADATA|AssignedPayroll|SourceSystemOwner|SourceSystemId|EffectiveStartDate|AssignmentNumber|PayrollDefinitionCode|LegislativeDataGroupName|StartDate
DELETE|AssignedPayroll|HDL_LOAD|E12345_US Biweekly B|2022/09/01|E12345|US Biweekly|US Legislative Data Group|2022/09/01
DELETE|AssignedPayroll|HDL_LOAD|E23456_US Biweekly B|2022/09/01|E23456|US Weekly|US Legislative Data Group|2022/09/01Sometimes, the delete HDL will not work and will throw an error “The values 300000030935380 aren’t valid for the attribute PayrollTermId.” and we will have to use the surrogate keys to delete the AssignedPayroll.
SQL Query to extract the AssignedPayroll deletes:
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
ORDER BY 2Sample HDL for AssignedPayroll delete using surrogate keys:
METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner
DELETE|AssignedPayroll|300000032231398|2022/09/01|300000030002987|300000004610001|300000029139960|300000030002858|E12345_US Biweekly|HDL_LOADWith the surrogate key approach, we should be able to delete the AssignedPayroll information without any issues.
Thank you! This helped!