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;
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;
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
--------------- ------------------------------
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;
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
---------- ------------
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_STATUset 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;
--------------------------------------------- --------------- ---------------
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
------------------------------
DEACTIVATED_SYSTEM
SQL> /
COMPONENT_STATUS
------------------------------
STARTING
------------------------------
STARTING
SQL> /
COMPONENT_STATUS
------------------------------
RUNNING
------------------------------
RUNNING
SQL>
Show inbound,reply and account information:
set pagesize 100
set linesize 132
set feedback off
set verify off
set linesize 132
set feedback off
set verify off
col value format a35
col component_name format a30
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;
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
------------ ------------------------------ ------------ --------------------------
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"
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;
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 |
