Oracle HCM: Utilization of Table Value Sets in EIT in Migration to Redwood Pages

Business Challenge

Oracle Fusion HCM customers extensively use Independent Value Sets attached to flexfield segments in Extra Information Types (EIT). These value sets provide both a code and description, offering users a clear selection mechanism with meaningful context.

In the traditional ADF-based EIT screens, users experience an intuitive interface where they select a code and see the corresponding description displayed as a read-only field below. This design provides clarity and prevents confusion during data entry.

However, after migrating to Oracle’s new Redwood user experience, organizations encounter a significant usability issue: Oracle renders each Independent Value Set field as two separate updatable columns – one for the code and one for the description. This creates confusion for end users and can lead to data inconsistency issues.

Example of the Problem:

• ADF EIT Screen: User selects “EMP001” and sees “Full-Time Employee” as read-only description
• Redwood EIT Screen: User sees two editable fields – one for code, one for description, leading to potential data entry errors

Solution Overview

The solution involves creating a new Table Value Set that queries the existing Independent Value Set data from Oracle’s standard tables and presents it in a format optimized for Redwood’s rendering engine.

This approach:

1. Maintains the existing data integrity of your Independent Value Sets
2. Provides a proper single-field selection experience in Redwood
3. Eliminates the confusing dual-column display
4. Requires minimal configuration changes

Implementation Details

Step 1: Identify Your Current Independent Value Sets

First, identify all Independent Value Sets currently used in your EIT configurations that are experiencing the Redwood rendering issue.

Navigate to Setup and Maintenance > Manage Value Sets and document the names of Independent Value Sets attached to your EIT flexfield segments.

Step 2: Create a New Table Value Set

1. Navigate to Setup and Maintenance > Manage Value Sets
2. Click Create Value Set
3. Configure the new value set:
   • Value Set Code: CUSTOM_WRAPPER_TABLE_VS (or descriptive name)
   • Description: Add meaningful description for your table value set
   • Module: Select Global Human Resources
   • Validation Type: Select Table
   • Value Data Type: Select Character (or match your original value set)
   • Security enabled: Check if you need data security (optional)

Step 3: Configure the Table Value Set Query

Based on the Create Value Set screen configuration, you need to populate the following fields:

FROM Clause:

fnd_flex_values_vl v, fnd_flex_value_sets s

Value Column Name: DESCRIPTION

Description Column Name: DESCRIPTION

ID Column Name: FLEX_VALUE

Enabled Flag Column Name: ENABLED_FLAG

WHERE Clause:

v.flex_value_set_id = s.flex_value_set_id
and s.flex_value_set_name = 'YOUR_VALUE_SET_NAME'

ORDER BY Clause:

v.flex_value

Complete Query Structure (for reference):

select v.flex_value, v.description, v.enabled_flag
from fnd_flex_values_vl v, fnd_flex_value_sets s 
where v.flex_value_set_id = s.flex_value_set_id
and s.flex_value_set_name = 'YOUR_VALUE_SET_NAME'
order by v.flex_value

Critical Best Practice – Enabled Flag Mapping:

Do NOT filter by enabled_flag = 'Y' or date filters in the WHERE clause. Simply map the enabled_flag column and let Oracle handle the filtering automatically.

Why this approach is essential:

✅ Preserves Historical Data Readability: Records with existing disabled values will continue to display their meaningful descriptions instead of showing cryptic codes

✅ Controls New Value Selection: When users create new records or modify existing ones, disabled values will NOT appear in the dropdown selection list

✅ Dual Behavior Implementation: Oracle intelligently handles both scenarios – displaying meanings for old disabled values while hiding them from new value selection

Example Scenario:

• Employee has an old record with value “TEMP001” (Temporary Contract) that was disabled last year
• With proper enabled_flag mapping:
  • Viewing existing records: User sees “Temporary Contract” description when viewing the historical record
  • Creating new records: “Temporary Contract” option doesn’t appear in the dropdown, preventing new assignments
• If you incorrectly filter by enabled_flag = ‘Y’: Historical records would show only “TEMP001” code without meaningful description, creating confusion

This dual behavior ensures:

• Data integrity for historical records remains intact
• Business rules are enforced for new data entry
• User experience is optimal in both viewing and editing scenarios

Step 4: Update EIT Configuration

1. Navigate to Setup and Maintenance > Manage Extra Information Types
2. Edit the EIT that contains the problematic flexfield segment
3. Update the Value Set assignment from the Independent Value Set to your new Table Value Set
4. Save and deploy the configuration

Step 5: Test in Redwood Interface

1. Navigate to a Redwood-enabled EIT screen
2. Verify that the field now displays as a single dropdown
3. Confirm that users see descriptions but the system stores codes
4. Test with different user roles to ensure proper security behavior
5. Verify that historical disabled values show descriptions while being excluded from new selections

Advanced Configuration Options

For Multi-Language Environments: The fnd_flex_values_vl view automatically handles language filtering, so no additional language conditions are needed in your query.

For Date-Effective Values: As mentioned in best practices, avoid filtering by start/end dates in your WHERE clause:

-- DO NOT add these filters:
-- and trunc(sysdate) between nvl(v.start_date_active, trunc(sysdate))
--                        and nvl(v.end_date_active, trunc(sysdate))

Let Oracle handle date-effective filtering automatically through the enabled_flag mapping.

Migration Best Practices

1. Test Thoroughly: Always test the new Table Value Set in a development environment before promoting to production
2. Backup Configurations: Document your original Independent Value Set configurations before making changes
3. User Training: Inform users about the improved interface experience
4. Performance Monitoring: Monitor query performance, especially for large value sets
5. Maintain Consistency: Apply this pattern consistently across all affected EIT configurations
6. Enabled Flag Best Practice: Never filter by enabled_flag in WHERE clause – map the column and let Oracle handle the filtering logic

Benefits of This Solution

✅ Improved User Experience: Single dropdown selection eliminates confusion

✅ Data Integrity: Maintains existing code-description relationships

✅ Redwood Compatibility: Optimized for Oracle’s modern interface

✅ Minimal Impact: Requires no changes to existing data or business processes

✅ Scalable Approach: Can be applied to multiple Independent Value Sets

✅ Future-Proof: Aligns with Oracle’s Redwood design principles

✅ Smart Value Handling: Disabled values remain readable in historical data but hidden from new selections

Conclusion

The migration to Oracle’s Redwood user experience brings many benefits, but it also requires adjustments to existing configurations. By converting Independent Value Sets to Table Value Sets using the approach described above, organizations can maintain the intuitive user experience while leveraging Redwood’s modern interface capabilities.

This solution ensures that your EIT configurations continue to provide clear, user-friendly data entry options while preventing the confusion caused by dual-column rendering in Redwood pages. The intelligent enabled flag mapping preserves historical data readability while enforcing current business rules for new data entry.

Need assistance with your Oracle HCM Redwood migration?

Contact CID Solutions today for expert guidance on optimizing your Fusion HCM configurations for the Redwood experience!