REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc
REM $RMINC_TOP/sql/RMINC_rpthis.sql

REM Concurrent Request History (RMINC)

REM User has to enter a report name.  Can enter a username.  Must
REM enter a date from which to start reporting the history.
REM This report tells us the concurrent manager history of a given
REM report, including who ran it, how long it ran, and the parameters
REM that were selected.
REM
REM This program assumes you've gone to the trouble of creating a history table for
REM older concurrent requests using RMINC_fnd_concurrent_requests.sql and 
REM RMINC_fnd_concurrent_requests.trg.  If you haven't, use the scripts from 
REM How to Manage the Concurrent Manager (or How to Herd Cats)instead.
REM 
REM Parameters:
REM &1 concurrent request name
REM &2 user name
REM &3 start date
REM &4 completion date
REM &5 order by
REM &6 order by
set timing off
set verify off
set show off
set echo off
REM set pagesize 45
set feedback off
set heading off
column today new_value _date noprint
select to_char(SYSDATE,'DD-MON-YY') today from dual;
column Name new_value report_name format a50
REM break on Name skip page
column Name new_value report_name noprint
column ProgID new_value prog_id noprint

set heading on
ttitle CENTER 'Report History for Concurrent Request: ' report_name -
RIGHT _date ' Page:' FORMAT 999 sql.pno SKIP 2 -
'This report shows the report history for a given report.  By looking at the ' -
'parameters that have been passed by other users and the time that it took to' -
'run the program' SKIP 1 - 
'with those parameters, you may be able to gauge ' -
'how long a report COULD run.  Try to avoid running ' -
'programs that have taken more than 30 minutes that have similar parameters' SKIP 1 -
'to the ones that you plan to select during peak business ' -
'hours (8am-6pm). ' SKIP 2 -
'For STATUS, the following codes apply:' SKIP 1 - 
'E = Error, C = Completed Normal, X = Terminated, W = Warning' SKIP 2 -
'It is a little difficult to match the parameters that were passed for some ' -
'reports with the fields that you entered.  Generally they are in the same ' -
'order that you entered them. ' SKIP 1 -
'If you go to the screen where you enter reports you can compare what you ' -
'were asked for ' -
'with the translated parameters that were passed and generally get an ' -
'idea of what is' SKIP 1 -
'going on.' SKIP 2 -
'If you run this report and notice a pattern emerging - for example, if ' - 
'every time you ran it a certain way, say for one month of data, it took ' -
'longer each time, then call it in' SKIP 1 -
'to the Help Desk - it may be that as we add more and ' -
'more data, we need to add an index to improve your performance.  Without ' -
'the index, the report SHOULD take longer each time' SKIP 2

column STARTED format a18 heading 'Started'
column FINISHED format a18 heading 'Finished'
column RUNTIME format 999999.99 heading 'Minutes'
column status format a6 heading 'Status'
column Parameters format a100 heading 'Parameters'
column req.request_id format a10 heading 'Request'
column user_name format a10 heading 'User'

select
to_char(req.actual_start_date,'dd-mon-yy hh24:mi:ss') STARTED,
to_char(req.actual_completion_date,'dd-mon-yy hh24:mi:ss') FINISHED,
req.request_id Request,
prg.user_concurrent_program_name Name,
prg.concurrent_program_id ProgramID,
usr.user_name,
req.argument_text Parameters,
status_code Status,
round((req.actual_completion_date-req.actual_start_date)*(60*24),6) RUNTIME
from applsys.RMINC_fnd_concurrent_requests req,
     apps.fnd_concurrent_programs_vl prg,
     apps.fnd_user usr
where
     req.concurrent_program_id = prg.concurrent_program_id
     and req.program_application_id = prg.application_id
     and req.requested_by = usr.user_id
     and req.phase_code = 'C'
     and req.status_code not in ('D','X')
     and upper(prg.user_concurrent_program_name) like upper('&1%')
     and upper(usr.user_name) like upper('&2%')
     and req.actual_start_date >= '&3'
     and req.actual_completion_date <= '&4'
union
select
to_char(req.actual_start_date,'dd-mon-yy hh24:mi:ss') STARTED,
to_char(req.actual_completion_date,'dd-mon-yy hh24:mi:ss') FINISHED,
req.request_id Request,
prg.user_concurrent_program_name Name,
prg.concurrent_program_id ProgramID,
usr.user_name,
req.argument_text Parameters,
status_code Status,
round((req.actual_completion_date-req.actual_start_date)*(60*24),6) RUNTIME
from apps.fnd_concurrent_requests req,
     apps.fnd_concurrent_programs_vl prg,
     apps.fnd_user usr
where
     req.concurrent_program_id = prg.concurrent_program_id
     and req.program_application_id = prg.application_id
     and req.requested_by = usr.user_id
     and req.phase_code = 'C'
     and req.status_code not in ('D','X')
     and upper(prg.user_concurrent_program_name) like upper('&1%')
     and upper(usr.user_name) like upper('&2%')
     and req.actual_start_date >= '&3'
     and req.actual_completion_date <= '&4'
order by &5, &6;