a_conc_cleanup.sql

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 APPS_CONC_MAN
REM
REM Author:
REM Vitaliy Mogilevskiy (www.dbatoolz.com)
REM
REM Purpose:
REM
REM Resets concurrent managers/processes.
REM Use if you killed any of the FND processes.
REM
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"