1、關于郵件發送原理的研究：
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:（Notification Mailer的實作是如下元件協作的結果）
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
如上圖三個步驟（藍色部分），涉及三個地方資料的存儲：2個進階隊列（ Advanced Queues (in red)）和1個表 (green).
注意： AQ并不是傳統的關系表. An additional care should be taken while accessing these tables (we will talk about this later).
郵件發送程序包括如下步驟：
1. EBS user sends email – EBS使用标準的API實作郵件發送，這個Email的API是用過plsql包 WF_NOTIFICATION來實作的，這個WF_NOTIFICATION包後續會講到。
1.1. Provides application data – 首先使用者session插入業務資料 (比如接收者，資訊類型，資訊内容) 到 WF_NOTIFICATIONS 表 (不要和上面提的 PL/SQL 包弄混了，這裡是表);
1.2. Defers processing Generates event –當一個使用者或者程序離開ebs去做發送郵件的工作時，是 通過一個 Business Events System (BES)來做. 會話通過WF_EVENT PL/SQL package 引發一個事件 “oracle.apps.wf.notification.send” 。每一個延遲的事件都被放入到下面這兩個進階隊列中的一個： WF_DEFERRED or WF_JAVA_DEFERRED 來進行接下來的工作。所有的郵件發送事件都會通過WF_DEFERRED隊列.
2. Deferred Agent Listener – 是一個處理所有ebs事件的程序。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.
2.1. 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).
2.2. 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.
2.3. 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 queueWF_NOTIFICATION_OUT.
3. 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.
3.1. Reads message – the Notification Mailer dequeues（出列） messages fromWF_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;
3.2. 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;
3.3. Updates status – as the last step in the notification sending process the Notification Mailer updates a MAIL_STATUS column in WF_NOTIFICATION table.（最後一步，Notification Mailer會更新WF_NOTIFICATION表的MAIL_STATUS列）
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.
根據如下例子我們分析問題：
1、EBS user sends email (第一行) – notification message 在 2009.06.29 14:07:15放入表, 并且enqueued to WF_DEFERRED queue a second later. MAIL_STATUS is set to “MAIL”. STATE in the deferred queue is “READY” ，資訊準備好被出隊列.
2、Deferred Agent Listener (第二行) – the deferred agent processed the message. It dequeued the message at 14:08:27 (1 minute 11 seconds after the EBS user initiated the sending process). The state of the message in deferred queue is “PROCESSED”. The message is enqueued in WF_NOTIFICATION_OUT queue and is ready to be processed by the next process.
3、Notification Mailer (第三行) – Notification Mailer dequeued the message at 14:09:03 (1 minute 48 seconds after it was submitted for sending) and sent it out via configured SMTP process. The MAIL_STATUS field is updated to indicate the sending fact.
As you see the method described gives us detailed information on where the notification message is and what components we should focus our troubleshooting efforts on in case of any problems.
Let’s summarize what we have described in this article. The very first troubleshooting activity the Apps DBA should do in case of reported missing emails from EBS is to send the test email (noticing its ID). If it doesn’t arrive in 5-10 minutes we run described SQL using the notification id in order to find where the notification got stuck. Depending on the observation you focus your attention on the particular EBS component for further troubleshooting.
I hope you find this information useful and will use it in the Notification Mailer troubleshooting process. I am sure that your feedback will inspire me for further articles. Please do not hesitate to leave any feedback, questions or share any issue you had/have related to the topic. I will be more than happy to assist.