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.

How to create Data Template

XML Publisher is a template based publishing solution by Oracle which allows us to create visually rich documents with numerous formatting options.

The data templates are useful when you need to create a XML Publisher report without using the RDF, SQL or PL/SQL concurrent program. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.

Data Template:

The data template is an XML document that consists of four basic sections:

•   Parameters (Optional)
•  Triggers (Optional)
•   Data Query
•  Data Structure

Below is the simple data template

 

Data Template Declaration:

This is the root element of data template and it is required. It’s has 5 Attributes:

Attribute Name	Required	Description
Name	Yes	Data Template Name
description	No	Data Template Description
Version	Yes	Version of Data Template
dataSourceRef	Yes/No	Required when Data Template have lexical reference or other PL/SQL call like triggers
defaultPackage	No	The default data source reference for entire template

 Example:

<dataTemplate name=”testTemplate” version =”1.0” >

--- Data query, Data structure etc

</ dataTemplate>

1.     Parameters Section:

A parameter is a variable whose value you can set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. However, the Parameters section of the data template is optional and it’s has the following attributes:

Attribute Name	Required	Description
Name	Yes	Parameter Name
dataType	No	Data Type of the Parameter (character, number, data)
defaultValue	No	Default Value for Parameter
Include_in_output	No	Include Parameter in XML output

Example:

<parameters>

<parameter name="P_DATE_FROM" dataType="character" />

<parameter name="P_DATE_TO" dataType="character" />

</parameters>

Passing Parameter Example:

To pass parameters, (for example, to restrict the query), use bind variables in your query.

SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_DATE_FROM AND :P_DATE_TO;

2.     Data Query Section:

The section of the data template is required and it’s has the following attributes:

Attribute Name	Required	Description
Name	Yes	Query Name (Must be unique)

Example

<dataQuery>

<sqlStatement name="Q1">

<![CDATA[ SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_DATE_FROM AND :P_DATE_TO]]>

</sqlStatement>

</dataQuery>

1.   

2.   

1.   

2.   

2.1.  Lexical References:

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.

Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.

Syntax:

&ParameterName

Example:

<dataQuery>

<sqlstatement name="Q1">

<![CDATA[SELECT ENAME, SAL FROM EMP &where_clause]]>

</sqlstatement>

</dataQuery>

2.2.  Data Link:

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries. This element has the following attributes:

Attribute Name	Required	Description
Name	Yes	Link Name (Must be unique)
parentQuery	Yes	Parent Query Name (Must be defined in template)
parentColumn	Yes	Parent Column Name.
parentQuery	Yes	Child Query Name (Must be defined in template)
parentColumn	Yes	Child Column Name.

Example:

<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPARTMENTNO"/>

3.     Data Triggers:

Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.

Data triggers are optional, and you can have as many elements as necessary. It’s has the following attributes:

Attribute Name	Required	Description
Name	Yes	Trigger Name (Must be unique)
source	Yes	The PL/SQL where the executable code resides

Example:

<dataTrigger name="beforeReport1" source=" xx_test_pkg.beforeReport()"/>

<dataTrigger name="beforeReport2" source=" xx_test_pkg.beforeReport(:Parameter)"/>

4.     Data Structure Section:

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups.

Data Structure consists of 2 elements group and element. Group has the following attributes:

Attribute Name	Required	Description
Name	Yes	Group Name (Must be unique)
source	Yes	Query Source Name

Element has the following attributes:

Attribute Name	Required	Description
Name	Yes	Element Name (Must be unique)
value	Yes	Query Column Name or element name for aggregation
function	No	The aggregation function to be performed.

Example:

<dataStructure>

<group name="G1" source="Q1">

  <element name="LEDGER_ID" value="LEDGER_ID" />

  <element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME" />

  <element name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION" />

  <element name="LEDGER_NAME" value="LEDGER_NAME" />

  <element name="SUM_BAL_DR" value="G2.ACCT_BAL_DR" function="SUM()" />

  <element name="SUM_BAL_CR" value="G2.ACCT_BAL_CR" function="SUM()" />

  <group name="G2" source="Q1">

    <element name="PERIOD_YEAR" value="PERIOD_YEAR" />

    <element name="PERIOD_NUMBER" value="PERIOD_NUMBER" />

    <element name="PERIOD_NAME" value="PERIOD_NAME" />    

    <element name="CODE_COMB" value="ACCOUNTING_CODE_COMBINATION" />

    <element name="CODE_DESC" value="CODE_COMBINATION_DESCRIPTION" />

    <element name="ACCT_BAL_DR" value="BEGIN_BALANCE_DR" />

    <element name="ACCT_BAL_CR" value="BEGIN_BALANCE_CR" />    

   </group>

  </group>

</dataStructure>

Note Related To Better Performance:

·         BI Publisher tests have shown that using bind variables is more efficient than using the link tag.

·         Use a WHERE clause instead of a group filter to exclude records.

·         Perform calculations directly in your query rather than in the template.