rem cmqwdet.sql
rem     detailed queue wait time analysis for concurrent manager queues
rem --------------------------------------------------------------
clear columns
clear breaks
set recsep wrap
set lines 132
set pagesize 30
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 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 name <all> : '
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,'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 (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   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 (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_start_date - r.requested_start_date)*1440) <= .25 
             and &trgtque = 0
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > .25
             and ((r.actual_start_date - r.requested_start_date)*1440) <= .5
             and &trgtque = 1
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > .5
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 1
             and &trgtque = 2
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > 1
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 5
             and &trgtque = 3
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > 5
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 10
             and &trgtque = 4
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > 10
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 15
             and &trgtque = 5
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > 15
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 20
             and &trgtque = 6
           )
        or (     ((r.actual_start_date - r.requested_start_date)*1440) > 20
             and ((r.actual_start_date - r.requested_start_date)*1440) <= 30
             and &trgtque = 7
           )
        or (     ((r.actual_start_date - r.requested_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 l1.language = 'US'
   and l2.language = 'US'             
order by 1,2;