Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader)

Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship

To cancel the Work Relationship, we use HDL Source Key method.

Use the below sample template for reference

SET PURGE_FUTURE_CHANGES N

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag
DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y

In the above template, we do not know the values of the source keys
SourceSystemOwner
SourceSystemId
PersonId(SourceSystemId)

Use below query to fetch the SourceSystemOwner
Query:
select source_system_owner from hrc_integration_key_map
where surrogate_id = period_of_service_id  
-- pass period_of_service_id corresponding to Work Relationship

Use below query to fetch the SourceSystemId
Query:
select source_system_id from hrc_integration_key_map
where surrogate_id = period_of_service_id  
-- pass period_of_service_id corresponding to Work Relationship

Use below query to fetch the PersonId(SourceSystemId)
Query:
select source_system_id from hrc_integration_key_map
where surrogate_id = person_id  
-- pass person_id corresponding to Work Relationship

You can also formulate a query and create a report based on the query in case of bulk users

Use below query for reference. This is only for reference. Please make necessary changes as per your requirements

Query:

select

distinct

'DELETE' METADATA

,'WorkRelationship' 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

,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

1 = 1 and

paam.person_id = papf.person_id and

assignment_type = 'E' and

primary_flag = 'Y' and

papf.person_number in ('1234') and

paam.assignment_number in ('E1234')