Workflow Mailer Architecture

We had a very strange issue in one of the Production instances today. The mount on which 10.2.0 ORACLE_HOME was present had filled up 100%, but doing a du -sh on it showed that 10.2.0 directory occupied 10GB out of the 27GB allocated. We had no idea what happened to the remaining 17 GB as it wasn't showing up in du -sh output. We shutdown the DB, but two processes would not come down even if we tried to kill them. Bill restarted Sendmail on a hunch, and those two processes came down and we had 17 GB free again. It is still a mystery, what really happened.

To understand this in detail I searched Metalink for Workflow Mailer Architecture and found note 172174.1 which describes the architecture. Here's an extract:

The main component of the Oracle Workflow Notification Mailer is the executable
WFMAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling sendmail for
UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also
queries the local inbox for incoming messages. These messages are validated and
then passed to the database for response processing.

Next the mailer will go through an endless loop, only controlled by the presence
of the shutdown file. There are four stages to this loop:

1. Send Loop
2. Inbox Routing
3. Response Processing
4. Idle

1. Send Loop

The notification mailer above release 2.5.1 uses the SMTP queue.

Each of these WF_NOTIFICATIONS rows is verified that it is in the correct
status and is dispatched to the recipient that is defined within the
WF_NOTIFICATIONS.Recipient_Role column. The details of the recipient are
retrieved. Including the email_address, Territory and Language. If the email
address is blank, the mailer will assume that this is a role and will
attempt to send the notification to each of the members of the role.

Before the physical email is generated, the mailer will issue an ALTER
SESSION command to set the NLS settings to that of the recipient. The mailer
will then use the WF_MAIL.GetShortMessage API to generate the notification
contents.

The format of the notification is governed by the mail templates. These are
located in the System: Mailer (WFMAIL) item type, (viewable in Workflow
Builder). The template selected is governed by the status of the notification.

2. Inbox Routing

During Inbox Routing, the mailer opens the inbox (governed by the ACCOUNT=
configuration parameter in the wfmail.cfg file) and examines each incoming
email in turn. The mailer is specifically looking for incoming emails with
Notification ID specifications. These are strings in the form of:

a) NID[Notifcation ID/access key@node]
b) NID[Notifcation ID/access key@node][2]

Note: b) is used when the DIRECT_RESPONSE=Y configuration option is used.

If this string does not exist within the email, then the email is moved
directly to the discard file and a warning notification is generated and
sent back to the Reply-To address or the From address.

Acceptable emails, ones that contain Notification ID specifications, are
moved to the unprocessed file (UNPROCESS= configuration parameter).

For MAPI, the inbox is a MAPI folder. However, DISCARD, UNPROCESS and
PROCESS folders are implemented as files on the local file system of the
NT server.

Metalink Note 458665.1 gives these steps as the basic flow involved in the Inbound Mailer processing

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 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.

3. Response Processing

Once the Inbox Routing has finished, then the mailer only has to operate on
the unprocessed file(UNPROCESS= configuration parameter). The Inbox Routing
has ensured that the contents of the unprocessed file are intended for this
Oracle Workflow Instance.

Each incoming email is retrieved from the unprocessed file and passed to the
server side PL/SQL for final verification and applying the contents of the
email to the workflow process identified by the NID specification.

4. Idle

The Idle Sleeps for IDLE= n seconds, default is 30 seconds.

5. To troubleshoot Notification Mailer setup issues, refer to Note 125078.1


B. STARTUP AND SHUTDOWN NOTIFICATION MAILER
--------------------------------------------

The mailer program can be started from command line or through a concurrent
program called "Notification Mailer".

When the notification mailer is started as a concurrent request in Oracle
Applications, the OS process is run by the Applications Manager (APPLMGR) user.

To avoid looping issue as described in Note 77276.1, migrate your Workflow
Mailer to the APPLMGR account as per Note 158818.1

To start it from the application, submit the "Notification Mailer"
concurrent request.

To start it from command line on UNIX, execute the following command:
$FND_TOP/bin/WFMAIL apps/pwd@SID 0 Y $FND_TOP/resource/wfmail.cfg &

To start it from command line on NT, execute the following command:
drive:\orant\bin\wfmlr -f drive:\orant\wf\data\wfmail.cfg

To shutdown, create a file name "shutdown" as specified in the wfmail.cfg file
or terminate the concurrent program. When starting up, the mailer reads the
configuration file to initialize its parameters. It then checks for the shutdown
file, if it exists, the mailer terminates gracefully.


C. WORKFLOW DIRECTORY SERVICES
------------------------------

1. Directory Service is a directory repository that Oracle Workflow uses to
reference users and roles information. Notification Mailer relies on the
workflow directory service to route notifications to user/role.

Oracle Workflow Directory Service consists of the following views:

a) WF_USERS view: references information about all the individuals who may
receive workflow notifications.

b) WF_ROLES view: references information about all the roles in your
organization who may receive workflow notifications.

Note: the role information will override the user information when the
Notification System delivers a notification to the role.

c) WF_USER_ROLES: is an intersection of the users and roles in WF_USERS and
WF_ROLES.

Wfdirhrv.sql that creates the views is automatically installed when installing
Oracle Applications Release 11i. At any time, if you suspect that your Workflow
Directory Service is not being setup correctly, you may run the script to
recreate the views.

Script is located under $FND_TOP/admin/sql

Directory service is mapped to:

a) Oracle Human Resources tables
b) Oracle Application Object Library tables
c) WF_LOCAL tables
d) Various Applications tables

2. Run $FND_TOP/sql/wfdirchk.sql to troubleshoot workflow directory
service problem.

I'll update this post as I learn more.