Find Value Sets

#=======================================================================

# Technology   : ORACLE

# Author            : Lokanadham Thandlam

#=======================================================================

---1) Sql Query to get Value sets by Concurrent program name as a parameter

SELECT fcpl.user_concurrent_program_name, fcp.concurrent_program_name,

       fav.application_short_name, fav.application_name, fav.application_id,

       fdfcuv.end_user_column_name, fdfcuv.form_left_prompt prompt,

       fdfcuv.enabled_flag, fdfcuv.required_flag, fdfcuv.display_flag,

       fdfcuv.flex_value_set_id, ffvs.flex_value_set_name,

       flv.meaning default_type, fdfcuv.DEFAULT_VALUE

  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,

       fnd_application_vl fav

 WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id

   AND fcpl.user_concurrent_program_name =

          NVL

             (:concurrent_programme_name, fcpl.user_concurrent_program_name)

                                          -- 'XXX MTL Metallised Roll Sticker'

   AND fcpl.LANGUAGE = 'US'

   AND fav.application_id = fcp.application_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 flv.LANGUAGE(+) = USERENV ('LANG');

---------------------------------------------------------OR-----------------------------------------------------------------------

SELECT   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",

         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"

    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 fcpl.user_concurrent_program_name =

            NVL

               (:cp_name, fcpl.user_concurrent_program_name)