/* This script returns information on all jobs run through a specific */
/* queue within the date range with a run time >= the minimum execution */
/* time. */
/* Required Input: Start/End date; minimum execution time in seconds; */
/* the long program name */


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) :'
accept que_name char prompt 'Enter beginning or all of the Queue Name: '

ttitle off
set linesize 180
set pagesize 60
set pause off
set verify off
set termout on
ttitle 'CM Analysis Report' skip1

col conc_que format a15 heading "Conc Queue"
col user_name format a15 heading "User"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "StDate"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 999,999 heading "ExTme|(Min)"
col wtime format 999,999 heading "WtTme|(Sec)"
col pname1 format a40 heading "Short Name"
col pname2 format a65 heading "Prog Name"

select
trunc(actual_start_date) sdate,
request_id reqid,
substr(user_name,1,15),
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*60*24) wtime,
((actual_completion_date - actual_start_date)*60*24) rtime,
que.concurrent_queue_name conc_que,
prog.user_concurrent_program_name pname2
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
concurrent_queue_name like '&que_name%'
and
((actual_completion_date - actual_start_date)*60*24) >= &min_exec
and
actual_start_date between
to_date('&s_dte','DD-MON-YYYY') and
(to_date('&e_dte','DD-MON-YYYY') + 1)
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
concurrent_queue_name,
actual_start_date
-- trunc(actual_start_date),
-- ((actual_completion_date - actual_start_date)*60*60*24) desc
/
ttitle off