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) ++++++
