Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues

Find Workflow Notification Mailer is up and Running:

SELECT component_name, component_status FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory

Find the Failed Notifications

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';


Check pending e-mail notification that was pending for process.

SELECT COUNT(*), message_name FROM wf_notifications WHERE STATUS='OPEN' AND mail_status = 'MAIL' GROUP BY message_name;

(or)

SELECT * FROM wf_notifications WHERE STATUS='OPEN' AND mail_status = 'SENT' ORDER BY begin_date DESC


Check the Workflow notification has been sent or not:

select mail_status, status from wf_notifications where notification_id=

-If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
-If mail_status is SENT, its means mailer has sent email
-If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
-Notification preference of user can be set by user by logging in application + click on preference + the notification preference

If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS where status = 'OPEN';
or
Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date from WF_NOTIFICATIONS where status = 'OPEN';

Users complain that notifications are stuck:

Use the following query to check to see whatever the users are saying is correct

select message_type, count(1) from wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;

E.g
MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent ---
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error

If Mail not received by User:

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS from wf_users where DISPLAY_NAME=’xxx,yyy’ ;

Status – Active
Notification_preference-> Mailtext
Email Address should not be null

Notification not sent waiting to be mailed:

select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status =‘MAIL’;

To debug the notification id:

$FND_TOP/sql
run wfmlrdbg.sql

References:

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications
Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues)