Events in Oracle E-Business Suite

Oracle EBS provides us a functionality to raise business event and then subscribe to those events.
Internally it used oracle advance queue (AQ). In this exercise we will create an event both from the EBS workflow page and from pl/sql and then we will subscribe to that event using a pl/sql code.

Later we will see how we can call a web service and how oracle apps adapter can be used to read data from the events.

First of all create a table where in we will store the data retrieved from Event triggered.

CREATE TABLE xxar_employee( p_user_id INTEGER, p_user_name VARCHAR2(50))

Create a function to insert data in to this table after getting data from the event

CREATE OR REPLACE FUNCTION xxar_event (P_GUID in raw,p_event_data IN OUT NOCOPY wf_event_t) RETURN VARCHAR2
IS
l_username VARCHAR2 (50);
L_USERID integer;
L_EVENTNAME varchar2(100);
L_EVENKEY varchar2(100);
l_eventdata CLOB;
BEGIN
L_EVENTNAME := p_event_data.geteventname ();
L_EVENKEY := p_event_data.geteventkey ();
l_eventdata := p_event_data.geteventdata ();
L_USERID := p_event_data.GETVALUEFORPARAMETER ('XXAR_UserID');
l_username := p_event_data.getvalueforparameter ('XXAR_UserNAME');

IF l_userid IS NOT NULL
THEN
INSERT INTO xxar_employee(p_user_id, p_user_name) VALUES (l_userid, l_username);
else
INSERT INTO xxar_employee(p_user_id, p_user_name)VALUES (420, SYSDATE );
END IF;
COMMIT;
RETURN 'SUCCESS';
end xxar_event;
/




This function accepts a guid and Event data as input parameter and based on the event data it retrieve the event name , event key, event data ,user id and user name. As you can observe the parameter p_event_data is of type wf_event_t. Following link provide more details on the object type wf_event_t

http://docs.oracle.com/cd/B10501_01/workflow.920/a95265/evtapi05.htm


Finally when raising event if user id is passed then insert the values from the event to the table that we have created otherwise insert some dummy values into the table.

Now once we have create a subscription we will go ahead and create and event and see if this works fine.

Navigate in Oracle EBS as follows

System Administrator > Workflow > Administrator Workflow > Business Events > Create Event

Create an event xxar.oracle.apps.customEvent

Once you have created the event Search for the event that you have just created.
Now click on the subscription button as shown below and create a subscription for the event just created

Click on create subscription

Get the System detail from th LOV

Select source type as local

Event filter -xxar.oracle.apps.customEvent

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

Action Type as custom and say next

In the Action page

specify the name of pl/sql function that you have created in the begining to subscribe to the event.

Specify the owner name and owner tag.

Now go back to the event page and tye to raise an event from the workflow page as shown

Specify the EVENT key

And add two event parameters.These event parameters are the same that we have defined in our PL/SQL fuction.

Validate if the event is raised or not.

Once the event is validated check the back end table that we have created

so now we have validated that we are able to raise and event from the workflow and also able to subscribe it. So we will now try to create a pl/sql code and try to raise the event from pl/sql. We will use the embedded API of workflolw to raise the event.

you can get more details on the API from the following link

http://docs.oracle.com/cd/B14117_01/workflow.101/b10286/evtapi10.htm

With the same write the query to raise event

declare
X_EVENT_PARAMETER_LIST WF_PARAMETER_LIST_T;
L_EVENT_NAME varchar2 (100) := 'xxar.oracle.apps.customEvent';
L_EVENT_KEY varchar2 (100) := 'AR234';
begin
X_event_parameter_list := wf_parameter_list_t ();

WF_EVENT.ADDPARAMETERTOLIST('XXAR_UserID', 840, X_EVENT_PARAMETER_LIST);
WF_EVENT.AddParameterToList('XXAR_UserNAME', 'Chandan', X_event_parameter_list);

/* Raise the event */
wf_event.RAISE
(P_EVENT_NAME => L_EVENT_NAME,
P_EVENT_KEY => L_EVENT_KEY,
p_parameters => X_event_parameter_list
);
END;
/

Again here you can see we are programmatically adding the parameters.
Execute this code and again query the table and you will get following result.