Oracle Workflow Notification Mailer Outbound Processing

Workflow Mailer Outbound Processing

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key

2. There is seeded subscription to this Event

3. Event is placed on WF_DEFERRED agent

4.Event is dequeued from WF_DEFERRED and subscription is processed

5. Subscription places event message to WF_NOTIFICATION_OUT agent.

6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address

 

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED   and
b) Notification mail_status is MAIL or INVALID  and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status  FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)

.

Reference

Workflow Administrator Guide  (Page 73-75)

How to troubleshoot outbound Workflow Notification Mailer Issues in Oracle E-Business Suite 11i/R12 coming soon … 

Share This Post with Your Friends over Social Media!

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Oracle Gold Partner specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

28 comments
SubbaRao says July 17, 2008

Hi Atul,

Thanks for Nice Post. I want to share the following things, which I noticed.

I recently Configured Oracle Workflow Notification Mailer in 11.5.10.2 Instance. I noticed one thing that, Recently we applied RUP5 Patch to present 11.5.10.2 Instance. Once We Applied RUP5, We are not receiving Oracle Alerts Mails. Finally I got it from RUP5 features and found that, Before RUP4, Oracle Alert was using Unix Send Mail to send Oracle Alerts. From RUP4, Workflow Notification Mailer needs to be configured for the same purose.

According to your above procedure, Oracle Alerts directly Enques into WF_NOTIFICATION_OUT table and deques it from there and send the relevant mail to users.

I noticed this one regarding Alerts. If we are not getting mails in time, we better to rebuild the wf_notification_out table using $FND_TOP\patch\115\sql\wfntfqup.sql script, this one we need to run this script with the help of Oracle Support.

We need to schedule the “Purge Obsolete Workflow Run time data” Program to purge obsolete workflow data.

Thanks again for nice post Atul.

Reply
lakshmikanthan says July 18, 2008

hi all,
when am doing perl adcfgclone.pl dbTier
that time error was occured

please help me very urgent…………

ERROR:
RC-40201: Unable to connect to Database YTEN.

StackTrace:
java.lang.Exception: Cannot connect to database using DBUtil
at oracle.apps.ad.clone.ApplyDatabase
.checkDBConnection(ApplyDatabase.java:3382)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:524)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:328)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:304)
at oracle.apps.ad.clone.ApplyDBTier.(ApplyDBTier.java:154)
at java.lang.reflect.Constructor.newInstance(Native Method)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:68)
at java.lang.Thread.run(Thread.java:479)

DEBUG: checkDBConnection out()
Executing runInstallDriver…
Started unzipping files…
UNZIP used: UnZip 5.51 of 22 May 2004, by Info-ZIP. Maintained by C. Spieler. Send
Verifying correctness of adcrdbclone.sql
Instantiating /test/yten/r02/oracle/ytendb/9.2.0/appsutil/template/adcrdbclone.sql to /test/yten/r02/oracle/ytendb/9.2.0/appsutil/install/YTEN_ebs/adcrdbclone.sql
Clustered Database: false
instantiate file:
source : /test/yten/r02/oracle/ytendb/9.
2.0/appsutil/template/adcrdbclone.sql
dest : /test/yten/r02/oracle/ytendb/9
.2.0/appsutil/install/YTEN_ebs/adcrdbclone.sql.bak
AC-00439: Creation of file /test/yten/r02/oracle/
ytendb/9.2.0/appsutil/install/YTEN_ebs/adcrdbclone.sql.bak failed
Raised by oracle.apps.ad.autoconfig.InstantiateFile
instantiate file:
source : /test/yten/r02/oracle/ytendb/9.2.0/a
ppsutil/install/YTEN_ebs/adcrdbclone.sql.bak
dest : /test/yten/r02/oracle/ytendb/9.2.0/ap
psutil/install/YTEN_ebs/adcrdbclone.sql
DEBUG : The destination file does not have write permission /tmp/adcrdbclone.sql
DEBUG : Changing the permission of the file failed
AC-00444: Unable to write to file: java.io.FileNotFoundException:
/test/yten/r02/oracle/ytendb/9.2.0/appsutil/instal
l/YTEN_ebs/adcrdbclone.sql.bak (No such file or directory)
Raised by oracle.apps.ad.autoconfig.InstantiateFile

setting permissions: /tmp/adcrdbclone.sql a+r
AC-00425: Setting permissions failed for file: /tmp/adcrdbclone.sql
Raised by oracle.apps.ad.autoconfig.InstantiateFile
AC-50401: Command: su root -c “cp
/tmp/adcrdbclone.sql /test/yten/r02/oracle/ytendb
/9.2.0/appsutil/install/YTEN_ebs/adcrdbclone.sql” failed
Raised by oracle.apps.ad.autoconfig.InstantiateFile
AC-00439: Creation of file /test/yten/r02/oracle/ytendb/9.2.
0/appsutil/install/YTEN_ebs/adcrdbclone.sql failed
Raised by oracle.apps.ad.autoconfig.InstantiateFile

