How To Rebuild Workflow Mailers Queue When it is Inconsistent or Corrupted in Oracle Apps


In some cases, the mailer's queue (WF_NOTIFICATION_OUT) may be corrupted or inconsistent, and the mailer may not be able to send the email notifications as it should.

It is inconsistent when, for instance, each notification in OPEN / MAIL status in table WF_NOTIFICATIONS, does not have a corresponding message in "Ready" status in the mailer's queue.

Another case is when you changed the profile option "WF: Workflow Mailer Framework Web Agent", but messages in the mailer's queue still refer to the old value.

In such situations, the mailer's queue must be rebuilt.

Rebuild the mailer's queue.

The solution below applies ONLY when there are no Oracle Alert messages in the mailer's queue.

Please refer to Note 332152.1 for the complete procedure to rebuild mailer's queue when it contains Oracle Alert messages.


the 

1. Stop Workflow Agent Listeners and Mailers.

2. Find the tablespace where indexes on corrid for queue tables are created:

sqlplus apps/<apps_pwd>

select distinct tablespace_name
from dba_indexes,dba_queues
where index_name='WF_NOTIFICATION_OUT_N1'
and table_name=queue_table
and name like 'WF%';

3. Rebuild mailer's queue:


sqlplus apps/<apps_pwd> @$FND_TOP/patch/115/sql/wfntfqup APPS <apps_pwd> APPLSYS

4. Recreate Index on corrid column:

sqlplus applsys/<applsys_pwd>

CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE &tbs; 

When prompted for the tablespace, enter the tablespace name found at step 2 above.

5. Start Workflow Agent Listeners and Mailers.

6. Confirm Mailer is sending email notifications as expected.

Important
wfntfqup.sql - This script rebuilds the WF_NOTIFICATION_OUT queue. It drops and recreates the WF_NOTIFICATION_OUT queue, removes pending notification messages from the WF_DEFERRED queue, and repopulates the WF_NOTIFICATION_OUT queue from the Oracle Workflow Notification System tables.

Note: If you have implemented Oracle Alert and the WF_NOTIFICATION_OUT queue contains any pending alert e-mail messages, those messages must be processed before the queue can be rebuilt. The wfntfqup.sql script checks the queue for any alert e-mail messages and, if it finds any, exits without performing any changes. Instead, the script returns an error message that specifies how many alert e-mail messages are pending and indicates that these messages must be processed before the script can run.

You must stop the service component containers for notification mailers and agent listeners before you run this script, and restart the containers after the script completes. The container for notification mailers is named Workflow Mailer Service. The container for agent listeners is named Workflow Agent Listener Service.





If you like please follow and comment