Cancel an existing Work Relationship using HDL for employees

SET PURGE_FUTURE_CHANGES N
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag
DELETE|WorkRelationship|HRC_SQLLOADER|1122334455|123456789_1|Legal Entity Two||2010/01/01|E|Y

When we have a requirement to cancel multiple work relationships due to bad data load/some data issues, we can use the below SQL to get this HDL file prepared using BI Report and then upload the file to HCM Data Loader for processing.

SQL Query to get HDL file for cancelling work relationships:

SELECT HDL_DATA
FROM (
	SELECT 'SET PURGE_FUTURE_CHANGES N' HDL_DATA
		,1 seq
	FROM dual
	
	UNION
	
	SELECT 'METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|' || 'PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|' || 'DateStart|WorkerType|CancelWorkRelationshipFlag' HDL_DATA
		,2 seq
	FROM dual
	
	UNION
	
	SELECT 'DELETE|WorkRelationship|' || SourceSystemOwner || '|' || SourceSystemId || '|' || "PersonId(SourceSystemId)" || '|' || LegalEmployerName || '|' || PersonNumber || '|' || DateStart || '|' || WorkerType || '|' || CancelWorkRelationshipFlag HDL_DATA
		,3 seq
	FROM (
		SELECT DISTINCT (
				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
			,papf.person_number PersonNumber
			,(
				SELECT min(to_char(date_start, 'yyyy/mm/dd'))
				FROM per_periods_of_service ppos
				WHERE ppos.person_id = paam.person_id
				) DateStart
			,'E' WorkerType
			,'Y' CancelWorkRelationshipFlag
		FROM per_all_people_f papf
			,per_all_assignments_m paam
		WHERE paam.person_id = papf.person_id
			AND paam.assignment_type = 'E'
			AND paam.primary_flag = 'Y'
			AND paam.effective_latest_change = 'Y'
			AND sysdate BETWEEN papf.effective_start_date
				AND papf.effective_end_date
			AND sysdate BETWEEN paam.effective_start_date
				AND paam.effective_end_date
			AND papf.person_number IN ('1234')
		)
	)
ORDER BY seq ASC

Once we get the output of the above query, we can save it as Worker.dat and then zip it and upload it to HCM Data Loader page to get the work relationships deleted.

Tip: Use this at your own discretion with due diligence. Once you cancel the work relationship, it can’t be activated again. Double check employees list before loading the file.