R12 – Workflow Mailer Troubleshooter Guide & Key Points

Oracle Workflow

• Oracle workflow is used to integrate ERP business processes into Oracle applications.
• Oracle workflow is a solution for integrating a complete end-to-end business process in ERP.
• Oracle workflow mailers allow people to send/receive emails or notifications for approval.

Workflow Mailer Notification via email

The following are the key points that need to be taken care of if workflow emails are not received for the particular notification:

1. A valid email address should be set for the recipient, for whom the email notification has to be received.
2. The notification preference of the recipient must be ‘MAILHTML’ i.e ‘HTML Mail with attachments’, you can set the same from the preferences section once the user login.

3. You can check the notification preference and email address details from wf_local_roles/wf_roles tables.
4. The workflow notification Mailer should be up and running (Outbound thread count should >=1 and Inbound thread count = 1)

Workflow Background process

• The Workflow Background process is the standard concurrent program which should be scheduled for every 10 minutes with the following parameters:
• Y, N, N
• N, Y, N
• N, N, Y

Notification Workflow Mailers

• Check if all the agent listeners are up and running as shown below:
• Navigation Path:
  • Go to ‘Workflow Administrator Web Applications’ responsibility and click on ‘Workflow Manager’ as shown below.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Notification Mailers, Agent Listeners, Background Engines, etc., should be up and running

The cause of the workflow issue can be workflow not running or Notifications not being fired.

• If the workflow process is not getting initiated, get the workflow name and itemkey. Itemkey is a key to identify the workflow instances
• Eg: itemkey examples

1. PO Approval Workflow

• SELECT wf_item_key FROM po_headers_all
• WHERE segment1 = :po_number
• AND org_id =  :organization_id ;

2. Requisition Workflow 

• SELECT wf_item_key FROM po_requisition_headers_all
• WHERE segment1 = :requisition_number
• AND org_id = :organization_id
• With the workflow name and the itemkey for the workflow which is failed, follow the below steps:

Go to workflow status monitor

Enter the Workflow type and Itemkey of the workflow

• Status of each workflow can be of Active/Error/Complete/Deferred
• Workflow Notifications not getting triggered:
  • All the oracle workflow notifications are stored in the WF_NOTIFICATIONS table.
  • Select * from WF_Notifications where notification_id = :notification_id and item_key = :item_key(po_header_id or requisition_header_id);
  • Mail_status:
    • Sent: – Mails are successfully sent to the recipients.
    • Error: – Mails are not delivered to the recipient due to an invalid email address.
  • Status:
    • Open: – Mails are sent to the recipient, but the user has not read the email.
    •      Closed: – Mail has been viewed by the recipient.
    • Error: – The mail server is not able to deliver the message.
    • Canceled:- Workflow got canceled
    • Timeout:- Notification got timed out

  Failed Notifications

  • If notifications are not received, check if the record exists in the wf_notifications table.
  • select * from wf_notifications where item_key = (Item key from status monitor window). Check the status and recipient_role values.
  • Mail_status of the notifications should be ‘SENT’ for all open notifications. If notification is closed, Mail_Status will be null.
  • Recipient_role should be approver name.
  • To update the notification_preference from the backend.
  • update wf_local_roles set notification_preference = ‘MAILHTML’ where name = ‘ASECO.DAVID’

  Tips:

  • Clear the cache always if the notifications listed in the notification worklist were not able to open.
  • Set the workflow Administrator privilege to “*” from sysadmin login to view all the workflow status diagrams of all the workflows owned by other users.

Debugging steps if Workflow errors out

• Login à User (with Workflow Administrator responsibility)
• Workflow Administrator -> Administrator Workflow -> Status Monitor-> Enter Type Internal Name (,POAPPRV, REQAPPRV, XXEFTPYM )-> Go
• A list of workflows will be displayed. Select the workflow with respective to Payment batch id.
• Click the status diagram/Activity History button. Errors can be listed out on the particular activity if any.

Check if the Workflow Mailer 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 the user is “Don’t send email”
• Notification preference of user can be set by the user by logging in application -> click on preference -> the notification preference

How to Resend OPEN, CANCELLED Workflow Notification Mailers

• Many notification mailers stuck in the mailer queue (status=’OPEN’ and mail_status=’MAIL’)
• SQL> select count(*) from wf_notifications where status=’OPEN’ and mail_status=’MAIL’;
• select notification_id, status, mail_status, subject, from_user, begin_date from WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;
• Oracle Workflows notifications cannot be sent due to mail server problems, network problems
• We can resend the notifications by doing the following:
• 1) stop workflow mailer
• 2) cd $FND_TOP/patch/115/sql
• 3) sqlplus APPS/<pwd> @wfntfqup.sql APPS <pwd> APPLSYS
• 4) start workflow mailer and monitor the queue

Vacation Rules:

• Vacation rules handle notifications automatically when the users are not available or on vacation/leave to manage their notifications personally. These rules are defined by the user according to the item type for the particular notification. In Oracle Applications, we can control what item types are available for vacation rules using the WF: Routing Rule Item Types lookup type and the WF: Vacation Rules – Allow All profile option.
• Note – If the reassign button is hidden from the notifications tab, then the vacation rule will not function.

FYI Notifications are Autoclosed

• If FYI notifications for Approve/Reject has been autoclosed, then Autoclose FYI Flag in workflow notification mailer should be set to “N”

Metalink References

• 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 troubleshooting Workflow Notification Emails – Inbound and Outbound
• Note: 1012344.7 – Notifications Not Being Sent In Workflow
• Note: 560472.1 – Workflow Mailers Not Sending Notifications