This is a simple demo for creating and using a Business Event. We shall
- Create a business event
- Create a subscription to this business event
- Create a PL/SQL code to raise the business event
- 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
Display Name: xx.oracle.apps.test
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.
|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,
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.
- 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)