Thursday 17 July 2014

List of Concurrent Programs that are not in any Request Set Query

/* Formatted on 7/17/2014 1:01:10 PM (QP5 v5.115.810.9015) */
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