In Oracle E-Business Suite, Value Sets are the foundational components that drive data validation and control input values in Flexfields, Concurrent Programs, and Report Parameters. They act as gatekeepers, ensuring that only valid and meaningful data is entered into the system.

This article offers a comprehensive look at Value Sets: their purpose, types, advanced features, and best practices—complete with real-world examples.

Value Sets in Oracle Apps R12

What is a Value Set?

A Value Set in Oracle EBS is a container of pre-defined or dynamically validated values. It determines what values a user can enter for a particular Segment of a Flexfield or a Parameter in a concurrent program.

Think of it as a data filter applied on user input—whether the input is manually entered or selected from a list.

Where are Value Sets Used?

Value Sets are commonly used in:

• Descriptive Flexfields (DFFs)
• Key Flexfields (KFFs) – like Accounting, Item Categories
• Concurrent Program Parameters
• Oracle Forms or Custom PL/SQL Programs

Functionality of Value Sets

Types of Value Sets (with Examples)

1. None

• Purpose: No validation—accepts any value.
• Example: Notes field or free-text comments.

Value Set Name: ANY_TEXT_INPUT
Validation Type: None
Format Type: Char, Max Length: 60

Navigate to: Application Developer > Application > Validation > Set

2. Independent

• Purpose: Uses a fixed list of values defined manually.
• Example: List of Departments: HR, FINANCE, IT.

Value Set: DEPARTMENTS
Format Type: Char, Max Length: 60
Validation Type: Independent, Upper Case Only: True
Values: HR, IT, FINANCE, LEGAL

Navigate to: Application Developer > Application > Validation > Set

Navigate to: Application Developer > Application > Validation > Values

3. Dependent

• Purpose: Child value list depends on parent selection.
• Example: State list depends on Country.

Parent Value Set: COUNTRY (Independent)
Child Value Set: STATE (Dependent)

Navigate to: Application Developer > Application > Validation > Set

Navigate to: Application Developer > Application > Validation > Values

4. Table

• Purpose: Dynamically fetch values from a table using a SQL query.
• Example: List of active suppliers.

SELECT vendor_name, vendor_id FROM po_vendors WHERE enabled_flag= 'Y'

Navigate to: Application Developer > Application > Validation > Set

5. Special

A Special Value Set allows flexfield-specific features. Instead of being predefined, it validates data dynamically using flexfield routines (e.g., security rules, dependent LOVs). It’s often used in GL accounting flexfields where advanced validation is required.

Purpose

Used in Flexfield segments to call PL/SQL logic via a “special” validation routine.

When to Use

• For Key Flexfields (KFF) like GL Accounting where combinations must obey rules.
• When validation is not just a simple list but depends on flexfield logic.

Step-by-Step Example: Creating a Special Value Set

Step 1: Navigate

• Application Developer → Application → Validation → Sets

Step 2: Define Value Set

• Value Set Name: XXINV_GL_SPECIAL_VS
• Description: Special Value Set for GL Segment
• Validation Type: Special
• Security Type: Hierarchical (optional)
• Format Type: Character
• Save the definition.

Steps 3: Attach to a Flexfield Segment

For example, in GL Accounting Flexfield Setup:

• Segment → Company Code
• Assign Value Set → XXINV_GL_SPECIAL_VS

Oracle will validate based on the flexfield rules you define (cross-validation rules, security rules, or PL/SQL validation routines).

6. Pair

A Pair Value Set is used when you need to capture a range of values. It always has two segments: a low value and a high value. Oracle automatically ensures that the low value ≤ high value, making it useful in reporting parameters or accounting flexfields.

• Purpose: Holds a low and high value, often used in report parameters.
• Example: Date range filter or account range.

7. Translatable Independent / Dependent

• Purpose: Like Independent/Dependent, but stores values in multiple languages.

Key Difference: Pair vs. Special Value Set

📘 Real-World Example

Create a value set for selecting Inventory Organizations in a concurrent program.

Value Set Name: INV_ORGS
Validation Type: Table
Table: INV_ORGANIZATIONS
WHERE Clause: organization_type = 'INVENTORY'
Column: organization_code (Displayed), organization_id (Returned)

Value Set Name: INV_ORGS
Validation Type: Table
Table: INV_ORGANIZATIONS
WHERE Clause: organization_type = 'INVENTORY'
Column: organization_code (Displayed), organization_id (Returned)

Used In:

A parameter for an inventory movement report.

Common Use Cases

Hidden & Lesser-Known Facts

1. Table Value Sets can use views: You can point to secure or custom views to avoid exposing raw tables.
2. Bind Variables: Table value sets can use :$FLEX$ to dynamically bind parent values or context.
   • Example: WHERE location_id = :$FLEX$.LOCATION_ID
3. Using $FLEX$ for Context-Sensitive DFFs: A game changer for customizing DFFs.
4. Special Value Sets can trigger PL/SQL functions for advanced validations or transformations.
5. Multi-Org Filtering: Add org_id filters to value sets used in MOAC-enabled environments.
6. Security Rules can override value set behavior without modifying the value set itself.

Best Practices

• ✅ Use Independent value sets for short, rarely-changing lists.
• ✅ Prefer Table value sets for dynamic, large datasets.
• ✅ Always include WHERE conditions in table-based value sets to improve performance.
• ✅ Avoid None validation unless absolutely necessary.
• ✅ Use $FLEX$ binds for context-based filtering.
• ✅ Create reusable value sets for consistency across modules.

How to Create a Value Set: Step-by-Step (Quick Guide)

1. Navigate to: Application Developer > Application > Validation > Set
2. Define the Name, Description, and Validation Type
3. Specify Format, Max Length, Uppercase, etc.
4. Add LOV SQL, if using a Table-based set
5. Save → Use in Concurrent Program Parameters or Flexfield Segments

Bonus: Sample Table Value Set SQL

SELECT customer_name, customer_id
  FROM hz_cust_accounts
 WHERE status = 'A'
   AND ROWNUM < 100

Common Errors & Debugging

Conclusion

Oracle EBS Value Sets are more than just dropdown lists—they’re robust validation mechanisms that control data entry, enhance consistency, and support business logic across modules. Value sets adds more flexibility and validation than lookups. By mastering Value Sets, you unlock deeper customization potential in Oracle EBS with minimal code.

Whether you’re a Functional Consultant defining Flexfields or a Developer building custom reports, understanding value sets is critical to clean, efficient, and user-friendly Oracle applications.