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
