How to Diagnose Workflow Notification Mailer Issue
Outbound Notification Emails:
1. Check if the notification is present in the recipient/user's
Worklist/Notifications page.If it does not exist
then it means that notification itself is not created
and need to check the corresponding workflow
status.You may also query the notification from the
wf_notifications table
SQL> select
recipient_role,notification_id,status,mail_status from wf_notifications where
recipient_role like '&user_name';
The e-mail notification is sent only if all of following are true.
--Notification
status is OPEN or CANCELED
--Notification mail_status is MAIL or INVALID
2.
Check Recipient role has a valid e-mail address and notification preference
MAIL%
SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference)
FROM wf_roles
WHERE name = '&recipient_role';
Recipient can receive email notification only if
-- notification preference is
not set 'QUERY' / 'DISABLED' / 'SUMMARY' /
'SUMHTML' &
-- recipient has valid email address
3.
Check and ensure that the following Workflow Service Components are up and
running.
Workflow Deferred Agent
Listener
Workflow Deferred Notification Agent
Listener
Notification Mailer
-- If the Notification Mailer is not running, refer to the
following note to troubleshoot the mailer
Document 242941.1 -- How To Troubleshoot Java-based Workflow
Notification Mailer
4. The message makes two stops before it is sent as e-mail.
Send Event Raised -> Enqueued to WF_DEFERRED Queue --> Processed by Deferred Agent Listener -->
Enqueued to WF_NOTIFICATION_OUT Queue --> Processed by Notification Mailer and sent as e-mail;
5. 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.
Check the Sysadmin user Notifications/Worklist for error
notifications to find the cause.
6.
Run $FND_TOP/sql/wfmlrdbg.sql for 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
7.. Check for the volumes on the following queues
WF_DEFERRED
WF_NOTIFICATION_OUT
[SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 =
Exception',to_char(substr(wfe.state,1,12))) State,
count(*) COUNT
from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;]
[SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 =
Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT
from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;]
8. If the Message
events are not moving out from the wf_notification_out queue , check the
notification
mailer outbound thread count and ensure that it is
>=1
9.
Check the Mailer parameter Test address, if Test address is set to any
email address note that all the
outbound notification emails are sent to the Test
address only.
10.
Ensure that Global Preferences --> Notification Style is set to one of
the following values as desired.
HTML Mail
HTML mail with attachments
Plain text mail
Plain text mail with attachments
Navigation: Connect to Applications as Sysadmin user and select
responsibility Workflow
Administrator Web Applications --> Administration --> Global
Preferences
11.
Troubleshooting framework based notifications
a.
Identify the notification as Fwk based, run $FND_TOP/sql/wfmlrdbg.sql for
the notification_id and
check the column Fwk Content
value and if it 'Y' then it is Fwk based notification.
b. Follow Document 339718.1 and ensure that mailer setup and system
profiles are set
correctly.
12. Basic
steps to try in solving the outbound email issues,
a.
Stop Notification Mailer, rebuild Mailer Queue using
$FND_TOP/patch/115/sql/wfntfqup.sql. This
will recreate WF_NOTIFICATION_OUT
with new messages that are eligible to be e-mailed.
b. Bounce Workflow Mailer service.
II Inbound/Response Emails
1. Enable
the Mailer inbound processing by setting the mailer parameter Inbound thread
count=1 and
ensure that Notification mailer is up and running.
2.
From the mail client check the mailer imap account inbox to see if the
response email has arrived and is
in the unread status.
NOTE: Mailer will only read/process the unread emails.
3.
Workflow Notification Mailer process the valid response e-mail from IMAP
inbox and then parses
the content using NID string in the response e-mail and
enqueues the event
oracle.apps.wf.notification.receive to
WF_NOTIFICATION_IN and moves the message to Process
folder.
4.
If a valid response email is moved to Discard folder, check if the same IMAP
account details are shared
by the multiple notification mailers from the different
instance.
NOTE: It's recommended to use a dedicated IMAP account
with each mailer.
5. Workflow
Inbound Notifications Agent Listener listens to WF_NOTIFICATION_IN agent.
6. The message makes one
stop before making it to the Notification Activity.
IMAP Inbox -> Mailer builds XML with response attributes and enqueued to WF_NOTIFICATION_IN Queue
--> Workflow Inbound Notifications Agent Listener consumes the messages and
apply the response to the notification activity by running
the subscription.
7.
Run $FND_TOP/sql/wfmlrdbg.sql for notification id and check the status of the
message in
WF_NOTIFICATION_IN queue.
8.
If there is an error during response event processing the message is enqueued
to WF_ERROR queue
9.
If there is an exception/error while applying the response, the
Inbound Agent Listener sets the
mail_status of the notification to INVALID
and sends the INVALID response e-mail sent to the
responder.
Best
Practices/tips:
1.
Set the mailer parameter 'Processor Close on Read Timeout' to 'Y' i.e., select
the checkbox for it.
2.
Set the mailer parameter 'Expunge Inbox on Close' to 'Y' i..e., select the
check-box for it.
3.
Do not leave the mailer parameter 'NodeName' to default value i.e., WFMAIL
but change it to a value as relevant for the instance type or name.
Pre-11.5.10.2, the NodeName must be WFMAIL.
1.
Please follow Note 275379.1 (Script To Check What Workflow Related Patches and run to
check installed patches) run and upload results to the SR.
2. Please enable STATEMENT level logging:
a. From Self Service
> Select "Workflow Manager" under "Oracle Applications
Manager" > Click "Notification Mailers" > Service
Components (Service Components: <SID>) > select Workflow Notification
Mailer >
b. Click on Edit button > Advanced
c. Click on next until you are in the Details section (Step 2 of 8) > Change
the log level field from Error To statement>
d. Click on Next all the way to "Step 4 of 8" and click Finish
button.
3. Restart the Workflow Mailer and Agent Listener services
a. From Self Service > Select "Workflow
Manager" under "Oracle Applications Manager" > Click
"Notification Mailers" > Service Components (Service Components:
<SID>) >
b. Click "Workflow Mailer Service" under "Container"
Column.
e. From "Service Instances for Generic Service Component
Container:<SID>"page, click "Pull Down" button from the
Start All/ Stop All.
f . Select Stop All > Go.
g. Wait for the Services to read Actual 0 Target 0 and Deactivated.
h. Run the following SQL to make sure service are stopped:
SELECT component_name, component_status,
component_status_info
FROM fnd_svc_components_v
WHERE component_name like 'Workflow%';
i. Restart the mailer services using the "Start All" button.
j. Run scripted again from step "h." to see Services are RUNNING.
4. To test the state of Java Mailer, please run Java Mailer FYI test and
provide the Notification ID (NID) to step 5.
a. Please log in to OAM
b. Select Workflow Manager from "Navigate to" LOV and click
"GO"
b.1 Please run the following sql to make sure the user has a valid email
address and the preference is enabled to receive notification.
select name, notification_preference,
email_address
from WF_ROLES
where name = upper('<UserName>');
b.2. If there is no valid email address, please assign email address from
sysadmin > Security > User > Define form for the user and save. Also
if the value for notification preference is disable, please enable that from
the Self Service Preferences page by selecting HTML Mail with Attachment
(MAILHTML).
c. Click "Notification Mailers"
d. Click "View Details"
e. Click "Test Mailer"
f. Search "Recipient Role" and click "Go" then
"Accept".
g. Click "Send Test Message"
h. One will get conformation Notification ID (NID) and provide that for the
next step.
i. Please run the following sql to locate the new NIDs which will have one NID
with "Message_Name = PLSQL_MSG" and Second with "Message_Name =
OAFWK_MSG".
select *
from wf_notifications
order by 1 desc;
5. Please provide the output of the $FND_TOP/sql/wfmlrdbg.sql.
Example:
$ sqlplus apps/<Apps Password> @$FND_TOP/sql/wfmlrdbg.sql <NID1>
<NID2> <NIDn>;
$ sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql 942537 942536 942535;
6. Upload Fresh log file from $APPLCSF/$APPLLOG/FNDCPGSC*.txt i.e. the log file
for the Active process for Workflow Mailer Service and Agent Listener services.
To retrieve the last 2 log files for Workflow Mailer and Agent Listener
services, run the following command:
ls -lt $APPLCSF/$APPLLOG/FNDCPGSC*.txt
7. Is the Instance in a Load Balancing Environment? If that is the case, then
please check whether "WF Workflow Mailer Framework Web Agent and
Application Framework Agent" Profile options are set correctly.
a. "WF: Workflow Mailer Framework Web
Agent" [WF_MAIL_WEB_AGENT] profile option is set to a physical Web Server
host address (non-load balanced). The key here is if this profile option is not
set, notification mailers will use the Web agent specified in the Application
Framework Agent profile option (APPS_FRAMEWORK_AGENT) instead.
b. "Application Framework Agent profile option
(APPS_FRAMEWORK_AGENT)" is set to a virtual or load balancing URL like
BigIP.
Note: If the profile options are not set properly, Notifications with framework
regions such as REQAPPRV, FYI Notification with framework region
("Message_Name = OAFWK_MSG"), etc. will be affected. However, other
notifications with no framework regions like FYI Notifications
("Message_Name = PLSQL_MSG"), POAPPROV will not be affected.
One can check whether the affected notification has a Framework Region by
running the following sql:
SELECT notification_id, message_type,
message_name,
wf_notification.isFwkRegion(notification_id) FWK
FROM wf_notifications
WHERE notification_id IN (<NID>,<NID>,<NID>);
