Step-by-step tutorials for Oracle Fusion HCM including HDL templates, SQL queries, payroll guides, and solutions to common HCM errors.
Sunday, 14 January 2024
Avoid/Ignore if we have empty tag in XML.
Wednesday, 1 November 2023
Frequently asked questions with answer
WHEN Prenote.PRENOTE_DATE=TO_DATE('31/12/4712','DD/MM/YYYY') THEN 'ORA_R'
WHEN Prenote.PRENOTE_DATE=TO_DATE('01/01/0001','DD/MM/YYYY') THEN 'ORA_SK'
WHEN NVL((Prenote.PRENOTE_DATE),TO_DATE('31/12/4712','DD/MM/YYYY'))=TO_DATE('31/12/4712','DD/MM/YYYY') THEN 'ORA_N_S'
WHEN TRUNC(Prenote.PRENOTE_DATE+NVL(OrganizationPaymentMethodDEO.VALIDATION_DAYS,0)) <= TRUNC(SYSDATE) THEN 'ORA_C'
ELSE 'ORA_S'
END AS PRENOTE_STATUS
3. Select Functional Area as "Recruiting and candidate experience management" and click on "recruiting content library" task.
Thursday, 19 January 2023
HDL Template to load BLOB/CLOB files for Talent Profile
HDL Template to load BLOB/CLOB files for Talent Profile
File Name - TalentPofile.dat
You can see step by step video to load Talent Profile data using this Link
Please find the below sample template :
Add ItemClob1File Column in profile item metadata and clob files are placed in "ClobFiles" Folder.
METADATA|TalentProfile|SourceSystemOwner|SourceSystemId|Description|Summary|ProfileTypeCode|ProfileCode|ProfileStatusCode|ProfileUsageCode|PersonId
MERGE|TalentProfile|XXTEST|XXTEST_TALENT2|||PERSON|PERSON_TALENT|A|P|100000609812368
METADATA|ProfileItem|SourceSystemOwner|SourceSystemId|ProfileId(SourceSystemId)|ContentType|ContentItem|SectionName|CountryCountryCode|DateFrom|DateTo|RatingModelCode1|RatingModelCode2|RatingModelCode3|RatingLevelCode1|RatingLevelCode2|RatingLevelCode3|ItemClob1File
MERGE|ProfileItem|XXTEST|XXTEST_TALENTPROF2|XXTEST_TALENT2|LANGUAGE|Hindi|PERSON_LANGUAGE||2021/07/26||LANGUAGE|LANGUAGE|LANGUAGE|HIGH|HIGH|HIGH|
HDL Template to Absence Entry of Worker
✅ Oracle HCM HDL: Load Worker Absence Entry (PersonAbsenceEntry.dat) – Template + Examples
Absence management is one of the most common business requirements in Oracle Fusion HCM. If you need to load absence entries for employees in bulk (Sick leave, Earned leave, Casual leave, etc.), Oracle provides the HDL object:
👉 PersonAbsenceEntry (and its child object PersonAbsenceEntryDetail)
This post provides a complete HDL template with real working examples, along with prerequisites, common issues, and FAQ.
⭐ What is PersonAbsenceEntry in Oracle HDL?
PersonAbsenceEntry is an HDL business object used to create or update worker absence entries in Oracle Fusion HCM.
It supports:
-
Absence Type (Sick, Annual Leave, etc.)
-
Absence dates and time
-
Absence status + approval status
-
Assignment-level detail (via PersonAbsenceEntryDetail)
✅ Prerequisites (Important)
Before loading absence entry using HDL, verify:
1) Worker must exist
The PersonNumber must be valid.
2) Absence Plan must be enrolled first
If the worker is not enrolled in the absence plan, the absence entry load may fail.
✅ Example errors:
-
Plan not enrolled
-
Absence type not eligible
-
No accrual available
3) Absence Type must exist
Absence Type should be configured in:
Absence Administration → Absence Types
4) Employer / Legal Entity should match worker
Use correct employer name.
📂 HDL File Name
📌 File Name:
PersonAbsenceEntry.dat
🧾 HDL Sample Template: PersonAbsenceEntry
This is the main absence entry record.
METADATA|PersonAbsenceEntry|Employer|PersonNumber|AbsenceType|AbsenceReason|AbsenceStatus|ApprovalStatus|StartDatetime|EndDatetime|StartDate|StartTime|EndDate|EndTime|SourceSystemOwner|SourceSystemId
MERGE|PersonAbsenceEntry|US3 Legal Entity|XXTEST2|Sick||SUBMITTED|APPROVED||2021/07/12 08:00:00|2021/07/12|08:00|2021/07/12|16:30|XXTEST|XXTEST2_SICK1
🔍 Explanation of Key Fields
-
AbsenceType: Example: Sick, Annual Leave, etc.
-
AbsenceStatus: Usually SUBMITTED
-
ApprovalStatus: APPROVED / PENDING
-
StartDatetime / EndDatetime: Used for date+time entry
-
StartDate / EndDate: Used for date only entries
-
SourceSystemOwner / SourceSystemId: Must be unique per absence record
🧾 HDL Sample Template: PersonAbsenceEntryDetail
This object is used when you want to load daily/time details and connect absence to assignment.
METADATA|PersonAbsenceEntryDetail|AbsenceDate|Employer|PersonNumber|AbsenceType|AssignmentNumber|AbsenceStartDate|AbsenceStartTime|RowSeq|StartTime|EndTime|SourceSystemOwner|SourceSystemId
MERGE|PersonAbsenceEntryDetail|2021/07/12|US3 Legal Entity|XXTEST2|Sick|XXTEST_ASSIGN2-1|2021/07/12|08:00|1|06:00|16:30|XXTEST|XXTEST2_SICK1
⭐ Why PersonAbsenceEntryDetail is required?
You should use PersonAbsenceEntryDetail when:
✅ Absence is hourly
✅ Absence spans multiple dates
✅ You need assignment-level mapping
✅ You want exact daily breakdown
🧠 Recommended HDL Zip Structure
Your zip should look like this:
AbsenceEntryLoad.zip
└── PersonAbsenceEntry.dat
Upload from:
Data Exchange → HCM Data Loader → Import and Load
⚠️ Common Errors and Fixes
❌ Error: Worker is not enrolled in absence plan
✅ Fix: Enroll the worker first in the plan.
❌ Error: Invalid Absence Type
✅ Fix: Verify Absence Type spelling and configuration.
❌ Error: SourceSystemId already exists
✅ Fix: Use unique SourceSystemId for each absence entry.
❌ Error: AssignmentNumber invalid
✅ Fix: Confirm assignment number from Person Management.
🔗 Internal Links
HDL Load Worker Data (Person/Assignment)
HDL Bank → Branch → External Bank Account → Personal Payment Method
💬 Feedback & Comments
If this absence entry HDL template helped you, drop your feedback in the comment section 🙌
If you face any issue, share:
-
HDL error message
-
Absence Type name
-
Whether plan enrollment exists
-
Your sample Worker.dat snippet (remove sensitive data)
❓ FAQ
Q1. Can we load absence entries using HDL in Oracle HCM?
Yes. You can load worker absences using the HDL objects PersonAbsenceEntry and PersonAbsenceEntryDetail.
Q2. Is absence plan enrollment required before loading absence entry?
Yes. In most cases, the worker must be enrolled in the absence plan first.
Q3. Which file is used to load absence entry in HDL?
The commonly used HDL file is PersonAbsenceEntry.dat.
Q4. What is the purpose of PersonAbsenceEntryDetail?
It provides daily/hourly absence details and connects the absence entry to assignment.
Q5. Can we load hourly absences using HDL?
Yes. Hourly absences are supported by providing StartTime and EndTime in PersonAbsenceEntryDetail.
Q6. What should be the AbsenceStatus and ApprovalStatus?
Typically:
-
AbsenceStatus = SUBMITTED
-
ApprovalStatus = APPROVED (or PENDING depending on workflow)
HDL Template to Accrual Plan Enrollment of Worker
HDL Template to Accrual Plan Enrollment of Worker
Please find the below sample template :
File Name - PersonAccrualPlanEnrollment.dat
METADATA|PersonAccrualPlanEnrollment|PersonNumber|WorkTermAsgNum|PlanName|EnrtStDt|SourceSystemId|SourceSystemOwner
MERGE|PersonAccrualPlanEnrollment|XXTEST22|XXTEST_WRKTERM22|Sick|2021/01/01|XXTEST_SICK1|XXTEST
HDL Template to Disability of Worker
HDL Template to Person Disability
Please find the below sample template :
File Name- PersonDisability.dat
METADATA|PersonDisability|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|LegislationCode|PersonId|PersonNumber|SelfDisclosedType|Category|QuotaFTE|Status
MERGE|PersonDisability|XXTEST|XXTEST_DIS21|2021/07/07|4712/12/31|US|100000609337422|XXTEST21|ORA_PER_YES_US|NA|1|A
Wednesday, 18 January 2023
HDL Template to Change Hire Date of Worker
✅ HDL Template to Change Hire Date of Worker in Oracle Fusion HCM (Worker.dat)
Changing an employee’s Hire Date (Start Date) is a common requirement in Oracle Fusion HCM, especially when correcting wrong employee joining dates loaded from legacy systems or during data migration.
In this blog, you will learn how to change Hire Date of a worker using HDL (HCM Data Loader) with Worker.dat file.
🔥 What is Hire Date in Oracle Fusion HCM?
In Oracle Fusion HCM, the hire date is stored at the Work Relationship level.
That’s why to change Hire Date using HDL, you must update the WorkRelationship component inside Worker.dat.
📌 HDL File Used
✅ Worker.dat
⚠️ Important Note Before Updating Hire Date
Before you change hire date, ensure:
-
Worker already exists in Fusion HCM
-
You know correct identifiers like PersonId or PersonNumber
-
Hire date change may impact:
-
Payroll
-
Absence
-
Seniority
-
Benefits eligibility
-
Assignment start dates
-
So always validate in lower environment first.
✅ HDL Template to Change Hire Date of Worker (Worker.dat)
Use below template exactly:
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
🧾 Explanation of Fields (Worker.dat)
| Column | Meaning |
|---|---|
| SourceSystemOwner | Your source system name (ex: XXTEST) |
| SourceSystemId | Unique record identifier for this HDL row |
| PersonId(SourceSystemId) | PersonId of the worker (can be PersonNumber too in other templates) |
| LegalEmployerName | Must match worker’s legal employer |
| NewStartDate | The corrected hire date you want to set |
⭐ Why We Use SET PURGE_FUTURE_CHANGES N
This line controls whether future-dated changes should be purged.
✅ Recommended:
SET PURGE_FUTURE_CHANGES N
Because:
-
It keeps future-dated transactions safe
-
Prevents accidental loss of future assignment updates
🧭 Step-by-Step: How to Load HDL to Change Hire Date
Step 1: Prepare Worker.dat file
-
Copy the template
-
Replace with your employee values
Step 2: Create ZIP
Zip the file as:
Worker.zip
└── Worker.dat
Step 3: Upload in HCM Data Loader
Navigation:
My Client Groups → Data Exchange → HCM Data Loader
-
Click Import and Load
-
Upload the ZIP file
-
Submit the process
Step 4: Validate Results
After load:
-
Check Import and Load Status
-
Review logs if errors occur
✅ How to Verify Hire Date is Updated
Go to:
Person Management → Search Worker → Employment → Work Relationship
Check:
-
Start Date / Hire Date updated correctly
❗ Common Errors and Fixes
1) Legal Employer Name Invalid
Error: LegalEmployerName not found
✅ Fix: Use exact legal employer name from Fusion setup.
2) PersonId Incorrect
Error: PersonId(SourceSystemId) not found
✅ Fix:
-
Use correct PersonId
-
Or use PersonNumber based mapping (if your environment supports)
3) Future Changes Conflict
Error: Cannot update because of future-dated changes
✅ Fix:
-
Review future-dated transactions
-
Keep
SET PURGE_FUTURE_CHANGES N
🔗 Internal Links
-
HDL Template to Load Worker Data (Worker.dat)
HDL Template for Salary of Worker
How to Load Worker Salary in Oracle Fusion HCM Using HDL (Salary.dat)
Learn how to create or update worker salary in Oracle Fusion HCM using HDL Salary.dat. Step-by-step guide with sample template, required fields, salary basis, action codes, approval flag, and common error fixes.
Oracle Fusion HCM allows you to load or update worker salary using HCM Data Loader (HDL).
This is useful when you need to migrate salary data during:
-
New implementation (initial data load)
-
Bulk salary updates
-
Salary corrections
-
Salary changes during hiring
In this blog, you will learn:
✅ Salary HDL file name and structure
✅ Required attributes
✅ Salary basis and approval flag
✅ Sample Salary.dat template
✅ Common errors and troubleshooting
You can see step by step video to load Salary data using this Click Here
📌 HDL File Used
To load salary data, use:
✅ Salary.dat
✅ Prerequisites (Very Important)
Before loading salary using HDL, make sure:
1) Worker & Assignment already exist
Salary is always tied to an assignment.
So these must exist first:
-
Worker record
-
AssignmentNumber
2) Salary Basis must exist
SalaryBasisName should already be configured.
Example:
-
US1 Annual Salary
-
Monthly Salary
-
Hourly Rate
Navigation:
My Client Groups → Salary → Salary Basis
3) Worker must have valid payroll / employment info
Salary often fails if assignment is missing:
-
legislation code
-
grade
-
payroll details (in some setups)
✅ Salary.dat Template (Sample)
Use the below sample:
METADATA|Salary|AssignmentNumber|DateFrom|DateTo|SalaryAmount|SalaryBasisName|ActionCode|SalaryApproved
MERGE|Salary|ORACLE_ASG12|1991/01/01||10000|US1 Annual Salary|HIRE|Y
✅ Field Explanation (Salary HDL)
1) AssignmentNumber
This is the unique assignment number of the worker.
Example:
ORACLE_ASG12
2) DateFrom / DateTo
-
DateFrom = salary effective start date
-
DateTo = keep blank unless you want to end-date salary
Example:
1991/01/01
3) SalaryAmount
Salary amount to load.
Example:
10000
4) SalaryBasisName
This must match exactly with configured Salary Basis.
Example:
US1 Annual Salary
5) ActionCode
Defines why salary is being loaded.
Common values:
-
HIRE
-
SALARY_CHANGE
-
PROMOTION
-
TRANSFER
Example:
HIRE
6) SalaryApproved
Salary approval flag.
Values:
-
Y = approved
-
N = not approved
Example:
Y
✅ Step-by-Step: Load Salary Using HDL
Step 1: Create Salary.dat file
Create a .dat file and add metadata + merge line.
Step 2: Create .zip file
Create a zip file:
Example:
Salary.zip
Inside zip:
Salary.dat
Step 3: Upload in HDL
Navigate to:
My Client Groups → Data Exchange → HCM Data Loader
Steps:
-
Import and Load Data
-
Upload Salary.zip
-
Submit
Step 4: Monitor HDL Status
Check:
-
Import Status
-
Load Status
-
Completed Successfully / Error
Step 5: Validate Salary in UI
Navigate to:
My Client Groups → Person Management → Search Worker → Salary
Confirm:
-
Salary Amount
-
Salary Basis
-
Effective date
⭐ Example: Load Salary for Multiple Workers
METADATA|Salary|AssignmentNumber|DateFrom|DateTo|SalaryAmount|SalaryBasisName|ActionCode|SalaryApproved MERGE|Salary|ORACLE_ASG12|1991/01/01||10000|US1 Annual Salary|HIRE|Y MERGE|Salary|ORACLE_ASG13|1991/01/01||12000|US1 Annual Salary|HIRE|Y MERGE|Salary|ORACLE_ASG14|1991/01/01||15000|US1 Annual Salary|HIRE|Y
🔥 Common HDL Errors & Fixes
❌ 1) Salary basis not found
Error:
SalaryBasisName is invalid
✅ Fix:
-
Verify Salary Basis exists
-
Match exact name (case-sensitive)
❌ 2) Assignment number not found
Error:
AssignmentNumber doesn’t exist
✅ Fix:
-
Confirm assignment is created successfully
-
Load worker/assignment first
❌ 3) Salary overlaps existing salary
Error:
Overlapping salary records
✅ Fix:
-
End-date previous salary using DateTo
-
Or use correct DateFrom
❌ 4) Salary not approved
Error:
Salary not visible in UI
✅ Fix:
-
Set SalaryApproved = Y
-
Ensure approvals not required in workflow
🔗 Internal Links
-
Load Worker Data using HDL (Worker.dat)
⭐ FAQs
Q1. Which file is used to load salary using HDL?
Salary is loaded using Salary.dat in Oracle Fusion HCM.
Q2. What is required before loading salary using HDL?
Worker and Assignment must exist, and Salary Basis must be configured.
Q3. Can we update salary using MERGE?
Yes, MERGE can create or update salary records depending on the effective date.
Q4. What is SalaryApproved in Salary.dat?
SalaryApproved is a flag that indicates whether the salary record is approved (Y) or not approved (N).
Q5. What is the most common reason salary HDL fails?
The most common reasons are invalid SalaryBasisName and incorrect AssignmentNumber.
HDL Template for Worker Extra Information of Worker (EIT)
Wednesday, 28 December 2022
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|
Tuesday, 27 December 2022
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.
Monday, 26 December 2022
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'
)
Monday, 28 February 2022
How to Change Non-Primary Work Relationship to Primary Using Worker.dat (Oracle Fusion HDL)
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
Featured post
Oracle Fusion HCM: US Tax Withholding Information Using HCM Data Loader (HDL)
Overview of US Tax Withholding Information in Oracle Fusion HCM In Oracle Fusion HCM, Tax Withholding calculation cards store informatio...