Technical

SQL Query for Extracting UDT Data in Oracle Fusion

Learn how to extract User Defined Table (UDT) data in Oracle Fusion using SQL queries. Complete guide with practical examples and best practices.

SQL QueriesUDTUDT Tables
Oracle Fusion HCM

SQL Query for Extracting UDT Data in Oracle Fusion

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

Understanding how to query and extract UDT data is essential for anyone working with Oracle Fusion customizations, reporting, or troubleshooting fast formulas.

What Are User Defined Tables (UDTs)?

A User Defined Table (UDT) is a table structure you create to store semi-static data that your fast formulas or customizations reference. Unlike traditional database tables, UDTs are managed through the Oracle Fusion interface and are effective-dated for version control.

Common Uses of UDTs:

  • Salary components and rates - Store allowances, deductions, and rates
  • Lookup values - Define custom lists for dropdowns
  • Calculation tables - Store thresholds or calculation matrices
  • Tax data - Maintain tax rates and brackets
  • Configuration data - Store business rules and parameters

Use Case: When You Need This SQL Query

This query is useful when you want to:

✅ Audit UDT values - Track what data is stored in your UDTs ✅ Validate UDT data - Ensure correct values are being used in fast formulas ✅ Extract data for reporting - Pull UDT data for analysis or dashboards ✅ Debug formula results - Understand which UDT values are causing incorrect calculations ✅ Migrate data - Export UDT data for moving to another instance

The SQL Query: Extract UDT Data

Here’s the complete, production-ready SQL query to extract UDT data:

SELECT FFUTL.USER_TABLE_NAME AS UDT_NAME
     , FFURT.ROW_NAME AS ROW_NAME
     , FFUCT.USER_COLUMN_NAME AS COLUMN_NAME
     , FFUIF.VALUE AS COLUMN_VALUE
     , FFURF.EFFECTIVE_START_DATE
     , FFURF.EFFECTIVE_END_DATE

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;

Understanding the Query Components

Table Structures Explained

FF_USER_TABLES & FF_USER_TABLES_TL

  • Purpose: Identifies the User Defined Table and fetches the table name
  • TL suffix: Translation table that stores names in different languages
  • Role: Provides the UDT name and descriptive information

FF_USER_ROWS_F & FF_USER_ROWS_TL

  • Purpose: Retrieves row names (keys) defined inside the UDT
  • F suffix: Indicates fact table with effective dating
  • Role: Contains the row identifiers and effective dates

FF_USER_COLUMNS & FF_USER_COLUMNS_TL

  • Purpose: Fetches column names defined for the UDT
  • TL suffix: Stores column names in different languages
  • Role: Defines the structure and columns of the UDT

FF_USER_COLUMN_INSTANCES_F

  • Purpose: Stores the actual values entered against each row and column combination
  • F suffix: Effective-dated fact table
  • Role: Contains the actual data values in the UDT

Key Conditions Explained

Effective Date Filter:

AND TRUNC(SYSDATE) BETWEEN FFURF.EFFECTIVE_START_DATE AND FFURF.EFFECTIVE_END_DATE

This ensures you only retrieve currently active rows and values. If you need historical data, remove this condition or modify the dates.

Language Filter:

AND FFUTL.LANGUAGE = 'US'

Specifies the language for UDT names. Change ‘US’ to your required language code (e.g., ‘FR’ for French, ‘DE’ for German).

Table Name Filter:

AND FFUTL.USER_TABLE_NAME = 'Your_Table_Name'

Replace ‘Your_Table_Name’ with the actual UDT name you want to query.

Example Output

When you run this query against your Oracle Fusion database, you’ll get results similar to this:

UDT_NAMEROW_NAMECOLUMN_NAMECOLUMN_VALUE
GCS_LEAVE_RATESCASUAL_LEAVERATE2.5
GCS_LEAVE_RATESCASUAL_LEAVECAP12
GCS_LEAVE_RATESSICK_LEAVERATE2.0
GCS_LEAVE_RATESSICK_LEAVECAP8

Common Modifications

Get Only Active UDT Values

The query already filters for current dates. To see all versions (including historical), remove or modify the effective date conditions.

Query All UDTs (Without Filtering by Name)

Remove this line:

AND FFUTL.USER_TABLE_NAME = 'Your_Table_Name'

This returns data from all UDTs in your system.

Include Effective Dates

The modified query above already includes effective dates. This is useful for understanding when values are active.

Get UDT Structure Without Values

SELECT DISTINCT FFUTL.USER_TABLE_NAME
     , FFUCT.USER_COLUMN_NAME
FROM FF_USER_TABLES FFUT
     , FF_USER_TABLES_TL FFUTL
     , FF_USER_COLUMNS FFUC
     , FF_USER_COLUMNS_TL FFUCT
WHERE FFUT.USER_TABLE_ID = FFUTL.USER_TABLE_ID
  AND FFUC.USER_TABLE_ID = FFUT.USER_TABLE_ID
  AND FFUCT.USER_COLUMN_ID = FFUC.USER_COLUMN_ID
  AND FFUTL.LANGUAGE = 'US'

Best Practices for UDT Queries

✅ Always use effective date filters - Ensures you get the right version of data ✅ Specify language explicitly - Avoid confusion with multilingual data ✅ Join through TL tables - Get user-friendly names instead of just IDs ✅ Use TRUNC(SYSDATE) - Compare dates without time component ✅ Document your UDTs - Maintain a list of all UDTs and their purposes ✅ Test queries thoroughly - Validate results before using in reports

Conclusion

Understanding how User Defined Tables are structured in Oracle Fusion Cloud database is essential for consultants, developers, and technical analysts. This SQL query provides a clear, structured way to:

  • Extract UDT data for analysis
  • Validate data in fast formulas
  • Troubleshoot calculation issues
  • Create automated reports
  • Migrate UDT data between instances

Master this query and you’ll significantly improve your ability to troubleshoot, debug, and manage Oracle Fusion customizations!

💡 Ready to Master Oracle Fusion SQL?

Learning SQL query writing for Oracle Fusion is a game-changer for your technical skills. The more you understand the data model, the better you can:

  • Create accurate reports
  • Troubleshoot issues efficiently
  • Build robust customizations
  • Optimize system performance

🚀 Keep Learning

  • Subscribe to GrowCloudSkills for more SQL queries and technical guides
  • Follow us on LinkedIn for daily Oracle Fusion tips
  • Watch our tutorials on YouTube for visual learning

About GrowCloudSkills

GrowCloudSkills is your trusted partner for mastering Oracle Fusion Cloud Applications through:

✅ Practical SQL queries ready to use in your environment ✅ Real-world examples from actual implementations ✅ Step-by-step guides for complex technical topics ✅ Best practices based on consulting experience ✅ Community support from experienced professionals

Whether you’re a junior consultant learning the ropes or a senior architect designing complex solutions, we’re here to help you succeed.

Connect With Us

Have questions about this query or other Oracle Fusion topics? Drop a comment below or reach out on LinkedIn. We’d love to help!

Happy querying! 🚀