User Defined Tables (UDTs) play a crucial role in Oracle Fusion Cloud, especially in HCM Fast Formulas, where they are used to store configurable business values such as rates, allowances, limits, and lookup-like data.

In this blog, we will look at a practical SQL query that helps fetch UDT name, row name, column name, and values from Oracle Fusion Cloud database tables.

Use Case

This query is useful when you want to:

• Audit User Defined Table values
• Validate UDT data used in Fast Formulas
• Extract UDT data for reporting or analysis
• Debug incorrect formula results caused by UDT values

SELECT FFUTL.USER_TABLE_NAME AS UDT_NAME
     , FFURT.ROW_NAME AS EXACT
     , FFUIF.VALUE AS AMOUNT_VALUE
     , FFUCT.USER_COLUMN_NAME
FROM FF_USER_TABLES FFUT
     , FF_USER_TABLES_TL FFUTL
     , FF_USER_ROWS_F FFURF
     , FF_USER_ROWS_TL FFURT
     , FF_USER_COLUMNS FFUC
     , FF_USER_COLUMNS_TL FFUCT
     , FF_USER_COLUMN_INSTANCES_F FFUIF
WHERE 1=1
  AND FFUT.USER_TABLE_ID = FFUTL.USER_TABLE_ID
  AND FFUTL.USER_TABLE_NAME = 'Your_table_name'
  AND FFUTL.LANGUAGE = 'US'
  AND FFURF.USER_TABLE_ID = FFUT.USER_TABLE_ID
  AND FFURT.USER_ROW_ID = FFURF.USER_ROW_ID
  AND FFURT.LANGUAGE = 'US'
  AND FFUC.USER_TABLE_ID = FFUT.USER_TABLE_ID
  AND FFUCT.USER_COLUMN_ID = FFUC.USER_COLUMN_ID
  AND FFUCT.LANGUAGE = 'US'
  AND FFUIF.USER_ROW_ID = FFURF.USER_ROW_ID
  AND FFUIF.USER_COLUMN_ID = FFUC.USER_COLUMN_ID
  AND TRUNC(SYSDATE) BETWEEN FFURF.EFFECTIVE_START_DATE AND FFURF.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN FFUIF.EFFECTIVE_START_DATE AND FFUIF.EFFECTIVE_END_DATE
ORDER BY FFURT.ROW_NAME;

Explanation of the Query

• FF_USER_TABLES / FF_USER_TABLES_TL
  Identifies the User Defined Table and fetches the table name in the required language.
• FF_USER_ROWS_F / FF_USER_ROWS_TL
  Retrieves row names (keys) defined inside the UDT.
• FF_USER_COLUMNS / FF_USER_COLUMNS_TL
  Fetches column names defined for the UDT.
• FF_USER_COLUMN_INSTANCES_F
  Stores the actual values entered against each row and column combination.
• Effective Date Filters
  The TRUNC(SYSDATE) condition ensures only currently active rows and values are returned.

Conclusion

Understanding how User Defined Tables are stored internally in Oracle Fusion Cloud helps consultants and developers troubleshoot Fast Formulas efficiently. This SQL query provides a clear and structured way to extract UDT data with effective-dated accuracy.

If you are working extensively with Oracle Fusion HCM, mastering such queries can significantly improve your debugging and reporting skills.