Cancel an existing Work Relationship using HDL for employees
Cancel an existing Work Relationship using HDL for employees

 1,226 total views

In this article we will look into cancelling an existing Work Relationship of an employee using HDL process.

Want to learn Fusion Technical tools? Checkout this article

We can prepare the HDL file with the required user keys to delete the work relationship.

Sample HDL to delete Work Relationship(Worker.dat):

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.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 4 Comments

  1. Anonymous

    i am getting below error while running this code.
    "ORA-00972: identifier is too long"

  2. There was a small issue with the SQL as it was missing a ". I have added it now. Please copy and run it.

    Thanks,
    Sricharan

  3. Anurag

    Awesome Sricharan!!
    Do we have similar query to create manager change worker hdl file?
    Please do post.
    Once again thanks a lot for your all efforts!

Leave a Reply