SQL Query to get the Valueset and Lookup Values
SQL Query to get the Valueset and Lookup Values

 664 total views

We will try to extract Valueset and Lookup values using SQL Query.

Independent Valueset details:

SELECT ffvs.flex_value_set_name
 ,ffvs.description set_description
 ,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 = 'ABCD_BRANCH_VS'

Table based Valueset details:

SELECT ffvs.flex_value_set_id
 ,ffvs.flex_value_set_name
 ,ffvs.description set_description
 ,ffvs.validation_type
 ,ffvt.value_column_name
 ,ffvt.meaning_column_name
 ,ffvt.id_column_name
 ,ffvt.application_table_name
 ,ffvt.additional_where_clause
FROM fnd_flex_value_sets ffvs
 ,fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
 AND ffvs.flex_value_set_name = 'HRX_US_FED_TYPE_EMPLOYMENT'

Lookup details:

SELECT lookup_type
 ,lookup_code
 ,meaning
 ,description
 ,tag
FROM fnd_lookup_values flv
WHERE flv.enabled_flag = 'Y'
 AND flv.LANGUAGE = 'US'
 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active)
 AND NVL(TRUNC(end_date_active), TO_DATE('4712/12/31', 'YYYY/MM/DD'))

These SQLs will become handy when you have requirement to extract this information.

If you have any questions, please feel free to reach out to me by posting in comments section.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply