R12: Conc Manger Status

Hi Everyone!!

Good to meet you all in this post.
Here is the piece of code which DBA's can make use of in sql prompt to check the concurrent manager's statuses with out going to GUI.

Run the Below Code in sqlplus as APPS account. (tested in R12.1)

-- http://applicationsdba.blogspot.com
set verify off
set lines 256
set trims ON
set pages 60
col concurrent_queue_id format 99999 heading "QUEUE Id"
col concurrent_queue_name format a20 trunc heading "QUEUE Code"
col user_concurrent_queue_name format a30 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"
col target_node format a15 heading "Node"
col status format a12 trunc heading "Status"
col run format 9999 heading 'Run'
col pend format 9999 heading 'Pending'
col cmgr_program FOR a65;
SELECT 'Instance : '
    ||NAME instance_name
FROM   v$database;

Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
      q.concurrent_queue_name,
      q.user_concurrent_queue_name,
      q.target_node,
      q.max_processes,
      q.running_processes,
      running.run                               running,
      pending.pend,
      Decode(q.control_code, 'D', 'Deactivating',
                             'E', 'Deactivated',
                             'N', 'Node unavai',
                             'A', 'Activating',
                             'X', 'Terminated',
                             'T', 'Terminating',
                             'V', 'Verifying',
                             'O', 'Suspending',
                             'P', 'Suspended',
                             'Q', 'Resuming',
                             'R', 'Restarting') status
FROM   (SELECT concurrent_queue_name,
              COUNT(phase_code) run
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'R'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) running,
      (SELECT concurrent_queue_name,
              COUNT(phase_code) pend
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'P'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) pending,
      apps.fnd_concurrent_queues_vl q
WHERE  q.concurrent_queue_name = running.concurrent_queue_name(+)
      AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
      AND q.enabled_flag = 'Y'
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;
                                                       

This is as equivalent as Concurrent -> Manager -> Administer in forms GUI.


Hope this will help you.

Disclaimer: Please test this before you use.