Subscribe Business Event Using Oracle Apps adapter

In this exercise we will create a simple BPEL process that will subscribe to the event raised in Oracle EBS. The event will be a custom event and we will be sending a xml payload along with the event.There are few very important points that you need to take care of when creating an apps adpater.We will see all of them. First of all it is very important to fix the structure of XML document that you wanted to subscribe from the Apps event. So lets create a simple XML schema (Employee.xsd)which our process will use to subscribe xml from apps event.

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org" targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Employee">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpName" type="xsd:string"/>
<xsd:element name="EmpId" type="xsd:integer"/>
<xsd:element name="EmpSalary" type="xsd:integer"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


Now once you have the schema document.

Create a SOA empty composite and paste the schema is the XSD folder of the composite.

Now we will create a event in Oracle apps so that our BPEL process can initiate from that point.

Log in to Oracle Administration Workflow page

And create a new business Event



Now in your composite drag and drop an apps adapter in the Exposed services swimlane and configure it to subscribe to the created business event in oracle apps



Give it some logical name and say next



Point it to the ebs instance where you have created your event





Search for your business event





Now in the next step, switch it to specific schema and point it to the schema you have already copied as shown below



Say next and finish your wizard



Now just drag and drop a BPEL process in composite lane and link it with the apps adpater. Create an assign activity to map the event data to one of the string variable you have created.



Now most of the part is done. Now in order to trigger this process you need to raise an event.YOu can raise an event in two ways one is from the front end screen of Workflow and other is from Pl/SQL. We will first see how we can raise the event from workflow page.

Go to workflow page and search for your business event.

Once you get the event click on test icon as shown below



Now is the important point when testing the event there are some important points that needs to be taken care of

1> The event key is a unique parameter and everytime you test your event you have to pass a unique value to it.

2> The event data will contain the XML content. The XML content must be namespace qualifier otherwise your BPEL process will go to an infinite loop and keep polling and erroring out. Hence it is very important that the xml payload should be namespace qualified with the same namespace of which the schema is made up of.

For our exercise i have already created a sample XML file.

<xsd:Employee xmlns:xsd="http://www.example.org">
<xsd:EmpName>Arpit</xsd:EmpName>
<xsd:EmpId>2224</xsd:EmpId>
<xsd:EmpSalary>450000</xsd:EmpSalary>
</xsd:Employee>

As you can see it contains the same namespace of which the schema is made up of, we will pass this as xml content.

So overall the input will be something like this



There is one more important point that is you have to raise the event in pl/sql as highlighted

Once you raise an event you can see a row getting created in the WF_BPEL_QTAB table in the EBS instance



You can validate if the message is processed or not by again checking another view called as AQ$WF_BPEL_QTAB. The MSG_STATE will tell you whether the message is processed by SOA or not.



A ready state means it is not yet processed and there is some issue. For me the issue was that i have not yet deployed the process to soa, I deployed the soa process and as soon as an instance got created the status changed from ready to processed.

The other way to raise an event which is normally used in integraiton scenario is to raise event throuhg pl/sql.

Take an example you want that as soon as the ship order is delivered the information should be passed on to the legacy system from Oracle EBS. So you can have a trigger on the status of shipment delivery and as soon as it is delivered you can raise the custom event which will pass the payload whatever you want to pass.

In our case we will just create a simple table and try to retrieve data and pass it as the xml format we have designed earlier.

Lets first see the table and then see how we are passing the xml element from this table.

we are using an employeedata table which has following values



Then i am using the following pl/sql code to raise the event with the same payload.

SET SERVEROUTPUT ON
declare
L_EVENT_NAME varchar2 (100) := 'TestEvent';
L_EVENT_KEY varchar2 (100) := 'A23412test1';
L_EVENT_DATA CLOB;

begin
/* Raise the event */

DBMS_OUTPUT.put_line ('event qry start');
select xmlserialize(content (XMLElement("xsd:Employee",XMLATTRIBUTES( 'http://www.example.org' AS "xmlns:xsd"),
XMLForest(EmployeeName as "xsd:EmpName",Department as "xsd:EmpId",EmployeeSalary as "xsd:EmpSalary")))) INTO L_EVENT_DATA FROM employeedata;
DBMS_OUTPUT.put_line ('captured in L_EVENT_DATA');
wf_event.RAISE
(P_EVENT_NAME => L_EVENT_NAME,
P_EVENT_KEY => L_EVENT_KEY,
p_event_data => L_EVENT_DATA
);
DBMS_OUTPUT.put_line ('event raised and commited'||L_EVENT_DATA);
END;
/

Since server output is on you can see the output generated from the query.As you can observe it is namespace qualified and has the exact structure we were looking for to be passed.

1 rows deleted.
1 rows inserted.
anonymous block completed
event qry start
captured in L_EVENT_DATA
event raised and commited



<xsd:Employee xmlns:xsd="http://www.example.org"><xsd:EmpName>Arpit</xsd:EmpName><xsd:EmpId>Oracle</xsd:EmpId><xsd:EmpSalary>4566666</xsd:EmpSalary></xsd:Employee>


Once again go to your WF_BPEL_WTAB table and observe a new record getting created.

Validate the soa instance and validate if the data is getting parsed correctly.