Oracle Ebusiness Suite
(EBS) Workflow Mailer Troubleshooting
Check EBS workflow
mailer service current status
Number of running processes should be greater than 0
Current mailer status
Possible values:
Stop notification mailer
Start notification mailer
A workflow notification send event (notification email) can fail at several
different points, so monitoring it using one method usually is not going to
give you a complete picture.Additionally, you have to keep in mind that the
process is dynamic, meaning that as transactions are created into the queues
they are also mailed out; so a
Here is a more robust script for monitoring the wf_notifications table:
Some messages like alerts don't get a record in wf_notifications table so
you have to watch the WF_NOTIFICATION_OUT queue.
select corr_id, retry_count, msg_state, count(*)
SQL to collect all the info except IMAP account password.
select p.parameter_id,
How to Enable the Oracle EBS Workflow Logging:
From Self Service > Select “Workflow Manager” under “Oracle Applications
Manager” > Click “Notification Mailers” > Service Components (Service
Components: <SID>) >
How does workflow Notification Mailer IMAP (Inbound Processing) Works:
1. Approver sends email response which is directed to the value defined in
Replyto address.
3. The message is scanned to see if it matches entries in the TAG file . Mail
tags are defined in the OAM mailer configuration pages and these list strings
of text and actions to take if those strings are encountered. An example of
this are ‘Out of Office’ replies. If the string of the message matches a mail
tag and the action is ‘Ignore’ then nothing else will happen.
4. The message is then scanned for the presence of a Notification Id (NID).
This NID is matched against valid NID for the mailer node.
5. If valid NID is not detected, (and there is no matching TAG file entry) then
the message is placed into the DISCARD folder.
6. If a valid NID is detected the listener raises a subscription to the
WF_NOTIFICATION_IN queue and places the mail message in the Processed folder.
7. From this point on the message is handled by the product workflow (like PO
APPROVAL) . An event created by that group will monitor the WF_NOTIFICATION_IN
queue and will trigger the rest of the approval workflow.
Steps/events for Oracle Workflow Notification Outbound Processing(eMail from
Oracle Applications Workflow to Users)
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
E-Mail Notification is sent if all below conditions are truea) Notification
status is OPEN or CANCELED and
To check a) & b) run below query
mail_status >> SENT means mail has gone out of mailer to user
To check c) run below query
To check d) & e) Use OAM (Oracle Application Manager)
How to purge e-mail notifications from the Workflow queue
The below outlines the steps, Please take proper backup before performing the
below.
1) You need to update the notifications you do not want sent, in the
WF_NOTIFICATIONS table.
2) Check the WF_NOTIFICATIONS table as below. Records where status = ‘OPEN’ and
mail_status = ‘MAIL’ are notifications that will have an e-mail notification
sent.
SQL> select notification_id,status,mail_status,begin_date from
WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;
3) This should show you which notifications are waiting to be e-mailed.
4) To update a notification so that it will not get e-mailed. Set the
MAIL_STATUS = ‘SENT’. The mailer will think the e-mail has already been sent
and it will not send it again.
SQL> update WF_NOTIFICATIONS set mail_status = ‘SENT’ where mail_status =
‘MAIL’;
-> This will update all notifications waiting to be sent by the mailer.
5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and
rebuild it with data currently in the WF_NOTIFICATIONS table. This is what
purges all notifications waiting in the queue to be sent.Only the ones where
mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the
WF_NOTIFICATION_OUT queue and sent by the mailer.
SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD
APPLSYS
6) Now you can start your WF Containers and then Mailer
Workflow Mailer Debugging Script for Debugging Emails issues
--Checking workflow Components status whether are they running or stopped.
Query to get the log file of active workflow mailer and workflow agent
listener Container
Linux Shell script Command to get outbound error in Mailergrep -i
'^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*'
<logfilename> | tail -10 ;
Linux Shell script Command to get inbound processing error in Mailer grep -i
'^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*'
<logfilename> | tail -10 ;
Query to Check Workflow Mailer Backlog:
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by
tab.msg_state ;
Check any particular Alert Message email has be pending by Mailer
Check The Workflow notification has been sent or not
Check Whether workflow background Engine is working for given workflow or
not in last 2 days
Check whether any business event is pending to process
When the workflow mailer is either disabled or the mail server restricts its
access to the workflow mailer, all the workflow notifications will retry and
upto the limit set at the workflow mailer configuration and later all the
notifications will be failed. This will result in 2 things at Application level
i) MAIL_STATUS column of the WF_NOTIFICATIONS set to FAILED.
ii) After a series of failure notifications the Notification Style for the
respected User will be set as Disabled and a corresponding user details are
sent to SYSADMIN.
A similar issue araised in one of the client and the many of the users
notification style has been set to Disabled.
Solution:
To overcome the above issue, initially fix the workflow mailer issue and later
need to change the Notification Preference at Workflow Administrator to
"HTML with Attachments" (or any other as per the client's
requirement).
But sometime this setting won't be affected to all the users whose Preference
is set to Disabled. To overcome this situation:
i) Backup the following tables:
CREATE FND_USER_PREFERENCES_BAK as select * from FND_USER_PREFERENCES;
ii) To set the mail preference for all the users execute the following:
update wf_local_roles
update fnd_user_preferences
iii) To update the mail preference of the users only whose preference is set to
"FAILED", execute the below steps:
update wf_local_roles
update fnd_user_preferences
Possible values for <wished_preference> are:
iii) To Resend all the failed notifications again to the respected users run
the following concurrent program as SYSADMIN, with the relevant parameters.
Retry Error Workflow Activities
Happy Learning!
Number of running processes should be greater than 0
Current mailer status
Possible values:
Stop notification mailer
Start notification mailer
A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a
Here is a more robust script for monitoring the wf_notifications table:
Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.
select corr_id, retry_count, msg_state, count(*)
SQL to collect all the info except IMAP account password.
select p.parameter_id,
How to Enable the Oracle EBS Workflow Logging:
From Self Service > Select “Workflow Manager” under “Oracle Applications Manager” > Click “Notification Mailers” > Service Components (Service Components: <SID>) >
How does workflow Notification Mailer IMAP (Inbound Processing) Works:
1. Approver sends email response which is directed to the value defined in Replyto address.
3. The message is scanned to see if it matches entries in the TAG file . Mail tags are defined in the OAM mailer configuration pages and these list strings of text and actions to take if those strings are encountered. An example of this are ‘Out of Office’ replies. If the string of the message matches a mail tag and the action is ‘Ignore’ then nothing else will happen.
4. The message is then scanned for the presence of a Notification Id (NID). This NID is matched against valid NID for the mailer node.
5. If valid NID is not detected, (and there is no matching TAG file entry) then the message is placed into the DISCARD folder.
6. If a valid NID is detected the listener raises a subscription to the WF_NOTIFICATION_IN queue and places the mail message in the Processed folder.
7. From this point on the message is handled by the product workflow (like PO APPROVAL) . An event created by that group will monitor the WF_NOTIFICATION_IN queue and will trigger the rest of the approval workflow.
Steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
E-Mail Notification is sent if all below conditions are truea) Notification status is OPEN or CANCELED and
To check a) & b) run below query
mail_status >> SENT means mail has gone out of mailer to user
To check c) run below query
To check d) & e) Use OAM (Oracle Application Manager)
How to purge e-mail notifications from the Workflow queue
The below outlines the steps, Please take proper backup before performing the below.
1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.
2) Check the WF_NOTIFICATIONS table as below. Records where status = ‘OPEN’ and mail_status = ‘MAIL’ are notifications that will have an e-mail notification sent.
SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;
3) This should show you which notifications are waiting to be e-mailed.
4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = ‘SENT’. The mailer will think the e-mail has already been sent and it will not send it again.
SQL> update WF_NOTIFICATIONS set mail_status = ‘SENT’ where mail_status = ‘MAIL’;
-> This will update all notifications waiting to be sent by the mailer.
5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.
SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS
6) Now you can start your WF Containers and then Mailer
Workflow Mailer Debugging Script for Debugging Emails issues
--Checking workflow Components status whether are they running or stopped.
Query to get the log file of active workflow mailer and workflow agent listener Container
Linux Shell script Command to get outbound error in Mailergrep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
Linux Shell script Command to get inbound processing error in Mailer grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
Query to Check Workflow Mailer Backlog:
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;
Check any particular Alert Message email has be pending by Mailer
Check The Workflow notification has been sent or not
Check Whether workflow background Engine is working for given workflow or not in last 2 days
Check whether any business event is pending to process
When the workflow mailer is either disabled or the mail server restricts its access to the workflow mailer, all the workflow notifications will retry and upto the limit set at the workflow mailer configuration and later all the notifications will be failed. This will result in 2 things at Application level
i) MAIL_STATUS column of the WF_NOTIFICATIONS set to FAILED.
ii) After a series of failure notifications the Notification Style for the respected User will be set as Disabled and a corresponding user details are sent to SYSADMIN.
A similar issue araised in one of the client and the many of the users notification style has been set to Disabled.
Solution:
To overcome the above issue, initially fix the workflow mailer issue and later need to change the Notification Preference at Workflow Administrator to "HTML with Attachments" (or any other as per the client's requirement).
But sometime this setting won't be affected to all the users whose Preference is set to Disabled. To overcome this situation:
i) Backup the following tables:
CREATE FND_USER_PREFERENCES_BAK as select * from FND_USER_PREFERENCES;
ii) To set the mail preference for all the users execute the following:
update wf_local_roles
update fnd_user_preferences
iii) To update the mail preference of the users only whose preference is set to "FAILED", execute the below steps:
update wf_local_roles
update fnd_user_preferences
Possible values for <wished_preference> are:
iii) To Resend all the failed notifications again to the respected users run the following concurrent program as SYSADMIN, with the relevant parameters.
Retry Error Workflow Activities
Happy Learning!
