Wednesday, July 6, 2016

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;

Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3rbFkzfm99W3IaJGwoQkAwEZx78x6L_yr8omXH7l36xIaFwJ7qi6q47r3_OgJfu-_EgmrWp2ClqvBAt2kh1Nfwzglkj6HXDRLTMrb1uxQTDiGKsB9qsE2svANqUzukLtKTA83yMigivE/s640/conc_prog_value_sets.png


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  =  'XX AR Conversion Program'  -- <change it>
 ORDER BY fdfcuv.column_seq_num;

Description: https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdLkUPO8AtLzNESDlxA2S41si8NXwjXtgdWYbGdtljKTVXaUmHnFh8TfuoIs-9Q8PzGOWcqY8x4Jz7IBFxGgDNeezwB7Js-uRz221Bb4z7o08l_Yj8MQwhm6SSsEvW87t5tDQkUIZJ_-k/s640/value_sets_conc_prog.png


No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...