FND_GLOBAL affected by New Global Performance Changes

After applying ATG Rollup 5 patch (and above) we discovered an issue with some of our custom developments. For some processes we got the following errors: ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 240 ORA-06512: at "APPS.FND_GLOBAL", line 1410 ORA-06512: at "APPS.FND_GLOBAL", line 1655 ORA-06512: at "APPS.FND_GLOBAL", line 2170 ORA-06512: at "APPS.FND_GLOBAL", line 2312 ORA-06512: at "APPS.FND_GLOBAL", line 2250 and this: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_paramenters('NLS_LANGUAGE','AMERICAN'). After some debug work we found that this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction. According to Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" this issue cause by a new global performance changes. Oracle Development said: "Very sorry if the new global performance changes have exposed you to this error, but there is no way we can back out these changes. They are not only complex and wide spread but required to maintain functional performance levels. Using fnd_global to change user/resp context from a trigger is not only not supported it is ill advised." OK, So we had to find a workaround to this issues and we found two... I'll start with a sample of the new behavior of fnd_global to demonstrate the issue and the solutions/workarounds will come right after. SQL> create table test1 (a number, b number); Table created SQL> insert into test1 (a) values (1001); 1 row inserted SQL> insert into test1 (a) values (1002); 1 row inserted SQL> commit; Commit complete SQL> create or replace trigger test1_trg_bi   2  after update on test1   3  for each row   4  begin   5       fnd_global.APPS_INITIALIZE(:new.a,1,1);   6       -- fnd_request.submit_request...   7       -- ....   8       -- ....   9  end; 10  / Trigger created SQL> select fnd_global.user_id from dual;     USER_ID ----------         -1 SQL> update test1 set b=1101 where a=1001; 1 row updated SQL> select fnd_global.user_id from dual;     USER_ID ----------       1001 SQL> update test1 set b=1102 where a=1002; update test1 set b=1102 where a=1002 ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 240 ORA-06512: at "APPS.FND_GLOBAL", line 1410 ORA-06512: at "APPS.FND_GLOBAL", line 1655 ORA-06512: at "APPS.FND_GLOBAL", line 2170 ORA-06512: at "APPS.FND_GLOBAL", line 2312 ORA-06512: at "APPS.FND_GLOBAL", line 2250 ORA-06512: at "APPS.TEST1_TRG_BI", line 2 ORA-04088: error during execution of trigger 'APPS.TEST1_TRG_BI' As you can see, the second update failed because apps_initialize was executed for the second time in the same transaction. Now I'll show two ways to workaround this issue: 1) As suggested in Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" a wrapper Concurrent Request which contain a call to the context set (apps_initialize) and afterwards submits the original request, is one possible solution. instead: create or replace trigger test1_trg_bi after update on test1 for each row declare      . . . begin      fnd_global.APPS_INITIALIZE(:new.a,1,1);      ret_code := fnd_request.submit_request ('OWNER', 'ORIGINAL_CONC', . . .);      . . .      . . . end; create the following trigger: create or replace trigger test1_trg_bi after update on test1 for each row declare      . . . begin      ret_code := fnd_request.submit_request ('OWNER', 'WRAPPER_CONC', . . . , :new.a, . . . );      . . .      . . . end; additionally - create a new plsql concurrent (WRAPPER_CONC) that contains the fnd_global.apps_initialize and submits the ORIGINAL_CONC concurrent request. This way, the apps_initialize statement executed in a separate transaction with no error. This is the preferred and recommended solution by Oracle. 2) The second solution is easier to implement, works fine but according to Note: 556391.1 is not supported since it contains calls to fnd_global within a database trigger. Anyway... The idea is to call the apps_initialize in an Autonomous Transaction procedure. Follow this sample: SQL> create or replace procedure test1_apps_init (p_user_id number) is   2  pragma autonomous_transaction;   3  begin   4       fnd_global.APPS_INITIALIZE(p_user_id,1,1);   5       commit;   6  end;   7  / Procedure created SQL> create or replace trigger test1_trg_bi   2  after update on test1   3  for each row   4  begin   5       test1_apps_init (:new.a);   6       -- fnd_request.submit_request...   7       -- ....   8       -- .....   9  end; 10  / Trigger created SQL> select fnd_global.user_id from dual;     USER_ID ----------         -1 SQL> update test1 set b=1101 where a=1001; 1 row updated SQL> select fnd_global.user_id from dual;     USER_ID ----------       1001 SQL> update test1 set b=1102 where a=1002; 1 row updated SQL> select fnd_global.user_id from dual;     USER_ID ----------       1002 As you can see, the update statements were executed successfully this time and the session was updated with the appropriate user context in each update statement. Those two solutions are working fine, but keep in mind that the second is not supported. You are welcome to leave a comment. Aviad