How to Reverse Terminate employees using HDL?

In this article, we will look into how to reverse terminate employees using the HDL file. After the employee is terminated and if that employee needs to be reverse terminated, we can use the HDL file to do the reversal of termination.

Worker.dat for Reverse Termination

METADATA|WorkRelationship|LegalEmployerSeniorityDate|LegalEntityId|EnterpriseSeniorityDate|DateStart|OnMilitaryServiceFlag|PeriodOfServiceId|PersonId(SourceSystemId)|PrimaryFlag|PersonNumber|LegalEmployerName|WorkerType|GUID|SourceSystemId|SourceSystemOwner|NewStartDate|ActionCode|TerminateWorkRelationshipFlag|ActualTerminationDate|LastWorkingDate|NotifiedTerminationDate|ReverseTerminationFlag
MERGE|WorkRelationship|||||||123456789_1||||||1122334455|HRC_SQLLOADER|||||||Y

We can also get this HDL file created automatically using a SQL Query.

SELECT DISTINCT 'MERGE' METADATA,
	papf.person_number WorkRelationship,
	(
		SELECT source_system_owner
		FROM hrc_integration_key_map
		WHERE surrogate_id = paam.period_of_service_id
			AND rownum = 1
		) SourceSystemOwner,
	(
		SELECT source_system_id
		FROM hrc_integration_key_map
		WHERE surrogate_id = paam.period_of_service_id
			AND rownum = 1
		) SourceSystemId,
	(
		SELECT source_system_id
		FROM hrc_integration_key_map
		WHERE surrogate_id = paam.person_id
			AND rownum = 1
		) "PersonId(SourceSystemId)",
	(
		SELECT name
		FROM hr_organization_v
		WHERE sysdate BETWEEN effective_start_date
				AND effective_end_date
			AND organization_id = paam.legal_entity_id
			AND classification_code = 'HCM_LEMP'
			AND rownum = 1
		) LegalEmployerName,
	'Y' ReverseTerminationFlag,
	papf.person_number personnumber,
	to_char(ppos.date_start, 'YYYY/MM/DD') date_start,
	'E' Worker_Type
FROM per_all_people_f papf,
	per_all_assignments_m paam,
	per_periods_of_service ppos
WHERE 1 = 1
	AND paam.person_id = papf.person_id
	AND paam.assignment_type = 'E'
	AND paam.primary_flag = 'Y'
	AND paam.period_of_service_id = ppos.period_of_service_id 
	AND sysdate BETWEEN paam.effective_start_date AND paam.effective_end_date
	AND paam.assignment_status_type = 'INACTIVE'
	and papf.person_number in ('<list of employees>')