Oracle Worker.dat HDL Template with Example | Oracle Fusion HCM Guide

Introduction

Oracle Worker.dat HDL template is used to load employee, assignment, and work relationship data into Oracle Fusion HCM using HCM Data Loader (HDL). This tutorial explains Worker.dat HDL file structure, examples, loading steps, and common errors..

You will also find:

• Sample HDL templates

• Required components per object

• Loading steps and verification

• Related articles for Oracle Fusion troubleshooting

Oracle Cloud - Load Work Structure Components - HDL

Load Work Structure Using HCM Data Loader in Oracle Fusion HCM

Oracle Fusion HCM provides HCM Data Loader (HDL) as a powerful and efficient tool to load and maintain large volumes of HCM data. One of the most common technical requirements during implementation or data migration is loading Work Structure components.

In this blog, we will walk through how to load Work Structure using HCM Data Loader in Oracle Fusion HCM, with complete HDL file examples and step-by-step instructions.

In an earlier blog, we explained how to define Source Keys and created a custom Source System Owner – XXTEST. The same source system will be reused while loading Work Structure objects.

📽️ A step-by-step video walkthrough is also available using the provided link.

---

What Are Work Structures in Oracle Fusion HCM?

Work Structures define the organizational framework of an enterprise and are prerequisites for loading Worker and Assignment data.

Using HCM Data Loader, the following Work Structure components can be loaded:

• Grade

• Job

• Location

• Organization

• Position

Each component is loaded using its respective HDL object and must follow a proper dependency sequence.

---

Download Work Structure Object Templates

Before creating HDL files, download the required object templates from your Fusion instance.

Navigation:

Navigator → My Client Group → Data Exchange → View Business Objects

This page displays all business objects supported by HCM Data Loader. Download the templates related to Work Structure Objects.

Note: In this blog, only the required attributes are used from the template to simplify the data load.

---

Load Grade Using HCM Data Loader

Grades define employee levels within an organization.

HDL File – Grade

METADATA|Grade|SourceSystemOwner|SourceSystemId|EffectiveStartDate
|EffectiveEndDate|SetCode|GradeCode|GradeName|ActiveStatus

MERGE|Grade|XXTEST|XXTEST_GRADE01|1951/01/01|4712/12/31|COMMON
|XXTEST_GRADE01|XXTEST Grade01|A

🔑 Source Keys Used

• SourceSystemOwner: XXTEST

• SourceSystemId: XXTEST_GRADE01

📁 Save the file as Grade.dat

---

Load Job Using HCM Data Loader

Jobs represent roles such as Manager, Analyst, or Director.

HDL File – Job

METADATA|Job|SourceSystemOwner|SourceSystemId|EffectiveStartDate
|EffectiveEndDate|SetCode|JobCode|Name|ActiveStatus|RegularTemporary

MERGE|Job|XXTEST|XXTEST_JOB1|1951/01/01|4712/12/31|COMMON|XXTEST_CFO
|XXTEST: Chief Financial Officer|A|R

📁 Save the file as Job.dat

---

Load Location Using HCM Data Loader

Locations define physical or logical work locations.

HDL File – Location

METADATA|Location|SourceSystemOwner|SourceSystemId|EffectiveStartDate
|EffectiveEndDate|SetCode|LocationCode|LocationName|Description
|ActiveStatus|AddressLine1|AddressLine2|TownOrCity|Region1|Region2
|PostalCode|Country

MERGE|Location|XXTEST|XXTEST_LOC5|1951/01/01|4712/12/31|COMMON
|XXTEST_LOC5|XXTEST Location5|XXTEST Location6|A|250 Some st1||Pasadena
|Los Angeles|CA|91101|US

📁 Save the file as Location.dat

---

Load Organization Using HCM Data Loader

Organization loading requires two HDL objects.

1️⃣ Organization

METADATA|Organization|SourceSystemOwner|SourceSystemId|EffectiveStartDate
|EffectiveEndDate|Name

MERGE|Organization|XXTEST|XXTEST_DEPT1|1951/01/01|4712/12/31
|XXTEST Department1

