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.
