You're reading...
Business Event

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 APPS.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;
   
   l_event_name  VARCHAR2(100);
   l_event_key   VARCHAR2(100);
   l_event_data  CLOB;
BEGIN
    --read the parameters values passed from the event
    l_event_name := p_event.geteventname ();
    l_event_key := p_event.geteventkey ();
    l_event_data := p_event.geteventdata ();

   l_user_id := p_event.getvalueforparameter ('XX_TEST_USER_ID');
   l_user_name := p_event.getvalueforparameter ('XX_TEST_USER_NAME');

   IF l_user_id IS NOT NULL
   THEN
    /* Insert values passed by the event */
      INSERT INTO xx_event_result
                  (x_user_id, x_user_name
                  )
           VALUES (l_user_id, l_user_name
                  );
   /* If the event is raised from Oracle Apps without 
   the business  event parameters, XX_TEST_USER_ID
   and XX_TEST_USER_NAME */
   ELSIF fnd_profile.VALUE ('USER_ID') IS NOT NULL
   THEN
      /* Insert profile values if no value has been passed by the event */
      INSERT INTO xx_event_result
                  (x_user_id,
                   x_user_name
                  )
           VALUES (fnd_profile.VALUE ('USER_ID'),
                   fnd_profile.VALUE ('USERNAME')
                  );
   ELSE
      /* If we cannot get the profile value then enter a dummy user_id and the time */
      INSERT INTO xx_event_result
                  (x_user_id, x_user_name
                  )
           VALUES (0, SYSDATE
                  );
   END IF;

   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.

Action Type Description
Custom Execute custom business logic
Launch Workflow Launch the workflow specified in the subscription
Receive Trading Partner Message Receive a message from your trading partner
Send Notification Send a notification using standard or your own message templates
Send Trading Partner Message Generate a message and send the message to your trading partner
Send To Agent Send the event to the agent specified in the subscription

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 ();

/* We can add each parameter at a time and add a value as shown below
or we can add the values and the parameters in a single statement shown
   below
   */

   /* 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;
   */

/* Add parameters and their values directly*/
    WF_EVENT.AddParameterToList('XX_TEST_USER_ID', 101, x_event_parameter_list);
    WF_EVENT.AddParameterToList('XX_TEST_USER_NAME', 'TEST_USR', x_event_parameter_list);

   /* 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!

About these ads

About Abhijit Ray

I love sleeping, watching Hollywood blockbusters, my Wii, road trips and watching my 4 year old son grow up. In between I try to squeeze in some time to go to work.

Discussion

9 thoughts on “Workflow Business Event demo

  1. I was looking for this information long time back( 2005) when i was actively working on workflow. Its a good piece of information. Appreciate your work.

    Posted by PVR | November 2, 2012, 6:37 am
  2. Need workflow basic learning documents , pls share document to
    kishankulakarni1@gmail.com

    Posted by KISHAN | November 28, 2012, 1:36 am
  3. Hey, In my Responsibility: Workflow Administrator

    Navigation: Business Events > Events

    I am not getting create Event Button.

    Do you have any idea…what the Function name for this?

    Thanks Yogesh

    Posted by Yogesh | February 15, 2013, 2:39 pm
  4. I would like to trigger the event whenever there is any change in the status of a quote. how do I do that thru business event? Please let me know

    Posted by Reddy | November 18, 2013, 3:10 pm

Trackbacks/Pingbacks

  1. Pingback: Raising business events via concurrent requests/programs | Oracle Maniacs' Notes - March 6, 2014

  2. Pingback: Send emails using Periodic Alerts | Oracle Maniacs' Notes - March 10, 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Traffic

Site Stats

  • 511,515 views since Feb 2012

Archives

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 175 other followers

Flags

Free counters!
Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: