HCM Data Loader: the silent UTF-8 BOM issue that drops your first row

 The UTF-8 BOM encoding issue that silently drops or corrupts your first data row

After spending time investigating why the first record in a transformation input file was consistently being dropped or flagged as invalid during transaction loading in Oracle Retail Merchandising System (RMS), the root cause turned out to be something surprisingly subtle — a file encoding mismatch.

What is the issue?

When you prepare a .dat file for loading into Oracle Fusion HCM, the file must be encoded as plain UTF-8. However, many text editors and export tools save files as UTF-8 BOM (Byte Order Mark) by default — and this makes all the difference.

A BOM is a special invisible sequence of bytes (EF BB BF in hex) added at the very beginning of the file. The Oracle data conversion tool does not expect these extra bytes, so when it tries to read the first field of the first record, it encounters garbage characters instead of your actual data.

What happens next depends on the data type of your first field:
If the first field is a number → the record is rejected with "INVALID NUMBER".
If the first field is a varchar2 (text) → the junk BOM characters get silently appended to the field value, causing it to fail business validations later.

A real example

Let's say you have a deps1.dat file for loading department data. The first field is DEPT (a number). Your file looks like this in a text editor:

# What you THINK the file starts with:
1001|MENSWEAR|10|USD|...

# What UTF-8 BOM ACTUALLY saves at the start:
EF BB BF 1001|MENSWEAR|10|USD|...

# The tool reads the first field as:
1001 ← invisible BOM chars + "1001" = NOT a valid number

If your Fusion payroll file is failing to process only the first line, it is likely due to a Byte Order Mark (BOM) or a header formatting error that makes the first record unrecognizable to Oracle HCM.

Open the file in Notepad++ and navigate to View>Encoding, change it from UTF-8 BOM to UTF-8. Submit the UTF-8 file for the file load success.

How You Load Data for Multiple Contexts' Descriptive Flexfields in HDL

In the world of Oracle Fusion HCM integrations, we typically deal with a single context for a Descriptive Flexfield (DFF). It’s straightforward: one record, one context, one set of attributes.

However, "standard" doesn't always mean "simple." There are rare but critical scenarios where you must populate multiple contexts for the same DFF simultaneously. A classic example is when you have mandatory attributes in the Global Data Elements (the default context) while needing to populate a specific Custom Context (like XXABFUNDCODE) at the same time.

Here is how you handle this complexity in your data headers.

The Challenge: The "Double Context" Scenario

When you have a requirement to pass data for both a custom context and the global context, a standard single-column header won't suffice. If you try to pass them separately or in a sequence that the system doesn't recognize, the integration will likely fail or ignore one of the segments.

The Solution: Strategic Header Formatting

To successfully map these, you need to explicitly define the contexts within your header row. Using the example of an Assignment DFF (PER_ASG_DF), your file structure should look like this:

1. The Header Mapping

You must reference the DFF name and specify which attribute belongs to which context using the FLEX prefix notation.

The Syntax:

FLEX:DFF_CODE|AttributeName(FLEX:DFF_CODE=CONTEXT_CODE)

Your Header Example:

FLEX:PER_ASG_DF|abcId(FLEX:PER_ASG_DF=XXABFUNDCODE)|defId(FLEX:PER_ASG_DF=Global Data Elements)

2. The Data Row

Once the headers are mapped to their respective contexts, the data row follows the order defined above.

Your Data Example:

XXABFUNDCODE|ABCFUNDCODE|ValueForGlobalDataElements

---

Breakdown of the Logic

Component	Purpose
FLEX:PER_ASG_DF	This is the primary discriminator. It tells the loader which DFF is being targeted.
abcId(...=XXABFUNDCODE)	This explicitly routes the value ABCFUNDCODE into the custom segment of the XXABFUNDCODE context.
defId(...=Global Data Elements)	This ensures the mandatory global attribute receives its value, preventing validation errors.

This is how the syntax in the METADATA looks like,
FLEX:PER_ASG_DF|abcId(FLEX:PER_ASG_DF=XXABFUNDCODE)|defId(FLEX:PER_ASG_DF=Global Data Elements)
XXABFUNDCODE|ABCFUNDCODE|defValueGlobalData 

Balance Adjustments Load in Oracle Fusion HCM Payroll

 This guide provides a comprehensive walkthrough for creating and executing a Balance Adjustment using the HCM Data Loader (HDL) in Oracle Fusion HCM Payroll.

1. HDL File Construction

To perform a balance adjustment via HDL, you must prepare two specific .dat files. These files define the metadata and the actual data values to be adjusted.

A. BalanceAdjustmentHeader.dat

This file establishes the batch and the group parameters for the adjustment.

METADATA|BalanceAdjustmentHeader|BatchName|LegislativeDataGroupName

MERGE|BalanceAdjustmentHeader|BalanceAdjustmentsFITtoSITBatch_09092025_V1|US Legislative Data Group

METADATA|BalanceAdjustmentGroup|BatchName|EffectiveDate|LegislativeDataGroupName|ConsolidationSetName |PayrollName|PrepayFlag|BalanceAdjCostFlag

MERGE|BalanceAdjustmentGroup|BalanceAdjustmentsFITtoSITBatch_09092025_V1|2025/07/10|US Legislative Data Group |US Consolidation Group|Biweekly|Y|N

B. BalanceAdjustmentLine.dat

This file defines the specific lines of the adjustment, including assignment details and the input values being modified.

