Workflow Notification Mailer – Troubleshooting Outbound Processing

Overview

Oracle E-Business Suite’s Workflow Notification Mailer sends an
email notification in a multi-step process. If an email notification is
not sent, the following needs to be checked.

After a workflow notification is sent, it immediately appears in
the recipient’s EBS Worklist UI. For each workflow notification,
business event oracle.apps.wf.notification.send is raised to send the same notification as email. This business event, through its event group oracle.apps.wf.notification.send.group, has the following subscriptions:

For a workflow notification to be e-mailed, following statements should be true:

1. The notification’s STATUS is OPEN or CANCELED
2. The notification’s MAIL_STATUS is MAIL or INVALID
3. The recipient role has a valid email address
4.  The recipient role’s notification preference must be MAILTEXT, MAILATTH, MAILHTML or MAILHTM2
5. The Workflow Deferred Agent Listener is running
6. The Workflow Notification Mailer is running

Most of the information above can be obtained by running the diagnostic script $FND_TOP/sql/wfmlrdbg.sql. It takes the notification id as input.

Tracing Outbound Email Notification

After the business event oracle.apps.wf.notification.send is raised, it is processed through two queues before it is actually delivered as email to the recipient’s Inbox:

WF_DEFERRED

LOCAL subscription to the event is responsible for processing the
email notification content. Since it’s phase is 100, it is enqueued to
WF_DEFERRED queue as soon as it is raised. Workflow Deferred Agent Listener dequeues the event and performs below processing

1. Executes generate function associated to the send event – WF_XML.Generate. This generates the email notification payload.
2. Executes the rule function associated to the LOCAL subscription – WF_XML.Send_Rule. This determines if the email is eligible to be sent and enqueues to WF_NOTIFICATION_OUT queue.

If an error occurs while processing the send event, the ERROR subscription is executed and the notification event message is placed on WF_ERROR queue. The
Workflow Error Agent Listener dispatches the ERROR subscription for
oracle.apps.wf.notification.send that executes WF_XML.Error_Rule.

WF_NOTIFICATION_OUT

The Workflow Notification Mailer dequeues the send event messages
from this queue and dispatches it through the designated SMTP server.

To determine at a given time where the email notification is being processed, run $FND_TOP/sql/wfmlrdbg.sql for the notification id.

Here are possible stages:

Email Notification XML Payload

When the event is processed from WF_DEFERRED queue, WF_XML.Generate
function generates an XML payload that is later used by Workflow
Notification Mailer to create the email notification. The XML payload
can be seen in the output of wfmlrdbg.sql as it is generated again when
this script is run. It is recommended to actually review the XML payload
of the email notification already existing in queue WF_NOTIFICATION_OUT
which is the source of truth for what the Workflow Notification Mailer
would process.

To retrieve the original XML payload, the following SQL query can be used:

select tab.user_data.text_lob text_lob
from applsys.aq$wf_notification_out  tab
where tab.USER_DATA.get_string_property('BES_EVENT_KEY') = &NID;  

where NID is the notification id in question.

Related Articles

• Increase throughput of your Workflow Mailers
• Asynchronous Business Event Subscriptions – Troubleshooting Tips