Useful Scripts for Workflow Mailer Troubleshooting

-- to find status of Workflow Notification Mailer
SELECT component_name, component_status
  FROM fnd_svc_components
 WHERE component_type = 'WF_MAILER';

-- to find failed notifications
SELECT NOTIFICATION_ID,
       MESSAGE_TYPE,
       MESSAGE_NAME,
       STATUS,
       MAIL_STATUS,
       FROM_USER,
       TO_USER
  FROM wf_notifications
 WHERE MAIL_STATUS = 'FAILED';

-- to find pending e-mail notifications by group process
SELECT COUNT (*), message_name
    FROM wf_notifications
   WHERE STATUS = 'OPEN'
     AND mail_status = 'MAIL'
GROUP BY message_name;

--to find sent e-mail notifications 
  SELECT *
    FROM wf_notifications
   WHERE STATUS = 'OPEN' AND mail_status = 'SENT'
ORDER BY begin_date DESC;

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

-- to find a message processed in WF_DEFERRED queue
SELECT *
  FROM applsys.aq$wf_deferred a
 WHERE a.user_data.getEventKey () = '';

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

SELECT wf.user_data.event_name Event_Name,
       wf.user_data.event_key Event_Key,
       wf.user_data.error_stack Error_Stack,
       wf.user_data.error_message Error_Msg
  FROM wf_error wf
 WHERE wf.user_data.event_key ='';

-- to find SUCCESS and FAIL mails 
SELECT from_user,
       to_user,
       notification_id,
       status,
       mail_status,
       begin_date
  FROM WF_NOTIFICATIONS
 WHERE status = 'OPEN';

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';

-- to find Users that notifications got stuck
SELECT MESSAGE_TYPE, COUNT (1)
    FROM wf_notifications
   WHERE status = 'OPEN' AND mail_status = 'MAIL'
GROUP BY MESSAGE_TYPE;

/******
--For Example,
MESSAGE_TYPE    COUNT(1)
WFERROR         77799
WFTESTS          2
OEOH                 156  --mails of Orders not sent
OMERROR        1632 -- Error
********/

--to find mail not received by Users
SELECT Name,
       DISPLAY_NAME,
       EMAIL_ADDRESS,  --should not be NULL
       NOTIFICATION_PREFERENCE, --Mailtext
       STATUS --Active
  FROM wf_users
 WHERE DISPLAY_NAME in ('xxx','yyy');

--to find notification not sent and waiting to mail
SELECT notification_id,
       status,
       mail_status,
       begin_date
  FROM WF_NOTIFICATIONS
 WHERE status = 'OPEN' AND mail_status = 'MAIL';

/******************************
--To debug refer: $FND_TOP/sql execute wfmlrdbg.sql
--To find Workflow logs: FNDCPGSC*.txt under $APPLCSF/$APPLOG

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not
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), the same error is reported in this doc.
**********************************/