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
