4 Replies Latest reply: May 8, 2013 3:46 PM by manu.mur RSS

    Business Event not triggering the PLSQL procedure.. What to do?

    manu.mur
      We need to call a plsql procedure when the GL Approval workflow has ended with approval. I thought i could do this by customizing the relevant business event.

      We are on EBS 12.1.3 with RDBMS : 11.2.0.3.0.

      I saw that the business event oracle.apps.gl.Journals.journal.approve was disabled currently. I enabled it and created a subscription for it. Subscription was a PLSQL procedure. Currently, for test purpose only thing it is doing is to insert a row into a table.

      Business Event Subscription settings:
      System: ORDEBS.SYSTEM.COM

      Triggering Event
      Source Type:Local
      Event Filter: oracle.apps.gl.Journals.journal.approve


      Execution Condition
      Phase: 100
      Status: Enabled
      Rule Data: Message

      Action Type:Custom
      On Error: Stop and Rollback

      Action
      PL/SQL Rule Function: XX_GL_APPROVE_BE_PKG.Get_Attributes
      Priority: Normal

      Documentation (Not sure what value to be given for these. I went with the below values.)
      Owner: Company Name
      Owner Tag: Custom Schema

      Using the below query i can see that the business event is getting called when the approval happens (One row added each time approval happened). But I cant see any rows in the table where it should insert a row. What could be going wrong? How can i verify that the procedure has been called?

      select * from WF_DEFERRED where corrid ='APPS:oracle.apps.gl.Journals.journal.approve'

      Procedure:
      CREATE OR REPLACE PACKAGE BODY APPS.XX_GL_APPROVE_BE_PKG
      AS
      PROCEDURE Get_Attributes
      IS
      BEGIN
      INSERT INTO xx.xx_test_table VALUES ('From BE');
      COMMIT;
      END Get_Attributes;
      END XX_GL_APPROVE_BE_PKG;
        • 1. Re: Business Event not triggering the PLSQL procedure.. What to do?
          Jeet_A
          Hi,

          Can you try giving schema name

          Documentation (Not sure what value to be given for these. I went with the below values.)
          Owner: Company Name
          Owner Tag: Custom Schema


          as

          Documentation
          Owner: APPS
          Owner Tag: APPS

          It should be Application name as mentioned by Alejandro

          Thanks,
          Jit

          Edited by: appsjit on May 8, 2013 11:43 AM
          • 2. Re: Business Event not triggering the PLSQL procedure.. What to do?
            Alejandro Sosa-Oracle
            Hello,

            Ensure OWNER_TAG represents an actual licensed application, like 'FND' no quotes. The business event system will dispatch subscriptions for licensed products.

            Also, to detect any other possible errors please add an error type subscription. This way the BES will catch the error and will notify SYSADMIN of such failure, it won't go silent. You can use this link to see how: https://blogs.oracle.com/oracleworkflow/entry/defining_business_events_with_synchronous

            Regards,

            Alejandro
            • 3. Re: Business Event not triggering the PLSQL procedure.. What to do?
              Jeet_A
              Hi Alejandro,

              Thanks for correcting me. This describes in detail.

              Thanks,
              Jit
              • 4. Re: Business Event not triggering the PLSQL procedure.. What to do?
                manu.mur
                Thanks Alejandro. Now this is working.

                Changes i made:
                1. Added the WFERROR workflow as a subscription to this event. So i could see that the function i am calling from the event did not have proper signature.
                2. Changed the function signature to have standard parameters like:

                CREATE OR REPLACE PACKAGE BODY XX_GL_APPROVE_BE_PKG
                AS

                function subscription(p_subscription_guid in raw,
                p_event in out nocopy wf_event_t) return varchar2 is
                l_result varchar2(20);
                begin

                insert into xxvtv.xxvtv_test_table values ('From BE');
                commit;

                exception
                when others then
                wf_core.context('XX_GL_APPROVE_BE_PKG','function subscription', p_event.getEventName(), p_event.getEventKey());
                wf_event.setErrorInfo(p_event, 'ERROR');
                return 'ERROR';
                end subscription;

                END XX_GL_APPROVE_BE_PKG;

                3. Changed the owner name and owner tag both to the custom schema name (XX)