DBA tips for handling Workflow Notification Mailer issues
Ravishekhar Yelemane, Oracle Applications Database Administrator, Rackspace Technology
Introduction
When you manage issues, you deal with the process of recording, tracking, and resolving problems. Because Workflow Notification Mailer has many components, you might struggle to address issues related to it. To help you, I cover some issues and possible fixes. Don't miss out on the scripts that you need to manage Workflow Notification Mailer activities easily.
Workflow Notification Mailer generic issues and their fixes
The following sections explore some Workflow Notification Mailer issues and solutions.
Issue: Users don't get notifications
Users don't receive notification emails or cannot fix the failed notifications.
Cause
The following settings are set to DISABLED instead of MAILHTML:
- PREFERENCE_VALUE in the FND_USER_PREFERENCES table
- NOTIFICATION_PREFERENCE in the WF_LOCAL_ROLES table
FIX
1. Run the following commands to back up the tables:
SQL> create table fnd_user_preferences_bkp as select * from fnd_user_preferences;
Table created.
SQL> create table wf_local_roles_bkp as select * from wf_local_roles;
Table created.
2. Run the following commands to update the tables:
select user_name,preference_value from FND_USER_PREFERENCES where preference_value = 'DISABLED' and preference_name = 'MAILTYPE' and module_name = 'WF' and user_name like '%<USER_NAME>%';
update FND_USER_PREFERENCES set preference_value = 'MAILHTML' where preference_name = 'MAILTYPE' and preference_value = 'DISABLED' and module_name = 'WF' and user_name like '%<USERNAME>%';
select * from wf_local_roles where notification_preference = 'DISABLED' and name like '%<NAME>'
update wf_local_roles set notification_preference = 'MAILHTML' where notification_preference = 'DISABLED' and name like '%<NAME>%'
3. Run the request **Resend Failed/Error Workflow Notifications** from the System Administrator
responsibility to resend failed notifications. Set the date parameter, **Notifications sent on or after**,
to the specific date.
**Note:** Use the following format for the date: **DD-MMM-YYYY** (Example: 01-JAN-2020)
Issue 2: Emails go to the wrong folder
The system delivers expense notification approval emails to the DISCARD folder instead of the PROCESS folder.
The following example shows the standard approval email Action item generated when the approver clicks on the Approval link:
Action: 'Approve'
Note: ''
NID[5513471/192168752214903990869743932721878211126@WFMAIL]
When the notification goes to the Junk folder, the "Action" items appear as follows:
Action: 'Approve'Note: ''NID5389465/1779945055187563456464409735425450546126@WFMAIL
Cause:
The Microsoft® Outlook® email creation format setting is text mode, or you approve the notifications from a mobile device.
FIX:
Whenever you approve an email notification by using email text mode or from a mobile device, you should ensure that you do the following actions:
- Put a space between each field.
- Put the NID value in brackets.
The message from the preceding example should have at least one space between the fields, and the **NID** value should include square brackets ([ ]), as shown in the following example:
Action: Approve
Note: ''
NID[5389465/1779945055187563456464409735425450546126@WFMAIL
Issue 3: Users miss emails or get old emails
Users don't receive emails on time, or they get old notifications that are already closed or canceled
Cause:
The issue occurs when the Workflow Notification Mailer queue is full of junk data.
The wf_notifications parameter has mail_status set to MAIL for closed notifications or SENT for open notifications.
In this scenario, the wf\_notification\_out queue has very old entries from the wf_notifcations table. This occurrence sends an anomaly notification when the Workflow Background Process request parameters are incorrect.
Fix:
Use the following high-level steps to rebuild the Workflow Notification Mailer queue:
1. Check with the customer about how to purge the old notifications.
2. Keep the date for reference, such as the begin_date.
3. Shut down Workflow Notification Mailer.
4. Close all the old OPEN notifications that are older than begin_date.
5. Update the mail\_status to SENT for any notifications that are older than begin_date.
6. Rebuild the queue by using the following script as the APPS user:
SQL> @$FND_TOP/patch/115/sql/wfntfqup.sql apps <APSS_PASSWD> APPLSYS
Maintenance scripts for the Workflow Notification Mailer
The commands in this section help you to maintain the Workflow Notification Mailer. You can put the commands into scripts for your convenience.
Script 1
This script modifies the following settings:
- RUNNING
- STARTING
- STOPPED_ERROR
- DEACTIVATED_USER
- DEACTIVATED_SYSTEM
- NOT_CONFIGURED
Use the following commands to update the component_status for the Workflow Notification Mailer and modify the settings:
SQL> select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
COMPONENT_STATUS
------------------------------
STARTING
SQL> update fnd_svc_components set component_status='DEACTIVATED_SYSTEM' where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
1 row updated
SQL> commit;
Commit complete.
Script 2
You can use the following commands to set the Mailer STARTUP_MODE to MANUAL or AUTOMATIC:
SQL> select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
SQL> update fnd_svc_components fsc set fsc.STARTUP_MODE='MANUAL' where fsc.COMPONENT_NAME='Workflow Notification Mailer';
1 row updated.
SQL> commit;
Commit complete.
Script 3
This script updates the Workflow Notification Mailer password from the backend:
SQL> @wfmlrpwupd_in.sql
Enter value for password: *****
Enter value for componentid: 10006
Updated
SQL> @wfmlrpwupd_out.sql
Enter value for password: *****
Enter value for componentid: 10006
Updated
Script 4
Use the following single script to change the Workflow Notification Mailer values from the backend:
$FND_TOP/sql/afsvcpup.sql
Conclusion
The Workflow Notification Mailer has many components and some tricky issues. Make sure to back up the wf\_\ tables before updating them.
Refer to the relevant Oracle MetaLink note before running a script to apply the fixes. MetaLink has many other scripts besides the ones included in this post that can help you resolve issues in the Workflow Notification Mailer.
Recent Posts
How Rackspace Leverages AWS Systems Manager
October 9th, 2024
Windows Server preventing time sync with Rackspace NTP
October 7th, 2024
Building a Google Cloud Landing Zone in a Scalable, Repeatable and Secure Way (Part 1)
August 28th, 2024