Loading Global User-Defined Tables (UDT) Without an LDG using HCM Data Loader

Before we get into loading a User-Defined Table (UDT) without a Legislative Data Group (LDG) using HCM Data Loader, let's first understand what a UDT is and why it's an important feature in Oracle HCM.

What is a User-Defined Table (UDT)?
A User-Defined Table (UDT) in Oracle Fusion HCM is a flexible way to store business-specific data that can be accessed whenever it's needed. You can think of it as a spreadsheet built into Oracle HCM, where you define your own lookup values instead of hardcoding them into Fast Formulas or integrations.

Rather than embedding values directly in your logic, you store them in a UDT and retrieve them whenever required. This makes your solutions easier to maintain and update over time.

A User-Defined Table is made up of three main components:

Columns: These represent the different types of information you want to store.
Rows: These act as the lookup keys or criteria used to identify a specific record.
Column Instances: These are the actual values stored where a row and a column intersect.

Why Use User-Defined Tables?
If you've ever had to update hardcoded values in a Fast Formula, you already know how time-consuming and error-prone it can be. User-Defined Tables solve this problem by keeping your business data separate from your application logic.

Some of the key benefits include:

Simpler maintenance: Business values can be updated without modifying the Fast Formula itself.
Dynamic lookups: Functions such as GET_TABLE_VALUE allow you to retrieve values from the table based on the lookup key you provide.
Greater flexibility: UDTs can store text, numbers, ranges, and other business-specific values, making them suitable for a wide variety of use cases.

By using UDTs, you can build solutions that are easier to manage, more scalable, and adaptable to changing business requirements.

Why use HCM Data Loader to create the User-Defined Table?
Creating a simple User-Defined Table (UDT) with just a few rows and a single column is easy through the Oracle Fusion user interface. The process is straightforward, for a local table, select the appropriate Legislative Data Group (LDG) from the dropdown list. If you are creating a global table, simply leave the LDG field empty.

While this approach works well for small datasets, it becomes impractical in real-world business scenarios. Organizations often maintain hundreds or even thousands of records for items such as payroll mappings, regional allowances, or other reference data. Entering such large datasets manually through the application is both time-consuming and prone to errors.

This is where HCM Data Loader (HDL) becomes the preferred solution. HDL enables you to package large volumes of reference data into a single data file and import everything in one operation, making bulk data loading both efficient and reliable. However, moving from the user interface to HDL introduces an important challenge, particularly when working with global tables where the Legislative Data Group (LDG) field is intentionally left blank.

To begin, we will create a standard User-Defined Table (UDT) with a Legislative Data Group (LDG) using HCM Data Loader (HDL). For this, we will use the UserDefinedTable.dat business object.

Below are the Metadata and Merge lines for a sample UDT.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName
MERGE|UserDefinedTable|ORA_US_RELOCATION_ALLOWANCE_UDT|M|T|ORA_US_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|LegislativeDataGroupName|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_2

Once the data loader completes successfully, we can navigate to the User-Defined Tables section to verify that the table structure, column parameters, and row entries were correctly generated within our Legislative Data Group.

Now, let's try to create a global UDT by using the exact same METADATA and MERGE line structures, but with one change, we will remove the Legislative Data Group references entirely from the data file.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2

While you are trying to load the dat file, you will encounter the below issue.

To resolve this parent-child relationship issue, the deployment process must be divided into two sequential load stages.

First Load: Create a UserDefinedTable.dat file by including the UserDefinedTable, UserDefinedTableColumn, and UserDefinedTableRow METADATA and MERGE records together in a single HDL file. This load establishes the table structure without creating any UserDefinedTableColumnInstance records.

Second Load: After the first load completes successfully, create a separate UserDefinedTable.dat file containing only the UserDefinedTableColumnInstance METADATA and MERGE records.

First Load:
Below are the METADATA and MERGE lines.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||HRC_SQLLOADER|RELOCATION_ALLOWANCE_TABLE
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName|UserTableId(SourceSystemId)|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||RELOCATION_ALLOWANCE_TABLE|HRC_SQLLOADER|RELOCATION_ALLOWANCE_COLUMN
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableId(SourceSystemId)|RowName|RowLowRangeOrName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_1|GRADE_1||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_2|GRADE_2||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW2

We have added multiple METADATA fields to overcome the parent-child relationship issue.

Second Load:
For the second load pass to work, your data file must reference the exact records generated during the first step. Because we lack an LDG to use as a user-key qualifier, we must rely on database surrogate IDs. This means before building your column instance file, you need to run a quick BI Publisher SQL query to extract the newly generated UserColumnId and UserRowId values. Adding these unique system identifiers into your second file establishes the exact relationship required by the data loader engine.

Below is the SQL Query to get the UserColumnId and UserRowId.

select 
    t.base_user_table_name,
    r.user_row_id,
    r.row_low_range_or_name,
    c.user_column_id,
    c.base_user_column_name
from 
    ff_user_tables t,
    ff_user_rows_f r ,
    ff_user_columns c
where 
    t.base_user_table_name = 'ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT'
    and t.user_table_id = r.user_table_id
    and t.user_table_id = c.user_table_id

Below are the METADATA and MERGE lines for second load.

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|SourceSystemOwner|SourceSystemId|UserColumnId|UserRowId
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL1|300000118166578|300000118166576
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL2|300000118166578|300000118166577

With both load passes complete, let’s navigate back to the application to verify that our global table structure and data instances have been created properly.

Splitting your load into a two-step process and leveraging BI Publisher to extract those specific surrogate keys is the most reliable workaround to handle global UDT layouts. I hope this saves you time on your next Global UDT Creation.