Monday, 14 November 2011

How many concurrent program or request will be runing ?

SELECT r.request_id, u.user_name requestor, pt.user_concurrent_program_name,
       r.phase_code, r.status_code, r.priority_request_id, r.priority,
       r.request_date, r.requested_by, r.requested_start_date, r.hold_flag,
       r.has_sub_request, r.is_sub_request, r.update_protected,
       r.queue_method_code, r.responsibility_application_id,
       r.responsibility_id, r.save_output_flag, r.last_update_date,
       r.last_updated_by, r.last_update_login, r.printer, r.print_style,
       r.parent_request_id, r.controlling_manager, r.actual_start_date,
       r.actual_completion_date, r.completion_text, r.argument_text,
       r.implicit_code, r.request_type, r.program_application_id,
       r.concurrent_program_id, pb.concurrent_program_name program_short_name,
       pb.execution_method_code, pb.enabled_flag enabled,
       DECODE (r.description,
               NULL, pt.user_concurrent_program_name,
               r.description || ' (' || pt.user_concurrent_program_name || ')'
              ) program_name,
       pb.printer_name fcp_printer, pb.output_print_style fcp_print_style,
       pb.required_style fcp_required_style,
       s.user_printer_style_name user_print_style, r.description
  FROM applsys.fnd_concurrent_programs_tl pt,
       applsys.fnd_concurrent_programs pb,
       applsys.fnd_user u,
       applsys.fnd_printer_styles_tl s,
       applsys.fnd_concurrent_requests r
 WHERE pb.application_id = r.program_application_id
   AND pb.concurrent_program_id = r.concurrent_program_id
   AND pb.application_id = pt.application_id
   AND pb.concurrent_program_id = pt.concurrent_program_id
   AND pt.LANGUAGE = USERENV ('LANG')
   AND u.user_id = r.requested_by
   AND s.printer_style_name(+) = r.print_style
   AND s.LANGUAGE(+) = USERENV ('LANG')
   AND u.user_name = NVL(:p_requestor,u.user_name)
   ------Below code for Completed  Program---------
   AND phase_code = 'C' -- Completed
   AND status_code = 'C'
   ------Below code for Terminated Program---------
   AND status_code = 'X'
   ------Below code for Scheduled Program---------
   AND phase_code = 'P' -- Pending
   AND status_code IN ('I', 'Q')
     AND ((   DECODE (implicit_code, 'N', status_code, 'E', 'E', 'W', 'G') =
                                                                   status_code
           OR DECODE (implicit_code, 'W', 'E') = status_code
          )
         )
     AND (NVL (request_type, 'X') != 'S')

No comments:

Post a Comment