Workflow Notification Mailer

Workflow Notification Mailer

Steps to start/stop notification mailer

1. Check workflow mailer service current status

  sqlplus apps/<apps password>

  select running_processes

    from apps.fnd_concurrent_queues

   where concurrent_queue_name = 'WFMLRSVC';

 

  Number of running processes should be greater than 0

2. Find current mailer status

  sqlplus apps/<apps password>

  select component_status

    from apps.fnd_svc_components

   where component_id =

        (select component_id

           from apps.fnd_svc_components

          where component_name = 'Workflow Notification Mailer');

 

  Possible values:

  RUNNING

  STARTING

  STOPPED_ERROR

  DEACTIVATED_USER

  DEACTIVATED_SYSTEM

2. Stop notification mailer

  sqlplus apps/<apps password>

  declare

       p_retcode number;

       p_errbuf varchar2(100);

       m_mailerid fnd_svc_components.component_id%TYPE;

  begin

       -- Find mailer Id

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

       select component_id

         into m_mailerid

         from fnd_svc_components

        where component_name = 'Workflow Notification Mailer';

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

       -- Stop Mailer

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

       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);

       commit;

  end;

  /

3. Start notification mailer

  sqlplus apps/<apps password>

  declare

       p_retcode number;

       p_errbuf varchar2(100);

       m_mailerid fnd_svc_components.component_id%TYPE;

  begin

       -- Find mailer Id

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

       select component_id

         into m_mailerid

         from fnd_svc_components

        where component_name = 'Workflow Notification Mailer';

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

       -- Start Mailer

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

       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);

       commit;

  end;

  /

 

A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a

count of data is at best only a snapshot of a particular moment in time.

1. Here is a more robust script for monitoring the wf_notifications table:

select message_type, mail_status, count(*) from wf_notifications

where status = 'OPEN'

GROUP BY MESSAGE_TYPE, MAIL_STATUS

messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'

messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'

2. Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.

 

select corr_id, retry_count, msg_state, count(*)

from applsys.aq$wf_notification_out

group by corr_id, msg_state, retry_count

order by count(*) desc;

Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql

1.Please provide the below query output in excel sheet.
select * from fnd_nodes;

2.Please provide the screen shot of below profile option value.

WF: Workflow Mailer Framework Web Agent

1. The uploaded file “ATGSuppJavaMailerSetup12_9392180_diag.txt” shows that the correct parameter was not used when attempting to run. For instance “‘R-ex-hub01.internal.kfshrc.edu.sa'” was used for “Inbound mail server (INBOUND_SERVER)” and on the other hand the file “wfmlrdbg23569786.html” shows the INBOUND_SERVER is “appforms3.kfshrc.edu.sa”. For that reason the test failed and “Server {R-ex-hub01.internal.kfshrc.edu.sa} at port {143} is not reachable” was generated.

Please do the following this.

a. Please un the following SQL to collect necessary info before executing “ATGSuppJavaMailerSetup12.sh”. The SQL will collect all the info required to run the script except IMAP account password.

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’)

b. Then run “ATGSuppJavaMailerSetup12.sh” per the steps from 748421.1.

3. Please upload the new output to help us collect the data to help us troubleshoot the issue.

4. Please also upload the Autoconfig log files

a. Database Tier Autoconfig log :

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/<MMDDHHMM>/adconfig.log
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/<MMDDHHMM>/NetServiceHandler.log

b. Application Tier Autoconfig log –

$INST_TOP/apps/$CONTEXT_NAME/admin/log/<MMDDHHMM>/adconfig.log

5. Restart the Workflow Mailer and Agent Listener services

a. From Self Service > Select “Workflow Manager” under “Oracle Applications Manager” > Click “Notification Mailers” > Service Components (Service Components: <SID>) >
b. Click “Workflow Mailer Service” under “Container” Column.
e. From “Service Instances for Generic Service Component Container:<SID>”page, click “Pull Down” button from the Start All/ Stop All.
f . Select Stop All > Go.
g. We conformed that for the Services to read Actual 0 Target 0 and Deactivated.
h. Restart the mailer services using the “Start All” button.
I. We run the following SQL to make sure service are stopped.

SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like ‘Workflow%’;

6.However all the services are sill down (Stopped).

STATEMENT level logging is already enabled.

7.About to upload the log files from $APPLCSF/$APPLLOG/FNDCPGSC*.txt i.e. the log file for the Active process for Workflow Mailer Service and Agent Listener services.

To retrieve the last 2 log files for Workflow Mailer and Agent Listener services, run the following command:

ls -lt $APPLCSF/$APPLLOG/FNDCPGSC*.txt

