How To Rebuild Mailers Queue

In some cases, mailer's queue (WF_NOTIFICATION_OUT) may be corrupted or inconsistent, and 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 mailer's queue.

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

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

The goal of that Note is to provide instructions how to rebuild the mailer's queue.

Solution:

1. Stop Workflow Agent Listeners and Mailers.

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

select distinct  tablespace_name from dba_indexes,dba_queues

where index_name like 'WF%N1' and table_name=queue_table

and name like 'WF%'; 

3.It is showing which notifications are ready to be e-mailed when Workflow Notification Mailer is started.

Updating the notification so that it will not be e-mailed is done by setting the MAIL_STATUS = 'SENT' which will cancel the job at startup. Also, need to provide the item type desired.

(WFERROR messages)

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL' and message_type='WFERROR';

4.Rebuild mailer's queue: 
sqlplus apps/<apps_pwd> @$FND_TOP/patch/115/sql/wfntfqup APPS <apps_pwd> APPLSYS


5.Recreate Index on corrid column: 

sqlplus 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; 

6.Check for invalids, if yes, recompile to make sure 0 invalids

select owner,object_name,object_type from dba_invalid_objects;

OWNER                          OBJECT_NAME          OBJECT_TYPE

------------------------------ -------------------- -------------------

APPS                           WF_DIAGNOSTICS       PACKAGE BODY

APPSREAD                       WF_NOTIFICATION_OUT  SYNONYM

APPS                           WF_NOTIFICATION_OUT  SYNONYM

as a sysdba----

alter SYNONYM apps.WF_NOTIFICATION_OUT compile;

alter PACKAGE apps.WF_DIAGNOSTICS compile body;

alter SYNONYM APPSREAD.WF_NOTIFICATION_OUT compile;

7.Start Workflow Agent Listeners and Mailers.

http://allappsdba.blogspot.in/