Create a User-Defined Table through HDL and fetch UDT data through query

This article aims to discuss the creation of a User-Defined Table using HDL. In Fusion Applications, a User-Defined Table is referred to as UDT. Before we begin creating a UDT, let's talk a bit about User-Defined Tables.

User-Defined Table is a specialized table defined within the Fusion Applications framework, specifically designed to hold and manage various types of data that are tailored to meet the unique needs of an organization. These tables allow users to create custom data structures that can accommodate specific business requirements, enabling them to capture and store information that may not be available in standard system tables.

By leveraging User-Defined Tables, organizations can enhance their data management capabilities, streamline processes, and ensure that all necessary data is collected and maintained in an organized manner. Additionally, these tables can be integrated with other components of the Fusion Applications ecosystem, allowing for seamless data flow and reporting across different modules, ultimately supporting better decision-making and operational efficiency.

Basic steps for creating UDT through UI:

Navigate to Setup and Maintenance >User-Defined Tables Task

1. Creating a UDT

2. Defining UDT properties

3. Creating UDT Columns

4. Creating UDT Rows

5. Creating column instances for UDT

Enter the column Instance value and then click on Save.

We must create the UDT in the Application before generating the HDL file for it.

After clicking the save button, submit.

Once the UDT is created on the application, proceed to create the HDL file.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName

MERGE|UserDefinedTable|TEST_GRADE|M|T||TEST_GRADE|SA Legislative Data Group

METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName|FormulaName

MERGE|UserDefinedTableColumn|A|T|A|TEST_GRADE|SA Legislative Data Group|

METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|RowHighRange|RowLowRangeOrName|UserTableCode|LegislativeDataGroupName|RowName

MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01||1st Grade|TEST_GRADE|SA Legislative Data Group|1st Grade

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|RowLowRangeOrName|UserTableCode

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|150|SA Legislative Data Group|Domestic|1st Grade|TEST_GRADE

The HDL file mentioned above contains 4 sections

The first section is designated for the basic details of UDT

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName

MERGE|UserDefinedTable|TEST_GRADE|M|T||TEST_GRADE|SA Legislative Data Group

The 2nd section is utilized for generating a Table Column

METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName|FormulaName

MERGE|UserDefinedTableColumn|A|T|A|TEST_GRADE|SA Legislative Data Group|

The third section is utilized for generating a Table Row

METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|RowHighRange|RowLowRangeOrName|UserTableCode|LegislativeDataGroupName|RowName

MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01||1st Grade|1st Grade |SA Legislative Data Group|1st Grade

The fourth section is utilized for generating a Table Column Instance

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|RowLowRangeOrName|UserTableCode

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|150|SA Legislative Data Group|A|1st Grade|TEST_GRADE

SQL query to retrieve UDT DATA

Select

fur.ROW_LOW_RANGE_OR_NAME,fur.ROW_NAME,to_number(fuci.VALUE)

From

ff_user_tables_vl fut ,ff_user_columns_vl fuc ,ff_user_rows_vl fur ,ff_user_column_instances_f fuci

Where

fut.user_table_name= ‘TEST_GRADE’

AND fuc.USER_COLUMN_NAME=' TEST_GRADE '

AND fut.user_table_id = fuc.user_table_id

AND fut.user_table_id = fur.user_table_id

AND fuc.user_column_id = fuci.user_column_id

AND fur.user_row_id = fuci.user_row_id

AND TRUNC(SYSDATE) BETWEEN fur.effective_start_date AND fur.effective_end_date

AND TRUNC(SYSDATE) BETWEEN fuci.effective_start_date AND fuci.effective_end_date