Introduction to User Defined Table (UDT) in Fusion HCM
Introduction to User Defined Table (UDT) in Fusion HCM

 2,187 total views

In this article we will look into details of User Defined Table as known as UDT in Fusion parlance. It is widely used across modules to maintain custom mapping data which can be created/updated by end users.

Want to learn Fusion Technical tools? Checkout this article

  1. What is User-Defined Table?
  2. Where is the UDT Used?
  3. How to Create UDT?
  4. How to Bulk Load data into UDT?
  5. How to use the UDT in BI Report and Fast Formula?
  6. How to Delete data in UDT?
  7. How to Migrate data in UDT to another Instance?

What is User-Defined Table?

User-Defined Table is a table defined in Fusion application which can hold data and can be used to validate or map data in BI Reports/Fast Formula. These tables have a unique structure and are quite different from the traditional RDBMS tables like PER_ALL_PEOPLE_F which we will directly select from to get the data.

As we cannot create new tables in Fusion SaaS application and UDT is the only way to achieve any custom tables. Otherwise we will have to get PaaS license and use the DBCS to create custom tables and OIC to read and load data into it.

Where is the User-Defined Table used?

UDT provides us the flexibility to create a table like structure and is widely used to store mapping data of third party systems

Ex: Vendor ADP might use different company codes and in fusion we might use different legal entities. So we store them in UDT and transform the company and send it over to ADP.

Another example could be a benefits vendor asking for a different plan codes other than the ones which are configured in Fusion application. So we will define the one to one mapping of Benefit plans to Plan Codes and transform the output.

How to Create UDT?

As I said earlier, UDTs have a different structure than the other backend tables, it is not a straight forward to create it.

Let’s understand the UDT creation steps:

  • Create the User-Defined Table Properties
  • Create the Columns for UDT
  • Create Rows for UDT
  • Create Column Instances for UDT
User Defined Table Explanation, how to load, delete , copy and migrate data in fusion hcm oracle hcm cloud fusion user table
User Defined Table Explanation

Column1-4 are the columns on the UDT.
Row1-3 are the Rows on the UDT.
The remaining cells corresponding to Rows and Columns intersection are the Column Instances which specifies the actual values of the table.

Let’s create an UDT in Fusion application for the Legal Entity transformation to ADP Code and then the interface will pick up the transformed values and send it to ADP system.

Navigate to Setup and Maintenance > Manage User-Defined Tables task

Click on the + icon to create a new UDT

Creating User Defined Table Fusion User Table FF_USER_COLUMNS FF_USER_ROWS_F get_table_value oracle apps user tables Oracle hcm cloud
Creating new UDT

Select the Legislative Data Group for which the UDT needs to be created and provide the input date for Effective As of Date(Make sure the date is pretty old in order to avoid any date issues in SQL Query or Fast Formula) and click continue.

oracle fusion hcm user defined tables oracleapps user tables oracle hcm cloud UDT
ff_user_tables ff_user_rows_f ff_user_columns ff_user_columns_tl ff_user_column_instances_f
Input details and click save

Name – Provide the name of the User-Defined Table.
Range or Match – We can either select Range or Match. If the row value is a number and you want the column instance values to vary based on number range then select Range. If you are looking for an exact value then use Match.
Unit of Measure – This defines the Unit of Measure for the Row. It will accept Number/Character/Date as UOM.
Row Title – This is the title of the Row values. It is an optional field however it would be better to provide value to it as it is easy to identity what values are getting stored in the Row values if we see the Title. In the example, I have used ‘LEGAL_ENTITY’.
Column Name -This is the actual Column Name to store the values of mapping values.
Data Type – This is used the specify the data type of the column. Number/Character/Date.
Formula – This is used to validate the column value using Fast Formula. I haven’t used it, you can explore more on it.

Once you enter the above data, you need to save the UDT to proceed further to load the Rows and Column instances.

Adding Row Value Details for user defined table fusion hcm oracle hcm cloud oracleapps user table
Adding Row Value Details

Click on + button for User-Defined Rows and add the sequence as 1 and Row Value of ‘XX Legal Entity’ as we are using Rows as Legal Entities. Click Save.

Now we have created User-Defined Table, added Column and row as well. Now we will try to add the column instance for this row and column combinaton.

Adding column instance to User defined table in fusion hcm oracle hcm cloud
Select the Column and then click Next to add Column Instance

Select the Column and click Next to add Column Instance.

Adding column instance in user defined table fusion hcm oracle hcm cloud udt

Once you click + button, it will prompt for selecting the row for which you want to add the column instance value. Select the row “XX Legal Entity” and click OK.

