rem ********************************************************************
rem * Filename          : jobs.sql - Version 1.4
rem * Author            : Virag Saksena
rem * Original          : 02-APR-95
rem * Last Update       : 20-DEC-95
rem * Description       : Information about jobs running on the system
rem * Usage             : start jobs.sql
rem ********************************************************************
set lines 132 
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999
col "Parent" form a7
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname 
      ,a.request_id "Req Id" 
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.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_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,v$session vs
    ,v$process vp
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 c2.concurrent_program_id = c.concurrent_program_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 c2.language = 'US'
  and vs.process (+) = b.os_process_id
  and vs.paddr = vp.addr (+)
order by 1,2
/