R12: Find Service Transaction Managers Status

select *
  from (select q.user_concurrent_queue_name service_name,
               a.application_name srvc_app_name,
               a.application_short_name srvc_app_short_name,
               q.concurrent_queue_name service_short_name,
               decode( ( select count(*)
          from apps.fnd_concurrent_processes fcp1
         where fcp1.concurrent_queue_id = q.concurrent_queue_id
           and fcp1.queue_application_id = q.application_id
           and ( fcp1.process_status_code in ('C','M')
               or (fcp1.process_status_code in ('A', 'D', 'T')
               and exists (select 1
                             from gv$session
                            where fcp1.session_id = audsid )))
        )/*actual_processes */, 0,
       decode(q.max_processes, 0,'NOT_STARTED', 'DOWN'),
              q.max_processes, 'UP', 'WARNING' ) service_status,
              q.max_processes target_processes,
        (select count(*)
           from apps.fnd_concurrent_processes fcp2
          where fcp2.concurrent_queue_id = q.concurrent_queue_id
            and fcp2.queue_application_id = q.application_id
            and ( fcp2.process_status_code in ('C','M')
             /* Connecting or Migrating */
             or ( fcp2.process_status_code in ('A', 'D', 'T')
            and exists (select 1 from gv$session
                         where fcp2.session_id = audsid)))) actual_processes,
                        '' message, s.service_handle srvc_handle
                        from apps.fnd_concurrent_queues_vl q,                            
                                 apps.fnd_application_vl a,
                                apps.fnd_cp_services s
                       where q.application_id = a.application_id
                         and s.service_id = q.manager_type
                      UNION
                   /* Need to cover the case where a manager has no rows in
                      FND_CONCURRENT_PROCESSES. Outer joins won't cut it. */
                     select q.user_concurrent_queue_name service_name,
                            a.application_name srvc_app_name,
                            a.application_short_name srvc_app_short_name,
                            q.concurrent_queue_name srvc_short_name,
                            decode( q.max_processes, 0, 'NOT_STARTED', 'DOWN') service_status,
                            q.max_processes target_processes,
                            0 actual_processes,
                            '' message, s.service_handle srvc_handle
                      from apps.fnd_concurrent_queues_vl q, apps.fnd_application_vl a,
                           apps.fnd_cp_services s
                     where q.application_id = a.application_id
                       and s.service_id = q.manager_type
                       and not exists (select 1 from apps.fnd_concurrent_processes p
                                        where process_status_code in ('C','M','A','D','T')
                                          and q.concurrent_queue_id = p.concurrent_queue_id
                                          and q.application_id = p.queue_application_id) )
                   where service_name= 'MRP Manager'   --'Inventory Manager'
               order by service_status;