How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent
First, Verify or set the 'WF: Workflow Mailer Framework Web Agent' profile value to physical host name and port number, DO NOT enter the Load Balancer URL
Example
WF: Workflow Mailer Framework Web Agent=http://erpdev01.blogspot.com:8008
1. Verify the current status of each notifications found in the WF_NOTIFICATIONS table that has potential for being sent when the Java Mailer gets started.
SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
where status in ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
order by notification_id;
Normally, only records where status = 'OPEN' and mail_status = 'MAIL' are notifications that would be sent, but there are programs that also can retry Canceled or Invalid notifications, so we included these as well.
This query should show which notifications are waiting to be e-mailed.
2) Use BEGIN_DATE in the where clause to help narrow down the emails not to get sent by the Mailer from a specific date range.
For example :
SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
Where Status In ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
and begin_date < sysdate-90 -- List only emails older than 30 days ago
order by notification_id;
3) To update a notification so that it will not get e-mailed, simply set the MAIL_STATUS = 'SENT', and rebuild the Mailer queue using wfntfqup.sql
The mailer will think the e-mail has already been sent and it will not send it again.
Note : Users can still reply to all these notifications from the worklist page in the applications.
Example:
SQL> update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-90;
(Remember to include any other filters you want like begin_date < sysdate-30)
This will update all notifications waiting to be sent by the mailer to SENT, and therefore will not get emailed when the Mailer is restarted.
example
----------------
update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status in ('MAIL','INVALID') and Status In ('OPEN', 'CANCELED') and begin_date >= trunc(TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'),'MM/DD/YYYY'))
4) Run the script $FND_TOP/patch/115/sql/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. (or CANCELED and INVALID if certain concurrent reports are run)
Example :
$ 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
5) Now start the Workflow Java Mailer.
First, Verify or set the 'WF: Workflow Mailer Framework Web Agent' profile value to physical host name and port number, DO NOT enter the Load Balancer URL
Example
WF: Workflow Mailer Framework Web Agent=http://erpdev01.blogspot.com:8008
1. Verify the current status of each notifications found in the WF_NOTIFICATIONS table that has potential for being sent when the Java Mailer gets started.
SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
where status in ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
order by notification_id;
Normally, only records where status = 'OPEN' and mail_status = 'MAIL' are notifications that would be sent, but there are programs that also can retry Canceled or Invalid notifications, so we included these as well.
This query should show which notifications are waiting to be e-mailed.
2) Use BEGIN_DATE in the where clause to help narrow down the emails not to get sent by the Mailer from a specific date range.
For example :
SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
Where Status In ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
and begin_date < sysdate-90 -- List only emails older than 30 days ago
order by notification_id;
3) To update a notification so that it will not get e-mailed, simply set the MAIL_STATUS = 'SENT', and rebuild the Mailer queue using wfntfqup.sql
The mailer will think the e-mail has already been sent and it will not send it again.
Note : Users can still reply to all these notifications from the worklist page in the applications.
Example:
SQL> update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and begin_date < sysdate-90;
(Remember to include any other filters you want like begin_date < sysdate-30)
This will update all notifications waiting to be sent by the mailer to SENT, and therefore will not get emailed when the Mailer is restarted.
example
----------------
update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status in ('MAIL','INVALID') and Status In ('OPEN', 'CANCELED') and begin_date >= trunc(TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY'),'MM/DD/YYYY'))
4) Run the script $FND_TOP/patch/115/sql/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. (or CANCELED and INVALID if certain concurrent reports are run)
Example :
$ 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
5) Now start the Workflow Java Mailer.