REM RMINC_mgrp.sql
REM Concurrent Program Name:  Concurrent Manager Performance History
REM Copyright 2006, All Rights Reserved, Reed-Matthews, Inc.
REM
REM Use this report to tell how the concurrent managers (rather than the concurrent programs)
REM are performing.  Don't run it for longer than you save data in your concurrent
REM manager tables.  So if you run the Purge Concurrent Requests and/or Manager Data
REM for everything older than 7 days, then you should only run this for 7 days
REM worth of information.  You might schedule this to run every week, save the results
REM and compare them over time to look for patterns.

set verify off
set feedback off
REM accept edate prompt 'Enter start date: '
set pagesize 59
set linesize 180
set newpage 02
set recsep off
set heading on
column today new_value _date noprint;
select to_char(SYSDATE,'DD-MON-YY') today from dual;
column startdate format a15 heading 'START|DATE';
REM column edate NOPRINT format a18;
ttitle "Concurrent Manager Performance History from: &&1 to  &&2" skip 2;
break on actual_start_date skip 1;
compute sum of cnt on actual_start_date;
compute sum of elapsed on actual_start_date;
compute sum of average on actual_start_date;
compute sum of waited on actual_start_date;
compute sum of avewait on actual_start_date;
column elapsed format 99999.99 heading 'TOTAL|HOURS';
column average format 9999.99 heading 'AVG.|HOURS';
column waited format 99999999.99 heading 'WAITED|HOURS';
column avewait format 9999.99 heading 'AVG.|WAIT';
column cnt format 999,999 heading 'COUNT';
column manager format a35 heading 'CONCURRENT MANAGER';

set feedback off
set head on

select	q.user_concurrent_queue_name manager,
 	count(*) cnt,
 	sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,
 	avg(r.actual_completion_date - r.actual_start_date) * 24 average,
 	sum(r.actual_start_date - r.requested_start_date) * 24 waited,
 	avg(r.actual_start_date - r.requested_start_date) * 24 avewait
from 	apps.fnd_concurrent_programs p, 
	apps.fnd_concurrent_requests r,
	apps.fnd_concurrent_queues_tl q,
 	apps.fnd_concurrent_processes 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') and 
	r.controlling_manager=p.concurrent_process_id and
 	q.concurrent_queue_id=p.concurrent_queue_id and
	r.concurrent_program_id=p.concurrent_program_id and
        actual_start_date between '&&1' and '&&2'
group by q.user_concurrent_queue_name;
set feedback on