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_NOTIFICATIONSwhere status = 'OPEN' and mail_status = 'MAIL'; Above script willshow which notifications are waiting to be e-mailed.
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 apps/apps @$FND_TOP/patch/115/sql/wfntfqup.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jun 22 16:10:37 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @@wfntfqup.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.01
Commit complete.
Elapsed: 00:00:00.00
**** TEMPORARY TABLES / AQs created ****
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
**** Messages backed up ****
Commit complete.
Elapsed: 00:00:00.00
**** wfaqback.sql completed, executing SQLs from wfntfqup
PL/SQL procedure successfully completed.
Elapsed: 00:02:10.21
*** invoking wfaqrenq.sql ******
*** Re-enqueing messages
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
Commit complete.
Elapsed: 00:00:00.01
***** Re-enqueue OF Alerts completed *****
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
847889.1 - Stop Workflow Notification Emails During Clone
828812.1 - How To Stop Old Outbound Workflow Notification Email Messages During Clone Activity
562551.1 - Workflow Java Mailer FAQ
Which Table Stores the NEW Test Address 'set override address'?
SQL> select fscpv.parameter_value
from fnd_svc_comp_params_tl fscpt
,fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;
3. Now start the mailer.
SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONSwhere status = 'OPEN' and mail_status = 'MAIL'; Above script willshow which notifications are waiting to be e-mailed. To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. Themailer 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'; ORupdate wf_notificationsset mail_status = 'SENT'where end_date is not nulland 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 apps/apps @$FND_TOP/patch/115/sql/wfntfqup.sql 828812.1 - How To Stop Old Outbound Workflow Notification Email Messages During Clone Activity
562551.1 - Workflow Java Mailer FAQ
3. Now start the mailer. 