/* cmlong.sql
     monitor script to list the ccmgr jobs running longer than 10 minutes
       and exceeding the average historical runtime for this particular
       job
*/
col program form A35 trunc head "Program Full Name"
col intprog format a20 trunc head "Internal Name"
col time form 9999.99
col "Req Id" form 9999999
col qname head "Concurrent Manager Queue" format a25 trunc
rem select q.concurrent_queue_name qname
select q.concurrent_queue_name || ' - ' || target_node qname
      ,a.request_id "Req Id"
      ,a.phase_code,a.status_code
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name  || 
	 ' (' || to_char(c.concurrent_program_id) || ')' intprog, 
       ctl.user_concurrent_program_name "program"
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 a.phase_code in ('I','P','R','T')
  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 (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10
  and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 
   ( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
       from APPLSYS.fnd_Concurrent_requests a2,
	    APPLSYS.fnd_concurrent_programs c2
      where c2.concurrent_program_id = c.concurrent_program_id
        and a2.concurrent_program_id = c2.concurrent_program_id
        and a2.program_application_id = c2.application_id
        and a2.phase_code || '' = 'C'
   )
order by 5 desc;