Initializing oracle apps from database triggers/Setting up fnd_apps_initialize in DB trigger's

Problem statement: Cannot set the fnd_apps_initialize in the DB triggers which calls the package to submit the concurrent requests.

Requirement: To trigger the batch concurrent programs from the DB trigger based on the batch_type and responsibility derived from the lookups defined based on the Purchasing unit value.

Error encountered:  ORA-02074: Cannot SET NLS
Related errors:  ORA-02074: Cannot SET NLS in a Distributed Transaction

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_parameters('NLS_LANGUAGE','AMERICAN')

has been detected in fnd_global.set_nls

Useful Info:
please follow below and see its helpful for your issue:

ORA-04092: Cannot Set NLS In A Trigger: Error Occurs When Entering SIT Or Absence Information In Core HR and SSHR [ID 758435.1]
Why are Concurrent Programs Calling FND_GLOBAL.APPS_INITIALIZE Using DBLinks Failing? [ID 466800.1]
ORA-04092: Cannot SET NLS in a Trigger Error When Creating A Formula [ID 456358.1] 
ORA-02074: Cannot SET NLS in a Distributed Transaction [ID 556391.1]

Solution Built:

Write a procedure call the fnd_apps_initilize and make the procedure AUTONOMUS_TRANSACTION, call the procedure from the trigger, (Ideally After Update) and then a statement to call the concurrent requests.

Example:

XX_CUSTOM_BATCH_HEADERS a Table which stores the batch detail information

XX_CUSTOM_BATCH_DETAILS à Table to store the dervied values such as batch_type, purchasing_unit and responsibility thru which the concurrent program should be submitted.

Trigger on xx_custom_batch_headers à XX_CUSTOM_BATCH_HEADERS_BIR

*************************************************************************

CREATE OR REPLACE TRIGGER XX_CUSTOM_BATCH_HEADERS_BIR

EFORE INSERT ON XX_CUSTOM.XX_CUSTOM_BATCH_HEADERS

FOR EACH ROW

WHEN (

NEW.STATUS='PENDING'

      )

DECLARE

l_status    VARCHAR2(25);

