REM RMINC_rpthis.sql
REM Concurrent Program History Report
REM Copyright 2003, Reed-Matthews, Inc.
REM For 11i
REM barb@oncalldba.com
REM
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. 
set timing off
set verify off
set show off
set echo off
set pagesize 66 
set linesize 180
set feedback off
set newpage 0
set heading off
column today new_value _date noprint
select to_char(SYSDATE,'DD-MON-RR') today from dual;
column Name new_value report_name format a50
break on Name skip page
column Name new_value report_name noprint
column ProgID new_value prog_id noprint
column user_name format a10 heading 'User'
set heading on
/*
ttitle CENTER 'Request History for Concurrent Program: ' 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 10 minutes that have similar parameters' SKIP 1 -
'to the ones that you plan to select during peak business ' -
'hours (8am-6pm).  We only hound the people whose jobs take more than 10 ' - 
'minutes during those hours.' 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 IS - 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
*/
ttitle CENTER 'Report History for Concurrent Request: ' report_name -
RIGHT _date ' Page:' FORMAT 999 sql.pno SKIP 2 -
'For STATUS, the following codes apply:  E = Error, C = Completed Normal, X = Terminated, W = Warning' 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 argument_text format a100 heading 'Parameters Passed'
column request format 99999999 heading 'Request'

spool &&7

select
to_char(req.actual_start_date,'DD-MON-RR HH24:MI:SS') STARTED,
to_char(req.actual_completion_date,'DD-MON-RR HH24:MI:SS') FINISHED,
req.request_id Request,
prg.user_concurrent_program_name Name,
prg.concurrent_program_id ProgID,
usr.user_name,
argument_text,
status_code status,
round((req.actual_completion_date-req.actual_start_date)*(60*24),6)
RUNTIME
from fnd_user usr,
     fnd_concurrent_programs_vl prg,
     fnd_concurrent_requests req
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 upper(prg.user_concurrent_program_name) like upper('&&1%')
     and upper(usr.user_name) like upper('&&2%')
     and req.actual_start_date >= '&&3'
     and trunc(req.actual_completion_date) <= '&&4'
union
select
    to_char(req.actual_start_date,'DD-MON-RR HH24:MI:SS') STARTED,
    to_char(req.actual_completion_date,'DD-MON-RR HH24:MI:SS') FINISHED,
    req.request_id Request,
    prg.user_concurrent_program_name Name,
    prg.concurrent_program_id ProgID,
    usr.user_name,
    argument_text,
    status_code status,
    round((req.actual_completion_date-req.actual_start_date)*(60*24),6) RUNTIME
from fnd_user usr,
     fnd_concurrent_programs_vl prg,
     fnd_concurrent_requests req
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 upper(prg.user_concurrent_program_name) like upper('&&1%')
    and upper(usr.user_name) like upper('&&2%')
    and req.actual_start_date >= '&&3'
    and trunc(req.actual_completion_date) <= '&&4'
order by 5 desc, &&6 &&5;