REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.

REM $RMINC_TOP/sql/RMINC_rptsump.sql

REM Concurrent Request Performance History (RMINC)

REM If you haven't gone to the trouble of creating a historical
REM table to save older fnd_concurrent_request data, then
REM use the version of RMINC_rptsump.sql available under
REM 'How Manage the Concurrent Manager (or How to Herd Cats).
REM It uses just what is available in the fnd_concurrent_requests
REM table

REM This report shows the elapsed time history
REM for all the concurrent requests that have
REM run during a given time period.  You can
REM use it to help assign requests that always
REM run fast to one queue, while assigning
REM requests that always run slow to a slow
REM queue, and leaving the inconsistent ones in
REM the standard queue.
REM &1 = number of days of history
REM &2 = order by
REM &3 = order by

set timing off
set heading on
set verify off
set show off
set echo off
set feedback off
column today new_value _date noprint
select to_char(SYSDATE,'DD-MON-YY') today from dual;
column fromday new_value _fromdate noprint
select to_char(SYSDATE-&1,'DD-MON-YY') fromday from dual;
ttitle CENTER 'Report Summary by Program' SKIP 1 -
CENTER 'All Times are Elapsed Time - Ordered by ' &2 ',' &3 ' from ' _fromdate ' thru: ' _date -
RIGHT ' Page: ' FORMAT 999 sql.pno SKIP 2  
column elapsed format 999.99 heading 'TOTAL|HOURS';
column average format 99.99 heading 'AVG.|HOURS';
column max     format 99.99 heading 'MAX.|HOURS';
column min     format 99.99 heading 'MIN.|HOURS';
column waited  format 999999999.99 heading 'TOTAL WAITED|HOURS';
column avewait format 9999.99 heading 'AVG.|WAIT';
column concurrent_program_name format a20 heading 'PROGRAM';
column description format a80 wrap word;
column cnt format 999,999 heading '# TIMES|EXECUTED';
column app format a8 heading 'APP';
compute sum of waited on actual_start_date;
compute sum of avewait on actual_start_date;

select
   f.application_short_name app,
   p.user_concurrent_program_name description,
   p.concurrent_program_name,
   count(*) cnt,
   sum(actual_completion_date - actual_start_date) * 24 elapsed,
   avg(actual_completion_date - actual_start_date) * 24 average,
   max(actual_completion_date - actual_start_date) * 24 max,
   min(actual_completion_date - actual_start_date) * 24 min,
   sum(actual_start_date - requested_start_date) * 24 waited,
   avg(actual_start_date - requested_start_date) * 24 avewait
from fnd_application f,
     applsys.RMINC_conc_mgr_history r,
     fnd_concurrent_programs_vl p
where r.program_application_id = p.application_id
   and r.concurrent_program_id = p.concurrent_program_id
   and r.status_code in ('C','G','E')
   and actual_completion_date > sysdate - &1
   and p.application_id = f.application_id
   and r.program_application_id = f.application_id
group by
   f.application_short_name,
   p.concurrent_program_name,
   p.user_concurrent_program_name
order by &2 desc,&3 desc ;