Query To Check Inactive/Down Concurrent Managers Details

Download(downcmstat.sql)

REM +==========================================================================+
REM                    
REM File Name: downcmstat.sql
REM 
REM Description:
REM   Query To Check Concurrent Managers Which are Currently Down or INACTIVE
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @downcmstat.sql 
REM   
REM +==========================================================================+

Clear columns
SET lines 180
 COL "Concurrent Manager" for a50
 col "Node" for a20
 SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
    ,DECODE (b.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 apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
  WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.running_processes <> a.max_processes
 UNION
 SELECT b.user_concurrent_queue_name "Concurrent Manager", a.TARGET_NODE "Node", a.running_processes "ACTUAL Processes", a.max_processes "TARGET Processes"
    ,DECODE (b.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 apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
  WHERE a.concurrent_queue_id = b.concurrent_queue_id AND a.concurrent_queue_name = 'FNDICM' AND (a.running_processes = 0 OR a.max_processes = 0);