REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	a_conc_man.sql
REM
REM <SQLDIR_GRP>APPS_CONC_MAN</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Prints concurrent managers and 
REM	allows drilldown to running requests
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	a_conc_man.sql
REM 
REM Example:
REM	a_conc_man.sql
REM
REM
REM History:
REM	MM-DD-RRRR	VMOGILEV	Created
REM
REM

set verify off
set lines 132
set trims on
set pages 60

col CONCURRENT_QUEUE_ID         format 9999        Heading "QUEUE Id"
col CONCURRENT_QUEUE_NAME       format a10 trunc   Heading "QUEUE Code"
col USER_CONCURRENT_QUEUE_NAME  format a25 trunc   Heading "Concurrent Queue Name"
col MAX_PROCESSES               format 999 Heading "Max"
col RUNNING_PROCESSES           format 999 Heading "Act"
col running                     format 999 Heading "Run"

select q.CONCURRENT_QUEUE_ID
,      q.CONCURRENT_QUEUE_NAME
,      q.USER_CONCURRENT_QUEUE_NAME
,      q.MAX_PROCESSES
,      q.RUNNING_PROCESSES
,      running.total running
from (select /*+ ORDERED */
            count(*)                 total
      ,     prc.CONCURRENT_QUEUE_ID
      from  apps.fnd_concurrent_processes prc
      ,     apps.FND_CONCURRENT_REQUESTS  req
      where req.phase_code='R'
      and   req.controlling_manager = prc.concurrent_process_id
      group by prc.CONCURRENT_QUEUE_ID)  running
,    apps.fnd_concurrent_queues_vl q
where q.CONCURRENT_QUEUE_ID = running.CONCURRENT_QUEUE_ID(+)
order by
   DECODE(q.application_id,0,DECODE(q.CONCURRENT_QUEUE_ID,1,1,4,2))
,  sign(q.max_processes) desc
,  q.CONCURRENT_QUEUE_NAME
,  q.application_id;


accept q_id prompt "Enter Queue ID to report Running Processes: "

col request_id        format 99999999  Heading "APPS Req ID"
col oracle_process_id format a10       Heading "OS Pid"
col sid               format 9999      Heading "Sid"
col serial#           format 999999    Heading "Serial"
col program           format a45 trunc Heading "Program"
col log               format a132      Heading "Concurrent Log File"
col out               format a132      Heading "Concurrent Out File"

select /*+ ORDERED */
       req.request_id
,      req.oracle_process_id
,      ses.sid
,      ses.serial#
,      ses.program 
,      req.logfile_name log
,      req.outfile_name out
from  v$process pid
,     v$session ses
,     apps.fnd_concurrent_processes prc
,     apps.FND_CONCURRENT_REQUESTS req
where req.phase_code='R'
and   req.controlling_manager = prc.concurrent_process_id
and   prc.CONCURRENT_QUEUE_ID = &q_id
--  and   req.oracle_process_id IS NOT NULL
and   req.oracle_process_id = pid.spid(+)
and   pid.addr = ses.paddr(+);


--update apps.fnd_concurrent_requests set phase_code='C', status_code='X' where request_id = 'req_id_to_cancel';