Resend, OPEN,CANCELLED Workflow Notification mails

Many notification mailers stuck in the mailer queue (status='OPEN' and mail_status='MAIL')

SQL> select count(*) from wf_notifications where status='OPEN' and mail_status='MAIL';

select count(*),status, MAIL_STATUS from wf_notifications group by status, MAIL_STATUS order by status;

or , for a more detailed list:

select subject,from_user,notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

Solution:

the original problem could be caused by any of the following - mail server problems, network problems

Resend them by doing the following:

1) stop workflow mailer

2) cd $FND_TOP/patch/115/sql

3) sqlplus APPS/<passwd> @wfntfqup.sql APPS <passwd> APPLSYS

4) start workflow mailer and monitor the queue

Workflow mailer log file:

cd $APPLCSF/$APPLLOG

/u02/PROD/inst/apps/PROD_??????/logs/appl/conc/log >ls -ltr FNDCPG*

To purge old pending notification messages:

update wf_notifications set mail_status = 'SENT' where status = 'OPEN';

$FND_TOP/sql/wfrmitms.sql (to delete status information in Oracle Workflow runtime tables for a particular item type),
$FND_TOP/sql/wfrmitt.sql (to delete all data in all Oracle Workflow design time and runtime tables for a particular item type).
$FND_TOP/sql/wfrmall.sql (to delete all data in all Oracle Workflow design time and runtime tables for all item type).

Check workflow mailer service current status

 sqlplus apps/<apps password>
  select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

 Find current mailer status

  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

How to resend a missed out e-mail notification which was sent out for approval of Purchase orders.

To implement the solution perform the following steps:

1. Update the WF_NOTIFICATIONS table using the following script:

SQL> update wf_notifications set status ='OPEN', mail_status ='MAIL', end_date=NULL where notification_id=<NID>;

SQL> commit;

The example below, wil create a sql script which you can run to do the update for all OPEN/MAIL workflow mails:

select 'update wf_notifications set status ='||'''OPEN'''||',
mail_status='||'''MAIL'''||', end_date=NULL
where notification_id='||notification_id||';' 
from wf_notifications
where status='OPEN'
and mail_status='MAIL';

NOTE: HERE THE end_date COLUMN IS NOT UPDATED.

2. Then execute the following command:

SQL> execute wf_xml.EnqueueNotification(<NID>);

SQL>commit;

The example below will spool a sql script which you can run for all OPEN/MAIL workflow mails:

select 'execute wf_xml.EnqueueNotification('||notification_id||');' from wf_notifications where status='OPEN' and mail_status='MAIL';

and confirm that queue WF_SMTP_O_1_QUEUE is started

execute DBMS_AQADM.start_queue('APPLSYS.WF_SMTP_O_1_QUEUE',TRUE,TRUE);

Possible values for <wished_preference> are:

QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments")
MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTML (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")