In many Oracle Fusion HCM implementations, organizations need a flexible way to store and retrieve reference data that does not exist in standard HCM objects. Examples include incentive thresholds, eligibility rules, regional allowances, or custom parameters used in calculations. This is where UDT Query in Oracle Fusion HCM becomes extremely useful.
A User Defined Table (UDT) allows organizations to store custom data in a structured table format within Oracle Fusion HCM. Once data is stored in a UDT, it can be accessed using UDT queries, typically through Fast Formulas, Payroll calculations, Absence policies, or other configurable business logic.
From a consultant’s perspective, understanding how to design and use UDT queries properly is critical because many enterprise implementations rely on them for configurable business rules without customization.
In this guide, we will explore how UDT Query in Oracle Fusion HCM works, how it is configured, and how consultants use it in real-world implementations.
What is a UDT Query in Oracle Fusion HCM?
A UDT Query refers to retrieving data from a User Defined Table (UDT) within Oracle Fusion HCM using business logic components such as:
Fast Formulas
Payroll calculations
Absence eligibility rules
Compensation calculations
Benefits eligibility logic
A UDT acts like a configurable lookup table that stores business parameters.
For example:
| Grade | Location | Allowance Amount |
|---|---|---|
| G1 | India | 5000 |
| G1 | USA | 10000 |
| G2 | India | 7000 |
Instead of hardcoding values inside a formula, the system retrieves values dynamically using a UDT Query.
This approach provides:
Flexible configuration
Easier maintenance
No code changes when business values change
Key Features of UDT Queries in Oracle Fusion HCM
User Defined Tables combined with UDT queries offer several powerful capabilities for HCM implementations.
1. Flexible Data Storage
Organizations can store custom parameters such as:
Incentive rates
Allowances
Bonus percentages
Absence accrual rates
Tax thresholds
2. Dynamic Data Retrieval
Values are retrieved dynamically using Fast Formulas.
Example:
This ensures business rules remain configurable.
3. Effective-Dated Data
UDT rows support effective start and end dates, allowing historical tracking.
Example:
| Grade | Allowance | Effective Start Date |
|---|---|---|
| G1 | 5000 | Jan 2024 |
| G1 | 6000 | Jan 2025 |
The system automatically retrieves the correct value based on the transaction date.
4. Integration with Multiple Modules
UDT queries can be used across different HCM modules:
Payroll
Absence Management
Benefits
Compensation
5. No Customization Required
Since UDTs are standard configuration components, they support future upgrades and SaaS best practices.
Real-World Business Use Cases
In real Oracle Fusion HCM implementations, UDT queries are commonly used to externalize business rules.
Use Case 1 – Location-Based Allowances
A multinational company provides different housing allowances based on employee location.
Example UDT:
| Location | Allowance |
|---|---|
| India | 4000 |
| UK | 8000 |
| USA | 10000 |
The payroll formula retrieves the value using a UDT query during salary calculation.
Use Case 2 – Absence Accrual Rules
Organizations often calculate leave accrual based on employee grade.
Example UDT:
| Grade | Monthly Leave Accrual |
|---|---|
| G1 | 1.5 |
| G2 | 2 |
| G3 | 2.5 |
The Absence Accrual Fast Formula queries the UDT to determine accrual rate.
Use Case 3 – Performance Bonus Calculation
A company calculates bonus percentages based on performance rating.
Example:
| Rating | Bonus Percentage |
|---|---|
| 5 | 20% |
| 4 | 15% |
| 3 | 10% |
During compensation calculation, a Fast Formula retrieves the bonus value using a UDT query.
Configuration Overview
Before using UDT queries, the following configurations must be completed.
| Configuration Step | Purpose |
|---|---|
| Define User Defined Table | Create custom table |
| Define Columns | Define table attributes |
| Add Rows | Store business data |
| Create Fast Formula | Query the table |
| Assign Formula | Use within payroll or absence |
All configurations are done using Functional Setup Manager (FSM).
Step-by-Step Configuration in Oracle Fusion
Step 1 – Navigate to User Defined Tables
Navigation:
Navigator → My Client Groups → Payroll → User Defined Tables
or
Navigator → Setup and Maintenance → Search Task → Manage User Defined Tables
Step 2 – Create a User Defined Table
Click Create and enter the following values.
| Field | Example Value |
|---|---|
| Table Name | LOCATION_ALLOWANCE_TABLE |
| Legislative Data Group | Vision LDG |
| Effective Start Date | 01-Jan-2025 |
Explanation of important fields:
Table Name
Defines the name used in Fast Formula queries.
Legislative Data Group
Specifies the legal entity group where the table applies.
Effective Date
Determines when the configuration becomes active.
Click Save.
Step 3 – Define Table Columns
Next, configure table columns.
Example structure:
| Column Name | Data Type |
|---|---|
| LOCATION | Character |
| ALLOWANCE_AMOUNT | Number |
Columns represent the attributes stored in the table.
Step 4 – Add Table Rows
Add values inside the UDT.
Example:
| LOCATION | ALLOWANCE_AMOUNT |
|---|---|
| India | 4000 |
| UK | 8000 |
| USA | 10000 |
Steps:
Click Manage Rows
Enter values
Save
These rows become the data retrieved through UDT queries.
Using UDT Query in Fast Formula
Once the table is created, it can be accessed inside Fast Formula.
Navigation:
Navigator → My Client Groups → Payroll → Fast Formulas
Create a formula.
Example formula:
DEFAULT FOR LOCATION IS ‘India’
ALLOWANCE = GET_TABLE_VALUE
(
‘LOCATION_ALLOWANCE_TABLE’,
‘ALLOWANCE_AMOUNT’,
LOCATION
)
RETURN ALLOWANCE
Explanation:
| Element | Description |
|---|---|
| LOCATION_ALLOWANCE_TABLE | UDT name |
| ALLOWANCE_AMOUNT | Column being retrieved |
| LOCATION | Input parameter |
This formula dynamically retrieves the allowance based on employee location.
Testing the Setup
After configuration, consultants should always validate the UDT query.
Example Test Scenario
Employee Details:
| Attribute | Value |
|---|---|
| Employee Name | John Smith |
| Location | India |
Test Steps:
Run payroll calculation
Trigger Fast Formula
Check returned value
Expected Result:
Allowance = 4000
Validation Checks
Consultants should verify:
Correct table name
Column name accuracy
Input parameter values
Effective date alignment
Architecture and Technical Flow
A typical UDT query flow works as follows:
Employee transaction occurs (Payroll or Absence)
Fast Formula is triggered
Formula executes GET_TABLE_VALUE function
System searches UDT rows
Matching row is identified
Value returned to calculation engine
Flow Summary:
↓
Fast Formula
↓
UDT Query
↓
Retrieve Value
↓
Return Result
This design allows configurable business rules without changing formulas.
Common Implementation Challenges
During implementations, consultants often encounter issues with UDT queries.
1. Incorrect Column Names
If column names in Fast Formula do not match the UDT configuration, the formula fails.
Example:
Even minor spelling differences cause errors.
2. Effective Date Issues
If UDT rows are not effective on the transaction date, the system may return null values.
Always verify:
Effective Start Date
Effective End Date
3. Incorrect Input Parameters
UDT queries rely on correct input values.
Example:
If the UDT stores INDIA but the formula passes India, results may fail depending on configuration.
4. Legislative Data Group Mismatch
The table must belong to the correct LDG used by payroll.
Best Practices Used by Oracle Consultants
Experienced consultants follow several best practices when implementing UDT queries.
1. Use Clear Naming Conventions
Example:
UDT_BONUS_PERCENTAGE
UDT_LEAVE_ACCRUAL_RATES
This helps maintain clarity in large implementations.
2. Avoid Hardcoding Business Values
Always store variable parameters in UDT instead of Fast Formula.
Example:
❌ Hardcoded bonus = 10%
✔ Bonus retrieved from UDT
3. Maintain Documentation
Document:
UDT name
Columns
Purpose
Used formulas
This simplifies maintenance during future releases.
4. Use Effective Dating Strategically
Instead of modifying existing rows, create new rows with updated effective dates.
This maintains audit history.
5. Validate UDT Queries During Testing
Always test with multiple scenarios:
Different grades
Different locations
Future effective dates
Summary
Understanding UDT Query in Oracle Fusion HCM is essential for building flexible and maintainable business logic in enterprise implementations.
User Defined Tables allow organizations to store configurable business data, while UDT queries retrieve this information dynamically using Fast Formulas and other components.
Key benefits include:
No hardcoding of business rules
Easier maintenance
Configurable payroll and absence calculations
Support for effective dating
Reusable across multiple modules
In real-world Oracle Fusion implementations, UDT queries are widely used for allowances, bonus calculations, accrual rules, eligibility logic, and compensation structures.
Consultants who master this feature can significantly improve the flexibility and scalability of HCM solutions.
For deeper product documentation and technical references, refer to Oracle’s official documentation:
https://docs.oracle.com/en/cloud/saas/index.html
Frequently Asked Questions (FAQ)
1. What is a User Defined Table in Oracle Fusion HCM?
A User Defined Table (UDT) is a configurable table used to store custom business parameters such as allowances, bonus percentages, or accrual rates. These values can be accessed using Fast Formulas or other business logic.
2. Where are UDT queries typically used?
UDT queries are commonly used in:
Payroll calculations
Absence accrual formulas
Compensation calculations
Benefits eligibility rules
3. What function is used to retrieve UDT values?
The most commonly used Fast Formula function is:
It retrieves data from a User Defined Table based on input parameters.