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

Nov 19, 2010 / By Yury Velikanov

Tags: , , ,

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!

3 Responses to “EBS DBA: WF Mailer Override Address and Cloning: How to”

  • Amit Poddar says:

    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 => ‘test.mail@mail.com’
    x_customization_level => ‘L’
    x_object_version_number => -1,
    x_owner => ‘ORACLE’
    );

  • Amit Poddar says:

    By the way similar calls can be found in

    $FND_TOP/admin/template/txkJavaMailerCfg.sql

    The autoconfig script that updates workflow mailer details during cloning

  • Yury says:

    Hello Amit,

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>