Workflow Business Event demo

This is a simple demo for creating and using a Business Event. We shall

1. Create a business event
2. Create a subscription to this business event
3. Create a PL/SQL code to raise the business event
4. Create a table to store the data passed from the PL/SQL code via the business event.

   
The steps are given below.

Step 1: Create table

This table will store the data that will be sent via the Event subscription

CREATE TABLE xx_event_result  ( x_user_id INTEGER, x_user_name VARCHAR2(100) )

  
  
Step 2: Create the DB object to insert the data into the table

A function is written to insert data passed from the Business Event into the table. This function will be invoked by the Business Event.

CREATE OR REPLACE FUNCTION xx_test_event (p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
  RETURN VARCHAR2
IS
   l_user_name   VARCHAR2 (100);
   l_user_id     INTEGER;
BEGIN
  --read the parameters values passed from the event
   l_user_id := p_event.getvalueforparameter ('XX_TEST_USER_ID');
   l_user_name := p_event.getvalueforparameter ('XX_TEST_USER_NAME');

   -- Insert data into the table
   INSERT INTO xx_event_result
               (x_user_id, x_user_name
               )
        VALUES (l_user_id, l_user_name
               );

   COMMIT;
   RETURN 'SUCCESS';
END xx_test_event;
/

   
   
Step 3: Create the Business Event

Login to Oracle Apps

Responsibility: Workflow Administrator

Navigation: Business Events > Events > Create Event

Enter the details as shown below

Name: xx.oracle.apps.test

Display Name: xx.oracle.apps.test

Status: Enabled

Owner Name: SQLAP

Owner Tag: SQLAP

Note: Owner Name and Owner Tag field values should have values which correspond to the application short name. You can check for valid application short names from the following query,

select application_short_name from fnd_application

Click on Apply button and a confirmation message will be shown

   
Step 4: Create a subscription

Go to the Event form and search for the event you created in the previous step, i.e. xx.oracle.apps.test.

Click on Subscription icon. Note that the icon is white as the event is not subscribed as of now.

The subscription window opens. Click on Create Subscription button.

Enter the values as shown below,

System: <This will default from the LOV>

Source Type: Local

Event Filter: xx.oracle.apps.test

Phase: 99 (any value between 1 and 100 means the event will be triggered off immediately)

Action Type: Custom

Note: Action Type can have the following values. We have selected Custom as we want to kick off a PL/SQL function.

Click on Next button. Enter the function name created in Step 2 in the PL/SQL Rule Function field.

Scroll down on the same form

Enter the Owner Name and Owner Tag. Click on Apply button.

The confirmation message is shown.

Now the Event is created and is ready to be tested.

Test the Business Event

Raise the Business Event from Oracle Apps Events Test form

Query for the event as you did at the beginning of Step 4.

Note the Subscription icon. Previously it was a white icon indicating that there were no subscriptions on it.

Now click on Test icon. The Test Business Event page opens.

Enter the details,

Event Key: A3242 (any random value)

Now you need to enter 2 parameters. In the PL/SQL function, in Step 2, the code expects values for 2 parameters,

• XX_TEST_USER_ID
  
• XX_TEST_USER_NAME

You need to add these 2 parameters on this screen. On the Event Parameters section on the screen add the parameters and their values.

The screen will look like below

Now press Raise in PL/SQL button. A confirmation message is shown.

This means that the event has been raised.

Now query for the table (we created this table in Step 1) to which the data will be inserted when this event is raise. The table name is XX_EVENT_RESULT.

select * from xx_event_result

You can see that the data we had passed to the event has been inserted into the table via the PL/SQL function.

Raise the Business Event using PL/SQL

We have tested the event from the standard Apps screen. We can also write a PL/SQL code to raise the event. The following code will raise the event.

DECLARE
  x_event_parameter_list wf_parameter_list_t;
  x_user_id              INTEGER := 999;
  x_user_name VARCHAR2(100) := 'SA1';
  x_param                wf_parameter_t;
  x_event_name           VARCHAR2(100) := 'xx.oracle.apps.test';
  x_event_key            VARCHAR2(100) := 'XX_0001';
  x_parameter_index      NUMBER := 0;
BEGIN
  x_event_parameter_list := wf_parameter_list_t();
  --Lets add the first value to the Event Parameter i.e. user_id
  x_param := wf_parameter_t(NULL
                           ,NULL);

  x_event_parameter_list.EXTEND;

  -- Set the value for the first Event Parameter i.e. User ID
  x_param.setname('XX_TEST_USER_ID');
  x_param.setvalue(x_user_id);

  x_parameter_index := x_parameter_index + 1;
  x_event_parameter_list(x_parameter_index) := x_param;

  -- Set the value for the second Event Parameter i.e. User Name
  x_param := wf_parameter_t(NULL
                           ,NULL);
  x_event_parameter_list.EXTEND;
  x_param.setname('XX_TEST_USER_NAME');
  x_param.setvalue(x_user_name);

  x_parameter_index := x_parameter_index + 1;
  x_event_parameter_list(x_parameter_index) := x_param;

  -- Raise the event
  wf_event.RAISE(p_event_name => x_event_name
                ,p_event_key  => x_event_key
                ,p_parameters => x_event_parameter_list
             /*,p_event_data   =>  p_data*/
                );
END;
/

Execute the code in the database and query the table XX_EVENT_RESULT.

You can see a new record in the table, user_id 999. This record has been inserted by the event when it was raised by the previous PL/SQL code.

We have used a single subscription on this business event. We can have multiple subscriptions on this business event. Then raising the business event once will lead to multiple actions taking place based on the number and types of subscriptions.

Do consider business events in Oracle Apps customization designs. These are very useful, can be raised by a number of ways, and many functionalities can be built in to it.

Cheers!

Related articles

• Oracle Advanced Queue (AQ) demo (oraclemaniac.com)
• Register a custom table in Oracle Apps (oraclemaniac.com)
• Inbound transaction using XML Gateway (oraclemaniac.com)
• Create a custom top in Oracle Apps (oraclemaniac.com)
• Send emails to non Oracle Apps users through workflow (oraclemaniac.com)
• Encrypt/Scramble sensitive data in Oracle Apps (oraclemaniac.com)
• How to add Descriptive Flexfields (DFF) in an Oracle Apps Framework (OAF) page (oraclemaniac.com)
• How to develop Master – Detail workflow process (oraclemaniac.com)
• How to start a workflow using PL/SQL (oraclemaniac.com)
• INSTEAD OF trigger example (oraclemaniac.com)
• Send workflow notifications from PL/SQL without executing the workflow (oraclemaniac.com)