Oracle APPS- Purge Workflow email notifications from the Oracle Workflow queue so the Email is not sent
So we have a scenario typically when we create a cloned environment. 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.
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.
So we have a scenario typically when we create a cloned environment. 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.
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).
Example:
update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';
OR
update wf_notifications
set mail_status = 'SENT'
where end_date is not null
and status = 'CLOSED'
and 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 @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr
Example Syntax:
sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys
3. Now start the mailer.
