msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

How To Create and Use Custom Business Events in Oracle E-Business Suite

Oracle Workflow, a component of Oracle E-Business Suite, lets you set up subscriptions to business events which can launch workflows or enable messages to be propagated from one system to another when business events occur.

The Oracle Workflow Business Event System is an application service that leverages the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems.

Predefined events are provided with some Oracle E-Business Suite products. For example, the “Create Person Event” (oracle.apps.ar.hz.Person.create) is the name of a seeded business event in the Receivables application. The event is triggered when a person party record is created within the Trading Community Architecture (TCA).

Not all events are seeded. However, Oracle gives you the option to create a custom event. An example would be an event that is triggered when an invoice is matched to a purchase order.

In this article, I will show you how to create and use a custom business event. I will go through the following steps:

  1. Create a custom business event
  2. Create a subscription to the event
  3. Raise the event from PL/SQL
  4. Consume the event in PL/SQL

Note: I tested these steps in Oracle EBS 12.1.2 and DB 11.2.0.1.

1.Create a custom business event called xxu.oracle.apps.ap.inv.match: Login to Oracle Applications as SYSADMIN (or a user with the Workflow admin role). Click on the Workflow Administrator Web Applications responsibility. Click on Business Events. Click on Create Event. Supply the event name, display name, owner name and tag.

2.Create a subscription to this event:


Supply the PL/SQL Rule Function. This is the function that will be executed When the event is raised.

Here is the source code for the PL/SQL Rule Function xxu_bes.inv_po_matching. For testing purposes I insert the event data into a temp table:

    CREATE TABLE xx_temp (msg VARCHAR2 (4000));

    CREATE OR REPLACE PACKAGE xxu_bes
    AS
       FUNCTION inv_po_matching (
          p_subscription_guid   IN     RAW,
          p_event               IN OUT wf_event_t
       )
          RETURN VARCHAR2;
    END xxu_bes;

    CREATE OR REPLACE PACKAGE BODY xxu_bes
    AS
       FUNCTION inv_po_matching (
          p_subscription_guid   IN     RAW,
          p_event               IN OUT wf_event_t
       )
          RETURN VARCHAR2
       IS
          l_plist   wf_parameter_list_t := p_event.getparameterlist ();
       BEGIN
          IF p_event.geteventname () = 'xxu.oracle.apps.ap.inv.match'
          THEN
             INSERT INTO xx_temp (msg)
                  VALUES (
                               'Timestamp: '
                            || fnd_date.date_to_canonical (SYSDATE)
                            || ' | Event: '
                            || p_event.geteventname ()
                            || ' | Event Key: '
                            || p_event.geteventkey ()
                            || ' | SEND_DATE: '
                            || wf_event.getvalueforparameter (
                                  'SEND_DATE',
                                  l_plist
                               )
                            || ' | PO_NUMBER: '
                            || wf_event.getvalueforparameter (
                                  'PO_NUMBER',
                                  l_plist
                               )
                            || ' | event_data: '
                            || p_event.geteventdata ()
                         );
          END IF;

          RETURN 'SUCCESS';
       EXCEPTION
          WHEN OTHERS
          THEN
             wf_core.context (
                'xxu_bes',
                'inv_po_matching',
                p_event.geteventname (),
                p_subscription_guid
             );
             wf_event.seterrorinfo (p_event, 'ERROR');
             RETURN 'ERROR';
       END inv_po_matching;
    END xxu_bes;

3.Feed sample data into the event and then raise it:

    DECLARE
       l_parameter_list   wf_parameter_list_t;
       l_event_data       CLOB;
    BEGIN
       l_parameter_list :=
         wf_parameter_list_t (
         wf_parameter_t ('SEND_DATE', fnd_date.date_to_canonical (SYSDATE)),
         wf_parameter_t ('PO_NUMBER', '10100'),
          );
       l_event_data := '
            <matched>
                <send_date>2011/05/28 02:34:14</send_date>
                <po_number>1234</po_number>
            </matched>
            ';
       wf_event.raise (
          p_event_name   => 'xxu.oracle.apps.ap.inv.match',
          p_event_key    => SYS_GUID (),
          p_event_data   => l_event_data,
          p_parameters   => l_parameter_list
       );
       COMMIT;
    END;
    /

After the event is raised, query xx_temp. You will see the event data in the table.

Sources and resources:


Filed in Oracle on 21 Jul 11 | Tags: ,


Comments are closed.