8. The issue started yesterday after running Autoconfig to correct Discoverer issue per Oracle Support.
I might request Autoconfig logs after reviewing the mailer log..

1. Please run the script $FND_TOP/sql/afsvcpup.sql, and edit Notification Mailer , and change the parameter Framework URL Timeout from 30 to 120.

2. Use the same script to change mailer’s parameter Log Level to 1.

3. Set the profile option WF: Workflow Mailer Framework Web Agent to a physical web tier, using the syntax http://WEBHOST:port

4. Bounce the mailer.

5. Please follow the navigation below:
a. Workflow Administrator web Applications Responsibility
b. Workflow Manager
c. Click on the Notification Mailers icon
d. Click on Workflow Notification Mailers link
e. Click on Test Mailer button
Enter the user name of the user with the problem, and verify if a test mail is received.

Then, please provide the output of the script wfmlrdbg.sql using the steps from note 1364300.1 for the id’s returned from the following two queries:

select max(notification_id)
from wf_notifications
where message_type=’WFTESTS’
and message_name =’OAFWK_MSG’

. Then, provide the mailer log returned by the following query:

SELECT fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in (‘WFMLRSVC’)
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type =’CP_PROCESS_STATUS_CODE’
AND meaning=’Active’

Please run the diagnostics from Note 748421.1 Oracle Workflow ATG Support: R12 Java Mailer Setup Diagnostic Test and upload its archive file.

How does workflow Notification Mailer IMAP (Inbound Processing) Works:

This is the inbound flow:

1. Approver sends email response which is directed to the value defined in Replyto address.
a. This address has been setup by the customer’s mail administrator to route incoming mail to the IMAP Inbox folder.
2. The Workflow Inbound Agent Listener picks up the message. Only messages which are in ‘unread’ state are evaluated; the rest of the messages in the inbox are ignored.

3. The message is scanned to see if it matches entries in the TAG file . Mail tags are defined in the OAM mailer configuration pages and these list strings of text and actions to take if those strings are encountered. An example of this are ‘Out of Office’ replies. If the string of the message matches a mail tag and the action is ‘Ignore’ then nothing else will happen.

4. The message is then scanned for the presence of a Notification Id (NID). This NID is matched against valid NID for the mailer node.

5. If valid NID is not detected, (and there is no matching TAG file entry) then the message is placed into the DISCARD folder.

6. If a valid NID is detected the listener raises a subscription to the WF_NOTIFICATION_IN queue and places the mail message in the Processed folder.

7. From this point on the message is handled by the product workflow (like PO APPROVAL) . An event created by that group will monitor the WF_NOTIFICATION_IN queue and will trigger the rest of the approval workflow.

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key

2. There is seeded subscription to this Event

3. Event is placed on WF_DEFERRED agent

4.Event is dequeued from WF_DEFERRED and subscription is processed

5. Subscription places event message to WF_NOTIFICATION_OUT agent.

6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (IfTest Address/Override Address is set then email is sent to Test Address

 

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED   and
b) Notification mail_status is MAIL or INVALID  and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status  FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)

How to purge e-mail notifications from the Workflow queue

Some times Due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.

The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = ‘OPEN’ and mail_status = ‘MAIL’ are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = ‘SENT’. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = ‘SENT’ where mail_status = ‘MAIL’;

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer

 

Example Of Configuring Workflow Java Notification Mailer With Oracle Applications [ID 249957.1]
	Modified 25-FEB-2012     Type HOWTO     Status PUBLISHED

 

Applies to:

Oracle Workflow Cartridge – Version: 11.5.9 and later   [Release: 11.5 and later ]
Information in this document applies to any platform.
Checked for relevance on 21FEB2012

Goal

The purpose of this note is to highlight the steps required to succesfully configure the Oracle Workflow Java Notification Mailer.

Solution

The content of this note is directed at anyone who is configuring the Oracle Workflow Java Notification Mailer.  Each configuration step is validated during the configuration process to minimize the risk of errors later on.
This note should be used in conjunction with Note 231286.1 Configuring the Workflow Java-based Notification Mailer with Oracle Applications 11i.

The Oracle Workflow Java Notification Mailer is installed with patchset 11i.OWF.G – Patch 2728236. This patchset is included by default with Oracle Applications 11.5.9.

Pre-requisite Considerations:

• If one is applying patchset 11i.OWF.G, a mandatory step is to implement the new workflow java mailer.

• If one is configuring IMAP Server for Inbound Processing with Workflow Java Mailer, then one must have a unique IMAP4 compliant email account for the mailer.

