rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Dave Cook
rem * Original          : 28-FEB-95
rem * Last Update       : %H% - %T%
rem * Description       : Shows concurrent request stat's for capacity
rem *                     planning purposes.
rem * Usage             : start cmstats.sql
rem ********************************************************************

def aps_prog    = 'cmstats.sql'
def aps_title   = 'Concurrent Manager Statistics'
start apstitle

accept begin_date prompt  "Enter Starting Date: "
accept end_date   prompt  "Enter Ending   Date: "

col manager_name        format  a20     heading 'Manager Name'  justify c
col start_hour          format  a4      heading 'Hour'          justify c
col count               format  999     heading 'Count'         justify c
col avg_time            format  9990.00 heading 'Avg Time'      justify c
col min_time            format  9990.00 heading 'Min Time'      justify c
col max_time            format  9990.00 heading 'Max Time'      justify c

select  a.concurrent_queue_name manager_name,
  to_char(c.actual_start_date, 'hh24') start_hour,
  count(*) count,
  avg((c.actual_completion_date - c.actual_start_date) * (24*60)) avg_time,
  min((c.actual_completion_date - c.actual_start_date) * (24*60)) min_time,
  max((c.actual_completion_date - c.actual_start_date) * (24*60)) max_time
from fnd_concurrent_queues a,
     fnd_concurrent_processes b,
     fnd_concurrent_requests c
where c.controlling_manager = b.concurrent_process_id
  and b.concurrent_queue_id = a.concurrent_queue_id
  and c.status_code = 'C'
  and c.actual_start_date between
    to_date('&begin_date','DD-MON-YY') and
    to_date('&end_date','DD-MON-YY')
group by a.concurrent_queue_name,
         to_char(c.actual_start_date, 'hh24')
order by 1;
