Workflow Mailer Config/Status

 

Do the query below to get the config of the workflow mailer:
set linesize 130;
col value for a30;
select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER','ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;
SQL> /
PARAMETER_ID PARAMETER_NAME VALUE
------------ -------------- ------------------------------
10018 ACCOUNT workflow
10026 DISCARD DISCARD
10029 FROM Workflow Mailer
10033 INBOUND_SERVER 10.0.0.249
10034 INBOX INBOX
10037 NODENAME WFMAIL
10043 OUTBOUND_SERVER mailsrv.home.co.za
10044 PROCESS PROCESS
10053 REPLYTO workflow@home.co.za

9 rows selected.
SQL>

Display the status of all the listeners and agents:
SQL>
set pages 44;
set linesize 130;
select COMPONENT_NAME,COMPONENT_STATUS from fnd_svc_components;
COMPONENT_NAME COMPONENT_STATUS
--------------- ------------------------------
ECX Inbound Agent Listener STOPPED
ECX Transaction Agent Listener STOPPED
Workflow Deferred Agent Listener RUNNING
Workflow Deferred Notification Agent Listener RUNNING
Workflow Error Agent Listener RUNNING
Workflow Inbound Notifications Agent Listener RUNNING
Workflow Notification Mailer RUNNING
WF_JMS_IN Listener(M4U) RUNNING
Web Services OUT Agent STOPPED
Web Services IN Agent STOPPED
Workflow Java Deferred Agent Listener RUNNING
Workflow Java Error Agent Listener RUNNING
Workflow Inbound JMS Agent Listener STOPPED
13 rows selected.
SQL>
To check WF mail status
col mail_status for a12;
select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;
COUNT(*) MAIL_STATUS
---------- ------------
91 FAILED
218
3 MAIL
2 ERROR
471 SENT
SQL>

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;
COMPONENT_NAME STARTUP_MODE COMPONENT_STATU
--------------------------------------------- --------------- ---------------
WF_JMS_IN Listener(M4U) AUTOMATIC RUNNING
Workflow Deferred Agent Listener AUTOMATIC RUNNING
Workflow Deferred Notification Agent Listener AUTOMATIC RUNNING
Workflow Error Agent Listener AUTOMATIC RUNNING
Workflow Inbound Notifications Agent Listener AUTOMATIC RUNNING
Workflow Java Deferred Agent Listener AUTOMATIC RUNNING
Workflow Java Error Agent Listener AUTOMATIC RUNNING
Workflow Notification Mailer AUTOMATIC RUNNING
ECX Inbound Agent Listener MANUAL STOPPED
ECX Transaction Agent Listener MANUAL STOPPED
Web Services IN Agent MANUAL STOPPED
Web Services OUT Agent MANUAL STOPPED
Workflow Inbound JMS Agent Listener MANUAL STOPPED

13 rows selected.
SQL>
Status of the JAVA Workflow Mailer:
SQL> SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;
COMPONENT_STATUS
------------------------------
DEACTIVATED_SYSTEM
SQL> /
COMPONENT_STATUS
------------------------------
STARTING
SQL> /
COMPONENT_STATUS
------------------------------
RUNNING
SQL>
Show inbound,reply and account information:

set pagesize 100
set linesize 132
set feedback off
set verify off

col value format a35
col component_name format a30
select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by c.component_id, c.component_name,p.parameter_name;
COMPONENT_ID COMPONENT_NAME                 PARAMETER_ID         PARAMETER_NAME VALUE
------------ ------------------------------ ------------         --------------------------
10006        Workflow Notification Mailer   10018 ACCOUNT        workflow
10006        Workflow Notification Mailer   10033 INBOUND_SERVER 10.130.10.76
10006        Workflow Notification Mailer   10053 REPLYTO        workflow@customer.co.za

Workflow Mailer Config

set wrap on
set linesize 130;
col parameter_name format a30 head "Parameter Name"
col parameter_value format a50 head "Value"
SELECT
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name = 'Workflow Notification Mailer'
ORDER BY c.parameter_name;

