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_cleanup.sql
REM
REM <SQLDIR_GRP>APPS_CONC_MAN</SQLDIR_GRP>
REM 
REM Author:
REM     Vitaliy Mogilevskiy (www.dbatoolz.com)
REM 
REM Purpose:
REM     <SQLDIR_TXT>
REM     Resets concurrent managers/processes.
REM	Use if you killed any of the FND processes.
REM     </SQLDIR_TXT>
REM
REM Usage:
REM     a_conc_cleanup.sql
REM 
REM Example:
REM     a_conc_cleanup.sql
REM
REM
REM History:
REM     11-24-2001      VMOGILEV        Created
REM
REM

prompt "*** WARNING ***"
prompt "This script will re-set conc managers as if they were never started"
accept dummy prompt "press Enter to continue or CTL-C to exit"

spool a_conc_cleanup.log
set verify off
set lines 132
set trims on
set pages 60
set feedback on


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"

prompt CONCURRENT MANAGER CLEANUP
prompt ==========================
prompt UPDATING fnd_concurrent_queues (setting RUN and MAX to 0) ...
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;

update fnd_concurrent_queues set running_processes=0, max_processes=0
where (running_processes != 0 or max_processes != 0);



prompt UPDATING fnd_concurrent_processes (setting ACTIVE to K) ...
select CONCURRENT_PROCESS_ID 
FROM fnd_concurrent_processes
WHERE   process_status_code not in ('K', 'S');

UPDATE  fnd_concurrent_processes
SET     process_status_code = 'K'
WHERE   process_status_code not in ('K', 'S');



prompt UPDATING fnd_concurrent_queues (setting CONTROL CODE to NULL) ...
SELECT concurrent_queue_name
FROM  fnd_concurrent_queues
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;

UPDATE  fnd_concurrent_queues
SET     control_code = NULL
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;



prompt UPDATING fnd_concurrent_requests (setting PHASE=C, STATUS=X) ...
col oracle_process_id format a9 heading "ORA Pid"
select req.request_id
,      req.oracle_process_id
,      req.logfile_name log
,      req.outfile_name out
from  apps.fnd_concurrent_requests req
where req.phase_code='R';

update fnd_concurrent_requests
set    phase_code='C'
,      status_code='X'
where  phase_code='R';

spool off

prompt "For safety reasons manually commit or rollback your changes"