[EBS] SQL Query to get Request Set Stages and Parameter Details

 

 Use the below query to get Request Set Stages and Parameter Details

 

SELECT frs.user_request_set_name

    , frss.display_sequence

    , fcpt.user_concurrent_program_name

    , fx.executable_name

    , fx.execution_method_code

    , (   select meaning  FROM apps.fnd_lookups a

          where lookup_type like 'CP_EXECUTION_METHOD_CODE'

          and lookup_code= fx.execution_method_code

      ) executable_type

    , fx.execution_file_name

    , form_left_prompt

    , cp_param.default_type param_default_type

    , cp_param.default_value param_default_value

    , NVL ( (SELECT default_value

               FROM apps.fnd_request_set_program_args set_param

              WHERE set_param.descriptive_flexfield_name    = cp_param.descriptive_flexfield_name

                AND set_param.application_column_name       = cp_param.application_column_name

                AND set_param.descriptive_flex_appl_id      = cp_param.application_id

                AND set_param.request_set_id                = frs.request_set_id

                AND set_param.request_set_program_id        = frsp.request_set_program_id)

         ,  cp_param.default_value) REQUEST_SET_DEFAULT_VALUE

    , frss.user_stage_name

    , ( SELECT user_stage_name FROM apps.fnd_request_set_stages_vl WHERE request_set_stage_id = frss.success_link) success_link

    , ( SELECT user_stage_name FROM apps.fnd_request_set_stages_vl WHERE request_set_stage_id = frss.warning_link) warning_link

    , ( SELECT user_stage_name FROM apps.fnd_request_set_stages_vl WHERE request_set_stage_id = frss.error_link) error_link

FROM apps.fnd_request_sets_tl frs

    , apps.fnd_req_set_stages_form_v frss

    , apps.fnd_request_set_programs frsp

    , apps.fnd_concurrent_programs fcp

    , apps.fnd_concurrent_programs_tl fcpt

    , apps.fnd_executables fx

    , apps.fnd_descr_flex_col_usage_vl cp_param

WHERE 1 =1

AND frss.request_set_id                     = frs.request_set_id

AND frsp.request_set_id                     = frss.request_set_id

AND frsp.request_set_stage_id               = frss.request_set_stage_id

AND fcpt.concurrent_program_id              =  frsp.concurrent_program_id

AND fcpt.concurrent_program_id              = fcp.concurrent_program_id

AND fx.executable_id                        = fcp.executable_id

AND fx.application_id                       = fcp.executable_application_id

AND UPPER (frs.user_request_set_name) LIKE UPPER ('%REQUEST SET NAME%')

AND cp_param.descriptive_flexfield_name(+)  = '$SRS$.' || fcp.concurrent_program_name

AND cp_param.application_id(+)              = fcp.application_id

ORDER BY frss.display_sequence

   ;