Script to Find High Impact Concurrent Programs

Sql Script to find High Impact concurrent Programs

#Script to find High Impact Concurrent Programs in html format
set linesize 4000
set verify off
set feedback off
set pagesize 999
set markup html on entmap on spool on preformat off
spool Highimpactconcurrentprograms.html

PROMPT High Impact Concurrent Programs

select substr(cname.user_concurrent_program_name,1,80) "prog name",
exec_date,creq.occur,creq.max_time,creq.min_time,creq.avg_time
from (select
concurrent_program_id,trunc(actual_start_date) exec_date,
count(concurrent_program_id) occur,
round(min(actual_completion_date-actual_start_date)*24*60,2) min_time,
round(max(actual_completion_date-actual_start_date)*24*60,2) max_time,
round(avg(actual_completion_date-actual_start_date)*24*60,2) avg_time
from apps.fnd_concurrent_requests
where status_code='C'
and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id ,trunc(actual_start_date)
having max(actual_completion_date-actual_start_date)*24*60 >30) creq
,apps.fnd_concurrent_programs_tl cname
where creq.concurrent_program_id=cname.concurrent_program_id
and cname.language='US'
order by max_time,occur;

set markup html off entmap off spool off preformat on
set linesize 2000 verify on feedback on
spool off