/* This script returns information on all jobs run within the date */
/* range with a run time >= the minimum execution time. */
/* Required Input: Start/End date; minimum execution time in minutes */
/* This script also includes running request parameters*/


spool off
prompt Enter request dates in DD-MON-YYYY format
accept s_dte char prompt 'Start Date: '
accept e_dte char prompt 'End Date: '
accept min_exec number prompt 'Enter minimum execution time in minutes (0 for all runs) :'
prompt Enter filename for report file.
prompt If no report is desired, simply hit <Enter>
accept spool_file char prompt 'Report File: '

spool &spool_file

ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on
ttitle 'CM Analysis Report' skip1

break on sdate skip page

--break on rtime
col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a51 heading "Prog Name"
col args format a30 heading "Arguments"
select
trunc(actual_start_date) sdate,
req.request_id reqid,
user_name,
to_char(actual_start_date,'HH24:MI:SS') astart,
to_char(actual_completion_date,'HH24:MI:SS') acomp,
((actual_start_date - requested_start_date)*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2,
substr(argument_text,1,30) args
from
applsys.fnd_concurrent_queues que,
applsys.fnd_user usr,
-- applsys.fnd_concurrent_programs prog,
apps.fnd_concurrent_programs_vl prog,
applsys.fnd_concurrent_requests req,
applsys.fnd_concurrent_processes proc
where
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
que.application_id= proc.queue_application_id
and
que.concurrent_queue_id = proc.concurrent_queue_id
and
req.controlling_manager= proc.concurrent_process_id
and
usr.user_id = req.requested_by
and
prog.concurrent_program_id = req.concurrent_program_id
and
prog.application_id = req.program_application_id
and prog.concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by
trunc(actual_start_date),
prog.user_concurrent_program_name,
((actual_completion_date - actual_start_date)*60*60*24) desc
/
spool off
ttitle off