Oracle Assignment Supervisor HDL Template with Example | Worker.dat Guide

Overview

In Oracle Fusion HCM, assignment supervisor details (such as Line Manager) can be loaded or updated using HCM Data Loader (HDL). To load an assignment supervisor, Oracle uses the Worker.dat file with the AssignmentSupervisor business object.

You can see step by step video to load assignment supervisor using this Link

This blog explains:

• Prerequisites for loading Assignment Supervisor

• HDL template and sample data

• Key attributes and explanations

• Common errors and troubleshooting

---

Prerequisites

Before loading assignment supervisor data, ensure the following:

1. Worker data must already be loaded

   • Employee, Manager, and their assignments should exist in Fusion.

   • Assignment SourceSystemId must be available.

   • Load Worker data using Worker.dat

2. Assignment and Manager details

   • Both employee and manager must have valid assignments.

   • Manager person and assignment must exist on or before the supervisor effective date.

📌 Note: Assignment Supervisor data is loaded using the Worker.dat file, not a separate HDL file.

---

HDL File Used

Worker.dat

---

Assignment Supervisor HDL Template

METADATA Section

METADATA|AssignmentSupervisor|AssignmentId(SourceSystemId)|SourceSystemOwner
|SourceSystemId|EffectiveStartDate|EffectiveEndDate|
ManagerAssignmentId(SourceSystemId)|ManagerId(SourceSystemId)|
ManagerType|PersonId(SourceSystemId)|PrimaryFlag

Sample MERGE Data

MERGE|AssignmentSupervisor|XXTEST_ASSIGN2|XXTEST|XXTEST_MANAGER1
|2018/01/01|4712/12/31|XXTEST_ASSIGN1|XXTEST_PER1|LINE_MANAGER|
XXTEST_PER2|Y

---

Common Use Cases

• Load Line Manager during employee migration

• Update reporting manager

• Correct supervisor details

• Bulk manager changes using HDL

---

Common Errors & Troubleshooting

Assignment Supervisor Issue

---

Best Practices

• Always validate Assignment and Manager SourceSystemIds

• Use 4712/12/31 as EffectiveEndDate for active supervisors

• Avoid overlapping primary supervisors

• Test with a single record before bulk load

---

Frequently Asked Questions (FAQ)

❓ Which HDL file is used to load Assignment Supervisor?

Assignment Supervisor data is loaded using the Worker.dat file.

❓ Can I update an existing manager using HDL?

Yes, use the same SourceSystemId with updated manager details.

❓ Can I load multiple supervisors?

Yes, but only one primary supervisor is allowed per assignment.

❓ What ManagerType should I use for reporting manager?

Use LINE_MANAGER for standard reporting hierarchy.

❓ Can I end-date a supervisor using HDL?

Yes, update the EffectiveEndDate with the required date.

⭐ HDL Template for Worker in Oracle Fusion HCM (Worker.dat + Worker Example)

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 - Creation of Simple OTBI Report

Development of Simple OTBI Report

In this blog we are going to learn how to develop/create your first OTBI report.

You can see Step by Step Creation of OTBI Report in this video

We will develop the report for below details:

• Person Number
• Date of Birth
• First Name
• Last Name
• Grade Name
• Job Name
• Department

To achieve this requirement follow the below steps.

Step 1 - 

Open the Oracle Fusion Application.

Step 2 - Navigation

Follow the below navigation,

Navigator -> Tools -> Report & Analytics -> Click on Browse Catalog

Step 3 - Creation of Simple Report 

Click on New and then Analysis

It will pop up list of predefined Subject Areas in Oracle Fusion.

Select the appropriate Subject Area to develop the OTBI report.

As per our requirement we are going to select "Worker Management - Worker Assignment Real Time"

Subject Area.

It will open the report development page.

It contains 3 sections:

1) Subject Area - 

It hold all the selected Subject Area Details.

2) Selected Columns - 

In this section we can add columns required for report.

3) Filters - 

Using this section we can add filter to restrict the data in report.

Step 4 - Adding Column in Report

As per our requirement add the columns.

Person Number, DOB, First name, Last Name are related to Worker.

Expand the Selected Subject Area then Search for Worker Folder to add above columns.

Expand Worker Folder add above mentioned columns like Person Number, DOB etc.

Then add Job,Grade and Department Name columns from respective folders.

To run the report click on Results.

It will give you the output.

Step 5 - Change the Column Heading

Click Column Setting -> Column Properties -> Column Format (tab)

Enable the Custom Heading check box.

Enter the required Column Heading.

Run the report again to check if column heading are changed or not.

Step 5 - Add Filter on OTBI Report.

Suppose we need to apply filter on Person Number then click Column Setting  of Person Number, select Filter.

It will popup filter details window.

In this you can mention how you need to apply the filter based on your requirement.

Run the report to check if filter is working fine.

