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

HDL Template for Job Requisition

HDL Template for Job Requisition 

For this use JobRequisition.dat file.

You can see step by step video to load Job Requisition data using this Link

Please find the below sample template :

METADATA|JobRequisition|RequisitionNumber|RecruitingType|JobCode|JobFamilyCode|JobFunction|NumberOfOpenings|UnlimitedOpeningsFlag|RequisitionTitle|HiringManagerId|RecruiterId|PrimaryLocationId|PrimaryWorkLocationId|OrganizationId|DepartmentName|LegalEmployerName|WorkerType|RegularOrTemporary|FullTimeOrPartTime|EducationLevel|CurrentPhaseCode|CurrentStateCode|ExternalApplyFlowCode|PositionCode|BaseLanguageCode|BusinessUnitShortCode|CandidateSelectionProcessCode

MERGE|JobRequisition|XXTEST100|ORA_EXECUTIVES|XXTEST_PM|HUMAN RESOURCES|GSE_TEACH|2|N|XXTEST100|100000610420334|100000610420436|300000186934005|300000221843104|300000047826629|XXTEST Department1|US1 Legal Entity|E|Regular|FULL_TIME|13|REQUISITION_OPEN|REQUISITION_POSTED|ORC_FACULTY|XXTEST_POS1|US|US1 Business Unit|ORC_FACULTY

METADATA|PostingDetails|StartDate|ExternalOrInternal|RequisitionNumber

MERGE|PostingDetails|2021/08/23 00:00:00|ORA_INTERNAL|XXTEST100

MERGE|PostingDetails|2021/08/23 00:00:00|ORA_EXTERNAL|XXTEST100

Oracle Fusion HCM HDL Candidate.dat File – Load Candidate, Address, Phone, Name & Profile (Sample Template)

 

🧾 Oracle Fusion HCM – Load Candidate Data using HDL (Candidate.dat)

If you want to load candidate details into Oracle Fusion Recruiting, you can use HCM Data Loader (HDL) with the file Candidate.dat.

This blog covers:

✅ Candidate basic data
✅ Candidate address
✅ Candidate phone
✅ Candidate name
✅ Candidate profile (Degree / Certifications etc.)
✅ Sample Candidate.dat file format

---

📌 Prerequisites

Before loading Candidate HDL:

• You must have access to HCM Data Loader

• CandidateNumber must be unique

• Use valid codes for:

  • Country (US, IN, etc.)

  • LegislationCode

  • AddressType

  • PhoneType

---

🎥 Step-by-Step Video Reference

You can see step by step video to load Candidate data using this Link:
👉  Link

---

📂 Candidate.dat File – Full Sample Template

1️⃣ Candidate (Basic Candidate Record)

📌 HDL Metadata

METADATA|Candidate|CandidateNumber|CandPrefLanguageCode|StartDate|ConfirmedFlag|CountryOfBirth|DateOfBirth|RegionOfBirth|Source|SourceMedium|TownOfBirth|VisibleToCandidateFlag
✅ Sample Data

MERGE|Candidate|CT123|US|2019/03/20|Y||1980/08/05||direct Source|||Y

---

2️⃣ CandidateAddress

📌 HDL Metadata

METADATA|CandidateAddress|CandidateNumber|EffectiveStartDate|AddressLine1|AddressType|AddressLine2|Country|PostalCode|Region2|Region1|TownOrCity|EffectiveEndDate
✅ Sample Data

MERGE|CandidateAddress|CT123|2019/03/20|1257 Test1|HOME|Test|US|12576|NC|Wayne|Pikeville|

---

3️⃣ CandidatePhone

📌 HDL Metadata

METADATA|CandidatePhone|CandidateNumber|DateFrom|PhoneType|PhoneNumber|CountryCodeNumber|AreaCode|DateTo|Extension|LegislationCode|SpeedDialNumber|Validity|GUID
✅ Sample Data

MERGE|CandidatePhone|CT123|2019/03/20|H1|9605804|1|262|4712/12/31||US|||

---

4️⃣ CandidateName

📌 HDL Metadata

METADATA|CandidateName|EffectiveStartDate|EffectiveEndDate|CandidateNumber|NameType|LegislationCode|FirstName|LastName
✅ Sample Data

MERGE|CandidateName|2019/03/20||CT123|GLOBAL|US|Amar|Test

---

5️⃣ CandidateProfile (Degree / Profile Items)

CandidateProfile is used for things like:

• Degree

• Certifications

• Skills

• Previous employment

• Education details

📌 HDL Metadata

METADATA|CandidateProfile|CandidateNumber|ContentType|ContentItem|SectionContext|DateFrom|ItemDate7|ItemNumber9|ItemText2401|CountryGeographyCode|CountryCountryCode|ProfileItemId

✅ Sample Data

MERGE|CandidateProfile|CT123|DEGREE|BA - Bachelor of Arts|PERSON_DEGREE|2019/03/20|2019/03/20|||US|US|122

---

✅ Best Practices (Important)

✔ Always Follow this Order in Candidate.dat

Recommended sequence:

1. Candidate

2. CandidateName

3. CandidateAddress

4. CandidatePhone

5. CandidateProfile

---

✔ Date Format

Use:

• YYYY/MM/DD
  Example:

• 2019/03/20

---

✔ Effective End Date

Use:

• 4712/12/31

---

❌ Common Errors & Fixes

1) Candidate already exists

Error: Duplicate candidate number
✅ Fix: Use unique CandidateNumber

---

2) Invalid country / legislation code

Error: Invalid CountryCode or LegislationCode
✅ Fix: Use valid values like:

• Country: US, IN

• LegislationCode: US, IN

---

3) AddressType invalid

Error: AddressType HOME not valid
✅ Fix: Use correct address type based on setup:

• HOME

• MAILING

• WORK

---

4) ProfileItemId issue

Error: Profile item not created
✅ Fix: Ensure ProfileItemId exists OR remove ProfileItemId for new inserts.

---

🔗 Internal Links 

Add these links at the bottom or inside the blog:

• HDL Load Worker Data (Complete Guide)

• HDL Template – Benefit Enrollments

• HDL Template – Person Contact

• eText BIP Report

• Common HDL Errors and Fixes

---

⭐ FAQ 

Q1. Which file is used to load candidate data in HDL?

Candidate.dat is used to load candidate records in Oracle Fusion Recruiting.

Q2. Can we load Candidate Address using HDL?

Yes, using CandidateAddress in Candidate.dat.

Q3. How to load candidate education or degree in Oracle HDL?

Use CandidateProfile with ContentType like DEGREE,Education etc.

Q4. What is the best Effective End Date in HDL?

Most implementations use 4712/12/31.

HDL Template for Content Item

 For this use ContentItem.dat file.

You can see step by step video to load Content Item data for talent profile using this Link

Find Content Item Value set Name using below Query - 

SELECT 

a.content_value_set_id as ContentItemValueSetId, 

a.content_value_set_code, 

b.value_set_name as ContentItemValueSetName, 

value_set_description

FROM FUSION.hrt_content_tp_valuesets_b a, FUSION.hrt_content_tp_valuesets_tl b

WHERE a.content_value_set_id = b.content_value_set_id

and b.language = 'US' ;

Please find the below sample template :

METADATA|ContentItem|SourceSystemOwner|SourceSystemId|ContextName|ContentItemCode|Name|ItemDescription|DateFrom|DateTo|ContentItemValueSetName

MERGE|ContentItem|XXTEST|XXTEST_BTECH|DEGREE||BTECH|BTECH|1951/01/01||Degrees