rem cmqtimes.sql
rem     queue wait time analysis for concurrent manager queues
rem     current categories are:
rem        <= 15 seconds
rem        > 15 secs, <= 30 secs
rem        > 30 secs, <= 1 min
rem        > 1 min, <= 5 mins
rem        > 5 mins, <= 10 mins
rem        > 10 mins, <= 15 mins
rem        > 15 mins, <= 20 mins
rem        > 20 mins, <= 30 mins
rem        > 30 mins
rem --------------------------------------------------------------
clear columns
clear breaks
set lines 132
set verify off
set feedback off
set pages 30
col minq head MinQTime format 9999.99
col maxq head MaxQTime format 9999.99
col avgq head AvgQTime format 9999.99
col avgr head AvgRun format 9999.99
col totreq head "# Jobs" format 99999
col qcat head "Queue Wait Times Category" format a25
col qcatno noprint
col qname head "Queue Name" format a25 trunc
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
break on qcat nodup on qname nodup on phasetxt nodup
accept trgtrange char default T prompt 'See queue wait times for (t)oday or (h)istorically <t> : '
accept trgtqname char default ALL prompt 'Limit to which ccmgr queue name <all> : '
accept trgtprog char default ALL prompt 'Limit to which ccmgr job name <all> : '
select 0 qcatno,
       '(0) 15 seconds or less' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) <= .25
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 1 qcatno,
       '(1) Up to 30 seconds' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > .25
   and ((r.actual_start_date - r.requested_start_date)*1440) <= .5
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 2 qcatno,
       '(2) Up to 1 minute' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > .5
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 1
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 3 qcatno,
       '(3) Up to 5 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 1
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 5
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 4 qcatno,
       '(4) Up to 10 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 5 
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 10
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 5 qcatno,
       '(5) Up to 15 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 10 
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 15
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 6 qcatno,
       '(6) Up to 20 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 15 
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 20
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union 
select 7 qcatno,
       '(7) Up to 30 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 20 
   and ((r.actual_start_date - r.requested_start_date)*1440) <= 30
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
union
select 8 qcatno,
       '(8) Over 30 minutes' qcat,
       q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       min((r.actual_start_date - r.requested_start_date)*1440) minq,
       max((r.actual_start_date - r.requested_start_date)*1440) maxq,
       avg((r.actual_start_date - r.requested_start_date)*1440) avgq,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_lookup_values l1,
       applsys.fnd_lookup_values l2,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       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 ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and ((r.actual_start_date - r.requested_start_date)*1440) > 30 
   and l1.lookup_type = 'CP_STATUS_CODE'
   and l1.lookup_code = r.status_code
   and l2.lookup_type = 'CP_PHASE_CODE'
   and l2.lookup_code = r.phase_code
  and l1.language = 'US'
   and l2.language = 'US'             
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
 group by q.concurrent_queue_name,l2.meaning,l1.meaning
order by 1,3,4 desc;