Workflow Mailer Configuration / Status

The following Query to get the config of the workflow mailer:

SQL> conn apps/apps

Connected.

SQL> set linesize 130;

col value for a30;SQL> 

SQL> 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;  

PARAMETER_ID PARAMETER_NAME                 VALUE

------------ ------------------------------ ------------------------------

       10018 ACCOUNT                        NoImapUser

       10026 DISCARD                        DISCARD

       10029 FROM                           Workflow Mailer

       10033 INBOUND_SERVER                 NoImapHost.NoImapDomain

       10034 INBOX                          INBOX

       10037 NODENAME                       WFMAIL

       10043 OUTBOUND_SERVER                localhost.localdomain

       10044 PROCESS                        PROCESS

       10053 REPLYTO                        NoReplyTo

9 rows selected.

Display the status of all the listeners and agents:

SQL> set pages 44;

set linesize 130;

SQL> 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                                                 STOPPED
Workflow Deferred Notification Agent Listener                                    STOPPED
Workflow Error Agent Listener                                                    STOPPED
Workflow Inbound Notifications Agent Listener                                    STOPPED
Workflow Notification Mailer                                                     NOT_CONFIGURED
WF_JMS_IN Listener(M4U)                                                          STOPPED
Web Services OUT Agent                                                           STOPPED
Web Services IN Agent                                                            STOPPED
Workflow Java Deferred Agent Listener                                            STOPPED
COMPONENT_NAME                                                                   COMPONENT_STATUS
-------------------------------------------------------------------------------- ------------------------------
Workflow Java Error Agent Listener                                               STOPPED
Workflow Inbound JMS Agent Listener                                              STOPPED
13 rows selected.


To check WorkFlow mail status:

SQL> col mail_status for a12;

SQL> select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;  2    3    4

no rows selected

Check the status of Agent Listeners:

SQL> set pagesize 400

set linesize 120

set pagesize 50

column COMPONENT_NAME format a45

column STARTUP_MODE format a15

column COMPONENT_STATUS format a15

SQL> 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

--------------------------------------------- --------------- ---------------

Workflow Notification Mailer                  AUTOMATIC       NOT_CONFIGURED

WF_JMS_IN Listener(M4U)                       AUTOMATIC       STOPPED

Workflow Deferred Agent Listener              AUTOMATIC       STOPPED

Workflow Deferred Notification Agent Listener AUTOMATIC       STOPPED

Workflow Error Agent Listener                 AUTOMATIC       STOPPED

Workflow Inbound Notifications Agent Listener AUTOMATIC       STOPPED

Workflow Java Deferred Agent Listener         AUTOMATIC       STOPPED

Workflow Java Error Agent Listener            AUTOMATIC       STOPPED

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.

Status of the JAVA Workflow Mailer:

SQL> SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

COMPONENT_STATU

--------------------------------

NOT_CONFIGURED

Show inbound,reply and account information:

SQL> set pagesize 100

set linesize 132

set feedback off

set verify off

SQL> col value format a35

col component_name format a30

SQL> 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                        NoImapUser

       10006 Workflow Notification Mailer          10033 INBOUND_SERVER                 NoImapHost.NoImapDomain

       10006 Workflow Notification Mailer          10053 REPLYTO                        NoReplyTo

Workflow Mailer Config

SQL> set wrap on

set linesize 130;

col parameter_name format a30 head "Parameter Name"

col parameter_value format a50 head "Value"

SQL> 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                 Value

------------------------------ --------------------------------------------------

ACCOUNT                        NoImapUser

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            5

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://localhost.localdomain:8000/pls/sun

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                 NoImapHost.NoImapDomain

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                localhost.localdomain

OUTBOUND_SSL_ENABLED           N

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                        NoReplyTo

RESET_NLS                      N

SEND_ACCESS_KEY                Y

SEND_CANCELED_EMAIL            Y

SEND_UNSOLICITED_WARNING       Y

SUMHTML                        WFMAIL:SUMHTML

SUMMARY                        WFMAIL:SUMMARY

TEST_ADDRESS                   NONE

WARNING         WFMAIL:WARNING

Workflow Notification Mailer Setup in Oracle Apps R12

                      Oracle Notification Mailer is the program that does e-mail send and response processing for the Oracle Workflow Notification System in the R12 EBS system. It checks for the messages in the database table to send to the respective recipients.

1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.

Steps to configure Workflow Notification Mailer:

1. Login to Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager

If this is first time you are configuring Workflow Notification Mailer in Oracle Apps R12/12i you will see Notification Mailers as unavailable as shown in screenshot

Click on Notification Mailers

In next screen (as shown below, click on Edit Button)

Here you have option to select Inbound notification setup or Just outgoing Notification Setup.

Provide SMTP Server Name (ensure that CM node should be able to connect to SMTP Server or SMTP Relay)

Uncheck Inbound Processing (from above screen), if you don’t wish to configure Inbound Notification Mailer.

If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)

Click on Apply button to finish configuration, at this stage Notification Mailer will test SMTP Server & IMAP Server connectivity.

SQL to monitor (check status) of Workflow Notification Mailer (Java):

 SQL> SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS
where COMPONENT_ID=10006;

Metalink Notes for Notification Mailer1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer