wf_notifications mail_status mail

wf_notifications mail_status mail

 

Notification mailer is uses to send Email Notifications to the User. We see the Workflow Notifications in the Table wf_notification. If you want to check whether the Workflow Notification Mailer , has sent the Email notification or not , then you can take the help of this Table wf_notification. Column Mail_Status in the wf_notification shows the Email status of the Notification. if Mail_Status showing 'mail' Then it means , Workflow Notification mailer has not picked this record to send Email. This record is Pending to Process by Workflow Mailer to send Email to the Users. Once the Workflow Mailer picked the Record to Send Email and it process successfully then the column mail_status will be changed to 'Sent'.

This is the SQL Query to find the Pending Records of the Workflow Notification Mailer which is yet pending to sent emails.

Select * from Wf_Notifcations
Where Mail_Status='MAIL'.

 

 

 

 This is the SQL Query to find the Processed records of the Workflow notification to which the email notification has been sent.

Select * from Wf_Notifcations
Where Mail_Status='SENT'

 

 

Workflow notification mailer not sending emails

Workflow notification mailer not sending emails in Oracle Apps

In this Post , We will discuss Workflow Notifications Mailer Issues. We often find issues in Oracle Apps where our Workflow notification mailer not sending emails. There are many Steps to be involved to do the Debugging of the Workflow notification mailer issues.

 

Step1:- First of all we need to confirm that our Notification Mailer and Listeners is working or Not. Some time Notification mailer got some issues and it goes down automatically. So this is also be one of the case of Workflow notification mailer not sending emails.

 

To Check Notification Mailer and Listeners are working or not We need to go to the

'Workflow Administrator Web Applications' responsibility.

 

 

 

 

 

Step2:- If our Notification Mailer is running fine , Then in Step 2 , we will check the WF_NOTIFICATIONS table to check the status of the Email for the notification.

 

 

All the oracle workflow notifications are stored in the WF_NOTIFICATIONS table.

Query the WF Notification table for the Specific Case (PO , PR, Expense Report #)

If workflow email notification not working in oracle then in step 2 we need to extract the data of the wf_notifications table for specific item_key.

 

I am sharing the query to check workflow mailer status as below.

 

Select * from WF_Notifications where notification_id = :notification_id and item_key = :item_key(po_header_id or requisition_header_id or Expense Report Number);

In this Table We have column 'Mail _Status' which determine the status of the email that whether email is send or not.

WF_NOTIFICATIONS Important Column Description

Mail_status:

• Sent: – Mails are successfully sent to the recipients.
• Error: – Mails are not delivered to the recipient due to invalid email address.
• Mail :- This Notification is Eligible for Sending Email but not sent yet. This status will be converted to Sent or Error.
• Null:- IF Notification Mail status Null then it means this notification is not eligible not sending emails. If its null then it could be an issue in the Setup side.

 

IF wf_notifications mail_status mail then this Notification is Eligible for Sending Email but not sent yet. This status will be converted to Sent or Error.

Status:

• Open: – Mails are sent to the recipient, but the user not read the email.
•  Closed: – Mail has been viewed by the recipient.
• Error: – Mail server is not able to deliver the message.
• Cancelled :- Workflow got cancelled
• Timeout :- Notification got timed out

 

Recipient_role should be approver name.

 

Step3:- If your Particular Notification Record Mail_Status showing 'Null' then it means there is some issue in the Setups.

 

If user is not receiving the Workflow Email's  , then we need to check the User Preferences settings for the user. This could be the reason of Mail_Status Column Showing null for the Notification for Particular Recipient

 

Go to Your Application Home Page , Then Go to the Top Right Side of the corner

Select the Preferences Option.

 

 

In the Preferences Under Notifications Select the Email Style ' HTML mail with attachments' 

 

 

This Information Stores in the WF_LOCAL_ROLES TABLE.

To Update Preferences From Backend.

update wf_local_roles set notification_preference = ‘MAILHTML’ where name = ‘USER_NAME'

Check if the Workflow notification has been sent or not

select mail_status, status from wf_notifications where notification_id= :notification_id

If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the email notification

If mail_status is SENT, its means workflow mailer has sent email

If mail_status is Null and status is OPEN, then 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