rem cmquickq.sql
rem     see what jobs are queueing longer than they are running
rem --------------------------------------------------------------
clear columns
clear breaks
set recsep wrap
set pages 30
set lines 132
set verify off
col actq head QTime format 9999.99
col actr head "RunTime" format 9999.99
col reqid head "Req ID" format 9999999
col qcat head "Queue Wait Times" format a18
col qname head "Queue Name" format a25
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
col reqdte   head "Reqt Start" format a10
col startdte head "Started On" format a10
col compldte head "Finishd On" format a10
col progname head "Concurrent Program Name [Arguments]" format a90 trunc
col blankcol head " " format a25
break on qcat nodup on qname nodup
accept trgtrange char default T prompt 'See queue wait times for (t)oday or (h)istorically <t> : '
accept trgtlag number default 5 prompt 'What is the minimum number of minutes lag time <5> : '
select q.concurrent_queue_name qname,
       r.request_id reqid,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       to_char(r.requested_start_date,'hh:mi:ssAM') reqdte,
       ((r.actual_start_date - r.requested_start_date)*1440) actq,
       to_char(r.actual_start_date,'hh:mi:ssAM') startdte,
       to_char(r.actual_completion_date,'hh:mi:ssAM') compldte,
       ((r.actual_completion_date - r.actual_start_date)*1440) actr,
       '                         ' blankcol,
       g.concurrent_program_name || ' - ' ||
       gtl.user_concurrent_program_name || ' [' ||
       r.argument_text || ']' progname
  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_programs_tl gtl,
       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 r.concurrent_program_id = g.concurrent_program_id
   and g.concurrent_program_id = gtl.concurrent_program_id
   and gtl.language = 'US'
   and r.program_application_id = g.application_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) -
         ((r.actual_completion_date - r.actual_start_date)*1440) > &trgtlag 
       )
   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'
order by 1,6,2;