Monday, March 22, 2010

SQL: Override Address for Workflow Notification Mailer in the Oracle E-Business Suite

In the Oracle E-Business Suite I often need to develop code that sends emails. Easy enough to jump in and code it using a variety of options like workflow, utl_smtp etc. but what sort of things do we need to consider here?

  • Don't hardcode Outbound SMTP Server details in your code
  • Source the SMTP Server details from a single location
  • For test/development environments we don't want to send out email to real email addresses, so use an email address for test purposes and source it from a single location
  • If you select an email address from a record in the database, then put in an IF or CASE or DECODE statement to check if we should be using a test address and use that instead of the real email address!

So what we should do is pickup the settings from the Workflow Notification Mailer service component for the Outbound SMTP Server and Override Address and use them appropriately.

That way emails won't go to recipients from Test / Development environments where the Override / Test Email Address is set.

Workflow Notification Mailer Outbound SMTP Server

We can get the Outbound SMTP Server Name using the following SQL:

select fscpv.parameter_value smtp_server_name
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
,      fnd_svc_components fsc
where  fscpt.parameter_id = fscpv.parameter_id
and    fscpv.component_id = fsc.component_id
and    fscpt.display_name = 'Outbound Server Name'
and    fsc.component_name = 'Workflow Notification Mailer';

Workflow Notification Mailer Override Address or Test Address

And the Override Address (or Test Address in older terminology) from the following SQL:


select fscpv.parameter_value test_address
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
,      fnd_svc_components fsc
where  fscpt.parameter_id = fscpv.parameter_id
and    fscpv.component_id = fsc.component_id
and    fscpt.display_name = 'Test Address'
and    fsc.component_name = 'Workflow Notification Mailer';

And then we can use them consistently... perhaps following coding practises and wrapping the above in a PL/SQL function would be good!

Note that the above SQL assumes you are using the default seeded Workflow Notification Mailer queue and haven't set up your own. If you have then replace the name Workflow Notification Mailer in the SQL with the name you used.

Catch ya!
Gareth
This is a post from Gareth's blog at http://garethroberts.blogspot.com

Related Posts

10 comments:

jpiwowar said...

Solid gold. I'd always meant to go looking for this information, but had a shortage of "round tuits." Now I have a good reference. *Thank* you.

Regards,

John P.

Gareth said...

Hi John,

You're welcome! PS. Keep up the good work on the EBS front :-)

Regards,
Gareth

Kieron said...

Hi Gareth,
Thanks for posting this! I am integrating some of the sqls on this post into your XML Bursting solution; very handy. Appreciate it.
Kieron

John said...

Buddy!,

What a great blog! I've just come across your blog via the 'Oracle BI Publisher' blog as I was doing some research on bursting a custom PO report as e-mails to Requestors from EBS. I've just read right back into early 2008. John P. has it right 'Solid Gold'. Like Keiron I'll try intergrating the SMTP address from this into the solution.

Regards,

John.

mahakk01 said...

This post is about override address for workflow notification mailer in the Oracle E-business suite. This includes lot of new things about which I wasn't aware before reading this post. The code seems bit difficult. Thanks for the post.
sap upgrade testing

Sheamus said...

I continuously continue coming to your website once more simply in case you have posted new contents.htm

Mitcheal Clerk said...

This is my very first time that I am visiting here and I’m truly pleasurable to see everything at one place. www.listacademybonus.com

Chris Morris said...

I have got the superb information from these blogs finally.Mark Curry

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b

Sagar R said...

Oracle E-Business Functional Workflow for R12
This course provides an overview of the architecture and features of Oracle Workflow and the benefits of using Oracle Workflow in an e-business environment. You can learn how to design workflow processes to automate and streamline business processes, and how to define event subscriptions to perform processing triggered by business events. Students also learn how to respond to workflow notifications, how to administer and monitor workflow processes, and what setup steps are required for Oracle Workflow. Demonstrations and hands-on practice reinforce the fundamental concepts.
Features & Benefits of Oracle Workflow
 Use the Workflow Monitor to monitor a Workflow to completion.
 Describe Alerts and test a Periodic Alert.
 Describe the features of Oracle Business Intelligence (OBI) Applications.
 To join Online Training session kindly feel free to contact with us
 Name - saurabh srivastava
 Email - id - saurabh@maxmunus.com
 Contact No. - +91 -8553576305
 Skype- saurabhmaxmunus
 Company Website - http://www.maxmunus.com