rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Dave Cook
rem * Original          : 28-FEB-95
rem * Last Update       : %H% - %T%
rem * Description       : Shows individuall concurrent request stat's for
rem *                     capacity planning purposes.
rem * Usage             : start cmprun.sql <date>  e.g. 03-MAR-95
rem *                                        <Program Name>
rem *                                        <Max run time (min.)>
rem *
rem ********************************************************************

def end_date=&&1
def u_prog_name=&&2
def maxtime=&&3

def aps_prog    = 'cmptime.sql-1'
def aps_title   = 'Concurrent Program Statistics for &end_date (maxtime:&maxtime
)'
start apstitle

col xuser       format  a10     heading 'User'          justify c trunc
col program_name format a25     heading 'Program Name'  justify c trunc
col start_hour  format  a5      heading 'Start'         justify c
col tot_time    format  999,990.00 heading 'TTime (Q+R)'        justify c
col wait_time   format  999,990.00      heading 'QTime'         justify c
col run_time    format  999,990.00      heading 'RTime'         justify c

break on report
compute avg of tot_time wait_time run_time on report

select usr.user_name xuser, cp.user_concurrent_program_name program_name,
  to_char(cr.request_date, 'hh24:MI') start_hour,
  ((cr.actual_completion_date - cr.request_date) * (24*60)) tot_time,
  ((cr.actual_start_date - cr.request_date) * (24*60)) wait_time,
  ((cr.actual_completion_date - cr.actual_start_date) * (24*60)) run_time
from fnd_concurrent_programs cp,
     fnd_concurrent_requests cr,
     fnd_user usr
where cr.concurrent_program_id = cp.concurrent_program_id
  and cp.user_concurrent_program_name like '&u_prog_name%'
  and cr.status_code = 'C'
  and to_char(cr.request_date, 'DD-MON-YY') = '&end_date'
  and cr.requested_by = usr.user_id
  and (cr.actual_completion_date - cr.actual_start_date) * (24*60) < &maxtime
order by 3, 1, 2;
