0 Replies Last post: Apr 22, 2011 9:17 PM by Community Admin  
Novice 44 posts since
Mar 30, 2009
Currently Being Moderated

Apr 22, 2011 9:17 PM

Workflow Mailer not sending Alert Notifications

Oracle Workflow Cartridge version 11.5.10.2 (Release 11.5)

Issue: Workflow mailer is not sending Alert notifications

 

Cause: There were stuck notifications in the WF_NOTIFICATION_OUT queue.

Running wfver.sql shows:

WF_DEFERRED messages ready:0, waiting:0, expired:0, undeliverable:0,
processed:590
WF_NOTIFICATION_OUT messages ready:438437, waiting:0, expired:4,
undeliverable:0, processed:316
WF_NOTIFICATION_IN messages ready:0, waiting:0, expired:0, undeliverable:0,
processed:3
WF_ERROR messages ready:6, waiting:0, expired:0, undeliverable:0, processed:0


Solution:

STOP ALL Workflow Agent Listeners and the Mailer PRIOR to performing the following:

1. Backup the Oracle Alert messages that are unprocessed on the WF_NOTIFICATION_OUT.

ASSUMPTION: The corrupt messages are not belonging to Oracle Alert.

create table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK
as
select *
from APPLSYS.AQ$WF_NOTIFICATION_OUT
where CORR_ID like 'APPS:ALR%' and msg_state in ('READY','WAIT');

2. Drop the WF_NOTIFICATION_OUT queue to remove all the other messages:

sqlplus apps/<apps password>

exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);

exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force =>
TRUE);

3. Recreate WF_NOTIFICATION_OUT as it need to exist for $FND_TOP/patch/115/sql/wfntfqup.sql to run successfully:

a. Run the following to recreate the JMS queues:
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps

b. Add the subscribers:
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS APPS

4. Now run to repopulate the WF_NOTIFICATION_OUT queue:
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS
5. Start up the workflow listeners.

6. Test notification mailer

More Like This

  • Retrieving data ...