Query to find Concurrent Programs associated with a Value Set

Following query finds all the concurrent program(s) that are associated with a value set. Thus, before you want to make any change to that value set, you should be able to find what concurrent programs will be affected by your modification, so you can notify the concerned parties.

Change the value set name (ffvs.flex_value_set_name, see below) according to your search criteria. In this example, I used "CONV_TYPES" as my value set name.

-------------------------------------------------------------------------------

-- Query to find Concurrent Programs associated with a Value Set

-------------------------------------------------------------------------------

SELECT fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value",

       fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND ffvs.flex_value_set_name           LIKE  '%CONV_TYPES'  -- <change it>

 ORDER BY fcpl.user_concurrent_program_name;