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

2 min read

Nov 19, 2010 12:00:00 AM

I just recently faced an issue regarding how to automate the Workflow Mailer override address for development and testing environments during the EBS cloning process.

For those who may not be aware, the "override address" is an email address you can set per EBS environment where the WF Mailer will send ALL outgoing emails. This is a critical safety measure to avoid sending test notifications to real users during development.

The Challenge with Manual Configuration

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 email via the GUI interface.

While the GUI is fine for one-off changes, it introduces two major hurdles for Apps DBAs:

Verification Requirements: The GUI sends a verification code to the entered email address and requires you to enter it on the next screen to confirm the change.
Access Issues: We as Apps DBAs do not always have direct access to the specific override mailbox to retrieve that code.

Why Automation is Necessary for Cloning

If you are looking to script your entire EBS cloning process to make it repeatable and fast, a GUI-based step is a major bottleneck. To achieve a fully "hands-off" clone, we need a SQL or PL/SQL-based solution.

I performed several searches for an official PL/SQL API to handle this. While FND_SVC_COMP_PARAM_VALS_PKG.UPDATE_ROW exists, it is not exactly a user-friendly interface for quick scripting.

A SQL-Based Solution for Apps DBAs

After digging through the underlying tables, I came to the following solution. This script identifies the correct parameter ID for the "Test Address" and updates the value directly in the database, bypassing the GUI verification entirely.

Implementation Script

-- Check the current value 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 the override email address update fnd_svc_comp_param_vals fscpv  set fscpv.PARAMETER_VALUE = '<your_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' );  -- Verify the update 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;

Final Recommendations

I hope some of you find this useful for your automation efforts. As always, use this at your own risk and ensure you perform careful testing in a non-critical environment first.

If you know of a better or more "supported" way to address this challenge via a standard API, I would be more than happy if you could point me toward it!

Wishing you all the best in your cloning efforts. Thank God it's Friday!