I just recently faced an issue on how to automate the Workflow Mailer override address for development and testing environments during the EBS cloning process. For the information of others, the “override address” is an email address you can set per EBS environment where WF Mailer will send ALL emails avoiding sending emails to real users during testing and development.
The problem is that the only official way to set the override address in the latest ATG releases is to go through the OAM portal and change the override email via the GUI interface. The GUI sends a verification code to the email address entered, asking to enter it on the next screen to confirm the change. The issue is that we as Apps DBAs do not always have access to the override address mail box. On top of that if we are looking to script whole EBS cloning process we need to find SQL-PL/SQL based solution.
I did some searches for an “official” PL/SQL API to be used, however failed to find one (biside of FND_SVC_COMP_PARAM_VALS_PKG.UPDATE_ROW which isn’t the most user friendly interface anyway).
I came to the following solution:
select fscpv.parameter_value from fnd_svc_comp_params_tl fscpt ,fnd_svc_comp_param_vals fscpv where fscpt.display_name = 'Test Address' and fscpt.parameter_id = fscpv.parameter_id; update fnd_svc_comp_param_vals fscpv set fscpv.PARAMETER_VALUE = '<override email address>' where fscpv.parameter_id in ( select fscpt.parameter_id from fnd_svc_comp_params_tl fscpt where fscpt.display_name = 'Test Address'); select fscpv.parameter_value from fnd_svc_comp_params_tl fscpt ,fnd_svc_comp_param_vals fscpv where fscpt.display_name = 'Test Address' and fscpt.parameter_id = fscpv.parameter_id; commit;
Hope some of you will find it useful. As always use at your own risk and after careful testing :).
On the other hand I would be more than happy if you could point me to a better way of addressing the challenge.
Wishing all the best in your cloning efforts,
Thank God its Friday!
We have used the below call to successfully update the parameter values for the service component like workflow mailer
FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW ( x_component_name => ‘Workflow Notification Mailer’,
x_parameter_name => ‘TEST_ADDRESS’,
x_parameter_value => ‘[email protected]’
x_customization_level => ‘L’
x_object_version_number => -1,
x_owner => ‘ORACLE’
By the way similar calls can be found in
The autoconfig script that updates workflow mailer details during cloning
Thank you for sharing the syntax you are using for setting override address.
It looks like blogging is mutually beneficial process. You share information with others and get to valuable feedback as yours.
Thanks once again,