How to Rebuild Workflow Queues

STEP 1: Stop the workflow mailer and Mailer service components in OAM. Make sure the 3 service components are de-activated.

STEP 2: Make a note of the output for the below query:

select     

         message_type,

          mail_status , count(*)

from wf_notifications

where  mail_status <> 'SENT'

and status <> 'CLOSED'

group by message_type, mail_status

Record the notifications with mail_status = ‘MAIL’.

If there are any WFERROR message types then make a note of the following:

Select min(begin_date) from wf_notifications where message_type = ‘WFERROR’ and mail_status = ‘MAIL’;

Select max(begin_date) from wf_notifications where message_type = ‘WFERROR’ and mail_status = ‘MAIL’;

STEP 3: Execute the below script:

Update wf_notifications set status=’CLOSED’, mail_status=’SENT’ where message_type=’ WFERROR’ and mail_status = ‘MAIL’

Commit;

Re-run

select     

         message_type,

          mail_status , count(*)

from wf_notifications

where  mail_status <> 'SENT'

and status <> 'CLOSED'

group by message_type, mail_status

This time there should be no WFERROR.

STEP 4:  Rebuild the queues.

Note: Make sure the notification mailer and service components are de-activated.

Log in on unix as application owner (applmgr) and run the below command:

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

This may run from 15 to 30 minutes…

After the above command is completed, log back into sqlplus as apps and run:

select     

         message_type,

          mail_status , count(*)

from wf_notifications

where  mail_status <> 'SENT'

and status <> 'CLOSED'

group by message_type, mail_status

select count(*) from wf_notification_out; (It should be 0 at this time)

Log back into OAM :

Activate the service components. This will automatically start the mailers.

From sqlplus:

select count(*) from wf_notification_out; (The count should start increasing)

Now after a gap of 10 minutes:

Re-run

select     

         message_type,

          mail_status , count(*)

from wf_notifications

where  mail_status <> 'SENT'

and status <> 'CLOSED'

group by message_type, mail_status

There will not be any  mail_status = MAIL hence  all the stuck emails should be mailed out by now.