Check and Validate stuck workflow WF_NOTIFICATION

Introduction:

Workflow notification mailer is an important component of the application framework and notifications should be delivered in timely manner for smooth functioning of the business.

Workflow notification mailer sends the e-mail notification if all of following are true. 

               --Notification status is OPEN or CANCELED  in WF_NOTFICATIONS

               --Notification mail_status is MAIL or INVALID in WF_NOTIFICATIONS

The message makes two stops before it is sent as e-mail.

Send Event Raised -> Enqueued to WF_DEFERRED Queue --> Processed by Deferred Agent Listener -->  Enqueued to WF_NOTIFICATION_OUT Queue --> Processed by Notification Mailer and sent as e-mail;

 

Queries to check stuck notifications:

select count(*) from wf_notifications where status = 'OPEN' and mail_status = 'MAIL' and (sysdate - begin_date)*24*60 > 20;

select notification_id from wf_notifications where status = 'OPEN' and mail_status = 'MAIL' and (sysdate - begin_date)*24*60 > 20;

set linesize 200

set pagesize 100

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(+);

Troubleshooting/Resolution Steps:

When we get this alert, please perform the below troubleshooting checks / steps to resolve this

=> Ensure that below workflow components are up:

Below three services are used in notification delivery.  We need to ensure these are up. 

       Workflow Deferred Agent Listener 

       Workflow Deferred Notification Agent Listener 

       Notification Mailer

Login to applications as SYSADMIN user, Select System Administrator -> Oracle Application Manager -> Workflow

Check the status of Notification Mailers and it should be up.

If the mailers are down, please check the mailers log files and start the mailers. 

Click on Agent Listeners to see the status of “Workflow Deferred Agent Listener “ and “Workflow Deferred Notification Agent Listener “

Mailer Log files: Login to CM node,  check  $APPLCSF/$APPLLOG/FNDCPGSC*.txt  files. Start the above components if they are down. 

Check the Notification mailers status from back end.

set linesize 200

     set pagesize 100

     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(+);

Check the recipient details:

Please execute the below query to find out the recipient details for the notification_id which came in the alert:

select recipient_role,notification_id,status,mail_status from wf_notifications where notification_id = &notif_id ;

Then, execute the below query to check if recipient role has valid email address and mail preference:

SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference) FROM wf_roles WHERE name = '&recipient_role';

Recipient can receive notification only when notification preference is set to anything other than  'QUERY' / 'DISABLED' / 'SUMMARY' / 'SUMHTML' 

Recipient can receive notification only if he/she has valid email address. 

The above query gives details about both. 

If the email address is not specified: Need to update the source [ either FND_USER or PER ] with correct email address. We will have to ask the user to update the recipient to have valid email address. 

If Notification preference is not having correct value: We need to update the user to have correct preference for that userid.  Preferences can be updated by the respective user by login to ERP, clicking on Preferences and updating the notification preference.

Test the notification Mailer

If mailer is up and recipient has proper setups, then perform a test notification to see if the test notification are delivered properly.

a. Ensure that user has valid email address.  If not, please set the email address in Security -> User -> Define form 

b. Do a test notification to user.

Login to EBS as SYSADMIN -> System Administrator [ Responsibility ] -> Oracle Application Manager -> Workflow -> Click on button next to “Notification Mailer” -> Click on View Details -> Test Mailer -> Give username as recipient Role. 

After this, we should receive the notification email. If we get the notification email, then outbound notification mailer is working fine.  Issue could be specific to the notification.  If we are not getting notification emails, then there could be an issue with mailers itself. 

If we do not receive test notification emails [ even after correct email address and preference ], then check the notification mailer log files for any errors.  You may restart the notification mailer and see if the notification can send the notifications. 

Check the workflow Queue Tables

Get Additional details about the notification id for which alert is triggered:

Run $FND_TOP/sql/wfmlrdbg.sql  for the notification id reported in the alert. This script takes notification_id as its argument. Please run this from APPS user on application node. This will create a html in the current working directory with all the details about the notifications:

Please download the html from the server and check for the below: 

• Notification Recipient Role Information

• WF_DEFERRED Queue Status  [PROCESSED in WF_DEFERRED - The message is enqueued to WF_NOTIFICATION_OUT,  READY in WF_DEFERRED - Check if Deferred Agent Listener is running  ]

• WF_ERROR Queue Status

• Error Notification(s)

• WF_NOTIFICATION_OUT Queue Status  [PROCESSED in WF_NOTIFICATION_OUT - The message is sent as e-mail,  READY in WF_NOTIFICATION_OUT - Check if Notification Mailer is running  ]