Wednesday, April 28, 2010

List of Concurrent Programs that are not in any Request Set

This query can list all the concurrent programs that are not in any request set


SELECT cp.user_concurrent_program_name "Concurrent Program"
, e.execution_file_name "Execution File"
, lv.meaning "File Type"
, e.execution_method_code "Execution Method"
FROM apps.fnd_concurrent_programs_vl cp
, apps.fnd_lookup_values lv
, apps.fnd_executables e
WHERE cp.application_id IN ( 20024,20025)
/*
Modify above condition to change selection criterion
*/
AND cp.request_set_flag <> 'Y'
AND cp.enabled_flag = 'Y'
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 = cp.execution_method_code
AND NOT exists ( SELECT rsp.concurrent_program_id
FROM fnd_request_sets_vl rs
, apps.fnd_req_set_stages_form_v rss
, applsys.fnd_request_set_programs rsp
WHERE rs.application_id IN ( 20024,20025)
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
)
ORDER BY lv.meaning
, cp.user_concurrent_program_name

No comments:

Post a Comment