Step 6 - Save the OTBI Report.

Save the report in My Folder.

You can find your report in My Folder.

You can see Step by Step Creation of OTBI Report in this video

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 🚀

Oracle Fusion BIP Report Questions with Answers

Oracle Fusion BI Publisher (BIP) Report – Complete Beginner Guide

Introduction

Oracle BI Publisher (BIP) is a powerful reporting tool in Oracle Fusion Applications used to design, schedule, and distribute reports in multiple formats such as PDF, Excel, CSV, eText, and XML.

This blog covers:

• Navigation to build BIP reports

• Data Model basics

• Components of Data Model

• Triggers, scheduling, bursting

• Multi-language reports

• Common interview questions

This guide is useful for Oracle Fusion HCM/Financials technical consultants and beginners.

---

Navigation to Build the BIP Report in Oracle Fusion

Data Model Navigation

To create a Data Model:

Tools → Reports and Analytics → New → Data Model

The Data Model defines how and from where the data is fetched.

---

Report (Template) Navigation

To create a Report using an existing Data Model:

Tools → Reports and Analytics → New → Report

Here you attach:

• Data Model

• Layout Template (RTF, Excel, eText, etc.)

---

What Is a Data Model in BI Publisher?

A Data Model is an object that contains instructions for BI Publisher to:

• Retrieve data

• Structure data

• Pass parameters to the report

It defines:

• Data sources

• Data structure

• Parameters

• Bursting and triggers

👉 Think of the Data Model as the backend logic of a report.

---

Components of a Data Model

A Data Model consists of the following components:

1. Data Sets

2. Event Triggers

3. Flexfields

4. List of Values (LOVs)

5. Parameters

6. Bursting

---

What Is a Data Set?

A Data Set contains logic to retrieve data from a single data source.

Supported Data Sources

• SQL Query (Database)

• Web Service

• XML File

• Excel File

• BI View Object

Each Data Model can have multiple Data Sets.

---

Can We Use Triggers in BIP Reports?

✅ Yes

BI Publisher supports Event Triggers:

Types of Triggers

1. Before Data Trigger

2. After Data Trigger

3. Schedule Trigger

How Triggers Work

• Triggers call PL/SQL procedures

• Stored in Oracle Database

• Used for validations, logging, data preparation

Example:

• Before Data Trigger → Prepare temporary tables

• After Data Trigger → Cleanup data

---

What Is Split By and Deliver By?

Split By

• Defines how the report output is split

• Example: Split by Employee, Department, Business Unit

Deliver By

• Defines who receives the report

• Example: Email to manager, HR, payroll team

Used mainly in Bursting.

---

How to Generate Multi-Language Reports in BIP?

Oracle BI Publisher supports multi-language reports using translation.

Steps:

1. Open Report → Properties

2. Go to Translation

3. Click Extract Translation

4. Generate XLIFF file

5. Translate text

6. Upload translated XLIFF file

This allows the same report template to run in multiple languages.

---

How to Schedule BI Publisher Reports?

You can schedule reports using multiple options:

Option 1: Report and Analytics

Tools → Reports and Analytics → New → Report Job

Option 2: Published Reporting

Create Section → Published Reporting → Report Job

Option 3: Catalog

Catalog → Select Report → More → Schedule

Option 4: ESS Job

Reports can also be scheduled using ESS Jobs.

---

Common BI Publisher Interview Questions

Q1. What is the difference between Data Model and Report?

• Data Model → Fetches and structures data

• Report → Displays data using templates

Q2. Can a report have multiple templates?

• ✅ Yes (PDF, Excel, eText, etc.)

Q3. Can a Data Model have multiple data sets?

• ✅ Yes

---

Best Practices for BI Publisher Reports

✅ Keep SQL optimized
✅ Use parameters instead of hardcoding
✅ Validate data using triggers
✅ Use bursting for large outputs
✅ Always test scheduled jobs

---

Related Articles

• Introduction to BI Publisher eText Reports

• Difference Between Delimiter and Fixed Position eText

• HDL vs BI Publisher – When to Use What

• BI Publisher Bursting Real-Time Examples

---

Conclusion

BI Publisher is a core reporting tool in Oracle Fusion. Understanding Data Models, Data Sets, Triggers, and Scheduling is essential for building scalable and efficient reports.

This guide gives you a solid foundation to start working with Oracle Fusion BI Publisher Reports confidently.

HRMS Technical interview Questions

HRMS Technical Interview Questions

Q) What is SIT?

->   

•  SIT: SIT is basically a KFF.This we can use to capture additional person information.

