Concurrent Manager scripts

ADMINISTRATOR CONCURRENT MANAGER SCREEN
========================================
/* cmadmin.sql
see the administrative info for concurrent manager queues
same info as seen in the apps for the Administer Concurrent Managers screen
note: only shows the info for today
use cmrun.sql to see details for the jobs currently running
*/
set verify off
col qname head "Activated Concurrent Queue" format a26
col actual head "Actual" format 999999
col target head "Target" format 999999
col running head "Running" format 9999999
col pending head "Pending" format 9999999
col paused head "Paused" format 9999999
col influx head "InFlux" format 9999999
col avgqtime head "AvgQtime" format 99999.99
break on report
compute sum of actual on report
compute sum of target on report
compute sum of running on report
compute sum of pending on report
accept dispmethod char default n prompt 'Display only queues with running jobs [y/n] : '
rem select q.concurrent_queue_name qname,
select q.concurrent_queue_name || ' - ' || target_node qname,
q.running_processes actual,
q.max_processes target,
sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,
sum(decode(r.phase_code,'P',1,0)) pending,
nvl(sum(decode(r.status_code,'W',1,'H',1,'S',1,'A',1,'Z',1,0)),0) paused,
nvl(sum(decode(r.status_code,'F',1,'Q',1,'B',1,'T',0)),0) influx,
avg((nvl(r.actual_start_date,r.requested_start_date) - r.requested_start_date)*1440) avgqtime
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_queues q
where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes > 0
and ((r.phase_code in ('R','P','I') and upper('&dispmethod') = 'Y') or
upper('&dispmethod') != 'Y')
group by q.concurrent_queue_name || ' - ' || target_node, q.running_processes, q.max_processes;


concurrent manager and its OS process id.sql
==============================================
col os_process_id format a8
col prstdte format a18 head "StartedOn"
select q.concurrent_queue_name, p.os_process_id, to_char(p.process_start_date,'mm/dd/yy hh24:mi:ss') prstdte
from applsys.fnd_concurrent_processes p, applsys.fnd_concurrent_queues q
where p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and p.process_status_code = 'A'
order by 1
/



RESETS CONCURRENT MANAGER PROCESSES AS IF THEY WERE NEVER STARTED USE WHEN SOMEONE HAS KILLED SOME fnd pocess.txt
==================================================================================
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"


Retrieving Only the Internal Manager Logfile name.txt
======================================================

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';

WHICH MANAGER RAN REQUEST
===========================
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = *****;