clear columns
clear breaks
set lines 132
set pages 24
set verify off
col minr head MinRTime format 9999.99
col maxr head MaxRTime format 9999.99
col avgr head AvgRun format 9999.99
col avgq head AvgQTime format 9999.99
col totreq head "# Jobs" format 99999
col qcat head "Queue Run Times Category" format a25
col qcatno noprint
col qname head "Queue Name" format a15 trunc
col statustxt head Status format a1 trunc
col phasetxt head Phase format a1 trunc
col actstarttim format a6 trunc head Start
col actcomptim format a6 trunc head Finish
col actual_start_date noprint
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> : '
prompt Note - times are expressed in decimal minutes and are for today only
select q.concurrent_queue_name qname,
       count(r.request_id) totreq,
       g.concurrent_program_name,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       to_char(r.actual_start_date,'hh24:mi') actstarttim,
       to_char(r.actual_completion_date,'hh24:mi') actcomptim,
       r.actual_start_date,
       avg((r.actual_start_date - r.requested_start_date)*1440)   avgq,
       min((r.actual_completion_date - r.actual_start_date)*1440) minr,
       max((r.actual_completion_date - r.actual_start_date)*1440) maxr,
       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 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
   and trunc(r.actual_start_date) = trunc(sysdate)
 group by q.concurrent_queue_name,g.concurrent_program_name,l2.meaning,l1.meaning,
       to_char(r.actual_start_date,'hh24:mi'),
       to_char(r.actual_completion_date,'hh24:mi'),
	r.actual_start_date
order by r.actual_start_date;