Oracle WorkFlow 工作流 中篇

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.