SQL to check Workflow Agent Listeners are running

We often need to check the status of Workflow Agent listener , WF Java Deferred Agent Listeners in Oracle EBS environment. Sometime a transaction might be struck somewhere and we need to trouble shoot. The queries given in the below post will help in checking about workflow related settings

SQL to check the AQ setup

set echo on
set timing on
set feedback on
set pagesize 132
set linesize 100
col name form a30
col queue_table form a30
col enqueue_enabled form a7
col dequeue_enabled form a7
col retention form a20
--
select name,
queue_table,
enqueue_enabled,
dequeue_enabled,
retention
from dba_queues
where owner = 'APPLSYS'
and queue_type = 'NORMAL_QUEUE'
order by 1
/

Sql to check for Workflow Agent Listener/WF Java Deferred Agent Listeners are running

set pagesize 132
set linesize 90
col COMPONENT_NAME form a50
col COMPONENT_STATUS form a15
col COMPONENT_TYPE form a25
col STARTUP_MODE form a10
col CONTAINER_TYPE form a5
col INBOUND_AGENT_NAME form a20
col OUTBOUND_AGENT_NAME form a20
col CORRELATION_ID form a30
col MAX_IDLE_TIME form 9999999
rem
select
COMPONENT_NAME,
COMPONENT_STATUS,
COMPONENT_TYPE,
STARTUP_MODE,
CONTAINER_TYPE,
INBOUND_AGENT_NAME,
OUTBOUND_AGENT_NAME,
CORRELATION_ID,
MAX_IDLE_TIME
from FND_SVC_COMPONENTS
order by COMPONENT_NAME
/

select component_id, component_name NAME, component_status , component_type, container_type from fnd_svc_components;

SELECT module, 
 username, 
 status, 
 action, 
 schemaname, 
 osuser, 
 process, 
 machine, 
 program
 FROM   gv$session
 WHERE  module like 'APPS:SVC:GSM%' ;

$FND_TOP/sql/wfver.sql output also shows the status of the workflow services:

Service Instance Status 
______________________________________________________________________________ 
Workflow Agent Listener Service is enabled -> Actual: 1, Target: 1 
Workflow Mailer Service is enabled -> Actual: 1, Target: 1 
Workflow Document Web Services Service is enabled -> Actual: 1, Target: 1 

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not 
Running',fcp.OS_PROCESS_ID) PROCID, 
fcq.MAX_PROCESSES TARGET, 
fcq.RUNNING_PROCESSES ACTUAL, 
fcq.ENABLED_FLAG ENABLED, 
fsc.COMPONENT_NAME, 
fsc.STARTUP_MODE, 
fsc.COMPONENT_STATUS 
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES 
fcp, fnd_svc_components fsc 
where fcq.MANAGER_TYPE = fcs.SERVICE_ID 
and fcs.SERVICE_HANDLE = 'FNDCPGSC' 
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) 
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+) 
and fcq.application_id = fcp.queue_application_id(+) 
and fcp.process_status_code(+) = 'A' 
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE 

The TARGET and ACTUAL columns should be 1. If one of these columns is 1 and the other one zero then the workflow service is not running.

check the status of Agent Listeners:

set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

Check the status of messages in the Queue

select corr_id,
 count(*)
 from applsys.aq$wf_deferred
 where msg_state = 'READY'
 group by corr_id;

select msg_state,
 count(*)
 from applsys.aq$wf_java_deferred
 group by msg_state;

Check the messages in WF_NOTIFICATION queue

column corrid format A50
column state format A15
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,0,' 0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*) COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;

select count(*) COUNT,decode(wfe.state,0,' 0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State from applsys.wf_notification_out wfe group by state;

select count(*) COUNT, State from applsys.wf_notification_out wfe group by state;

select count(*) COUNT from applsys.wf_deferred  wfe where corrid='APPS:oracle.apps.wf.notification.send';

Query to get the log file of active workflow mailer and workflow agent listener Container

–Note All Workflow Agent Components logs will stored in single file ie. container log file.

select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;

Also Reads
log file location in oracle apps r12 : Check out this post for the common logfiles location for 11i,R12.0.R12.1 and R12.2. Both the database and application tier logfiles are given
ADMRGPCH : Learn about AD Utilities(adadmin, adrelink ,adident,ADMRGPCH),How to run them, How to maintain application files using adadmin
oracle ebs failed login attempts : check oracle ebs failed login attempts, How to enable signon auditing , Auditing reports in EBS,how to purge signon audit data,oracle ebs user login history
How to check the status of Notification Mailer : How to check the status of Workflow Notification Mailer from Backend, How to stop/start the Workflow mailer through script