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

5 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

Kron77 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

Unknown 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