Enter the Column Instance value and click save to save the UDT in fusion hcm
Enter the Column Instance value and click save

Enter the Column Instance value and click save. Voila.. We are done with creating UDT, adding column, row and column instance. Similarly we can add other rows and column instances as per the requirement.

How to Bulk Load data into UDT?

We have the Payroll Batch Loader (PBL) using which we can load data into UDTs. PBL is getting decommissioned by 20D.

Couple of releases back Oracle has introduced User-Defined Table Business Object in HCM Data Loader. So going forward we need to use the HDL files to load data into UDTs.

Sample HDL file to load the User Defined Table, Columns, Rows and Column Instances:

METADATA|UserDefinedTable|UserTableCode|LegislationCode|LegislativeDataGroupId|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableId|UserTableName|LegislativeDataGroupName
MERGE|UserDefinedTable|XXHR_UDT|||M|N|LEGAL_ENTITY||XXHR_UDT|US LDG
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName
MERGE|UserDefinedTableColumn|ADP_COMPANY|T|ADP_COMPANY|XXHR_UDT|US LDG
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|RowHighRange|RowLowRangeOrName|UserTableCode|LegislativeDataGroupName|RowName
MERGE|UserDefinedTableRow|1|4712/12/31|2000/01/01||XX Legal Entity|XXHR_UDT|US LDG|XX Legal Entity
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|RowLowRangeOrName|UserTableCode
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|XX_COMPANY|US LDG|XX Legal Entity|100018|XXHR_UDT

How to use the UDT in SQL Query?

We can use the seeded UDT package in the BI Report SQL to get the values from the UDT

select FF_USER_TABLES_PKG.GET_TABLE_VALUE_ENT(1, SYSDATE, 'XXHR_UDT', 'COLUMN1', ROW_VALUE) from dual

Let’s look at the parameters passed to this package:
1 – This should always be 1 i guess. I don’t see any documentation on this package, however with investigation i got some hands on this package.
SYSDATE – This is the date as of which you want to get the data from UDT.
‘XXHR-UDT’ – This is the User Defined Table Name for which you want to pull data from.
‘COLUMN1’ – This is the column for which you want to get the Column Instance Value.
‘ROW_VALUE’ – This the Row value of the UDT for which you want to fetch the column instance value.

SQL Query to get UDT details:

SELECT r.row_low_range_or_name ROW_VALUE 
,v.value COLUMN_INSTANCE_VALUE 
FROM ff_user_tables t ,
ff_user_rows_f r ,
ff_user_columns c ,
ff_user_columns_tl c_tl ,
ff_user_column_instances_f v 
WHERE t.base_user_table_name = 'XXHR_UDT' 
AND t.user_table_id = r.user_table_id 
AND c.user_column_id = c_tl.user_column_id 
AND t.user_table_id = c.user_table_id 
AND c.user_column_id = v.user_column_id 
AND r.user_row_id = v.user_row_id 
AND c_tl.user_column_name = 'COLUMN1'

How to use the UDT in Fast Formula?

We can also use the UDT in Fast Formula to get the column instance value by passing the UDT Name, COLUMN Name and ROW Value. HR_GET_TABLE_VALUE is the function that we need to use in Fast Formula and this function can be used in Fast Formula of any type. Here is the sample:

l_result = HR_GET_TABLE_VALUE
('XXHR_UDT' /* UDT Name */ 
,'COLUMN1'  /* UDT Column */ 
,'ROWVALUE' /* UDT Row name */ 
,' ')       /* Default Value */

How to Delete data in UDT?

We need to navigate to Manage User-Defined Tables task and open up the UDT. We need to manually delete the UDT in the reverse sequence of creation.

  1. Delete Column Instance Values
  2. Delete Rows
  3. Delete Columns
  4. Delete the UDT table

There is no easy way to delete the UDT in a single click of a button. If there are many rows and columns in UDT, then it is tough to do it manually. A BI Report can be built to get the output out of the UDT tables into an HDL file format and then it can be used to delete the data from UDT easily.

How to Migrate data in UDT to another Instance?

As far as I know there isn’t an automated way to migrate the UDT data from one instance to another. Similar to delete, a BI Report can be built which produces the Output as HDL file and it can be used to migrate data from one Instance to another. I don’t see any other seeded way using which we can migrate the UDTs.

Summary: We can create User Defined Table and use it to store mapping/ transformation data that we can use in SQL Query or Fast Formula to transform data and send it to third party systems.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 2 Comments

  1. John White

    Can you share using a UDT inside a HDL Transformation FF

Leave a Reply