Email override for concurrent request Delivery Options

R12 offers a great feature sending emails directly from Concurrent Requests using Delivery Options.
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;

• 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;