Sample HDL for Closing Payroll

METADATA|AssignedPayroll|AssignmentNumber|LegislativeDataGroupName|PayrollDefinitionCode|StartDate|CloseDate|EffectiveStartDate|EffectiveEndDate

MERGE|AssignedPayroll|E1001|US Legislative Data Group|Semi Monthly|2021/01/05|2021/03/31 00:00:00|2021/01/05|2021/03/31

MERGE|AssignedPayroll|E1002|US Legislative Data Group|Semi Monthly|2021/01/05|2021/03/31 00:00:00|2021/01/05|

Close Date should be passed in the format - yyyy/mm/dd hh24:mi:ss. Otherwise error will be thrown.

Effective End Date if left blank also works the same way in above case as I did for the second employee above.

Query to fetch Payroll Relationship and Person Numbers from Object Group

 Query to fetch Payroll Relationship and Person Numbers from Object Group which are added as static.

select
papf.person_number,ppnf.full_name,pprd.PAYROLL_RELATIONSHIP_NUMBER,poga.*
from 
per_all_people_f papf,
per_person_names_f ppnf,

per_all_assignments_f paaf,
pay_payroll_assignments ppasg,
pay_pay_relationships_dn pprd,
pay_object_group_amends poga,
pay_object_groups pog
where 1=1
and papf.person_id = paaf.person_id
and papf.person_id = ppnf.person_id
and paaf.assignment_id = ppasg.hr_assignment_id
--and paaf.person_id = ppasg.person_id
and ppasg.payroll_relationship_id = pprd.payroll_relationship_id
and pog.OBJECT_GROUP_ID=poga.OBJECT_GROUP_ID
and poga.object_id=pprd.payroll_relationship_id
and paaf.PRIMARY_FLAG ='Y'
--and paaf.ASSIGNMENT_TYPE = 'E'
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 paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between ppasg.start_date and ppasg.end_date
and ppnf.name_type='GLOBAL'
and base_object_group_name like 'Object_Group_Name'