SELECT a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,
sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
where a.concurrent_queue_id = b.concurrent_queue_id
AND b.Requested_Start_Date<=SYSDATE
GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;
*** Concurrent QUEUE Details ***
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue '||
'Concurrent Queue Name '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP
--for each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts,
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
Sample Out put
====================================================================================================
QueueID Queue Concurrent Queue Name MAX RUN Running Pending In CRM
====================================================================================================
0_______STANDARD Standard Manager 30 30 19 3 3
1_______FNDICM Internal Manager 1 1 0 0 0
4_______FNDCRM Conflict Resolution Manager 1 1 0 5 5
5_______FNDSCH Scheduler/Prereleaser Manager 1 1 0 0 0
1024____Fast Jobs Fast 5 5 0 0 0
1025____Slow Jobs Slow 2 2 0 2 2
1026____PS Manager Packing Slip Manager 6 6 0 0 0
1046____Pick Slip ManagPick Slip Manager 6 6 0 0 0
1066____Debug_Service Debug Service 1 1 0 0 0
1102____OAMCOLMGR OAM Metrics Collection Manager 1 1 0 0 0
1124____C_AQCT_SVC C AQCART Service 0 0 0 0 0
1214____FNDCPOPP Output Post Processor 5 5 0 0 0
1215____WFALSNRSVC Workflow Agent Listener Service 1 1 0 0 0
1216____WFMLRSVC Workflow Mailer Service 1 1 0 0 0
1217____WFWSSVC Workflow Document Web Services Service 1 1 0 0 0
1415____DownloadProcessEmail Center Download Processor - Normal0 0 0 0 0
1416____DownloadProcessEmail Center Download Processor - Migrat0 0 0 0 0
15______PODAMGR PO Document Approval Manager 3 3 0 0 0
222_____RCVOLTM Receiving Transaction Manager 3 3 0 0 0
1219____WMSTAMGR WMS Task Archiving Manager 0 0 0 0 0
10______INVMGR Inventory Manager 5 5 0 0 0
11______INVTMRPM INV Remote Procedure Manager 4 4 0 0 0
1079____OKCCONCMGR Contracts Core Concurrent Manager 0 0 0 0 0
1213____AMSDMIN Marketing Data Mining Manager 0 0 0 0 0
1080____IEXCONMGR Collections Manager 0 0 0 0 0
39______MRPMGR MRP Manager 2 2 0 0 0
12______CRPINQMGR CRP Inquiry Manager 0 0 0 0 0
1218____FTE_TXN_MANAGERTransportation Manager 0 0 0 0 0
1355____FFTM FastFormula Transaction Manager 0 0 0 0 0
====================================================================================================
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,
sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
where a.concurrent_queue_id = b.concurrent_queue_id
AND b.Requested_Start_Date<=SYSDATE
GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;
*** Concurrent QUEUE Details ***
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue '||
'Concurrent Queue Name '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP
--for each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts,
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
Sample Out put
====================================================================================================
QueueID Queue Concurrent Queue Name MAX RUN Running Pending In CRM
====================================================================================================
0_______STANDARD Standard Manager 30 30 19 3 3
1_______FNDICM Internal Manager 1 1 0 0 0
4_______FNDCRM Conflict Resolution Manager 1 1 0 5 5
5_______FNDSCH Scheduler/Prereleaser Manager 1 1 0 0 0
1024____Fast Jobs Fast 5 5 0 0 0
1025____Slow Jobs Slow 2 2 0 2 2
1026____PS Manager Packing Slip Manager 6 6 0 0 0
1046____Pick Slip ManagPick Slip Manager 6 6 0 0 0
1066____Debug_Service Debug Service 1 1 0 0 0
1102____OAMCOLMGR OAM Metrics Collection Manager 1 1 0 0 0
1124____C_AQCT_SVC C AQCART Service 0 0 0 0 0
1214____FNDCPOPP Output Post Processor 5 5 0 0 0
1215____WFALSNRSVC Workflow Agent Listener Service 1 1 0 0 0
1216____WFMLRSVC Workflow Mailer Service 1 1 0 0 0
1217____WFWSSVC Workflow Document Web Services Service 1 1 0 0 0
1415____DownloadProcessEmail Center Download Processor - Normal0 0 0 0 0
1416____DownloadProcessEmail Center Download Processor - Migrat0 0 0 0 0
15______PODAMGR PO Document Approval Manager 3 3 0 0 0
222_____RCVOLTM Receiving Transaction Manager 3 3 0 0 0
1219____WMSTAMGR WMS Task Archiving Manager 0 0 0 0 0
10______INVMGR Inventory Manager 5 5 0 0 0
11______INVTMRPM INV Remote Procedure Manager 4 4 0 0 0
1079____OKCCONCMGR Contracts Core Concurrent Manager 0 0 0 0 0
1213____AMSDMIN Marketing Data Mining Manager 0 0 0 0 0
1080____IEXCONMGR Collections Manager 0 0 0 0 0
39______MRPMGR MRP Manager 2 2 0 0 0
12______CRPINQMGR CRP Inquiry Manager 0 0 0 0 0
1218____FTE_TXN_MANAGERTransportation Manager 0 0 0 0 0
1355____FFTM FastFormula Transaction Manager 0 0 0 0 0
====================================================================================================
