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.
- What is User-Defined Table?
- Where is the UDT Used?
- How to Create UDT?
- How to Bulk Load data into UDT?
- How to use the UDT in BI Report and Fast Formula?
- How to Delete data in UDT?
- How to Migrate data in UDT to another Instance?
Table of Contents
- 1 What is User-Defined Table?
- 2 Where is the User-Defined Table used?
- 3 How to Create UDT?
- 4 How to Bulk Load data into UDT?
- 5 How to use the UDT in SQL Query?
- 6 How to use the UDT in Fast Formula?
- 7 How to Delete data in UDT?
- 8 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
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
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.
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.
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.
Select the Column and click Next to add Column Instance.
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. 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.
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.
- Delete Column Instance Values
- Delete Rows
- Delete Columns
- 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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM