HDL Template for Global Temporary Transfer of Worker

For this use Worker.dat

Please find the below sample template :

METADATA|WorkRelationship|PersonNumber|LegalEmployerName|DateStart|ActualTerminationDate|PrimaryFlag|WorkerNumber|WorkerType|RehireRecommendationFlag|OnMilitaryServiceFlag|ActionCode|ReasonCode|DateOfDeath

MERGE|WorkRelationship|XXTEST9|US1 Legal Entity|2018/01/01||Y|XXTEST9|E|Y|N|GLB_TEMP_ASG||

MERGE|WorkRelationship|XXTEST9|UK Legal Entity|2019/01/01||N|XXTEST9|E|Y|N|GLB_TEMP_ASG||

METADATA|WorkTerms|ActionCode|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|AssignmentType|PersonTypeCode|AssignmentStatusTypeCode|BusinessUnitShortCode|PrimaryWorkTermsFlag|SystemPersonType|ReasonCode

MERGE|WorkTerms|GLB_TEMP_ASG|XXTEST9|US1 Legal Entity|2018/01/01|E|XXTEST_WRKTERM9|2019/01/01|4712/12/31|Y|1|ET|Employee|SUSPEND_PROCESS|US1 Business Unit|Y|EMP|

MERGE|WorkTerms|GLB_TEMP_ASG|XXTEST9|UK Legal Entity|2019/01/01|E|XXTEST_WRKTERM9-2|2019/01/01|4712/12/31|Y|1|ET|Employee|ACTIVE_PROCESS|UK Business Unit|Y|EMP|

METADATA|Assignment|ActionCode|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentNumber|WorkTermsNumber|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|AssignmentType|PersonTypeCode|AssignmentStatusTypeCode|BusinessUnitShortCode|GradeCode|JobCode|LocationCode|DepartmentName|ManagerFlag|LabourUnionMemberFlag|ReasonCode

MERGE|Assignment|GLB_TEMP_ASG|XXTEST9|US1 Legal Entity|2018/01/01|E|XXTEST_ASSIGN9|XXTEST_WRKTERM9|2019/01/01|4712/12/31|Y|1|E|Employee|SUSPEND_PROCESS|US1 Business Unit|XXTEST_GRADE1|XXTEST_CFO|XXTEST_LOC12|XXTEST Department1|Y|N|

MERGE|Assignment|GLB_TEMP_ASG|XXTEST9|UK Legal Entity|2019/01/01|E|XXTEST_ASSIGN9-2|XXTEST_WRKTERM9-2|2019/01/01|4712/12/31|Y|1|E|Employee|ACTIVE_PROCESS|UK Business Unit|XXTEST_GRADE1|ADM|XXTEST_LOC12|XXTEST Department1|Y|N|

 

SQL Query to Fetch Global Transfer records in Fusion HCM

SQL Query to Fetch Global Transfer records in Fusion HCM 

select

papf.person_number, ppnf.full_name,

to_char(ppos.date_start,'mm/dd/yyyy') Hire_date

,(select hou.name from hr_organization_units hou where organization_id = ppos.LEGAL_ENTITY_ID)  Current_LE

from per_All_assignments_m paam

,per_all_people_F papf

,per_person_names_f ppnf

,PER_PERIODS_OF_SERVICE ppos

where paam.person_id = papf.person_id

and papf.person_id = ppnf.person_id

and ppos.period_of_service_id = paam.period_of_service_id

and ppnf.name_type = 'GLOBAL'

and sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between ppnf.effective_start_date and ppnf.effective_end_date

and  sysdate  between paam.effective_start_date and paam.effective_end_date

and paam.person_id = papf.person_id

and paam.assignment_status_type = 'ACTIVE' 

and paam.assignment_type = 'E'

and paam.action_code = 'GLB_TRANSFER'

Note :- Do let me know in case of any issue in query.

SQL Query to Fetch Inactive Employees in Fusion HCM

SQL Query to Fetch Inactive Employees in Fusion HCM 

select 

paam.*

from

 per_all_assignments_m paam ,

per_all_people_f  papf 

where 1=1

and papf.person_id = paam.person_id

and paam.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

and paam.effective_latest_change = 'Y'

and paam.primary_flag = 'Y'

and paam.assignment_type= 'E'

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and exists ( select '1' from per_all_assignments_m paam1 where paam1 .assignment_id = paam.assignment_id

             and trunc(sysdate) between paam1 .effective_start_date and paam1 .effective_end_date

and t1.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

)

  

How to Change Non-Primary Work Relationship to Primary Using Worker.dat (Oracle Fusion HDL)

Introduction

In Oracle Fusion HCM, an employee can have only one Primary Work Relationship at any given time.

During data corrections or integrations, you may encounter a requirement to change an existing non-primary work relationship to primary.

Oracle Fusion allows this update using HCM Data Loader (HDL) by passing specific attributes in the Worker.dat file.

This blog explains:

• When this scenario occurs

• Required HDL attributes

• Sample Worker.dat template

• What happens after upload