In Oracle Fusion HCM, data validation and controlled data entry are critical for maintaining clean and consistent enterprise HR data. One of the most powerful mechanisms used to control user input across the application is the Value Set Table in Oracle Fusion HCM.
Value sets define what values users are allowed to enter in specific fields. When organizations implement HR solutions such as employee onboarding, job management, compensation, or payroll processing, ensuring standardized values becomes extremely important. Without controlled value lists, users might enter inconsistent data such as different spellings for departments, job codes, or locations.
In real-world Oracle Fusion HCM implementations, consultants frequently configure table value sets when data must be dynamically retrieved from database tables. Unlike independent value sets where values are manually entered, table value sets fetch values from database queries, making them extremely flexible.
This article explains Value Set Tables in Oracle Fusion HCM, their purpose, configuration, real-world scenarios, and implementation best practices used by Oracle consultants in enterprise deployments.
What is a Value Set Table in Oracle Fusion HCM?
A Value Set Table is a type of value set that retrieves allowed values dynamically from a database table or view using a SQL query.
Value sets are used across multiple Oracle Fusion components such as:
Descriptive Flexfields (DFF)
Extensible Flexfields (EFF)
Key Flexfields
Lookup validations
HCM configuration screens
When a table value set is configured, Oracle Fusion executes a SQL query to retrieve valid values from a database table.
Simple Example
Suppose HR wants a custom field to select an employee’s work location. Instead of manually entering locations in the value set, the system can pull data from a location table such as:
The SQL query used in the value set may look like:
FROM HR_LOCATIONS_ALL
This allows the value set to automatically show all valid locations stored in the system.
This approach ensures:
Real-time data retrieval
No duplication of master data
Easy maintenance
Types of Value Sets in Oracle Fusion
Before diving deeper into table value sets, it is important to understand the different value set types available in Oracle Fusion.
| Value Set Type | Description |
|---|---|
| Independent | Values are manually entered and maintained |
| Dependent | Values depend on another value set |
| Table | Values retrieved dynamically from database tables |
| Translatable Independent | Supports multilingual values |
| Format Only | Used only for formatting input |
Among these, Table Value Sets are most commonly used in technical and advanced functional implementations.
Key Features of Value Set Table in Oracle Fusion HCM
1. Dynamic Data Retrieval
Values are fetched directly from database tables using SQL queries.
Example:
Job Codes
Departments
Locations
Business Units
If data changes in the table, the value set automatically reflects the change.
2. Real-Time Validation
Table value sets validate user input against the query results.
For example:
If a user tries entering a location code that does not exist in the query results, Oracle Fusion rejects it.
3. Supports Descriptive Flexfields (DFF)
Table value sets are heavily used in DFF segments.
Example:
A custom DFF segment called Project Code may retrieve values from a project table.
4. Supports WHERE Clause Filtering
Consultants can apply filtering logic.
Example:
FROM hr_departments
WHERE status = ‘ACTIVE’
Only active departments will appear.
5. Supports Validation Types
Oracle allows validation such as:
Exact match
No validation
Partial match
This ensures strict control over user inputs.
Real-World Business Use Cases
During enterprise HCM implementations, Table Value Sets are extremely common. Below are some practical examples used in projects.
Use Case 1: Employee Project Assignment
An organization wants HR to assign employees to active projects only.
Instead of maintaining project lists manually, a table value set retrieves projects from the project management table.
Query example:
FROM PJF_PROJECTS_ALL
WHERE project_status = ‘ACTIVE’
When HR assigns a project, only active projects appear in the dropdown.
Use Case 2: Cost Center Selection
Finance teams want employees to choose valid cost centers when submitting expense reports.
The value set retrieves cost centers from the finance table.
Example table:
Query:
FROM GL_COST_CENTER_VIEW
This ensures employees cannot select invalid cost centers.
Use Case 3: Location Mapping for Workforce Deployment
Large global companies often have hundreds of office locations.
Instead of manually maintaining values, consultants create a table value set that retrieves locations from:
This allows HR to assign employees to valid global office locations.
Configuration Overview
Before configuring a Value Set Table in Oracle Fusion HCM, certain prerequisites must be completed.
Required Setup
| Setup Area | Description |
|---|---|
| SQL Query Design | Query that retrieves required values |
| Table/View Access | Ensure correct database object |
| Data Type Definition | Character / Number / Date |
| Maximum Size | Length of field |
| Value Validation Type | Exact / Partial |
Step-by-Step Configuration in Oracle Fusion
Below is the typical process consultants follow to configure a table value set.
Step 1 – Navigate to Manage Value Sets
Navigation Path:
Search Task:
This page allows creation and management of all value sets.
Step 2 – Create a New Value Set
Click Create and enter the following details.
| Field | Example Value |
|---|---|
| Value Set Code | XX_EMP_LOCATION_VS |
| Description | Employee Location Value Set |
| Module | Human Capital Management |
| Validation Type | Table |
Step 3 – Define Data Type
Choose the appropriate data type.
Example:
| Field | Value |
|---|---|
| Data Type | Character |
| Maximum Length | 30 |
The data type must match the database column type used in the SQL query.
Step 4 – Configure Table Validation Details
After selecting Validation Type = Table, additional fields appear.
| Field | Description |
|---|---|
| FROM Clause | Table or view name |
| Value Column | Column storing the value |
| Meaning Column | Column shown in dropdown |
| ID Column | Optional identifier |
| WHERE Clause | Optional filter |
Example Configuration
| Parameter | Value |
|---|---|
| FROM Clause | HR_LOCATIONS_ALL |
| Value Column | LOCATION_CODE |
| Meaning Column | LOCATION_NAME |
| WHERE Clause | ACTIVE_STATUS = ‘Y’ |
This configuration ensures only active locations appear.
Step 5 – Save the Value Set
Click Save and Close.
The value set is now available for use in:
Descriptive Flexfields
Extensible Flexfields
Lookup validations
Using the Value Set in a Descriptive Flexfield
Most implementations use value sets within DFF segments.
Example scenario:
Adding a Project Code field to Employee Assignment.
Navigation:
Search Task:
Select:
Create a new segment and attach the table value set.
Testing the Setup
Once configuration is complete, consultants must validate the functionality.
Test Scenario
Create or update an employee assignment.
Navigation:
Steps:
Search for an employee
Edit assignment
Navigate to the custom DFF section
Open the dropdown list
Expected Result
The dropdown should display values retrieved from the configured SQL query.
Example:
| Location Code | Location Name |
|---|---|
| BLR | Bangalore |
| NYC | New York |
| LON | London |
If new records are inserted into the location table, they appear automatically.
Common Implementation Challenges
Even experienced consultants encounter issues while configuring table value sets.
1. Incorrect SQL Query
The most common problem is an invalid query.
Example issues:
Missing columns
Incorrect table names
Invalid WHERE clauses
2. Performance Issues
Large tables may cause slow dropdown loading.
Example:
Querying tables with millions of records.
Solution:
Use filters
Create views
Restrict results
3. Data Type Mismatch
If the value set expects a character value but the column is numeric, validation errors occur.
4. Security Restrictions
Some database tables may not be accessible in value sets.
In such cases consultants create database views.
Best Practices Used by Oracle Consultants
Use Database Views Instead of Direct Tables
Instead of querying base tables, create optimized views.
Example:
Benefits:
Better performance
Controlled data exposure
Always Filter Data
Avoid queries returning thousands of rows.
Example:
Use Meaning Column Carefully
Meaning columns should display business-friendly descriptions rather than technical codes.
Test with Multiple Scenarios
Always test with:
Valid values
Invalid values
Edge cases
Document SQL Logic
Every value set query should be documented in the implementation guide.
Summary
The Value Set Table in Oracle Fusion HCM is a powerful configuration mechanism that enables dynamic data validation across the application. Instead of maintaining static lists of values, organizations can retrieve data directly from database tables using SQL queries.
In real-world Oracle Fusion implementations, table value sets are widely used in Descriptive Flexfields, integrations, and custom validations. They help maintain data integrity, improve user experience, and reduce maintenance effort.
When implemented correctly with optimized queries and proper filtering, table value sets become an essential part of scalable Oracle Fusion configurations.
For deeper technical reference and official documentation, refer to Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html
Frequently Asked Questions (FAQ)
1. What is the difference between Independent and Table Value Sets?
Independent value sets store values manually within the value set itself, while table value sets retrieve values dynamically from database tables using SQL queries.
2. Where are table value sets commonly used in Oracle Fusion HCM?
They are commonly used in:
Descriptive Flexfields
Extensible Flexfields
Integration validations
Custom configuration fields
3. Can table value sets use complex SQL queries?
Yes. Consultants can include WHERE clauses, joins, and filters to retrieve specific values. However, queries must be optimized to avoid performance issues.