Valueset

All posts on Valuesets

Using Table based Valueset when DBI is unavailable

  • Post published:April 21, 2020
  • Post Category:HCM Extract
  • Post Comments:0 Comments
  • Post last modified:May 15, 2020

When we develop integrations using HCM Extracts, we normally come across many scenarios where in we are unable to get all the attributes required using the Database Items (DBIs) for the integration using a single/multiple User Entities (UEs). If you are new to HCM Extracts, I would suggest you to read through the below articles on HCM Extracts Basics of…

Continue Reading Using Table based Valueset when DBI is unavailable

SQL Query to get the Valueset and Lookup Values

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_descriptionFROM fnd_flex_value_sets ffvs ,fnd_flex_values ffv ,fnd_flex_values_tl ffvtWHERE 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_clauseFROM fnd_flex_value_sets ffvs ,fnd_flex_validation_tables ffvtWHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id AND ffvs.flex_value_set_name = 'HRX_US_FED_TYPE_EMPLOYMENT' Lookup details:SELECT…

Continue Reading SQL Query to get the Valueset and Lookup Values