EBS DBA: WF Mailer Override Address and Cloning: How to

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,
Yury

Thank God its Friday!