BEGIN

   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE= ''AMERICAN''';

--Procedure which filters data based on the batch_id, batch_type and inserts records into xx_custom_batch_details table this procedure can be different based on the requirement

   xx_custom_common_pkg.insert_records_batch_det(:NEW.batch_id

                                               ,:NEW.batch_type

                                               , l_status);

   :NEW.ERR_MESSAGE := l_status;

IF l_status = 'SUCCESS'

THEN

   :NEW.REQUEST_ID := 'Refer to xx_custom_batch_details';

   :NEW.STATUS := 'COMPLETED';

ELSE

   :NEW.REQUEST_ID := '';

   :NEW.STATUS := 'ERROR';

END IF;

END XX_CUSTOM_BATCH_HEADERS_BIR;

/

*************************************************************************

Trigger on xx_custom_batch_details XX_CUSTOM_BATCH_DETAILS_BIR

*************************************************************************

CREATE OR REPLACE TRIGGER XX_CUSTOM_BATCH_DETAILS_BIR

AFTER INSERT ON XX_CUSTOM.XX_CUSTOM_BATCH_DETAILS

FOR EACH ROW

DECLARE

l_request_id                 fnd_concurrent_requests.request_id%TYPE;

lt_resp_key                  fnd_responsibility.responsibility_key%TYPE;

lt_user_name                 fnd_user.user_name%TYPE;

lt_application_short_name    fnd_application.application_short_name%TYPE;

lt_concurrent_program_name   fnd_concurrent_programs.concurrent_program_name%TYPE;

lt_err_message               xx_custom_batch_headers.err_message%TYPE := '';

result                       BOOLEAN;

lv_errbuf                    varchar2(250);

ln_retcode                   number;

BEGIN

   

    dbms_output.put_line('Responsibility'||:NEW.RESPONSIBILITY_KEY);

   

XX_CUSTOM_SUBMIT_REQUEST_PKG.SUBMIT_REQ_program(errbuf  => lv_errbuf

                                               ,retcode => ln_retcode 

     ,p_batch_id => :new.batch_id

     ,p_batch_type => :new.batch_type

                                               ,p_resp_key=> 

:new.responsibility_key                                       ,p_pur_unit=> :new.purchasing_unit);

             

             dbms_output.put_line('Request ID'||l_request_id);

       

END XX_CUSTOM_BATCH_DETAILS_BIR;

/

*************************************************************************

PL/SQL Package which sets the FND_APPS_INTITIALISE and make the package PRAGMA AUTONOMOUS_TRANSACTION to run in separate session.

*************************************************************************

CREATE OR REPLACE PACKAGE BODY APPS.XX_CUSTOM_SUBMIT_REQUEST_PKG

AS

 PROCEDURE submit_req_program(errbuf       OUT NOCOPY VARCHAR2

                             ,retcode      OUT NOCOPY NUMBER

                             ,p_batch_id   IN         NUMBER

                             ,p_batch_type IN         VARCHAR2

                             ,p_resp_key   IN         VARCHAR2

                             ,p_pur_unit   IN         VARCHAR2)

IS

l_request_id                 fnd_concurrent_requests.request_id%TYPE;

lt_resp_key                  fnd_responsibility.responsibility_key%TYPE;

lt_user_name                 fnd_user.user_name%TYPE;

lt_application_short_name    fnd_application.application_short_name%TYPE;

lt_concurrent_program_name   fnd_concurrent_programs.concurrent_program_name%TYPE;

lt_err_message               xx_custom_batch_details.err_message%TYPE := '';

lv_batch_type                VARCHAR2(50) :=  P_BATCH_TYPE;

lv_errbuf                    VARCHAR2(250);

ln_retcode                   NUMBER;

l_resp_id                    NUMBER;

l_appl_id                    NUMBER;

l_user_id                    NUMBER;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

dbms_output.put_line('Inside 1st concurrent Program');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside the concurrent Program');

   BEGIN

        SELECT meaning

        INTO   lt_user_name

        FROM   fnd_lookup_values

        WHERE  lookup_type = 'XX_CUSTOM_INTERFACE'

        AND    lookup_code = 'USER_NAME'

        AND    language = xx_Custom_common_pkg.get_lang;

     EXCEPTION

     WHEN OTHERS THEN

         lt_err_message := lt_err_message || 'Error retrieving user name';

         lt_user_name := 'XXX';

         FND_FILE.PUT_LINE(FND_FILE.LOG,lt_err_message);

     END;

     BEGIN

       SELECT

           flv.meaning

         , flv.tag

       INTO

           lt_concurrent_program_name

          ,lt_application_short_name

       FROM

           fnd_lookup_values flv

       WHERE

           flv.lookup_type = 'XX_CUSTOM_CONC_PROG_TRIGGER'

           AND flv.enabled_flag='Y'

           AND flv.start_date_active <=SYSDATE

           AND NVL(flv.end_date_active,SYSDATE) >=SYSDATE

           AND language = xx_Custom_common_pkg.get_lang

           AND UPPER(flv.lookup_code) = UPPER(p_batch_type);

     EXCEPTION

        WHEN OTHERS THEN

           --lt_err_message := lt_err_message || ' Batch Type not defined in lookup';

           --retcode  := -1;

           FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch Type not defined in lookup');

     END;

      IF 
-- Procedure which sets FND_APPS_INITIALIZE
xx_custom_common_pkg.setup_apps_user(p_resp_key,lt_user_name) THEN

      FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Calling the program');

      dbms_output.put_line('Before Calling the program');

            BEGIN

l_request_id := 

fnd_request.submit_request(application => lt_application_short_name,

                 program     => lt_concurrent_program_name,

                 description => NULL,

                 start_time  => to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),

                 sub_request => FALSE,

                 argument1   => p_batch_id,

                 argument2   => p_pur_unit);

             EXCEPTION

               WHEN OTHERS THEN

                  lt_err_message := lt_err_message || fnd_message.get;

                  lt_user_name := 'XXX';

                 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while submitting the request: '||lt_err_message);

                 dbms_output.put_line('Error while submitting the request: '||lt_err_message);

                 retcode  := -1;

            END;

            IF  l_request_id = 0 THEN

                 dbms_output.put_line('Failed to submit the request with Req_ID:'||l_request_id );

            ELSE

                  dbms_output.put_line('Updating the batch details table');

                  dbms_output.put_line('Resp:'||p_resp_key);

                  dbms_output.put_line('batch_id:'||p_batch_id);

                  dbms_output.put_line('batch_type:'||p_batch_type);

            --Updating the batch_details table with requests

                          -- UPDATE xx_custom_batch_details

                             -- SET request_id          = l_request_id

                           -- WHERE batch_id            = p_batch_id

                           --   AND batch_type          = p_batch_type

                            --  AND responsibility_key  = p_resp_key;

                 dbms_output.put_line('Submitted the request with Req_ID:'||l_request_id );

            END IF;

      ELSE

          lt_err_message := lt_err_message || ' Error initialising responsibility/user';

          FND_FILE.PUT_LINE(FND_FILE.LOG,lt_err_message);

          dbms_output.put_line(' Error initialising responsibility/user');

          retcode  := -1;

      END IF;

retcode  := 0;

dbms_output.put_line('Before commit');

COMMIT;

dbms_output.put_line('After commit');

EXCEPTION

WHEN OTHERS THEN

   retcode := 2;

   dbms_output.put_line('Others Exception in Main Program:');

   FND_FILE.PUT_LINE(FND_FILE.LOG,'Others Exception in Main Program:'||substr(SQLERRM,1,200));

END;

END;

/

*************************************************************************

Thank you so much to Sandeep Jagirdar for sharing this post with his experience on the real time issue.

Cheers,

Rajesh