• If one does not have an IMAP server and requires inbound notification transaction processing, then one cannot apply OWF.G as this functionality will no longer work as it has been obsoleted.

• If one does not require inbound notification processing, one can now configure the java mailer without an IMAP account see Note 268274.1 How To Configure The Workflow Java Mailer Without An IMAP Account (Assumes No Inbound Processing).

• The new java based workflow mailer uses SMTP (port25) for outbound transactions, so client software is not required. The Workflow mailer makes a TCP/IP connection to the SMTP server on port 25 and issues the SMTP commands required to send the email.

Example Of Configuring the Workflow Java Notification Mailer

1. Setup an IMAP email user (e.g wfmailer) with a mailbox.

2. Configure an IMAP compliant email application to be able to connect to the account

Note: The IMAP email account MUST be able to receive emails before continuing to step 3
If you need help configuring IMAP4, please visit the URL http://imap.org

3. Connect an email client to the IMAP4 account and synchronize the IMAP folders.

4. Create the PROCESSED AND DISCARD folders from the EMAIL application (e.g. MS Outlook Express)

Note: this step can be also be performed from the IMAP4 server:
a) ‘Edit’ the mailbox for the wfmailer user
b) ‘Add’ the PROCESSED AND DISCARD folders.

5. From the Concurrent processing tier, issue the command ‘ping SERVERNAME’

Note 1: if there is no response from this command then there could be a network config issue.
In this case, add the following entry to your hosts file:
(Replace the ipaddress, servername and domain with their actual values)

ipaddress    SERVERNAME.DOMAIN     SERVERNAME

Note 2: please ensure you are able to ping the server successfully before proceeding to the next step.

6. Validate the SMTP server from the Concurrent processing tier, by using the command ‘telnet SERVERNAME 25’.

‘telnet SERVERNAME 25’ should return something similar to:
220 otdasol4.us.oracle.com ESMTP Sendmail 8.11.6+Sun/8.11.6; Thu, 2 Oct 2003 02:45:57 -0600 (MDT)

Note 1: verify that the response includes a reference to ‘SMTP’

Note 2: please ensure that the email account is able to send emails successfully via SMTP before
continuing to step 7.

7. Add the email address to Oracle Applications:

a) Log in to Oracle Applications as System Administrator and navigate to SECURITY>>USER>>DEFINE.

b) Query a user and add a valid email address.

8. Perform the required Workflow Mailer setup steps in OAM.  Please log in to OAM to edit WF Mailer configuration details

a) Please follow these example details for your configuration values in page 3.

Inbound EMail Account (from step 1)
* Protocol  IMAP
* Server Name =      SERVNAME.DOMAIN
* Username    = wfmailer
* Password    = ******
* Inbox Folder= INBOX
* Test Address=

Outbound EMail Account (from step 6)
* Protocol  SMTP
* Server Name = SERVNAME.DOMAIN

* Processed Folder   = PROCESS (from step 3)
* Discard Folder     = DISCARD

b) Select ‘Next’ in the OAM edit screen.

At this point, validation checking is performed for the above settings before progress to page 4.
Any errors are highlighted in red.

c) Select ‘Finish’ twice

9. Test the Mailer

a) Select the Workflow Notification Mailer hyperlink.

b) Select ‘Test Mailer’ button.

c) Enter the username that was added at step 7.

10. If no email is received then follow the troubleshooting steps below.

Troubleshooting steps:

If no email is received when testing the Mailer, please try the following steps:

1. Please check the status of the Java Mailer:

a) Navigate to the OAM Home Page =>Workflow Manager => Service Components.

b) Check the status of the java mailer.

2. If the Java Mailer is not working, check the java mailer log file for related errors.
Select the ‘Workflow Notification Mailer’ hyperlink => View Log

3. If the Java Mailer is not running, if any errors occur in the log file, please review the following troubleshooting notes:

Note 242941.1 How To Troubleshoot Workfow Java Notification Mailer In 11.5.9 and 11i.OWF.G

Note 260393.1 Java Mailer and Other Current Workflow 11.5.9/OWF.G Issues In Apps 11i

4. If no error is occurring, apply Patch 3203709 (just a c driver 49k.) This patch updates the
error messaging code for Workflow, so that more meaningful error messages return when problems arise.

5. Try enabling debugging:

a) Login to OAM
b) Navigate Workflow Manager =>Service Components =>Workflow Mailer Service =>Edit
c) open the Edit service parameters screen
d) set parameter SVC_CONTAINER_LOG_LEVEL=1   (1 is the highest 5 is the lowest)
e) select ‘OK’ to save the change
f) restart the mailer
g) reproduce the problem and review the logfile again