rem cmqrdet.sql
rem     detailed queue run time analysis for concurrent manager queues
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 Run 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 a11
col startdte head "Started On" format a11
col compldte head "Finishd On" format a11
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 run times for (t)oday or (h)istorically <t> : '
accept trgtque number default 8 prompt 'Which queue category to see [0-8] <8> : '
accept trgtqname char default ALL prompt 'Limit to which ccmgr queue name <all> : '
accept trgtprog char default ALL prompt 'Limit to which ccmgr job short name <all> : '
accept minqtime number default 0 prompt 'What is the minimum queue time <0> : '
rem select decode(&trgtque,
rem               0,'15 seconds or less',
rem               1,'Up to 30 seconds',
rem               2,'Up to 1 minute',
rem               3,'Up to 5 minutes',
rem               4,'Up to 10 minutes',
rem               5,'Up to 15 minutes',
rem               6,'Up to 20 minutes',
rem               7,'Up to 30 minutes',
rem               8,'Over 30 minutes',
rem               'Undefined Category') qcat,
select q.concurrent_queue_name qname,
       r.request_id reqid,
       l2.meaning phasetxt,
       l1.meaning statustxt,
       to_char(r.requested_start_date,'mm/dd hh24:mi') reqdte,
       ((r.actual_start_date - r.requested_start_date)*1440) actq,
       to_char(r.actual_start_date,'mm/dd hh24:mi') startdte,
       to_char(r.actual_completion_date,'mm/dd hh24:mi') 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 (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
   and gtl.concurrent_program_id = g.concurrent_program_id
   and gtl.application_id = g.application_id
   and gtl.language = 'US'
   and l1.language = 'US'
   and l2.language = 'US'
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and ((trunc(r.requested_start_date) = trunc(sysdate) and upper('&trgtrange') = 'T')
          or (upper('&trgtrange') != 'T'))
   and (   (     ((r.actual_completion_date - r.actual_start_date)*1440) <= .25 
             and &trgtque = 0
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > .25
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= .5
             and &trgtque = 1
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > .5
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 1
             and &trgtque = 2
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 1
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 5
             and &trgtque = 3
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 5
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 10
             and &trgtque = 4
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 10
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 15
             and &trgtque = 5
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 15
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 20
             and &trgtque = 6
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 20
             and ((r.actual_completion_date - r.actual_start_date)*1440) <= 30
             and &trgtque = 7
           )
        or (     ((r.actual_completion_date - r.actual_start_date)*1440) > 30
             and &trgtque = 8
           )
       )
   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 (((r.actual_start_date - r.requested_start_date)*1440) >= &minqtime or &minqtime = 0)
order by 6,actual_start_date,1,2;