Wednesday, April 28, 2010

Request Set Details

This query can give details of desired request sets.


SELECT rs.user_request_set_name "Request Set"
, rss.display_sequence Seq
, cp.user_concurrent_program_name "Concurrent Program"
, e.execution_file_name
, lv.meaning file_type
FROM apps.fnd_request_sets_vl rs
, apps.fnd_req_set_stages_form_v rss
, applsys.fnd_request_set_programs rsp
, apps.fnd_concurrent_programs_vl cp
, apps.fnd_executables e
, apps.fnd_lookup_values lv
WHERE rs.application_id IN ( 20025 )
/*
Above condition should be changed to select desired request sets
*/
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
AND rs.end_date_active IS NULL
ORDER BY 1



This query can little more information about request sets

SELECT rs.user_request_set_name "Request Set"
, rss.display_sequence "Stage Seq"
, rsp.sequence "Program Seq"
, cp_param.column_seq_num "Param Seq"
, cp.user_concurrent_program_name "Concurrent Program"
, e.execution_file_name "Execution File"
, lv.meaning "File Type"
, form_left_prompt "Parameter"
, cp_param.default_type
, cp_param.default_value "Prog Default"
, 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 = rs.request_set_id
and set_param.request_set_program_id = rsp.request_set_program_id
), cp_param.default_value) "Reqquest Set Default Value"
, rs.REQUEST_SET_ID
FROM apps.fnd_request_sets_vl rs
, apps.fnd_req_set_stages_form_v rss
, applsys.fnd_request_set_programs rsp
, apps.fnd_concurrent_programs_vl cp
, apps.fnd_executables e
, apps.fnd_lookup_values lv
, apps.fnd_descr_flex_col_usage_vl cp_param
WHERE rs.application_id IN (20026) /* Above condition should be changed to select desired request sets */
and rs.user_request_set_name LIKE 'USPS APEX Payment Processing Set'
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
AND rs.end_date_active IS NULL
AND cp_param.descriptive_flexfield_name (+) = '$SRS$.' || cp.concurrent_program_name
AND cp_param.application_id (+) = cp.application_id
AND cp_param.enabled_flag (+)= 'Y'
ORDER BY rss.display_sequence
, rsp.sequence
, cp_param.column_seq_num

3 comments:

  1. It was really helpful :) Thanks

    ReplyDelete
  2. Appreciate this query man. I search it a lot and finally came across your blog.

    ReplyDelete