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.
This table will store the data that will be sent via the Event subscription
1
| 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| CREATE OR REPLACE FUNCTION APPS.xx_test_event ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t) RETURN VARCHAR2IS 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 <span class="skimlinks-unlinked">fnd_profile.VALUE</span> ('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 (<span class="skimlinks-unlinked">fnd_profile.VALUE</span> ('USER_ID'), <span class="skimlinks-unlinked">fnd_profile.VALUE</span> ('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
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,
1
| 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 isXX_EVENT_RESULT.
1
| 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| 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) := '<span class="skimlinks-unlinked">xx.oracle.apps.test</span>'; 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 belowor 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 */ <span class="skimlinks-unlinked">wf_event.RAISE</span> (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.
0 comments