Aloz 的 Oracle ERP 天地

In this Document
  Goal
  Solution

Applies to:

Goal

The Workflow mailer has not been running. There are a large number of e-mail notifications accumulating in the queue.  How do we 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. You need 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 you which notifications are waiting to be e-mailed.

You can use the BEGIN_DATE column to help narrow down the ones you do not want sent, if you only want 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.

wfntfqup.sql是在 $FND_TOP/patch/115/sql 之下