Resolving workflow mailer issue if it does not send email and fail repeatedly in Oracle Apps.

Resolving workflow mailer issue if it does not send email and fail repeatedly in Oracle Apps.

A common problem in Oracle R12 workflow emailer is that it is failing repeatedly due to
some problem. Some times it process few emails then again it fails. When a number of
reason can cause the issue, now you can debug and fix this kind of issue.
In general I follow the following approach.

•check the log file of workflow emailer

•fix the issue

•recreate the workflow notification out queue to clear all corrupted mails

A. Check the log file of workflow emailer
After setting app env file run the following command.

grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log

Check the above three files for any error, exception in workflow notification

B. Fix the issue
After checking exact issue from the above log file, fix the issue and start wf emailer. If
it does not start to process email normally recreate the queue by following SECTION C

C. recreate the workflow notification out queue to clear all corrupted mails
Stop the workflow mailer and it's three corresponding services.
take a backup of the affected table after log in using apps user.
create table APPLSYS.AQ$WF_NOTIFICATION_OUT_19JUL13
as
select * from APPLSYS.AQ$WF_NOTIFICATION_OUT

Run the following two commands to delete the current queue.

1.exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
2.exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force
=>TRUE);
Then run the following commands to recreate the queue again

1.sqlplus apps/ @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys
2.sqlplus apps/ @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS
3.sqlplus apps/ @$FND_TOP/patch/115/sql/wfntfqup APPS  APPLSYS

The above three commands will create a new queue and clear corrupted messages.

Start the workflow mailer.
You can check whether emails are getting processed or not by using following command.

select decode(state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Processed/Retained',
3, '3 = Exception',
to_char(substr(state,1,12))) State,
count(*) from wf_notification_out
group by state;

or [in details]

select corrid,
decode(state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Processed/Retained',
3, '3 = Exception',
to_char(substr(state,1,12))) State,
count(*) from wf_notification_out
group by corrid, state;