StackTrace:
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1591)
at oracle.apps.ad.util.SystemC
heck.setPermissions(SystemCheck.java:1250)
at oracle.apps.ad.clone.ApplyDatabas
e.runInstallDriver(ApplyDatabase.java:1001)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:580)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:328)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:304)
at oracle.apps.ad.clone.ApplyDBTier.(ApplyDBTier.java:154)
at java.lang.reflect.Constructor.newInstance(Native Method)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:68)
at java.lang.Thread.run(Thread.java:479)

Thank you
lakshmi

Reply
Atul says July 18, 2008

Check these lines carefully …

DEBUG : The destination file does not have write permission /tmp/adcrdbclone.sql
setting permissions: /tmp/adcrdbclone.sql a+r
AC-00425: Setting permissions failed for file: /tmp/adcrdbclone.sql

AC-50401: Command: su root -c “cp
/tmp/adcrdbclone.sql /test/yten/r02/oracle/ytendb
/9.2.0/appsutil/install/YTEN_ebs/adcrdbclone.sql” failed
Raised by oracle.apps.ad.autoconfig.InstantiateFile

Which user you are using to run postclone ?
Check file permissions ?

Reply
vijay says September 11, 2008

brother,

I am sure u ran the adcfgclone script as a root user.

vijay.

Reply
vrushali says December 15, 2008

Hi,

Cna I get the etrm for relation between queue tables and notification tables?I mean to say wf_notification and wf_notification_out

Reply
siva says January 8, 2009

How to integrate IdMUnit with oracle identity manager

IdMUnit is an xUnit automated testing framework for Identity Management solutions.

http://sourceforge.net/projects/idmunit

Reply
siva says January 12, 2009

Hi atul,

Can u please tell me how to integrate idMUnit with oracle identity manager

Reply
brijesh.201@gmail.com says February 6, 2010

hi atul i am still waiting ur post on troubleshooting of workflow notification mailer.

Thanks
Brijesh

Reply
guts says March 10, 2010

Hi Atul,

Pls post

How to troubleshoot outbound Workflow Notification Mailer Issues in Oracle E-Business Suite 11i/R12 coming soon …

Regards,
Gurudatt

Reply
Anil K A says March 22, 2010

Hi,

I have a requirement from my clients.

Standard Functionality: When a Manager clicks on the APPROVE (button / link) to approve a workflow notification from a mailing application, a (response) mail gets composed to the Workflow Mailbox with the NID and Access Key. The content as shown below.

===========================
Action: ‘Approve’
Comments: ”

NID[3699/874478985@WFMAIL3]
===========================

Requirement:

The requirement is that, when the Maanger clicks on Approve / Reject, the original message body also has to get populated into the response mail along with the NID and Access Key.There would be audits conducted(looking into the workflow mail box) on the approval / rejection decisions taken by the users. The intention of getting the originial message body is to identify, to which notification(or letter) this action was taken. Me, being a Technical person can get it from the Notification Id in the workflow mail box, but the users cant identify that. So, I need to get the original message body into the response. This is the reason for the requirement.

Actually, I dont need the whole message body, but the value in an URL attribute, which is one of the SEND attributes of the notification in question.

fyi, It is a VOTING ACTIVITY and hence, I donot know how many users would get the notification.

I tried creating a RESPONSE Attribute(I understand that, a response attribute is an attribute where I accept inputs from the user), but couldnt assign the value to that attribute.

Regards
Anil K A

Reply
Ashish Tiwari says April 15, 2010

Hi Atul,

We are getting this error in our Production for WF.

An Error occurred in the following Workflow.

Item Type = APEXP
Item Key = 356377
User Key =ER-356377

Error Name = WF_ERROR
Error Message = [WF_ERROR] ERROR_MESSAGE=3835: Error ‘-1001 – ORA-01001: invalid cursor’ encountered during execution of Generate function ‘WF_XML.Generate’ for event ‘oracle.apps.wf.notification.send’. ERROR_STACK=
Wfa_Sec.Get_Role_Info2(KRSS-IN)
Wfa_Sec.Get_Role_Info3(KRSS-IN)
Wf_Directory.GetRoleInfo2(KRSS-IN)
Wf_Directory.GetRoleInfoMail(KRSS-IN)
WF_XML.Generate(oracle.apps.wf.notification.send, 15355045)
WF_XML.Generate(oracle.apps.wf.notification.send, 15355045)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 15355045, WF_XML.Generate)
Wf_Event.dispatch_internal()
Error Stack =

Activity Id = 203007
Activity Label = REQUEST_APPROVAL_PROCESS:REQUEST_APPROVE_FROM_APPROVER
Result Code = #MAIL
Notification Id = 15355045

