Do you know how to Reverse Terminate Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to Reverse Terminate Work Relationship

In order to cancel a Terminated Work Relationship, we need to Reverse Terminate that Work Relationship first

Use the below sample template for reference

SET PURGE_FUTURE_CHANGES N
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|ReverseTerminationFlag
MERGE|WorkRelationship|FUSION|1234|WRK_1234|LegalEmployer|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
'MERGE' 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
,'Y' ReverseTerminationFlag
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')