wf post cloned activity

EBS -- Cancel Workflow Notification Mails to be sent and purge out queue ..

　

　

　

　

Notification mails are created by the workflow engine to inform EBS users about an ongoing activity.. According to the scenario of the workflow, these mails can inform the related users about a Purchase and Order incident, maybe about a new hired employee and so on..

Notificaton mails are sent by the Notification Mailer in EBS.. Notification Mailers is a seeded technology that Oracle delivers with EBS system..

　

After this general information phase, lets look at the issue with those notification mails that can be annoying..

When you clone an EBS environment, usually you deactivate the notification mailer unless told to do the opposite..

Consider the following scenario,

You cloned a EBS environment, and didnt activate the notification mailer.. Time passed and large number of notifications have been created because of the workflow activities.. These notifications could not be sent to the related users because Notification mailer was deactivated..

One day, you ve been told to activate the notification mailer on this clone environment.. You set the smtp address ( assuming you dont you imap), and started the notification mailer..

Shortly after, you got a call from the business saying, users are receiving tons of notification mails.. Mail servers hang , please stop the notification mailer..

　

So this is the issue and the worst case scenario...

　

To prevent this unwanted situation, you need to do the following in the cloned environment --before activating the notification mailer.. Note that, this method applies to EBS 11.5-R12..

　

Connect to EBS database of the cloned environment with APPS user and update wf_notifications table..

　

sqlplus apps/apps_passwd

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

　

　

　

Above action will update mail_status column in the wf_notifications table. Thus, notification mailer will not find any mails to be sent.. Note that, notification mailer send notification mails if it find the records with mail_status column = MAIL in the wf_notifications table.

Then run the following sql to rebuild WF_NOTIFICATIONS_OUT queue ..

　

Login to the application server of the cloned environment with the application owner and execute the wfntfqup.sql with necessary argument (apps apps_pwd applsys) This action will clear the mails which are placed already in the WF_NOTIFICATIONS_OUT and waiting in this queue to be sent.. After clearing those records, the wf_notifications_out will be recreated by the wfntfqup.sql using the WF_NOTIFICATIONS table..

　

sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

　

Ex: sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys

　

　

　

　

After this actions you are safe, you can start the notification mailer in the cloned environment..