• The KFF which we are using here is Personal Analysis Keyflexfield. We can create and assign N number of SITs to a person.
• Table PER_PERSON_ANALYSES and PER_ANALYSIS_CRITERIA
• The API to create SIT information is HR_SIT_API.
•  Define a structure for the Personal Analysis Keyflexfield. For this you have to navigate to Application Developer > Flexfield > Key > Segments Query for Personal Analysis Flexfield.
• Enable the SIT.
• For this navigate to US Super HRMS Manager > Other Definitions > Special Information Types
• Select the special information types you want to use in your Business Group.
• Enable each type by checking the Enabled check box.
• Check the other boxes to specify how you plan to use the special information type. This makes it available in the list of values in other windows as follows:
• a. Job : in the Job Requirements window
• b. Position : in the Position Requirements window
• c. Other : in the Special Information window
• d. Skill : in the Skill Provisions and Search for Event windows in Oracle Training Administration
• The OSHA and ADA check boxes are for US users only.

 Q) What is EIT?

-> 

• EITs are basically Descriptive Flexfield (DFF).Standard product has EIT DFFs defined in forms such as Person, Assignment, and Job etc. We can add segments to the defined DFF based on our requirements so that it will appear in corresponding screen. Compared to SIT EIT has added security. We can enable EIT for specific responsibility.
• The DFF which we are using here is Extra Person Information under Human Resources application.
• Register the Extra Information Types.
  Navigate to Super HRMS Manager Responsibility. Select Register Extra Information Types (EITs) standard concurrent program from the SRS window. Give appropriate values and submit.
  Concurrent Program Name:- Register Extra Information Types (EITs) Parameters Table Name :- PER_PEOPLE_INFO_TYPES [Select it from the drop down available.
  Since it is a people EIT hence, we have to select this value]
  Information Type :- <Name of our EIT> [identify the EIT from the LOV]
  Multiple Rows :- <Value can be Yes/No> Yes: - if we select Yes, it means we are allowing users to enter more
  than one record for this EIT definition No:- If we select No, it means users are allowed to enter only one
  record for this EIT definition. Legislation Code*:- <Select the proper legislation for the EIT information type>. Here we are selecting "United States".
• API Name : HR_PERSON_EXTRA_INFO_API
   Table Name : PER_PEOPLE_EXTRA_INFO
• Assign the EIT to responsibility.
  Navigate to HRMS responsibility -> Security > Information Types Security. 

Q) What are the standard KFF and tables used for the same?
->
 Table : 
              1. FND_ID_FLEXS
              2. FND_ID_FLEX_SEGMENTS
              3. FND_ID_FLEX_SEGMENTS_TL
              4. FND_ID_FLEX_STRUCTURES
              5. FND_ID_FLEX_STRUCTURES_TL
1. Grade :

• Flex Field Name: Grade Flexfield

• Flex Code : GRD

• Table Name : PER_GRADE_DEFINITIONS

2. Job :

• Flex Field Name: Job Flexfield
• Flex Code : JOB
• Table Name : PER_JOB_DEFINITIONS

3. Position :

• Flex Field Name: Position Flexfield
• Flex Code : POS
• Table Name : PER_POSITION_DEFINITIONS

4. Costing :

• Flex Field Name: Cost Allocation Flexfield
• Flex Code : COST
• Table Name : PAY_COST_ALLOCATION_KEYFLEX

5. Competence FlexFields. 

6. People Group FlexFields.

Q) Tables used for DFF.
-> 
Tables
1. FND_DESCRIPTIVE_FLEXS
2. FND_DESCRIPTIVE_FLEXS_TL
3. FND_DESCR_FLEX_COLUMN_USAGES
4. FND_DESCR_FLEX_COL_USAGE_TL
5. FND_DESCR_FLEX_CONTEXTS
6. FND_DESCR_FLEX_CONTEXTS_TL

 Q) Tables used for Fast Formula.
->

• FF_FUNCTIONS 
• FF_FUNCTION_PARAMETERS
• FF_FORMULAS_F 
• FF_FORMULA_TYPES 
• FF_DATABASE_ITEMS 
• FF_GLOBALS_F 

 Q) Can we run Quick Pay for multiple Employee?
-> No, We can run Quick Pay for single Employee because we need to run it from employee's Assignment Screen.

Q) How to debug Fast Formula?
->
1) Message in output format

e.g.
mess = 'person is' +first_name

return mess

2) Logging from fast formula execution engine

e.g.
ff_utils.set_debug (ff_utils.ROUTING + ff_exec.FF_CACHE_DEBUG);

Q) Can we use Database function in Fast Formula? If yes then how?
-> Yes, we need to register the Database Function using below navigation.
Total Compensation -> Basic -> Fast Formula -> Formula Function

Q) Tables affected by Hiring an employee?
->
Per_all_people_f
Per_all_assignments_f
Per_periods_of_service
Hz_parties

Q) What is the difference between Rehire and Reverse Termination?
-> Rehire - Select Ex-Employee after interview for respective Position. It will create new service record(Person_id will change).
    Reverse Termination - It is the process of retain the employee. Service period will be the same.