PARAMETER_NAME	PARAMETER_VALUE
ACCOUNT	workflow
ALLOW_FORWARDED_RESPONSE	Y
ALTERNATE_EMAIL_PARSER	oracle.apps.fnd.wf.mailer.DirectEmailParser
ATTACHED_URLS	WFMAIL:ATTACHED_URLS
ATTACH_IMAGES	Y
ATTACH_STYLESHEET	Y
AUTOCLOSE_FYI	Y
CANCELED	WFMAIL:CANCELED
CLOSED	WFMAIL:CLOSED
COMPONENT_LOG_LEVEL	3
DEBUG_MAIL_SESSION	N
DIRECT_RESPONSE	N
DISCARD	DISCARD
EMAIL_PARSER	oracle.apps.fnd.wf.mailer.TemplatedEmailParser
ENABLE_STYLESHEET	N
EXPUNGE_ON_CLOSE	Y
FRAMEWORK_APP	1
FRAMEWORK_RESP	20420
FRAMEWORK_URL_TIMEOUT	30
FRAMEWORK_USER	0
FROM	Workflow Mailer
HTMLAGENT	http://ebs.home.co.za:80/
HTML_DELIMITER	DEFAULT
HTTP_USER_AGENT	Mozilla/4.76
INBOUND_CONNECTION_TIMEOUT	120
INBOUND_FETCH_SIZE	100
INBOUND_MAX_IGNORE_SIZE	1000
INBOUND_MAX_LOOKUP_CACHE_SIZE	100
INBOUND_MAX_RET_EMAIL_SIZE	100
INBOUND_PROTOCOL	IMAP
INBOUND_SERVER	10.0.10.76
INBOUND_SSL_ENABLED	N
INBOUND_UNSOLICITED_THRESHOLD	2
INBOX	INBOX
INLINE_ATTACHMENT	N
MAILER_SSL_TRUSTSTORE	NONE
MAX_INVALID_ADDR_LIST_SIZE	100
MESSAGE_FORMATTER	oracle.apps.fnd.wf.mailer.NotificationFormatter
MORE_INFO_ANSWERED	WFMAIL:MORE_INFO_ANSWERED
NODENAME	WFMAIL
OPEN_INVALID	WFMAIL:OPEN_INVALID
OPEN_INVALID_MORE_INFO	WFMAIL:OPEN_INVALID_MORE_INFO
OPEN_MAIL	WFMAIL:OPEN_MAIL
OPEN_MAIL_DIRECT	WFMAIL:OPEN_MAIL_DIRECT
OPEN_MAIL_FYI	WFMAIL:OPEN_MAIL_FYI
OPEN_MORE_INFO	WFMAIL:OPEN_MORE_INFO
OUTBOUND_CONNECTION_TIMEOUT	120
OUTBOUND_PROTOCOL	SMTP
OUTBOUND_SERVER	oraclerh5pw7.home.co.za
OUTBOUND_SSL_ENABLED	N
OUTBOUND_USER	changeOnJavaMailerInstall
PROCESS	PROCESS
PROCESSOR_DEFER_EVTDATA_READ	Y
PROCESSOR_ERROR_LOOP_SLEEP	60
PROCESSOR_IN_THREAD_COUNT	1
PROCESSOR_LOOP_SLEEP	5
PROCESSOR_MAX_ERROR_COUNT	10
PROCESSOR_MAX_LOOP_SLEEP	60
PROCESSOR_OUT_THREAD_COUNT	1
PROCESSOR_READ_TIMEOUT	10
PROCESSOR_READ_TIMEOUT_CLOSE	Y
REPLYTO	workflow@home.co.za
RESET_NLS	N
SEND_ACCESS_KEY	N
SEND_CANCELED_EMAIL	Y
SEND_UNSOLICITED_WARNING	Y
SUMHTML	WFMAIL:SUMHTML
SUMMARY	WFMAIL:SUMMARY
TEST_ADDRESS	NONE
WARNING	WFMAIL:WARNING