---

2️⃣ Org Unit Classification

METADATA|OrgUnitClassification|SourceSystemOwner|SourceSystemId
|EffectiveStartDate|EffectiveEndDate|OrganizationId(SourceSystemId)
|ClassificationCode|SetCode|Status

MERGE|OrgUnitClassification|XXTEST|XXTEST_DEPT_CLASS1|1951/01/01
|4712/12/31|XXTEST_DEPT1|DEPARTMENT|COMMON|A

📁 Save both records in a single file as Organization.dat

---

Load Position Using HCM Data Loader

Positions define individual instances of jobs within departments.

HDL File – Position

METADATA|Position|SourceSystemOwner|SourceSystemId|EffectiveStartDate
|EffectiveEndDate|PositionCode|Name|BusinessUnitName|ActiveStatus
|JobId(SourceSystemId)|DepartmentId(SourceSystemId)|HiringStatus
|PositionType|FTE|HeadCount|FullPartTime|WorkingHours|Frequency

MERGE|Position|XXTEST|XXTEST_POSITION1|1951/01/01|4712/12/31|
XXTEST_POS1|XXTEST Position1|US1 Business Unit|A|XXTEST_JOB1|
XXTEST_DEPT1|APPROVED|NONE|1|1|FULL_TIME|40|W

📁 Save the file as Position.dat

---

Create ZIP File for HDL Load

Create a ZIP file containing all HDL .dat files:

• Grade.dat

• Job.dat

• Location.dat

• Organization.dat

• Position.dat

Example:

WorkStructure_HDL_Load.zip

---

Load HDL Files into Oracle Fusion HCM

Navigation:

Navigator → My Client Group → Data Exchange → Import and Load → Import

Steps:

1. Upload the ZIP file

2. Submit the Import and Load process

3. Refresh to monitor the load status

Once the process completes successfully, the Work Structure data is loaded.

---

Verify Work Structure Data

After successful load, verify the data from respective pages:

• Manage Grades

• Manage Jobs

• Manage Locations

• Manage Organizations

• Manage Positions

---

Conclusion

Using HCM Data Loader, Work Structure components in Oracle Fusion HCM can be efficiently loaded by defining correct Source Keys, using the required attributes, and following the proper load sequence.

This approach is commonly used during:

• Data migration

• Initial implementation

• Test-to-production refresh

---

What’s Next?

➡️ In the next blog, we will cover how to load Worker Data in Oracle Fusion HCM using HCM Data Loader, including Worker, Assignment, and Element Entry objects.

Define Source System Owner in Oracle Fusion HCM (HDL Source Keys Explained)

Introduction

When loading data into Oracle Fusion HCM using HDL (HCM Data Loader), Source Keys play a critical role in uniquely identifying records across multiple data loads.
Without properly defining the Source System Owner, HDL loads will fail or behave inconsistently.

In this blog, you’ll learn:

• What Source System Owner is

• How it works with Source System ID

• Step-by-step process to define Source System Owner in Oracle Fusion

• Why it is mandatory for HDL loads

You can see step by step video using this link.

---

What Are Source Keys in Oracle Fusion HCM?

Source Keys are unique identifiers used by Oracle Fusion to track records loaded via HDL.

Source Keys consist of two components:

1. SourceSystemOwner

2. SourceSystemId

📌 These keys help Oracle Fusion:

• Identify records uniquely

• Support incremental loads

• Prevent duplicate data

• Enable updates using the same source reference

---

What Is Source System Owner?

Source System Owner represents the external or internal system from which the data originates.

Examples:

• Legacy HR System

• Payroll System

• Custom Data Migration Source

• Interface Program

Before using Source Keys in HDL files, the Source System Owner must be defined in Fusion.

---

Prerequisite

• Access to Setup & Maintenance

• Appropriate implementation privileges

---

Navigation to Define Source System Owner

Follow the steps below:

1. Navigator

2. Go to Setup & Maintenance

3. Open Task Panel tab

4. Click Search

5. Search for Manage Common Lookups

6. Open Manage Common Lookups

