/* cmadmin.sql
     see the administrative info for concurrent manager queues
     same info as seen in the apps for the Administer Concurrent Managers screen
     note: only shows the info for today
     use cmrun.sql to see details for the jobs currently running
*/
set verify off
col qname head "Activated Concurrent Queue" format a26
col actual head "Actual" format 999999
col target head "Target" format 999999
col running head "Running" format 9999999
col pending head "Pending" format 9999999
col paused head "Paused" format 9999999
col influx head "InFlux" format 9999999
col avgqtime head "AvgQtime" format 99999.99
break on report
compute sum of actual on report
compute sum of target on report
compute sum of running on report
compute sum of pending on report
accept dispmethod char default n prompt 'Display only queues with running jobs [y/n] <n> : '
rem select q.concurrent_queue_name qname,
select q.concurrent_queue_name || ' - ' || target_node qname,
       q.running_processes actual,
       q.max_processes target,
       sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
       sum(decode(r.phase_code,'P',1,0)) pending,
       nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
       nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
       avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager (+) = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and q.max_processes > 0
   and ((r.phase_code in ('R','P','I') and upper('&dispmethod') = 'Y') or
         upper('&dispmethod') != 'Y')
 group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;