Workflow Agent Listener Service WF_DEFERRED Will Not Start, Mailer May Be Down

To implement the solution, please execute the following steps:

1.  Use this query to identify any remaining items in wf_deferred queue:

SQL> select corrid,

decode(state,

0, '0 = Ready',

1, '1 = Delayed',

2, '2 = Retained',

3, '3 = Exception',

to_char(state)) State,

count(*) COUNT

from wf_deferred

group by corrid, state;

2. Items with the APPS corrid will not dequeue from agent listener via service container.  This runs the deferred listener from the command line. Make sure that the listener and workflow service components - including the mailer - are down before you run this. This is not intended to be a substitute for the normal operation of this service container from the OAM console. Use this only during this cleanup operation.

One will have to run this script manually:

begin

wf_log_pkg.wf_debug_flag := TRUE;

wf_event.listen('WF_DEFERRED');

end;

/

commit;

3.  Monitor the WF_DEFERRED queue. The events should move from 'ready' state to 'retained'.  Once they are in 'retained' status then the AQ time manager will automatically clean them off.

4. Use the OAM / workflow manager throughput / agent activity page to see if your events are enqueued into WF_NOTIFICATION_OUT queue.

NOTE:  Not all wf_deferred events are notification.send events so one may not have the same number of outbound mail messages as one had in wf_deferred.

5. Events can be cleaned off of WF_NOTIFICATION_OUT queue and rebuilt from the WF_NOTIFICATIONS table using the script wfntfqup.sql:

REBUILD THE WF_NOTIFICATION_OUT QUEUE

STEP 1. Stop the mailer, listeners and service component container in that order.

STEP 2. Run $FND_TOP/sql/wfntfqup.sql:

sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfntfqup apps <apps_passwd>

applsys

This job purges the wf_notification_out outbound message queue and repopulates from the

WF_NOTIFICATION table:

STEP 3. Ensure that this index is in place:

SQL> select index_name,column_name,column_position

from dba_ind_columns

where table_name='WF_NOTIFICATION_OUT' and

table_owner='APPLSYS'

> WF_NOTIFICATION_OUT_N1 CORRID

If it's not there one can add it back using $FND_TOP/sql/wfqidxc.sql. First check to see if

$FND_TOP/patch/115/sql/wfqidxc2.sql is present and if yes use that instead.

STEP 4. Restart the container, listeners and mailer in that order to test.

++++++ (Doc ID 958178.1) ++++++