Running Requests

It is possible for the number of running requests to be greater than the number of manager processes; this may be due to Request Set and Request Set Stages which, once running should not be counted as real requests. As such I have removed both Request Set and Request Set Stages from the running counts.

Pending Error

Pending Error is a pending request that is not associated with a manager or there is no manager available to run the request. I have ignored this pending status for this example.

SELECT fcwr.concurrent_queue_name ||'('||
       fcq.running_processes||')' manager,
       fcq.sleep_seconds sleep_seconds,
       fcq.cache_size cache,
       sum(decode(fcwr.phase_code, 'R', 1, 0)) running,
       sum(decode(fcwr.phase_code, 'P', 1, 0)) total_pending,
       sum(decode(fcwr.phase_code, 'P', decode(fcwr.hold_flag, 'Y', 0, decode(sign(fcwr.requested_start_date - sysdate), 1, 0, 1)), 0)) pending_normal,
       sum(decode(fcwr.phase_code, 'P', decode(fcwr.hold_flag, 'Y', 1, 0), 0)) on_hold,
       sum(decode(fcwr.phase_code, 'P', decode(sign(fcwr.requested_start_date - sysdate), 1, decode(fcwr.hold_flag, 'Y', 0, 1), 0), 0)) scheduled
  FROM apps.fnd_concurrent_worker_requests fcwr,
       applsys.fnd_concurrent_queues fcq
 WHERE fcwr.concurrent_queue_id = fcq.concurrent_queue_id
   and fcwr.concurrent_queue_name != 'FNDCRM'
 GROUP by fcwr.concurrent_queue_name,
          fcq.running_processes,
          fcq.sleep_seconds,
          fcq.cache_size
 ORDER by fcwr.concurrent_queue_name;