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] : '
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_dat e) - 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;