Resend Failed/Sent Notifications Again in Oracle Apps R12

In this post, I am sharing how to resend the notification mail to users if it fails or user have not received.

1) Check if the Notification mailer/Agent listener is running
    select (case when (select count(1)
                         from apps.fnd_concurrent_queues
                        where concurrent_queue_name = 'WFMLRSVC') > 0
                 then 'Notification Mailer Running' 
                 else 'Notification Mailer Not Running'
                end) "Status"
       from dual

2) Run the below query to get the exact number based on dates

       select *
        from wf_notifications
      where 1=1
         and trunc(begin_date) <= trunc(sysdate-4)
         and mail_status = 'FAILED'
         and status = 'OPEN'
         order by trunc(begin_date) 
Get details based on the recipient roles

  select * from wf_notifications where recipient_role='HIMANSHU';

3) Run the concurrent program 'Resend Failed/Error Workflow Notifications' from 'System  Administrator' responsibility.
     > All parameters are optional, please select the parameter values accordingly so that only required notifications get re-processed.

     Mail Status: Error/Failed
     Message Type: Workflow Item Types
     Recipient Role: List of workflow roles
     Notifications sent on or after: Date
     Notifications sent on or before: Date

4) For resending the Sent notifications, please 'Rewind'

    >> Goto Workflow > Status Monitor
        >> Search with 'Type Internal Name' and 'Item Key'
             >> You can get it from wf_notifications table

        >> Click on 'Activity History' > Select the Activity with 'Notified' Status and 'Rewind'
            >> Again select the same activity and 'Apply'
             >> You will get a message for Rewind.

    Check the wf_notifications table for the same 'message_type' and 'item_type', there should be two entries with the previous one 'Closed'.

5) If notification is getting failed, again and again, check the user preferences for that recipient.

      Select *
        from fnd_user_preferences
       where user_name = <> ;

You can also debug the notification with the notification id

Run the @$FND_TOP/sql/wfmlrdbg.sql  
 > Parameter: Enter Value for 1: <Notification_ID >

It will generate the debug file in the same directory.

If you like please follow and comment