1 Reply Latest reply: Mar 16, 2012 8:40 AM by AlexAnd RSS

    Unable to run Concurrent Request from Trigger

    user3530955
      Hi All

      Lots of Forums out there on this but I can't resolve it.

      Trigger as below does not run concurrent program.

      CREATE OR REPLACE TRIGGER APPS.XXGL_PNL_CONTROL_snapshot
      AFTER INSERT OR UPDATE
      ON XXOBI.XXGL_PNL_CONTROL
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      WHEN (
      NEW.STATUS ='APPROVED'
      )
      declare
      l_errbuf varchar2(4000);
      l_retcode varchar2(4000);
      L_REQUEST_ID NUMBER;
      l_phase VARCHAR2 (240);
      l_status VARCHAR2 (240);
      l_dev_phase VARCHAR2 (240);
      l_dev_status VARCHAR2 (240);
      l_message VARCHAR2 (2000);
      l_call_status BOOLEAN;
      L_set_mode boolean;

      PRAGMA AUTONOMOUS_TRANSACTION;

      begin

      L_Set_mode := FND_Request.Set_Mode(TRUE);


      fnd_global.APPS_INITIALIZE (fnd_global.user_id,fnd_global.resp_id,fnd_global.resp_appl_id);

      l_request_id := fnd_request.submit_request(application => 'RMB GL',
      program => 'XXGLBSSNAP',
      argument1 => :NEW.CALENDAR_DATE,
      argument2 => :NEW.PNL_ROLLUP
      );
      commit;

      end;
      /

      If I remove the commit I get the following error

      active autonomous transaction detected and rolled back

      Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.

      Action: Ensure that before returning from an autonomous PL/SQL block, any active autonomous transactions are explicitly committed or rolled back. ----------------------------------------------------------------------- 06520 through 06529 reserved for Foreign function errors

      Any help would be hugely appreciated
      Nicki
        • 1. Re: Unable to run Concurrent Request from Trigger
          AlexAnd
          after fnd_request.submit_request you must use - commit


          try change you code
          CREATE OR REPLACE TRIGGER APPS.XXGL_PNL_CONTROL_snapshot
          AFTER INSERT OR UPDATE
          ...
          
          begin
             cp_run(
                      :NEW.CALENDAR_DATE...
                      )
          end;
          
          
          CREATE OR REPLACE PROCEDURE  cp_run (
                 ... p_date indate ...
                 )
              IS
                 PRAGMA AUTONOMOUS_TRANSACTION;
          ...
          
          l_request_id := fnd_request.submit_request(application => 'RMB GL',
          program => 'XXGLBSSNAP',
          argument1 => p_date
          ...
          );
          commit;
          ...
          for example http://www.java2s.com/Tutorial/Oracle/0560__Trigger/MarktriggerwithPRAGMAAUTONOMOUSTRANSACTION.htm