Aloz 的 Oracle ERP 天地

In this Document
  Purpose
  Scope
  Tracking Workflow Notification Event Messages
     Track Outbound Notification Event Message
     Track Inbound Notification Event Message 

Applies to:

Purpose

The purpose is to provided a summarised steps on how to:

1) Track an Outbound Workflow Notification Event Message whenever such an event is raised but the end user does not receive the email

2) Track an Inbound Worklfow Notification Event Message whenever a notification is responded to and the transaction does not seem to progress further.

Scope

Applications DBAs

Support Analysts

Tracking Workflow Notification Event Messages

Track Outbound Notification Event Message

1. oracle.apps.wf.notification.send event is raised when a notification is sent.
   The event is raised with Notification ID as the event key.

2. The e-mail notification is sent only if all of following are true.

    i) Notification status is OPEN or CANCELED 

   ii) Notification mail_status is MAIL or INVALID

        SELECT status, mail_status
        FROM wf_notifications
        WHERE notification_id = '&nid';

   iii) Recipient Role has a valid e-mail address and notification preference is in the format MAIL% 

        SELECT email_address, 
        nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference)
        FROM wf_roles
       WHERE name = upper('&recipient_role');

   iv) Workflow Deferred Agent Listener is running

   v) Workflow Notification Mailer is running

3. The message makes two stops before it is sent as e-mail. 

 Raised --> WF_DEFERRED Queue --> Processed by Deferred Agent Listener --> WF_NOTIFICATION_OUT Queue --> Processed by Notification Mailer and sent as e-mail

4. If an error occurs during dispatch of oracle.apps.wf.notification.send, the message is enqueued to WF_ERROR queue. The Error Agent Listener dispatches the ERROR subscription for oracle.apps.wf.notification.send that executes WF_XML.Error_Rule. 

5. Run $FND_TOP/sql/wfmlrdbg.sql for a notification id and check the status of the message in WF_DEFERRED and WF_NOTIFICATION_OUT queue.

    PROCESSED in WF_DEFERRED - The message is enqueued to WF_NOTIFICATION_OUT

    PROCESSED in WF_NOTIFICATION_OUT - The message is sent as e-mail

    READY in WF_DEFERRED - Check if Deferred Agent Listener is running

    READY in WF_NOTIFICATION_OUT - Check if Notification Mailer is running

6. Analysing outbound XML message for a notification id. The wfmlrdbg.sql output provides the XML message generated by WF_XML.Generate at the time the script is run. If the original XML message used by the mailer is required to be analyzed following SQL may be used.

    SELECT tab.user_data.text_lob text_lob
    FROM applsys.aq$wf_notification_out tab
    WHERE dbms_lob.instr(tab.user_data.text_lob,'&1') > 0
    /

7. Try rebuilding the WF_NOTIFICATION_OUT queue if necessary

The wfntfqup.sql script rebuilds the WF_NOTIFICATION_OUT queue by dropping and recreating
that queue, removing pending notification messages from the WF_DEFERRED queue, and
repopulating the WF_NOTIFICATION_OUT queue from the Oracle Workflow Notification System
tables.

Wfntfqup.sql Version >=115.9

If you have implemented Oracle Alert and the WF_NOTIFICATION_OUT queue contains any pending
alert e-mail messages, those messages must be processed before the queue can be rebuilt. The
wfntfqup.sql script checks the queue for any alert e-mail messages and, if it finds any, the
script returns an error message that specifies how many alert e-mail messages are pending
and indicates that these messages must be processed before the script can run.

Wfntfqup.sql Version <= 115.8

If you have implemented Oracle Alert and the WF_NOTIFICATION_OUT queue contains any pending
alert e-mail messages, those messages must be processed before the queue can be rebuilt. The
wfntfqup.sql script checks the queue for any alert e-mail messages and, if it finds any,
exits without performing any changes.

Stop Notification Mailer, rebuild Mailer Queue using $FND_TOP/patch/115/sql/wfntfqup.sql.

Track Inbound Notification Event Message 

1. Workflow Notification Mailer's inbound thread reads the response e-mail from IMAP inbox. It then parses the content and identifies the notification id using NID string in the response e-mail and it identifies the response it expects. Using this information it parses the e-mail and finds out the response value. Using this information it builds a XML message under event name oracle.apps.wf.notification.receive event and enqueues to WF_NOTIFICATION_IN.

2. Workflow Inbound Notifications Agent Listener listens to WF_NOTIFICATION_IN agent.

3. The inbound agent listener dispatches event oracle.apps.wf.notification.receive that executes WF_XML.RECEIVE rule function. This rule function parses the response XML and applies the response value to the corresponding notification. It then calls WF_NOTIFICATION.Respond that completes the Notification Activity and the complete flow following the Notification Activity till a blocking activity is encountered.

4. The message makes one stop before making it to the Notification Activity.

Inbox -> WF_NOTIFICATION_IN Queue --> Processed by Inbound Agent Listener --> Workflow Engine

5. Run $FND_TOP/sql/wfmlrdbg.sql for notification id and check the status of the message in WF_NOTIFICATION_IN queue.

6. wfmlrdbg.sql output also provides the Inbound XML message in the later 11i.ATG_PF.H RUP releases. If the XML message is not available following SQL may be used.

    SELECT tab.user_data.text_lob text_lob
    FROM applsys.aq$wf_notification_in tab
    WHERE dbms_lob.instr(tab.user_data.text_lob,'&1') > 0;
   
7. If there is an error during response event processing the message is enqueued to WF_ERROR queue. The Error Agent Listener dispatches the ERROR subscription for oracle.apps.wf.notification.receive event that executes WF_RULE.Error_Rule.

8. If there is an exception thrown from WF_NOTIFICATION APIs when the response is applied, the Inbound Agent Listener sets the mail_status of the notification to INVALID and raises event oracle.apps.wf.notification.send that results in the INVALID response e-mail sent to the respoder.