/* top10jobs.sql
see the top10 jobs based on total runtime for today
*/
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col TotTime form 99999
col AvgTime form 99999.90
col qname head "Queue" format a15 trunc
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name || ' (' || to_char(c.concurrent_program_id) || ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11;