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

From my experience I have seen 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, in this article I'll show how 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;