Gone are the days where one had to build a new workflow and create a role to email a document to external party. With BI publisher the emails can be very fancy containing colors, graphics end everything else as long as MS Word and HTML can take it.
But adding this new great feature oracle broke something else. The Workflow Email Override Address does not apply to the Delivery Options and that means that one can send external emails form a cloned test instance. As you can imagine the consequences can be disastrous, i.e. we have a scheduled concurrent requests that email customers the AR invoices. Now if someone started testing AR invoices in the clone instance...
Some companies worked around the issue by blocking the email functionality on clone instances. That works but now one can't properly test the new workflow emails nor concurrent request emails. So there are 3 options:
- you can deploy new functionality related to workflow or concurrent request emailing but risk that critical documents such as invoices get emailed to customers from the cloned instance
- do not deploy any new functionality
- deploy in PROD but without complete test cycle
Surprisingly, Oracle DEV being the siloed organization they are, looked only at the Delivery Option side of the coin and recognized the need as an enhancement request #9308736. ERs are nice but they can take years to deploy, it is 2015 and and couple of ERs I created in 2002 and 2004 were just recently released.
If waiting 10 or 15 years is not an option for your organization here is how you can fix the issue:
- Create an email address where the string is equal to the SID of the test database and get access to the email box. It would have this format: TEST@yourcompanyname.com. Replace the values with proper SID and domain names
- Add this script to the cloning process. The script needs to be incorporated as integral part of the cloning process and executed after the SID has been already changed form prod to the test instance, but before concurrent manager is brought up:
-- this script must be executed
-- after the NAME column has been set in V$DATABASE to the correct clone instance
-- but before concurrent managers get up
--This section sets the workflow email override address to
--be equal to the SID of the test instance @yourcompanyname.com
update fnd_svc_comp_param_vals set parameter_value=(select name from v$database)||'@'||'yourcompanyname.com'
where parameter_id =(select parameter_id from fnd_svc_comp_params_tl where display_name = 'Test Address' );
commit;
-- This section updates all the concurrent requestsincluding those that are scheduled or were being
-- executed when the instance was cloned
-- It updates the email address of every request to eb that of the workflow email override
update fnd_conc_pp_actions set argument3=
( select fscpv.parameter_value -- go to workflow override email address
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),
argument4=( select fscpv.parameter_value -- go to workflow override email address
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),
argument5=null,
argument6=null,
argument8=null,
argument9=null,
argument10=null
where ACTION_TYPE =7 ;
commit;
-- after the NAME column has been set in V$DATABASE to the correct clone instance
-- but before concurrent managers get up
--This section sets the workflow email override address to
--be equal to the SID of the test instance @yourcompanyname.com
update fnd_svc_comp_param_vals set parameter_value=(select name from v$database)||'@'||'yourcompanyname.com'
where parameter_id =(select parameter_id from fnd_svc_comp_params_tl where display_name = 'Test Address' );
commit;
-- This section updates all the concurrent requestsincluding those that are scheduled or were being
-- executed when the instance was cloned
-- It updates the email address of every request to eb that of the workflow email override
update fnd_conc_pp_actions set argument3=
( select fscpv.parameter_value -- go to workflow override email address
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),
argument4=( select fscpv.parameter_value -- go to workflow override email address
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),
argument5=null,
argument6=null,
argument8=null,
argument9=null,
argument10=null
where ACTION_TYPE =7 ;
commit;
- Create trigger on the FND_CONC_PP_ACTIONS table to update any email specified by user during new request submission to the email override. This script needs to be executed at the same time as the previous one
CREATE OR REPLACE TRIGGER XX_EMAIL_OVERRIDE
BEFORE INSERT or UPDATE
ON fnd_conc_pp_actions
FOR EACH ROW
DECLARE
-- This trigger overrides send to email address on non production Oracle servers
v_override_email varchar2(100);
v_instance varchar2(100); -- name of the oracle instance
BEGIN
select name
into v_instance
from v$database;
If v_instance!='PROD' -- name of production instance, replace as appropriate
-
then
BEGIN
if :new.ACTION_TYPE =7 -- if the request output is going to be emailed in post processing
--if :new.ARGUMENT1 ='E' -- if the request output is going to be emailed in post processing
then
select decode(fscpv.parameter_value,'NONE',null,fscpv.parameter_value) -- go to workflow override email address
into v_override_email
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;
-- if v_override_email!='NONE' then -- if workflow override email is set
:new.ARGUMENT3:=v_override_email; --override sender address
:new.ARGUMENT4:=v_override_email; -- send the email to the work from over
:new.ARGUMENT5:=null;--delete any addtional cc email addresses
:new.ARGUMENT6:=null;
:new.ARGUMENT7:=null;
:new.ARGUMENT8:=null;
:new.ARGUMENT9:=null;
:new.ARGUMENT10:=null;
--end if; -- end for override email is !='NONE'
end if; -- end of if the output is to be emailed
EXCEPTION
WHEN OTHERS
then null;
END;
end if; -- end if the instance is not PROD
EXCEPTION
WHEN OTHERS
then null;
END;