---

Search for Source System Owner Lookup

1. In the Meaning field, enter:

   Source System Owner
   

2. Click Search

3. In the results, you will see the Source System Owner lookup definition along with existing lookup values

---

Add a New Source System Owner

In the Lookup Code Values section, click Add (+) and enter the following details:

Field	Value
Lookup Code	XXTEST
Display Sequence	1
Enabled	✔ Checked
Start Date	01/01/1951
End Date	12/31/4712
Meaning	XXTEST
Description	XXTEST

📌 Dates ensure the lookup remains valid indefinitely.

---

Save the Lookup

Click Save and Close.

✅ Your Source System Owner is now successfully defined.

---

How This Is Used in HDL

Once the Source System Owner is defined:

• You can reference it in HDL files

• Combine it with SourceSystemId to form Source Keys

• Use Source Keys to create or update records

Example:

SourceSystemOwner = XXTEST
SourceSystemId    = EMP001

This combination uniquely identifies the record in Oracle Fusion.

---

Common Issues & Tips

• ❌ HDL fails if Source System Owner is not defined

• ❌ Typo in SourceSystemOwner causes load errors

• ✔ Always use consistent Source Keys across loads

• ✔ Avoid changing Source System Owner once data is loaded

---

Conclusion

Defining Source System Owner is a mandatory and foundational step when working with HDL in Oracle Fusion HCM.
It ensures data integrity, smooth incremental loads, and reliable record identification.

Once configured, you can confidently use Source Keys across all HDL objects.

---

What’s Next?

👉 In the next blog, we will cover:

How to Load Work Structure in Oracle HCM Fusion using HDL

Stay tuned 🚀

Top 50 Fusion HCM Technical Questions - Part 1

Top 50 Fusion HCM Technical Questions

HCM Interview Questions
If you are preparing for an Oracle Fusion HCM Technical Interview, this blog will help you revise the most frequently asked Fusion HCM interview questions related to HCM Data Loader (HDL), HCM Extracts, BI Publisher (BIP), OTBI, and Global Person Model.

Top 50 Oracle Fusion HCM Technical Interview Questions – Part 2 Click 

Q) Explain HCM Loader Keys.

-> 

1. Source Key - Originate in the object source environment. Source Key has 2 components, SourceSystemOwner,SourceSytemId. 

      E.g.METADATA|Job|JobCode|EffectiveStartDate|EffectiveEndDate|Name|SetCode|SourceSystemOwner|SourceSystemId

MERGE|Job|SE|2010/01/01|4712/12/31|Software Engineer|COMMON|EBS-UK|12349

 2. User Key - User keys are part of that object that they belong to. You can use user to create and update object

e.g.

METADATA|Job|JobCode|EffectiveStartDate|EffectiveEndDate|Name|SetCode

MERGE|Job|SE|2010/01/01|4712/12/31|Software Engineer - Java|COMMON

 3. Surrogate Id - Surrogate Id is numeric value. It is automatically generated at the time of creation of object in fusion. You can use surrogate Id to identify existing objects only. Surrogate ID is the unique numeric value for that record. (like for location it is location id , for job it is job id)

e.g.

METADATA|Job|JobId|EffectiveStartDate|EffectiveEndDate|Name

MERGE|Job|13413|2010/01/01|4712/12/31|Software Engineer - Java

4. Fusion GUID - GUID is hexadecimal value. GUID is automatically generated at the time of creation of object.

e.g.

METADATA|Job|GUID|EffectiveStartDate|EffectiveEndDate|Name

MERGE|Job|2342UJHFI2323|2010/01/01|4712/12/31|Software Engineer - Java

Q) How to change Non primary Work Relation Flag to Primary?

->  Click Here to see details

Q) Where you can find the HCM Loader keys?

-> hrc_integration_key_map table

Q) Can you track multiple transaction happen in single day on assignment record?

-> Yes we can track. For some objects more than one update per day is maintained by creating physical record for each one. Such object include an effective sequence number in each record to show the order in which update made multiple times in a day.

Q) How to find records error out using HCM Data Loader?

