/* cmdailylong.sql
	see the top jobs based on total runtime > xx minutes for today
	first parameter is the nbr of minutes runtime to use
	SDR-Oracle 12/5/02 Created from various other scripts
*/
set pages 66
set lines 132
set verify off
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col RunTime form 99999
col actstart format a6 head Started
col actcompl format a6 head Finishd
col qname head "Queue" format a15 trunc
select q.concurrent_queue_name qname,
       c.concurrent_program_name  || 
	 ' (' || to_char(c.concurrent_program_id) || ')' intprog, 
       ctl.user_concurrent_program_name "program",
	request_id,
	phase_code,
	status_code,
	to_char(actual_start_date,'hh24:mi') actstart,
	to_char(actual_completion_date,'hh24:mi') actcompl,
      ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "RunTime"
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
  and ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) > &1
order by 9 desc;