How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent

Goal

The Workflow mailer has not been running. Which may have caused  a large number of e-mail notifications to accumulate in the queue.  How does one prevent these from being sent when the mailer is started.

Solution

Please take a backup before making any of these changes and try this on a Test instance first.

1. Update the notifications you do not want sent, in the WF_NOTIFICATIONS table.  Check the WF_NOTIFICATIONS table. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

This should show which notifications are waiting to be e-mailed.
One can use the BEGIN_DATE column to help narrow down the ones not to send if one only wants to stop the e-mails from a specific date range.

To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The
mailer will think the e-mail has already been sent and it will not send it again.. (Users can
still reply to the notification from the worklist page in the applications).

e.g. SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';
This will update all notifications waiting to be sent by the mailer.

2. Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.  It will then populate the queue with the current data in the wf_notifications table.
Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

sqlplus usr/passwd@db @wfntfqup APPSusr APPSpw FNDusr

Example Syntax:

sqlplus apps/apps@db @wfntfqup apps apps applsys

3. Now start the mailer.

Reference : Metalink Note : 372933.1