->  1) You can check the errors by clicking the message icon in import and export window.

     2) You can find in below Tables

          hrc_dl_message_lines

          hrc_dl_physical_lines

          hrc_dl_data_sets

          hrc_dl_file_rows

          hrc_dl_file_linesFusion Interview Question

     3) Using Run Diagnostics Test - Test Name  (HCM Data Loader Data-Set Status ) provide the         Content ID  for the same. This one is the oracle standard method to find error records in HCM Data loader.

Q) Common Entity used in HCM Fusion Extracts 

-> 

Person (PER_EXT_SEC_PERSON_UE)	Retrieves all persons across the enterprise and all person related attributes.
Worker Payroll (PER_EXT_PAY_EMPLOYEES_UE)	Retrieves all workers and their payrolls across the enterprise, all person, worker, payroll related attributes, and element entry data.
Extract Assignment Basic History (PER_EXT_ASSIGNMENT_BASIC_HISTORY_UE)	Retrieves assignment history as on the assignment effective start date.
Extract Assignment Basic Information (PER_EXT_SEC_ASSIGNMENT_BASIC_UE)	Retrieves assignment data as on the effective date.
Assignments Range (PER_EXT_SEC_ASSIGNMENT_RANGE_UE)	Retrieves assignment history as on the effective date.
Extract Current and Future Persons (PER_EXT_SEC_PERSON_NOW_FUTURE_UE)	Retrieves current and future person details. Specify advanced filter criteria to restrict person types.
Extract Current and Future Assignments (PER_EXT_SEC_ASSIGNMENT_NOW_FUTURE_UE)	Retrieves current and future assignments.

Fusion Interview Question

Q) Different type of person types in HCM Fusion?

->

Beneficiary, dependent,  retiree, APL, EMP, CWK, EX_APL, EX_CWK, EX_EMP, volunteer, etc.

Q) How you will correct termination date of Work Relationship in HCM Fusion application using HDL?

-> To correct the termination date, need to pass newActualTerminationDate and also set CorrectTerminationFlag attribute to Y.

Q) Significance of EffectiveSequenceNumber.

->  

If you are loading date effective history for an object with multiple changes in a single date, EffectiveSequencenumber will increase by 1. 

If only one change exists for an effective start date then effective sequence number must be 1. 

Do not leave EffectiveSequenceNumber blank when you are trying to load multiple changes on the same day.

Q) FBL v/s HDL

->

HDL Supports all flex field including Extensible Flex Field and People group Flex field.

Supply components data separate data files in FBL whereas you can supply data using single file in HDL.

FBL supports only GUID whereas HDL supports 4keys.

FBL supports around 21 objects and HDL supports around 90 objects. 

 Q) Can Oracle transaction Business Intelligence(OTBI) integrate with Fusion HCM extract?

->

Yes. You have two options:

1: HCM Extracts can extract the data and produce an output using CSV, XML or PDF. OTBI can then accept a data source in excel or XML format (SaaS customers will need Oracle to set this up for them).
2: BI Publisher can also accept a data source in excel or XML format (again SaaS customers will need Oracles help). HCM extracts has integration with BI Publisher.

 Q) What is the difference between the ‘Assignment Status Type’ and the ‘Extract Employee Assignment Status Type’ attributes? 

-> 

The 'Extract Employee Assignment Status Type' attribute is a DBI group which is directly associated with the user entity when defining the block. The 'Assignment Status Type' attribute is a DBI group which is associated with a different user entity, and is accessible because of contexts that have been set (indirectly derived).
Directly associated DBI groups are more efficient compared to indirect. Therefore, it would be better to use the 'Extract Employee Assignment Status Type' attribute rather than the 'Assignment Status Type' attribute because of performance considerations.

Q) Can you create a user entity? 

 -> No. 

Q) How do you schedule the HCM Extract in fusion application?

-> You can schedule the HCM extract using fast formula with 'Flow Schedule' as a type.

Q) How we can extract the changed data from last run date to till date using HCM Extracts?

