Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

Posted by Amar Alam at 02:49 4 comments

 Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

-------------------------------------------------------------------------------
-- Query to find Parameters and Value Sets associated with a Concurrent Program
-------------------------------------------------------------------------------

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  =  'IFFCO HRMS : Costing Summary Report - Consolidated'  -- 
 ORDER BY fdfcuv.column_seq_num;