Line Metadata: Defines the assignment number (E10020), the element name (Adjust Resident State), and the Tax Reporting Unit 

METADATA|BalanceAdjustmentLine|BatchName|EffectiveDate|BatchLineSequence|LegislativeDataGroupName|ConsolidationSetName|PayrollName|AssignmentNumber|ElementName|TaxReportingUnitName

MERGE|BalanceAdjustmentLine|BalanceAdjustmentsFITtoSITBatch_09092025_V1|2025/07/10|1|US Legislative Data Group|US Consolidation Group|Biweekly|E10020|Adjust Resident State|Good Samaritan Hospital

METADATA|BalanceAdjustmentValue|BatchName|EffectiveDate|BatchLineSequence|LegislativeDataGroupName|ConsolidationSetName|PayrollName|ElementName|InputValueName|EntryValue

MERGE|BalanceAdjustmentValue|BalanceAdjustmentsFITtoSITBatch_09092025_V1|2025/07/10|1|US Legislative Data Group|US Consolidation Group|Biweekly|Adjust Resident State|State|15

2. Importing the HDL Files

Once your .dat files are zipped, follow these steps to load them into the system:

Login to Fusion Applications.

Navigate to My Client Groups > Data Exchange.

Select Import and Load Data under the HCM Data Loader section.

Click Import File and choose Local File as the source.

Upload the BalanceAdjustmentHeader zip file first and click Submit.

Wait for the Header process to complete fully before submitting the BalanceAdjustmentLine zip file.

3. Executing the Balance Adjustment Flow

After the data is successfully loaded into the stage tables, you must run the payroll flow to apply the adjustments to the worker balances.

Navigation & Submission

Navigate to My Client Groups > Payroll > Submit a Flow.

Search for the flow Adjust Multiple Balances.

Enter a unique Payroll Flow name (e.g., Adjust Multiple Balances E10020).

Select the Batch Name that corresponds to your HDL load (e.g., BalanceAdjustmentsFITtoSITBatch_09092025_V1).

Set the Reason (e.g., Corrective Action) and click Submit.

Monitor the Payroll Checklist until the "Adjust Multiple Balances" task shows a status of Completed. Once finished, navigate to the worker's records to verify that the balances have been updated correctly.

Loading Bulk Values into a Value Set in Oracle Fusion

Managing large volumes of values in a Value Set can be time-consuming if done manually. Oracle Fusion provides a convenient way to bulk load values using the File Import/Export functionality. This guide walks you through the step-by-step process.

Step 1: Prepare the CSV File

Create a .csv file with the following structure:

Format Requirements:

• File type: .csv
• Delimiter: |
• Columns:

ValueSetCode|Value|Description|EnabledFlag

XXAB_VALUE_SET|AB Care Mgmt|AB Care Management|Y

XXAB_VALUE_SET|AB Heart Dx|AB Heart Center Diagnostics|Y

XXAB_VALUE_SET|AB Strat Plan|AB Strategic Planning|Y

XXAB_VALUE_SET|AB Hou Adm|AB Houston Administration|Y

XXAB_VALUE_SET|AB A&I|AB A&I Services|Y

Make sure:

• The ValueSetCode matches exactly with the existing Value Set
• EnabledFlag is either Y or N

Step 2: Upload File Using File Import/Export

1. Login to the Oracle Fusion Application (Prod)
2. Navigate to:
   Tools → File Import and Export  
3. Click on the ( + ) icon to upload a file

1. Select your prepared .csv file
2. Enter the account as:
   hcm/dataloader/import
3. Click Save and Close

Step 3: Navigate to Value Sets

1. Go to Setup and Maintenance
2. Search for the task: Manage Value Sets

Step 4: Import the Values

1. Select the Value Set
2. Click on Actions → Import
3. Enter:
   • File Name: Same as uploaded in Step 2
   • Account: hcm/dataloader/import
4. Click Upload

Step 5: Monitor the Upload

• The system will process the file and display the progress
• Once completed, a log file will be generated

Hire Date Update Recommendations

For Worker: This is straight forward when there are no changes to the Hire assignment.

The hire date is the start date of a work relationship. To change the hire date, provide the new date on the NewStartDate attribute of the Work Relationship component. You can load just the work relationship record. The employment terms and assignment records are maintained automatically.

This example changes the hire date for an existing worker:

 
SET PURGE_FUTURE_CHANGES N
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|NewStartDate
MERGE|WorkRelationship|<SRC_OWNER>|<SRCID>|<PERSONID>|<LEGALEMPLOYER>|<NewStartDate>

For Pending Worker: As this is Pending Worker, we will have to follow these options.

Step 1: Cancel the Incorrect Pending Worker Record

To remove the pending hire with the incorrect start date, use HCM Data Loader (HDL) as follows:
1. Create a .dat file specifically to cancel the existing pending work relationship.
2. Use the MERGE instruction on the WorkRelationship business object with the CANCEL action code.
E.g.: METADATA|WorkRelationship|PersonNumber|LegalEmployerName|ActionCode
MERGE|WorkRelationship|<PersonNumber>|<LegalEmployerName>|CANCEL
3. Load this HDL file. This will cancel the future-dated work relationship.

Step 2: Re-create the New Hire with the Correct Start Date

1. After successful cancellation of the incorrect record, proceed with hiring the employee.

2. Use your original HDL file for the new hire, but ensure the start date is correct.

3. Load the corrected HDL file. Since the conflicting record has been removed, the new hire process should now complete successfully.