R12 – Workflow Mailer Troubleshooting Guide & Key Points
Oracle Workflow
- Oracle
workflow is used to integrate ERP business processes into Oracle
applications.
- Oracle
workflow is a solution for integrating complete end-to-end business
process in ERP.
- Oracle
workflow allows people 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 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
- 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 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.
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/Defferred
- 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 invalid email address.
- 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
Failed
Notifications
- If
notifications are not received, check if record exists in
wf_notifications table.
- select
* from wf_notifications where item_key = (Item key from status monitor
windlow). 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 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
- List
of workflows will be displayed. Select the workflow with respective to
Payment batch id.
- Click
status diagram/Activity History button. Errors can be listed out on the
particular activity if any.
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
How to Resend OPEN, CANCELLED Workflow Notification mails
- 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
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