Plese can you help.

Best Regards,
Ashish

Reply
Mitzu says April 29, 2010

Hi Atul,
I’ve tried to send an test email, but I didn’t receive it.
I’ve checked all the points from a) to e) and everything is OK.

Can you please help me?

Regards,
Mitzu.

Reply
NDG says January 19, 2011

Hi Atul,

I want to configure workflow email notification so that user will recieve mail notification to there mail address and there they can approve or reject. I am working on R12
and I want the necessary steps for it.Can you provide the same.
Also I want to know whether these mails will go to blackberry and whether user can view these mails,currently we are having microsoft outlook express.

Thanks

Reply
NDG says January 19, 2011

Hi,

I want to test workflow notification by sending notification to only one person in test instance but when I start workflow mailer all the old mails go to users,so I want to delete these mail notifications how can it be done?

Reply
Atul Kumar says January 19, 2011

@ NDG,
To configure workflow notification mailer in R12 http://onlineappsdba.com/index.php/2007/12/27/workflow-notification-mailer-setup-in-oracle-apps-r1212i/

Q. I want to know whether these mails will go to blackberry and whether user can view these mails,currently we are having microsoft outlook express.

A. Notification mailer will use SMTP server defined in configiration which will relay it to destination and can be read by Blackberry or Outlook express as long as destination email server support access to these client applications.

Q. I want to test workflow notification by sending notification to only one person in test instance
A. Make sure test mail account is set during workflow notification mailer.

For notifications which you don’t want to go to users set
mail_status to SENT in workflow table

Reply
NDG says January 19, 2011

How to setuo test mail account ?

Reply
NDG says January 19, 2011

How to setup test mail account ?

Reply
abdul.hameed says March 21, 2011

kindly guid me to confiugre workflow mailer for Windows 2003 server.

Reply
Geerthiraman says March 14, 2012

Hi Atul,
Can you please tell me what is the use of Purge component in workflow mailer. I desperately need to know.

Regards,
Geerthi

Reply
Atul Kumar says March 15, 2012

@ Geerthiraman,
Purge as name suggests is used to purge workflow data which is no longer required.

Reply
Geerthiraman says March 16, 2012

Hi Atul,
Thanks for your reply. As of now on my instance purge service is down. Do i need to startup and keep it running? will it create any issues? Kindly suggest.

Reply
Sowmya says February 21, 2013

Hi,

Where is the sender email id is taken from?
I want to change the sender email id while sending PO request to supplier

Reply
bobby says December 4, 2013

Good Post Atul
HI Sowmya,
did you find a solution for this please let me know.
Atul in R12.1.3 how can you send undelivered email just to that specific buyer.
Thanks
BObby

Reply
suresh says December 11, 2014

workflow mailers were down from the past 4 months…why these mailes went down..i am not able to find…please help me how to find ..

here is teh log file

SYSADMIN(0):-1:Thread[GSMQueueProcessor,5,main]:1319081591:37671:1417699146612:2:STATEMENT:[SVC-GSM-WFALSNRSVC-57218 : oracle.apps.fnd.cp.gsc.Processor.sleep()]:Waiting 10 seconds (10000 ms)

Reply
chaitanya says January 10, 2015

Hi Atul,

in workflow notification when every am rejected that time statues is reject/terminated but the end_date is coming this formate “01-jan-4072”.
can you please explain why end date pick up future date like ‘4072’, and how to rectfie this error

Reply
Rajan says February 16, 2015

Hello,

Can someone give some guidance on configuring the cloud service outlook365 email as the smtp server for workflow notification.

best regards,
Rajan

Reply
JCRowan says July 9, 2015

Hello,

I am not very familiar with the WF Mailer, but I would like some direction on a problem we are experiencing in our production environment. First off, we are running EBS v12.1.3 – Here is the problem. When a change is made to an existing PO an email is sent via WF to the vendor/s, this we do not want to happen. If we turn off the mailer, then they don’t get the notifications for say, when their portal password has changed. We want it to work for the passwords but not for any PO changes. Where would I look to see where the application is configured for the PO change BES?

Thanks,
JC

Reply
JCRowan says August 4, 2015

My client has requirement that no vendors get notified of any PO change. They still need emails to flow to the approval trail, internal folks, but no emails should be sent to vendors. I found a column in the apps.ap_supplier_sites_all table, SUPPLIER_NOTIF_METHOD is the column name – It has a choice of values for this field, null, NONE, MAIL, FAX etc. I updated this field to have “NONE” for a vendor, went in and approved that PO and when I look in the wf_notificatons and the wf_notifications_out table it looks as though it would be mailed as the status as “OPEN” and the mail status as “MAIL” – So how would one stop a vendor from receiveing emails from WF if any PO modification takes place?

Reply
Add Your Reply