Value Set Query in Oracle Fusion HCM
Introduction
Value Set Query in Oracle Fusion HCM is one of the most powerful techniques used by consultants to dynamically populate field values using SQL logic. In many real-world Oracle Fusion implementations, business users expect fields such as department, cost center, job codes, or custom attributes to display values based on business rules rather than static lists. Query-based value sets make this possible by allowing administrators to retrieve values from database tables using SQL.
Within Oracle Fusion Cloud Applications (26A release), value sets are widely used across modules such as HCM, ERP, and SCM. In HCM specifically, they appear in areas like:
Descriptive Flexfields (DFF)
Extensible Flexfields (EFF)
Profile Options
Lookups
Payroll elements
Compensation setups
Instead of manually maintaining value lists, consultants often implement Value Set Query logic to dynamically fetch values from tables like departments, locations, jobs, or custom integration tables.
From an implementation standpoint, understanding how to design and configure query-based value sets is essential for both functional and technical consultants. This article explains the concept, architecture, configuration steps, and real-world implementation scenarios based on practical project experience.
What is Value Set Query in Oracle Fusion?
A Value Set Query in Oracle Fusion HCM refers to a table-based value set where the list of values is retrieved using an SQL query from database tables or views.
Unlike independent value sets where values are manually entered, query-based value sets dynamically fetch data from database objects.
For example:
Instead of manually entering department names in a list, the value set query can retrieve them directly from the departments table.
Example SQL query:
FROM hr_all_organization_units_f
WHERE sysdate BETWEEN effective_start_date AND effective_end_date
This query retrieves active departments from the HCM organization table.
The result is displayed in the application as a list of values (LOV) when the user selects the field.
Types of Value Sets in Oracle Fusion
Before understanding query-based value sets, it is important to understand the different types available.
| Value Set Type | Description |
|---|---|
| Independent | Values manually defined |
| Dependent | Values dependent on another value set |
| Table | Values retrieved from database tables |
| Format Only | Used for validation formats |
Value Set Query belongs to the Table Value Set category.
Table value sets provide the most flexibility and are widely used in real implementations.
Key Features of Query-Based Value Sets
Query-based value sets provide several advantages during Oracle Fusion implementations.
Dynamic Data Retrieval
Values are automatically retrieved from database tables.
Example:
If a new department is created, it automatically appears in the LOV without manual updates.
Data Validation
Value sets validate user input against database data.
Example:
If a user enters an invalid department code, the system prevents saving.
Filtering Capability
Queries can apply filters to show only relevant data.
Example:
Only show active departments.
Reusability
A value set can be reused across multiple fields.
Example:
A single Department Value Set can be used in multiple flexfields.
Integration Friendly
Query-based value sets are frequently used in integrations where external systems populate custom tables.
Example:
Integration loads project codes into a table, and value set displays them.
Real-World Business Use Cases
Query-based value sets are extremely common in enterprise implementations.
Use Case 1 – Department Selection
Scenario:
An organization wants users to select departments from a list.
Instead of manually maintaining the list, a query-based value set retrieves departments from the organization table.
Result:
Whenever a new department is created, it automatically appears in the field.
Use Case 2 – Cost Center Validation
Scenario:
Finance requires cost centers to be validated before submitting expense reports.
Solution:
A value set query retrieves cost centers from the GL code combinations table.
Result:
Users can only select valid cost centers.
Use Case 3 – Project Code Selection
Scenario:
Projects are maintained in a custom integration table.
Solution:
A value set query retrieves project codes dynamically.
Result:
Project values remain synchronized with external project management systems.
Architecture of Value Set Query
The architecture of query-based value sets involves several components.
↓
SQL Query
↓
Table Value Set
↓
Flexfield Segment
↓
User Interface LOV
When a user clicks the LOV icon:
System executes SQL query
Data retrieved from database
Results displayed in LOV
User selects value
This process happens in real time.
Common Tables Used in Value Set Queries
During implementations, consultants frequently use the following HCM tables.
| Table | Purpose |
|---|---|
| PER_ALL_PEOPLE_F | Employee information |
| PER_JOBS_F | Job information |
| HR_ALL_ORGANIZATION_UNITS_F | Departments |
| PER_LOCATIONS | Location information |
| PER_GRADES_F | Grade definitions |
Example query retrieving job names:
FROM per_jobs_f
WHERE sysdate BETWEEN effective_start_date AND effective_end_date
Prerequisites for Configuring Value Set Queries
Before creating query-based value sets, ensure the following prerequisites exist.
Identify Data Source
Consultants must determine which table contains the required data.
SQL Knowledge
Basic SQL knowledge is required to design the query.
Access to Setup and Maintenance
User must have access to configuration tasks.
Security Considerations
Only supported tables or views should be used.
Oracle recommends using secured views whenever possible.
Step-by-Step Configuration of Value Set Query
Now let’s walk through the actual configuration process used in Oracle Fusion 26A.
Step 1 – Navigate to Manage Value Sets
Navigation path:
Navigator → Setup and Maintenance → Manage Value Sets
Search for:
Step 2 – Create a New Value Set
Click Create.
Enter the following values.
| Field | Example |
|---|---|
| Value Set Code | DEPARTMENT_VS |
| Description | Department List |
| Module | HCM |
Value Set Type:
Step 3 – Configure Table Value Set
Enter the following configuration.
| Field | Example |
|---|---|
| From Clause | hr_all_organization_units_f |
| Value Column Name | organization_id |
| Meaning Column | name |
| ID Column | organization_id |
Step 4 – Add WHERE Clause
Filter only active departments.
Example:
This ensures only active records appear.
Step 5 – Save the Configuration
Click:
The value set is now available.
Using Value Set in Flexfields
Value sets are commonly attached to Descriptive Flexfields.
Example:
Adding a department field to employee records.
Navigation:
Navigator → Setup and Maintenance → Manage Descriptive Flexfields
Search for:
Steps:
Create segment
Assign value set
Deploy flexfield
Once deployed, the field will display the LOV populated by the query.
Testing the Value Set Query
After configuration, testing is required.
Example Test Scenario
Employee record update.
Steps:
Open employee record
Navigate to flexfield section
Click LOV icon
Expected Result:
List of departments appears.
Validation Checks
| Check | Expected Result |
|---|---|
| LOV appears | Yes |
| Values retrieved | Active departments |
| Selection allowed | Yes |
| Data saved successfully | Yes |
Common Implementation Challenges
Even experienced consultants encounter several issues with value set queries.
Query Performance Issues
Large tables may cause slow LOV loading.
Solution:
Use filters in WHERE clause.
Incorrect Column Mapping
If value or meaning columns are incorrect, LOV may not display properly.
Solution:
Verify table structure.
Security Restrictions
Some tables may not be accessible.
Solution:
Use secured views.
Flexfield Deployment Issues
Changes may not appear until flexfield is deployed.
Solution:
Redeploy flexfield.
Best Practices from Real Oracle Projects
Based on real implementations, consultants follow several best practices.
Always Use Effective Date Filters
Example:
Prevents inactive data from appearing.
Avoid Large Unfiltered Queries
Large datasets slow down LOV performance.
Reuse Value Sets
Instead of creating multiple value sets for the same data.
Document Query Logic
Complex queries should be documented for maintenance.
Test in Lower Environments
Always validate in development or test environments.
Frequently Asked Questions (FAQ)
1. What is the difference between independent and table value sets?
Independent value sets contain manually entered values, while table value sets retrieve values dynamically using SQL queries.
2. Can value set queries join multiple tables?
Yes. SQL joins can be used to retrieve data from multiple tables.
Example:
FROM hr_all_organization_units_f d,
per_locations l
WHERE d.location_id = l.location_id
3. Can value sets be used across multiple modules?
Yes. A value set can be reused across HCM, ERP, and SCM modules if applicable.
Summary
Query-based value sets are an essential configuration component in Oracle Fusion HCM. They allow administrators and consultants to dynamically retrieve values from database tables, ensuring that user interface fields always display current and valid data.
Key benefits include:
Dynamic data retrieval
Reduced manual maintenance
Improved validation
Seamless integration with database tables
High flexibility for enterprise implementations
In real Oracle Fusion projects, value set queries are frequently used for departments, locations, cost centers, job codes, project numbers, and integration-driven data. Understanding how to design efficient queries and configure table value sets is a critical skill for Oracle consultants.
For additional technical details and official documentation, refer to the Oracle documentation library:
https://docs.oracle.com/en/cloud/saas/human-resources/index.html