-> Yes, We can extract the changes only data from last run. We need to select the option "Include changes from last successful run" at the time of defining the HCM extract.

  
Q) What are the different type/attribute for changes only parameter in HCM Extract?
-> There are 6 values for this parameter.

Step by Step Explanation of Changes Only HCM Extracts  Click 

1. All Attribute
2. Changed Attributes
3. Changed and Marked Attributes
4. Changed and Marked Attributes with previous value
5. Changed, Marked Attributes under threading group
6. Changed, Marked Attributes, previous data under threading group

Q) Explain Global Person Model in HCM Fusion.
->  1) Person Records are global, create only once.
      2) Person Records are independent of legal Employer
      3) Person resign from the organization then Work Relationship records are terminated.
      4) Person Record having information like Name, Date of Birth, National ID etc.

   
Q)How to create dependent parameter for BI Publisher report in HCM Fusion?
-> We will take one simple example of dependent parameter for BIP report in HCM Fusion.

   1st Parameter  - Person Id 

This is the independent parameter. We will fetch person_id from per_all_people_f
table.

Define the parameter-

Define the List of Value -




2nd Parameter - Assignment Id Based on Person Id

Define Parameter -

Define List of Value -





Use this parameters in Data Set SQL.

Q) How can you purge Future assignment record using Data Loader?

-> Yes we can purge future changes.

     SET PURGE_FUTURE_CHANGES Y

     It will purge the future changes.

Q) How you will assign multiple assignment for a single work relationship?

-> You can assign multiple assignments for single work relationship using PrimaryAssignmentFlag.

     For primary assignment, set  PrimaryAssignmentFlag to Y.

Q) Explain Global Transfer Case?

-> Transfer the Person from one legal employer to another legal employer. In this current Work Relationship record got terminated and create new one record with another legal employer as Active.

Navigation - > Person Management  -> Employment Task

Impacted Tables
per_all_assignments_m
per_periods_of_service.

Q) Significance of Key Attribute field in HCM Extract?

-> We need to configure Key Attribute which is uniquely identify each row return by the data group.

     For Changes only mode, you must select at least one attribute in the block as key attribute.

     e.g. Person id for person details

Q) Advantages of HCM Extract Vs BIP Reports ?

->
BIP Reporting Tool - It is a simple reporting tool. You can write your own SQL and get data. You should have knowledge on joins between the tables. You can use BIP reporting tool for formatted output.
HCM Extract - For the complex reporting you can use HCM Extracts. Using Extract you can derived the data from different sources or modules, you can easily use hierarchy or parent-child relationship. You Can work at a level above the BI/SQL where you don't  worry about tables,joins.
You can extract in Full Mode or schedule interface to provide you the changes only data since last successful run.

Q)How you will validate data files are correctly formatted?

-> You can use Data File Validator tool to validate the format of data files including flexfield configuration.

Q) Can I use DELETE and MERGE metadata in the same data file during data load?

-> No, you can not use DELETE metadata for an object has the MERGE metadata in data file.

Q) How to load NULL values using HDL?

 -> Use #NULL to load the null values for any attribute in MERGE metadata.

Q) How to use the Source Keys in HCM Data Load?
-> To use the Source Key in HCM Load you have to configured it in
         HRC_SOURCE_SYSTEM_OWNER lookup in system. If it is not configured then by default system will use FUSION as a source system owner.

Q) What are the output format of HCM Extracts?
-> Output Format - CSV,XML,Excel,HTML,RTF,PDF

Q) What are the different types of  HCM Extracts?
-> Full Profile , Payroll Interface, Payments, Benefit Carrier, Archive Retrieval, HR Archive, Payroll Archive.    

Q) Delivery Type in HCM Extracts?
-> Document of Record,Email, FTP, FAX, File System, HCM Connect, HTTP, Printer.
Detailed Component :-
Email            Start Date,End Date, Delivery option name, Output Type (PDF,RTF),
                      Report (Path), Output Name(Template Name), Delivery Type(Email), 
                      CC, Email, BCC,Attachment(Y), Message Body,Subject
