Basics of Valuesets in Fusion HCM

• What is Valueset?
• Types of Valuesets?
• How to Valueset
• Use Cases for Valuesets
• SQL Queries to pull data from Valuesets

What is a Valueset?

A value set is a group of valid values that can be used to assign valid values to a flexfield segment or do some validations in Fast Formulas or retrieve some content in HCM Extracts where the data base item is not available.

Task name to be used to Create Valuesets is “Manage Valuesets” under Setup and Maintenance.

Types of Valuesets?

There are five types of Valuesets and they are listed below:

• Format Only
• Independent
• Dependent
• Subset
• Table

Format Only Valueset:

The format only validation type enables end users to enter any value, as long as it meets your specified formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set.
Ex:

Independent Valueset:

Independent Valueset is used to validate input against a list that isn’t stored in an application table, and not dependent on a subset of another independent value set.
Ex: Valueset Details:

Values:

Dependent Valueset:

The available values in a dependent list and the meaning of a given value depend on which value was selected for the independently validated segment. For every independent value from parent value set, we can add one/many values in the dependent value set. For example, the valid holidays depend on which country you are in
Ex: Valueset Details:

Values:

Subset Valueset:

Subset Valueset is used to limit the list of values based on a Valueset. However instead of creating a new Valueset, we can re-use the existing independent Valueset for values and choose the values that you want to create for the subset Valueset.
Ex: Valueset Details:

Values:

Table Valueset:

Table Valueset is used when the values you want to use are already maintained in an application table, such as a table of vendor names. Specify the table column that contains the valid value. You can optionally specify the description and ID columns, a WHERE clause to limit the values to use for your set, and an ORDER BY clause.
Ex:

Use Cases for Valuesets:

• All types of Valuesets can be used to validate the data for the flex field segments (DFF, EFF and KFF),
• Only Table type Valuesets are used to get data from the application tables and then they can be used in the fast formulas when there are NO database items defined.
• Only Table type Valuesets are used to get data in HCM Extracts when database items are not defined.

SQL Queries to pull data from Valuesets:

FND_FLEX_VALUE_SETS (holds value set property related details)
FND_FLEX_VALUES (holds actual values)

Independent Valueset Details SQL:

SELECT ffvs.flex_value_set_id
 ,ffvs.flex_value_set_name
 ,ffvs.description set_description
 ,ffvs.validation_type
 ,ffv.flex_value_id
 ,ffv.flex_value
 ,ffvt.flex_value_meaning
 ,ffvt.description value_description
FROM fnd_flex_value_sets ffvs
 ,fnd_flex_values ffv
 ,fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
 AND ffv.flex_value_id = ffvt.flex_value_id
 AND ffvt.LANGUAGE = USERENV('LANG')
 AND TRUNC(SYSDATE) BETWEEN NVL(ffv.start_date_active, trunc(SYSDATE))
  AND NVL(ffv.end_date_active, trunc(SYSDATE + 1))
 AND ffvs.flex_value_set_name = 'XX_VALIDATION_VS'

Table Based Valueset Details SQL:

SELECT ffvs.flex_value_set_name
 ,('select ' || ffvt.value_column_name || (CASE
    WHEN ffvt.meaning_column_name IS NOT NULL
     THEN ', ' || ffvt.meaning_column_name
    END) || (CASE WHEN ffvt.ID_COLUMN_NAME IS NOT NULL
     THEN ', ' || ffvt.ID_COLUMN_NAME
    END) || ' from ' || ffvt.application_table_name
 || ' ' || ffvt.additional_where_clause) SQL_Query
FROM fnd_flex_value_sets ffvs
 ,fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id

Tip: Table based Valuesets are an interesting feature and will be used widely across HR and Payroll Modules. They are used in Flexfields, Payroll Flows, Fast Formulas, HCM Extracts, etc.