Notification Mailer troubleshooting (Part 1)

This is the first article in series of articles dedicated to a “Notification Mailer” or in other words to an email sending functionality in EBS. We will talk about what steps involved in the email sending process and what tools and methods Apps DBAs have to troubleshoot each of these steps. From my 8 years (since 2001) experience working with EBS as Apps DBA it looks like “Notification Mailer” is one of the grey areas for Apps DBAs which aren’t described well enough and on the other hand we are asked to troubleshoot it on regular basis. As it is my very first personal article I would appreciate any feedback from your side. Please do not hesitate to leave comments, proposals or recommendations at the bottom of the page or dropping an email to me.

Some difficulties in troubleshooting “Notification Mailer” may be due to the fact that the email sending process in EBS isn’t transparent and intuitive. It involves several steps from different EBS areas and utilizes multiple Oracle technology components like:

Workflow Notification module

Generic Service Management Framework (GSM)

Business Event System (Subscriptions, Deferred processing)

Workflow Directory and users setup

Vacation rules or routing rules

Oracle Advance Queues

As you see the list includes some important components from the Applications Technology (ATG) modules. The Mailer example gives us an excellent opportunity to describe these areas and give some useful hints to Apps DBA for further troubleshooting. Obviously it is impossible to cover all these areas in one article. Hopefully with all your support in this series of articles I will describe the email sending process in details.

Overview of EBS email sending process

Let’s take a look on the overall email sending process and the main steps involved:

Mailer Flow

There are three main processes (in blue) and three data stores involved. The data stores include 2 Advanced Queues (in red) and 1 table (green). At this point I would like to mention that AQ are not traditional relational tables. An additional care should be taken while accessing these tables (we will talk about this later). The email sending process consists of the following main steps:

EBS user sends email – To send an email EBS modules use standard API. Email API is implemented in PL/SQL package WF_NOTIFICATION (I will cover it in the next article).

Defers processing Generates event – a user or process leaves EBS to run further email processing steps. It is done using a Business Events System (BES). Session raises an event “oracle.apps.wf.notification.send” via the WF_EVENT PL/SQL package (BES processing to be covered in the next articles). Each deferred event is put in one of the two Advanced Queues WF_DEFERRED or WF_JAVA_DEFERRED for further processing. All email sending events go through the WF_DEFERRED queue.

Deferred Agent Listener – is a process responsible for ALL BES events processing. It executes all deferred events calling subscriptions’ functions defined for each business event. There are several more things to explain about Agent Listeners and subscription processing (e.g. there are several differed agents, subscriptions groups etc.) This is one more subject for further articles.</li>

Reads event and starts subscriptions processing – Strictly speaking there is no any enabled subscription for the “oracle.apps.wf.notification.send” event (submitted during the first step). This event is a part of “oracle.apps.wf.notification.send.group” event group. The Deferred Agent executes subscriptions for that group rather than for the stand alone event. At this stage the Agent knows that it should process the notification with given notification id (it is a part of the event data passed via the event).

Reads application data – in order to generate the email/notification the Agent reads business data from the WF_NOTIFICATIONS table and a few related tables and during the next step builds up the email’s text in XML format.

Generates data for outbound interface – This is the last step executed by the Deferred Agent Listener. It generates XML representation of email to be sent and together with other important bits of information posts it to the Notification Mailer outbound queue WF_NOTIFICATION_OUT.

Notification Mailer – As you see it was a long journey even before we started to talk about the Notification Mailer. There are a lot of things which may go wrong and this is why it is important to know the whole flow of the events to troubleshoot the mail sending functionality in EBS. We’ve come to the last processing step before the message leaves EBS boundaries.</li>

Reads message – the Notification Mailer dequeues messages from WF_NOTIFICATION_OUT _queue on regular basis. In fact this is the only place where it looks for the new messages to be sent. This means if a notification doesn’t has a corresponding event ready for dequeuing in the _WF_NOTIFICATION_OUT queue it will never be send. As soon as a new message arrives Notification Mailer dequeues it and gets prepared for sending;

Sends email via SMTP – This is the step when the message leaves EBS. The Notification Mailer sends the email using text retrieved from the advanced queue during previous step;

Updates status – as the last step in the notification sending process the Notification Mailer updates a MAIL_STATUS column in WF_NOTIFICATION table.

This article gives us rather good basis for further discussion of how to troubleshoot the notifications/emails sending process in Oracle e-Business Suite. Now we are aware of the complete email sending flow and ready for the detailed discussion on how to troubleshoot it. Just to wrap up this very first article I would like to mention that the notification sending process didn’t change a lot since the moment when a Java based Notification Mailer has been introduced (~11.5.7 if I recall correctly) and it is still there in R12.1.1 version. That means that everything we discussed and will discuss is applicable to all current EBS versions.

Just to remind you - your feedback is important for me and it will assist to put efforts into the right direction in further contribution to the Apps DBA community.