-----------------------------------------------------------------------------------------------------------------------
FTP              Start Date,End Date, Delivery option name, Output Type (EFT),
                     Report (Path), Output Name(Template Name), Delivery Type
                     (FTP),Secure FTP(Y), Remote Directory, password, user Name, 
                     server name.
-----------------------------------------------------------------------------------------------------------------------
  

Q) Root Data Group is mandatory in HCM Extracts?
->  Yes, at least one of the data group must be declared as Root Data Group and other data groups have to be linked appropriately. 

Q) Explain threading database item in HCM Extracts?
-> Threading Database Item is required for implementing the changes only feature. It is the unique ID for that User Entity. You can declare the threading database item at root data group or child data group level where you required changes only data.

Q) How you can use BIP report in HCM Extracts?
->  You can launch BI Publisher from Report and Analytics Work Area. Install the BI Publisher plugin. You can develop RTF template in MS word using BI Publisher Plugin using Load XML Schema.
Now you have to define the report . For this use existing standard model. (/Human Capital Management/Payroll/Data Models/globalReportsDataModel). Save the report.Latest Datamodel have two parameters Payroll action id and Flow instance name. Flow instance name will be the flow pattern run given earlier when submitting the extract.

Q) How to schedule the HCM Extracts?
->  Yes, We can schedule the HCM Extract recursively like Daily, Weekly,Monthly.
     If you want to create custom schedule you need to create fast formula with type as Flow Schedule.


Q) Standard BIP Report in HCM
->  Person Enrollment Summary Report, Compensation Workbench Print Statement, Performance Document Rating Report, Total Compensation Statement report etc.

Q) How to Filter the Data in Template while developing the BIP Report?
->  You can filter the data using filter condition based on the Data Field, Operator and Value.
     You can filter the data for Chart view, table view.

Q) Different Delivery Option in BIP Report
->  Printer,Fax,Email,HTTP,FTP.
    You can define these delivery option using following navigation:
Administrator -> Manage BI -> Delivery -> Type of Delivery option 

Q) How to Schedule the BIP Report?
->  You can Schedule the report using following methods:
1) In report and analytics -> New -> Report Job
2) In Create Section -> Published Reporting -> Report Job
3) In Catalog -> Select Report -> More-> Schedule

You have to set Frequency like Once, Hourly, Daily, Weekly, Monthly, Annual, Specific Dates.

Q) How to Burst BIP report in Fusion HCM?
->  Using Bursting option you can split your Report into multiple documents and deliver it to one or more destination.
E.g. Report output having 3 columns Business Unit, Department,Location
You can split the report based on Business unit column with resp department and location for that Business unit.

Business unit	Department	Location
Progress US BU	Support PRG	Main Campus
UK BU	Support PRG UK	London
US1 BU	Finance PRG	RF Hospital
Progress US BU	Biology US	Main Campus
UK BU	Finance PRG UK	London
US1 BU	Biology US FN1	RF Hospital
UK BU	Facilities PRG UK	London
US1 BU	Facilities PRG US	RF Hospital

Split by BU

Business unit	Department	Location		Business unit	Department	Location
Progress US BU	Support PRG	Main Campus		UK BU	Support PRG UK	London
Progress US BU	Biology US	Main Campus		UK BU	Finance PRG UK	London
				UK BU	Facilities PRG UK	London
Business unit	Department	Location
US1 BU	Finance PRG	RF Hospital
US1 BU	Biology US FN1	RF Hospital
US1 BU	Facilities PRG US	RF Hospital

Create Bursting Definition.

Name , Type,Data Source Name, Split by , deliver By, SQL Query.

Split By On the basis of BU

Deliver By BU

In SQL Query you have to mention Template,Output Format, Delivery Channel.

Enable the Bursting output in report properties.

Q) Can we update Source Keys?
->  Yes, We can update it using SourceKey.dat.

Q) How to change hire date of existing Worker/Employee?
->  

SET PURGE_FUTURE_CHANGES N

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|NewStartDate
MERGE|WorkRelationship|XXTEST|XXTEST_HIREDATE1|100009|India Legal Entity|2002/02/10