Oracle E-Business Suite Adapter Overview

By Neeraj Chauhan[Mar-08]

The Oracle E-Business Suite Adapter provides comprehensive, bi-directional, multi-modal, connectivity to and from the Oracle E-Business Suite to provide seamless integration in the heterogeneous ecosystem of your enterprise. The Oracle E-Business Suite Adapter exposes the Oracle E-Business Suite’s interfaces as Web services. These Web services can in turn be composed into a SOA-based integration solution between business processes across enterprise applications, using Oracle Fusion Middleware.

Summary of some of the key features in the Oracle E-Business Suite Adapter:

Leverages Integration Repository

The Oracle E-Business Suite Adapter leverages the Oracle E-Business Suite Integration Repository to provide the meta-data of the public integration interfaces. The Integration Repository is the source of truth for external integration published by Oracle E-Business Suite. In E-Business Suite 11i10 the Integration Repository is a hosted instance (http://irep.oracle.com) whereas, in E-Business Suite R12.0 it is bundled with the product, under the "Integration Repository" responsibility.

Widest range of Interfaces Supported

The Oracle E-Business Suite Adapter supports different integration technologies from Oracle E-Business Suite including PL/SQL APIs, Business Events, Open Interface Tables, Concurrent Programs, XML Messages, eCommerce Gateway Interface and Interface Views.

Secured and Trusted Connection

Establishing the connection between the middleware and the enterprise application is of paramount importance, to avoid any misuse of shared connection authentication credentials. The Oracle E-Business Suite Adapter works in a secured and trusted connection environment between Oracle Fusion Middleware and Oracle E-Business Suite, that uses FND user name and password only and thus requires no other shared credentials. More details can be found on Metalink Note # 464164.1

Enables Functional Security from Oracle E-Business Suite

Security is the most critical feature that is designed to guard application content from unauthorized access. The Oracle E-Business Suite Adapter implements the functional security model of E-Business Suite to provide the security feature which only allows users with authorized privileges to execute APIs that they are exposed through the Oracle E-Business Suite Adapter. This feature is available with EBS 11i10 as well EBS R12.0 release. More details can be found on Metalink Note # 464164.1

Transaction Support

The Oracle E-Business Suite Adapter implements and provides two-phase commit transaction support from the technology foundation of the JCA compliant Oracle Fusion Middleware Adapter Framework. The two-phase commit feature is available via the implementation of Java Transaction API (JTA) under Java Connector Architecture (JCA), to ensure that either all the applications are updated or none, so that all the application databases remain synchronized.

Application Context Ready

Global application context allows an application context to be accessed across multiple database sessions, reducing or eliminating the need to create a separate application context for each user session. The Oracle EBS Adapter implicitly initializes a user session before invoking the EBS service. FND_GLOBAL.APPS_INITIALIZE procedure is called to initialize the global security context for a database session.

Oracle E-Business Suite R12.0 Support and Oracle EBS Adapter Roadmap

Support for E-Business Suite Release 12.0 is available with Oracle Fusion Middleware SOA Suite version 10.1.3.3, while the security features are available on 10.1.3.3 plus with patch numbers mentioned in Metalink Note #464164.1

There are certain roadmap features on the horizon for Oracle E-Business Suite Adapter, namely, unified error and exception handling and service monitoring at run-time.

Integrating with Oracle E-Business Suite using the Oracle E-Business Suite Adapter and E-Business Application Security

By James Lewis [Mar-08]

So, you made it past the long title. It’s good to see there are still people with enough patience for long-winded titles. I’m an integration architect for an Oracle partner consulting company called E2E Consulting out of Tempe, Arizona. In one of my SOA projects we had need to integrate with Oracle E-Business Suite, affectionately known as E-Biz. I had never integrated with Oracle E-Biz nor one of the big ERP systems before, but was very familiar with general integration with databases, queues, files, etc. So what’s the big deal, anyway? It turns out there really isn’t a big deal integrating with E-Biz, when you’re using Oracle’s SOA Suite and the E-Business Suite Adapter in Oracle JDeveloper. Of course, I’m sure some of you astutely noticed the abundance of the word Oracle in the previous sentence, which was on purpose. You would expect a company to have a seamless integration strategy among their own products, which is precisely what Oracle has done with the Oracle E-Business Suite Adapter.

For my project, E-Biz is the system of record for project management. So the first integration with E-Biz was sending it work orders from their various work management systems, which become tasks in E-Biz, in order for employees to be able to charge time against the task on their timesheet, and/or order materials using the task ID. Thankfully, since we were already knee-deep in BPEL using JDeveloper at this time, and noticed the handy E-Business Suite Adapter icon available as a BPEL service in JDeveloper, that seemed a logical choice. However, even when using the Oracle E-Business Suite Adapter, there are still several options for integrating to E-Biz using it.

Which Integration Method to Use?

As you can see in Figure 1, there are many options for integration with Oracle E-Biz. Since we were integrating from Oracle BPEL Process Manager to E-Biz, we could rule out several of the methods shown, which left the following options:

· Oracle XML Gateway
· Concurrent Programs
· PL/SQL APIs
· Interface tables

In this case, we selected to use PL/SQL APIs directly, to avoid additional configuration and maintenance in future upgrades of E-Biz. As seen in the figure above, internally the Oracle Applications adapter makes heavy use of J2CA (J2EE Connector Architecture) to make the physical connections to Oracle E-Biz. We’ll come back to configuring the J2CA connection in the Oracle E-Business Suite Adapter later in the blog. What is important for now is J2CA allows SOA Suite to hide the specific connectivity details of what each adapter needs by exposing just the J2CA connector to the BPEL process.

I’m guessing that if you’re reading this blog, you’re familiar with creating BPEL processes in JDeveloper already, and interested in seeing how easy it is to use the E-Business Suite Adapter to connect with E-Biz. If not, there’s an abundance of good tutorials out there that step through creating a BPEL process in JDeveloper, and even ones that step through using the E-Business Suite Adapter to connect to E-Biz. My intention is not to duplicate information on these sites, but to augment them by providing what insight I’ve gleaned through personal experience using the E-Business Suite Adapter in my project. However, it’s inevitable that I’ll present some steps that you’ll see elsewhere, but only to illustrate specific points.

One duplication is the Oracle Applications Module Browser, which is used by the Oracle Applications wizard in JDeveloper to determine which communication method and API to invoke. In our case we had an Oracle E-Biz support developer from the customer on the integration team who knew the specific APIs and package they were in that we needed to use, so it took no time finding the specific APIs for adding and updating tasks in the Projects module. Based on the communication methods listed previously, you could also filter the APIs shown by selecting only the communication methods you want to use at the top of the module browser. Figure 2 shows an example of what the Applications Module Browser looks like within JDeveloper.

Application Contexts and Oracle E-Business Suite

Defining what an application context is outside the scope of this blog, and you can read more about it here. What was primarily important is that in addition to using database-level security in the connection to the E-Biz database, E-Biz also uses application-level security to authorize use of its APIs. In order to support application-level authorization, the Oracle E-Business Suite Adapter allows the use of a header variable in the BPEL process. At a minimum, the header variable must contain the application username and responsibility to use to pass application level authorization. Additionally it can contain the organization to use for the username and responsibility, depending on the version of E-Biz and whether E-Biz is configured to use multiple organization units. In our situation we only needed the username and responsibility, and since we would need to invoke multiple PL/SQL APIs using this header variable, we declared a global variable of the header type and assigned the username and responsibility early in the BPEL process. Fortunately when we created the partner link service, JDeveloper created a WSDL named procedureAdapterHeader.wsdl that defined the message type for the Oracle Applications header, so we only needed to create a global variable of this type. Then, an assign activity was added to the BPEL process that allowed the global header variable to be initialized for use later. In order to allow access to all the APIs needed the username was set to Sysadmin and the responsibility was set to “Order Management Super User, Vision Operations (USA)”.

Then, when the Invoke activity in the BPEL process was added for adding a task in E-Biz, we selected the variable storing our E-Biz credentials to use in the API using the “Browse” flashlight icon. That’s it! And now we can access the APIs we selected before without errors from Oracle. We did the same steps when adding the invoke activity for updating a task in E-Biz, using the same variable.

Before leaving the discussion about application context, I’d be remiss if I didn’t mention that JDeveloper does embed a standard username and responsibility in the partner link WSDL it creates for E-Biz APIs. By default it will embed a username of “sysadmin” and responsibility of “System Administrator” in the <jca:operation> in the WSDL, so if that works for your installation of E-Biz and the APIs you need to access, then you won’t need to even use a header variable to set your application context in the API call.

Don’t Forget to Cross Your Eyes and Dot Your T’s

Even if you’ve been using the Database Adapter to integrate various systems in Oracle SOA Suite thus far, if this is the first time you’re using the Oracle E-Business Suite Adapter, you can still forget to configure the BPEL server instance completely. Just like with the Database Adapter, when you add a Database Adapter Partner Link to a BPEL process, it will use your JDeveloper database connection information in the WSDL for the Partner Link so in case you don’t configure the server’s J2CA adapter with the corresponding JNDI entry, it can at least function properly, even if inefficiently creating a new connection each invocation and pointing to a development database. Similarly, when a partner link using the Oracle E-Business Suite Adapter is created, JDeveloper embeds connection information into the WSDL for the web service. You’ll find a JDBC URL connection string, username and password to connect to the E-Biz database within a <jca:address/> element in the WSDL, as when using the database adapter. One diagram I found particularly useful in understanding which connection information is used by the adapters is located here. So based on this, we created a database connection pool and datasource in the Oracle Application Server that support XA transactions, and then edited the AppsAdapter’s oc4j-ra.xml file as mentioned in the WSDL created by JDeveloper to create a <connector-factory> element with a location attribute matching the location attribute within the <jca_address> element in the WSDL created by JDeveloper for the partner link. Finally we added a <config-property> element under the <connector-factory> whose name=”xADataSourceName” and value matched the XA datasource created. After restarting the OC4J instance we could deploy our BPEL process and have it use the configured datasource and connection pool to connect to E-Biz and not create a new connection using the WSDL properties each time the process was invoked.

No Pain, All Gain

The entire process above took only a few hours, which included time to read and investigate on how to setup the header variable for the Oracle EBS API calls. However, now having done it once it would really only take several minutes to connect to E-Biz and use the existing APIs provided, configure the server and deploy the BPEL process. Hopefully this blog will save some of your time that otherwise would be spent investigating how E-Biz credentials can be passed to the E-Biz adapter.

BI Publisher Standards

By Kevin Bouwmeester [Mar-08]

Fusion is coming. And in this setting BI Publisher (BIP) will be the main reporting tool. You will be amazed if you try and imagine the number of templates that will be worked on, in the coming years. On one hand, it is bliss that the creation of templates is so flexible, because it allows you to meet the requirements for your documents. But when every developer has their own way, this freedom can also become a serious problem for maintainability, readability and cooperation.

Eventually, all templates might need extension or customization. In the near future BI Publisher developers from EMEA (or even worldwide) will be adjusting templates build by others. Without some guidelines the work of others will not be as easy to read and adapt as it could be. That's why I think BI Publisher development can really use some standards and best practices. And guess what: this blog will provide you an overview for free!

Name The Names

One of the first things I can think of when tying to make BI Publisher development more intuitive, is a naming convention. In the end you will be working with several XML files: a data template, a bursting control file and some preview data. I have seen people get confused and load a data template in the Word template builder, to build their layout on! What I propose is really simple, but will save you the time figuring out which file is which:

· Data template - use _DT.xml as postfix for the filename.

· Bursting control file - use _BC.xml as postfix for the filename.

· Preview data - use _DATA.xml as postfix for the filename.

See below for a BI Publisher customization I have been working on, it's easy to see what each file is meant for.

Best Way To Do XML

When you begin building a BI Publisher report, you need to be sure you have all the answers you need. Therefore, it is important to think about several fundamental BIP aspects before beginning to define the XML structure:
· How many different layout templates are you going to develop on the XML structure? Take the requirements of each of the templates into account when thinking about the XML structure. The data needs to be complete and the hierarchy of the structure in the XML file needs to follow the structure (master and detail records) of the reports.
· Do you need to do bursting? If so take good care with the XML structure. Since bursting will split the XML data into smaller parts, all elements outside the scope of bursting won't be accessible after the bursting process. You have to be sure to put all relevant data under the XML element that is bursted on. Yes, this might mean having redundant data in the XML.
· Is translation needed? If so, the data should be available in multiple languages and you need to build this into queries, selecting the data for the XML.

When XML data is generated it is impossible to see how, and when it was generated. In order to track the source of bugs, it is sometimes important to know the database on which an XML file was generated, as well as the id and arguments of the concurrent request. For this pupose it is necessary to have some meta-data information in each XML file that is generated by BI Publisher. Below, I have given an example of a query that can be added to the data template in order to have some descriptive information in each generated XML data.

The Ideal RTF Template

There is no such thing as an ideal RTF template, but we can try to come as close as possible. An ideal template would resemble the layout of the report that is to be generated and would be easy and complete to the understanding of the developer. This can be contradictory, but with the hide function in Word it is possible to create two "views" in your RTF template, and thus comply to both requirements.

The idea is simple: make everything that only the developer wants to see hidden. Basically, these are the statements that control the processing flow, but do not print data. For example, this is the case for:

· conditions (<?if:CURRENCY='EUR'?> and <?end if?>)
· repetition (<?for-each:INVOICE?> and <?end for-each?>)
· defining sub-templates (<?template:footer?> and <?end template?>)

You can use this button to switch between the layout view and the developer view.

Furthermore, all the Text Form Fields that you use to insert data into the template can be given a descriptive name. In order to improve readability this name should be an example of the data that the Text Form Field represents. This will make sure that you have a view on how the generated document will look, based on example data. It is useful when you want to determine how wide columns should be for the data to fit in.

Want to know more?

My goal for this article was to provide you a glimpse into the best practices and standards that I have been working on, with my colleague Serge Vervaet. If you would like to know more, please contact me directly, for a broader dicussion about what's best in BI Publisher development. Allthough not yet officially available, I can tell you there is a BI Publisher Best Practices training coming ... so stay tuned for more BIP Best Practices!

Embedding Oracle Configurator in WebCenter / ADF Applications

By Varun Puri and Anand Verma [Mar-08]

About Oracle Configurator

Oracle Configurator is an application that offers guided selling to customers by enabling online product configuration. Oracle Configurator is part of both Oracle Order Management and Oracle CRM, and integrates seamlessly with other Order Management and CRM applications, including iStore, Order Management, Quoting, Sales and TeleSales.

How is it used?

The Configurator is installed in the source application. It provides a component called Configurator Developer to design the configurations of various products in the source application. It also provides a Servlet that is embedded in the application and enables a customer visiting the application web site to configure a selected product as per his / her choice and submit the configuration data. The Configurator Servlet in turn, returns the processed information such as Quote or Price based on the selected configuration.
Oracle Applications such as Oracle CRM and Order Management include Oracle Configurator as part their installation while other applications like iStore, Quoting, Sales and TeleSales provide out of box integration with Oracle Configurator.

How is Configurator integrated in a Custom Application

As mentioned in the previous section, Configurator provides a Servlet that should be embedded within the application to access the product configurations defined in the source application (CRM, Order Management etc).

To start using this servlet, it should be initialized first to create a session. To do this you need to pass an Initialization Message in the form of XML. The initialization message contains a list of parameters to decide which product’s configuration UI should be loaded in the browser. The following provides a sample Initialization Message XML:

There are a number of parameters that can be passed, and the combination of parameters to be passed, depends upon your requirements. The details of all of these parameters can be found in the Oracle Configurator Implementation Guide.

This data is submitted to the configurator servlet through an HTML form. The action attribute of the form should point to the Configurator Servlet: http://<host>:<port>/OA_HTML/CZInitialize.jsp. Where <host> and <port> are the host name and port number of the Configurator Servlet installed in the Source Application (CRM, Order Management etc). This loads up the UI of the specified configurable product. After the user is finished with the configuration he / she clicks the finish button. The submitted data is processed by the configurator and based upon the product configured in the Configurator UI; the Configurator Servlet posts a Termination Message in the form of XML to the URL specified in the return_url parameter. The termination message contains the processed information for the configured product. The developer of the return_url page needs to process the Termination XML to extract the information. The following figure shows a sample Termination Message XML:

This approach works well for simple J2EE applications, but to do the same in an ADF or WebCenter application you will need to do a little more work along with this. This blog provides step-by-step guidelines to integrate Oracle Configurator in an ADF Application.

Integrating with an ADF Application

The integration is achieved in three steps:
1. Creating a simple J2EE application and creating a page with an HTML Form to pass Initialization Message to the Configurator Servlet. Lets name it Initialization Page.
2. Creating a page to handle the Termination Message from Configurator Servlet. Lets name it Return Page.
3. Embedding the Initialization Page in your ADF Application.

Note: The guidelines below are based on developing applications using Oracle JDeveloper, but of course, you can utilize any other commonly used Java/J2EE Developer IDE(s).

Creating a simple J2EE Application and Initialization Page

1. Create a simple J2EE (non-ADF) application.
2. Create an HTML or JSP page in the application. This page will be used to submit the initialization data to the Configurator Servlet. This page will be embedded in your ADF Application later on using HTML IFrame.
3. Create an HTML Form in the page with following parameters:

4. Create a Hidden Type HTML Field in the Form with the name XMLmsg.

5. Specify the value of the field in the form of Initialization Message

Note: If you want to pass parameter values dynamically you should make this a JSP page rather than a static HTML page.

6. Put the javascript code onload=”document.forms[0].submit()” in your page’s <body> tag. Adding this code will submit the HTML Form as soon as the page loads up.

7. The return_url parameter in the initialization message should point to your return Page that you will create in the next section.

Note:You should specify absolute URL in the return_url parameter. This is required because; once the initialization page is submitted the control is transferred to the Configurator Servlet and on completing the product configuration, the Configurator Servlet passes the control back to the URL specified in the return_url parameter.

Creating Return Page

1. Create a JSP page in your simple J2EE application. This page will be used by the Configurator Servlet to pass the Termination Message once the user clicks finish in the Configurator Servlet UI.

2. The Termination Message is passed via HTTP Request Parameter XMLMsg. You can extract the message using HTTPRequestObject.getParameter(“XMLMsg”).

3. Now you need to use Javascript to parse the termination message xml and pass the extracted data to the ADF Application (remember this page will load in the IFrame within your ADF Application).

4. Lets assume, you are required to pass the config_header_id and config_rev_nbr parameters to the ADF page.

5. The following figure shows a sample JSP that you can use to start with:

Embedding Initialization Page in ADF Application

1. Open the ADF Page where you want to embed the Oracle Configurator functionality.

2. At the appropriate place in your page, insert an HTML IFrame as following:

3. Let's assume you have two fields on your ADF page, one for Configurator Header Id and the second for the Configurator Revision Number that you need to populate from the Configurator Servlet’s processed data.

4. Modify the af:form component and specify the Id attribute. In JDeveloper, you can do it using the Property Inspector of the Form. As shown in the figure below:

5. In the same manner specify the Id attributes for Configurator Header Id and Configurator Revision Number Fields as well. Make sure, you use the same Id(s) used in the Javascript function on the Return Page JSP that you created in the previous section.

6. Once you have the data on the ADF page, you can use it for processing in your ADF Application.

7. This completes the integration. Now run your ADF application and see the data being passed from the Configurator UI to the ADF Application.

Sample Screen Flow

The screen flow will be as follows:

Step 1: The Configurator Servlet opens up in the IFrame within ADF Application.

Step 2: Configure the Product in the Configurator UI and click Finish.

Step 3: The Configurator Servlet passes the control back to the Return URL page where using Javascript the data is passed to the ADF Application.

By Michael Baguely [Mar-08]

Release 12 introduces Trading Community Architecture (TCA) Business Object APIs. These are an abstract grouping of TCA entities to form an operable, logical business unit.

Put more simply, with one invocation of the Oracle E-Business Suite Adapter from your BPEL process, you can process multiple entities in the TCA instead of having to call granular APIs in turn. In addition to simplifying the creation of complex entities, the APIs also provide Update, Save (either create or update depending if the passed identification information matches an existing business object.) and Get (to extract and return business object data) capability.

So how might this work in practice?

The following example process uses the API HZ_PERSON_BO_PUB.get_person_bo to test whether the person already exists in the TCA and if the person doesn’t exist, a Person Party, Location, Party Site and Party Site Use is created using the HZ_PERSON_BO_PUB.create_person_bo API. The E-Business Suite Adapter has been used to call these APIs.

Start by defining an XML Schema that caters for the input data and any output parameters returned by the API (such as party_id).

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
               xmlns="http://www.mb.com/xsd"
               targetNamespace="http://www.mb.com/xsd"
               elementFormDefault="qualified">
<xsd:element name="Person">
  <xsd:annotation>
    <xsd:documentation> 
      A sample element:
    </xsd:documentation>
  </xsd:annotation>
 <xsd:complexType>
  <xsd:sequence>
    <xsd:element name="FirstName" type="xsd:string"/>
    <xsd:element name="LastName" type="xsd:string"/>
    <xsd:element name="Country" type="xsd:string"/>
    <xsd:element name="Address1" type="xsd:string"/>
    <xsd:element name="City" type="xsd:string"/>
    <xsd:element name="Zip" type="xsd:string"/>
    <xsd:element name="PartyId" type="xsd:int"/>
   </xsd:sequence>
  </xsd:complexType>
 </xsd:element>
</xsd:schema>

The following diagram shows the high level process. A check to determine whether the person already exists in the TCA is carried out in the PersonExistenceCheck Scope. The subsequent Switch activity caters for the cases of whether or not the person has been found in the TCA.

Each Oracle E-Business Suite Partnerlink uses the appropriate TCA Business Object API. Although officially documented in the TCA Technical Implementation Guide, the TCA BO PL/SQL packages may need to be located by navigating to the Other Interfaces – Custom Objects - PLSQL APIs in the Oracle Applications Module Browser.

A Transform activity is added prior to each Invoke activity to transfer data from process input variable to the input variable defined for the Invoke activity.

This also allows the payload to be transformed and default or mandatory values required by the API to be added. An example of a default value requirement is CREATED_BY_MODULE. Default values are added by right clicking an element and selecting Set Text… in the XSLT mapper.

A further benefit of using these APIs is that any error messages raised by the APIs are returned to the BPEL process in the x_messages element of the output variable.

The following diagram shows the complete process.

In conclusion, the increased functionality provided by these APIs makes them highly suited to use in BPEL processes. Processes will be quicker to develop, and be less complex and clearer as a result. You can find further information about the available APIs and their use in the TCA Technical Implementation Guide.

Integrating E-Business Suite using Enterprise Service Bus

By Jaco Verheul [Mar-08]

Introduction

This document describes a straightforward integration between E-Business Suite and a custom application. The integration is built with an Enterprise Service Bus.

This integration is the result of research done in the Appstech Community in the Netherlands. We wanted to be able to show out-of-the-box integration scenario’s in a customers environment. That’s why we defined certain integration scenario to be developed in a Proof Of Concept. This document describes one of them.

Integration scenario

Customer data is maintained in the TCA model in E-Business Suite. A custom application receives the new customer data. The E-Business Suite environment is the single source of truth for customer data. See diagram below.

For simplicity we assume a very simple customer table in the Custom Application. See table below.

In the TCA model of E-Business Suite, the data model is more complex. The information needed for this integration is stored in the following tables:
· hz_cust_accounts
· hz_parties
· hz_cust_acct_sites_all
· hz_party_sites
· hz_locations

Technical Design Overview

This paragraph describes the way the integration is built. The integration flow has the following steps:
· The integration utilizes the Business Event System. After creating a customer account, the event oracle.apps.ar.hz.CustAccount.create is fired. A Business Event in general only passes key field values as parameters. In this case, the parameter CUST_ACCOUNT_ID is most important.

· Because the business event only passes the ID of the created customer account as an event parameter, the other customer fields have to be retrieved to construct a CustomerCreated message. The business event data has to be enriched. This enrichment could be done in two ways:

o Let the business event trigger an ESB flow directly. Inside the ESB flow, query the needed fields from the e-Business Suite database with the e-Business Suite adapter or the Database Adapter.

o Create an PL/SQL event subscription for event oracle.apps.ar.hz.CustAccount.create. This event retrieves all customer data needed to create the message. The message, enriched with all the needed fields, is placed on a custom queue. This enriched message is used to trigger the ESB flow. In this scenario, more logic is put in PL/SQL.

· Because data from multiple tables is needed, enriching the event in PL/SQL is most easy. So an event subscription listens to event CustAccount.create. Based on the passed in CUST_ACCOUNT_ID, all other needed fields are retrieved from the e-business tables involved. A message is placed on a custom queue. This message has exactly the columns that are needed by receiving application.

· The Advanced Queue Adapter listens for new messages on the custom queue. The new message is routed to a Database Adapter that writes the message to the custom application.

Technical Design Details

This paragraph details the steps from the previous paragraph and also shows code examples.

Advanced Queue Setup

A custom queue is needed to store the enriched message. Before creating a queue, the queue message types has to be created.

Queue message type:

create or replace type xxjv_customer_t as object (
cust_id varchar2(20),
cust_num varchar2(20),
name varchar2(100),
street varchar2(100),
city varchar2(30),
zip varchar2(6),
country varchar2(100)); 

Note the similarity of the object type definition and the customer table definition of the receiving application.

After creating the message type, a Queue and Queue table have to be created:

Creation of the queue table:

dbms_aqadm.create_queue_table( 
	queue_table => 'xxjv_customers_sqtab', 
	comment => 'Queue table holding customer data for integration with ESB', 
	queue_payload_type => 'xxjv_customer_t',
	message_grouping => DBMS_AQADM.TRANSACTIONAL, 
	compatible => '9.2', 
	primary_instance => 1, 
	secondary_instance => 2);

Creation of the queue:

dbms_aqadm.create_queue (
	queue_name => 'xxjv_customers_new', 
	queue_table => 'xxjv_customers_sqtab');

After creation, the queue has to be started as follows:

Event Subscription

Now we have the Custom Queue in place, we can create an event subscription. We need a PL/SQL function that is:

· triggered by event oracle.apps.ar.hz.CustAccount.create,
· retrieves all needed information from the TCA tables,
· creates a message of type xxjv_customer_t and
· places this message on queue xxjv_customers_new.

For the source of this PL/SQL function see Appendix A: Source of Event Subscription

When this function is created, we have to administer the event subscription in E-Business Suite:

· Login as Workflow Administrator.

· Choose function “Business Events”.

· Query event oracle.apps.ar.hz.CustAccount.create.

· Click on Subscription. See screen shot below.

Create a new subscription. Fill the first screen in similar to the next screen shot.
Only the name of the System will be different.

Press next and fill in the next screen with exact data from the following screenshot.

You can test the setup so far, by creating a new Customer Account in e-Business Suite and check whether a message is placed in the custom queue.
The following code snippet listens for a new message on the custom queue. This script will wait until a message arrives.

SET SERVEROUTPUT ON;
DECLARE
    queue_options       DBMS_AQ.DEQUEUE_OPTIONS_T;
    message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
    message_id          RAW(2000);
    my_message          xxjv_customer_t;
BEGIN
--    queue_options := dbms_aq.dequeue_options_t();
    queue_options.navigation:=DBMS_AQ.FIRST_MESSAGE;
    DBMS_AQ.DEQUEUE(
        queue_name => 'xxjv_customers_new',
        dequeue_options => queue_options,
        message_properties => message_properties,
        payload => my_message,
        msgid => message_id );
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued cust_id: ' || my_message.cust_id);
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued cust_num: ' || my_message.cust_num);
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued zip: ' || my_message.zip);
END;
/ 
SET SERVEROUTPUT ON;
DECLARE
   queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
   message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
   message_id RAW(2000);
   my_message xxjv_customer_t;
BEGIN
-- queue_options := dbms_aq.dequeue_options_t();
   queue_options.navigation:=DBMS_AQ.FIRST_MESSAGE;
   DBMS_AQ.DEQUEUE(
        queue_name => 'xxjv_customers_new',
        dequeue_options => queue_options,
        message_properties => message_properties,
        payload => my_message,
        msgid => message_id );
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued cust_id: ' || my_message.cust_id);
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued cust_num: ' || my_message.cust_num);
    DBMS_OUTPUT.PUT_LINE(
        'Dequeued zip: ' || my_message.zip);
 END;
/ 

ESB Flow

Now we have the event subscription that places an enriched message on our custom queue, we can finalize the integration by creating the ESB flow.
See diagram below:

The flow starts from the left with an Dequeue operation that reads from the custom queue. In the setup wizard, you configure the name of the queue to listen on.
See screenshot below.

The Dequeue operation passes the message to Routing Service CustCreation_RS. This Routing Services passes the message both to File Adapter CustWriteToFile for logging purposes and to Database Adapter WriteCustomer.

Conclusion

The integration scenario presented in this document, is straightforward. The main characteristic is the enrichment in PL/SQL. When knowledge of both E-Business Suite and SOA Suite is available, this type of integration can be built in just a few days.

Appendix A: Source of Event Subscription

Function xx_event_subst reads all needed customer information, creates a message from it and put the message on a custom queue, where ESB will do the rest of the processing.

-- event subscription for oracle.apps.ar.hz.CustAccount.create
-- receives cust_account_id from event
-- reads account and customer information
-- put information on custom queue for esb processing
CREATE OR REPLACE FUNCTION xx_event_subst(
	    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;

  cursor c_cust (b_cust_id number) is
  SELECT c.cust_account_id cust_id
    , p.party_number cust_num
    , p.party_name name
    , l.address1 street
    , l.city 
    , l.postal_code zip
    , l.country
    FROM hz_cust_accounts c JOIN hz_parties p ON (c.party_id = p.party_id)
    JOIN hz_cust_acct_sites_all ca ON (c.cust_account_id=ca.cust_account_id)
    JOIN hz_party_sites ps ON (ca.party_site_id = ps.party_site_id)
    JOIN hz_locations l ON (l.location_id = ps.location_id)
    WHERE c.cust_account_id = b_cust_id;
  r_cust c_cust%rowtype;

  l_cust_account_id number;
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message_id RAW(16);
  my_message xxjv_customer_t;

BEGIN
  l_cust_account_id := p_event.getvalueforparameter('CUST_ACCOUNT_ID');
  open c_cust (l_cust_account_id);
  fetch c_cust into r_cust;
  close c_cust;

  my_message := xxjv_customer_t(
       r_cust.cust_id,
       r_cust.cust_num,
       r_cust.name,
       r_cust.street,
       r_cust.city,
       r_cust.zip,
       r_cust.country
       );
DBMS_AQ.ENQUEUE(
       queue_name => 'xxjv_customers_new',
       enqueue_options => queue_options,
       message_properties => message_properties,
       payload => my_message,
       msgid => message_id);

RETURN 'SUCCESS';
END;
/

By Michael Baguley [Mar-08]

Many Oracle E-Business Suite products leverage the Oracle Workflow Business Event System for business process integration. Whilst this is not the only method available for integrating E-Business Suite into a business process, it does allow an ESB or BPEL process to be event driven using standard E-Business Suite functionality.

Subscriptions to Business Events have a payload type of WF_EVENT_T. The Oracle E-Business Suite Adapter can be used to integrate with Oracle E-Business Suite to use this payload. However the WF_EVENT_T payload may not meet your particular use case. One way that this situation could be addressed is by combining the Business Events System with Oracle Advanced Queuing (AQ). The Oracle AQ Adapter could then be used to start a BPEL or ESB process following triggering of a Business Event.

In summary this approach requires:

· Creation of an Advanced Queue in the E-Business Suite.

· Creation of an Agent in the Business Events System for the Advanced Queue.

· A Subscription to the required Event to build the XML message.

· A Subscription to the same Event to post the message to the Agent.

· An XML Schema that matches the payload of the Advanced Queue message.

· A BPEL process that commences with an AQ Partnerlink that dequeues messages from the Advanced Queue or if Enterprise Service Bus is to be used, an AQ Adapter service that dequeues messages from the Advanced Queue.

The use of multiple Business Event subscriptions follows the same approach that would be used if subscriptions were initiating processes internal to the E-Business Suite, for example starting a Workflow.

Business Events System Configuration

1. Create an AQ in the APPS Schema for use with the Business Events System. The APPS_OUT_Q example presented here uses a RAW payload.

2. A custom Queue Handler is needed to support the Business Events Agent. Create a package in the APPS Schema with procedures to be used by the Business Events System to enqueue and dequeue messages on the AQ. The standard WF_EVENT_QH package can be used as the basis for this development. The Package name is used later as the Queue Handler value when configuring the Agent in the Business Events System. As the AQ will only be used for outbound messages the dequeue procedure can be a stub without any processing logic.

/*--------------------------------------------------
| Queue Handler Enqueue Procedure
--------------------------------------------------*/
PROCEDURE enqueue (p_event IN WF_EVENT_T
, p_out_agent_override IN WF_AGENT_T)
IS
/*--------------------------------
| AQ Variables
--------------------------------*/
l_msgid RAW(16);
l_enq_opt dbms_aq.enqueue_options_t;
l_msg_prop dbms_aq.message_properties_t;
l_xml_clob CLOB;
l_amt NUMBER;
l_data VARCHAR2(32000);

BEGIN

/*----------------------------------------
| Event XML to CLOB for AQ
----------------------------------------*/
dbms_lob.createtemporary( lob_loc => l_xml_clob
                    , cache => FALSE
                    , dur => dbms_lob.call);
l_amt := length(p_event.getEventData()); -- set amount to process
/*--------------------------------
| Enqueue RAW message
--------------------------------*/
DBMS_LOB.READ (
lob_loc => p_event.getEventData(),
amount => l_amt,
offset => 1,
buffer => l_data);
IF l_data != 'VOID' THEN
  DBMS_AQ.ENQUEUE
    ( queue_name => 'APPS_OUT_Q'
    , enqueue_options => l_enq_opt
    , message_properties => l_msg_prop
    , payload => utl_raw.cast_to_raw(l_data)
    , msgid => l_msgid);
  END IF;
END ENQUEUE;

PROCEDURE dequeue (p_agent_guid IN RAW,
p_event OUT WF_EVENT_T)
IS
BEGIN
  NULL;
END;

3. The next step is to set up an Agent in the Business Events System:

4. A PL/SQL function is required for use with the first subscription. This will build the XML message. Parameters are extracted from the Event payload (WF_EVENT_T) and used in the subscription. You can use the Agent Activity in Oracle Applications Manager to view processed subscriptions in the WF_DEFERRED queue to see parameter lists used by an event.

The function takes the standard format for subscriptions, for example:

				  FUNCTION person_record
                    ( p_subscription_guid IN RAW
                    , p_event IN OUT WF_EVENT_T) RETURN VARCHAR2

The function needs to:

· Retrieve the Event parameters, for example:

  l_user_id:= to_number(p_event.GetValueForParameter (pName => 'USER_ID'));

· Initialise the E-Business Suite environment using FND_GLOBAL.APPS_INITIALIZE with the user credentials retrieved from the Event parameters.

· Create the XML message. The PL/SQL Package DBMS_XMLQUERY can be used to build the XML payload into a CLOB.

· Set the value of the p_event.event_data CLOB with the value of the CLOB returned by using DBMS_XMLQUERY:

  p_event.setEventData(l_xml_query_result);

· The first subscription builds the XML message. The PL/SQL Rule Function value is the function defined in step 4.

· The second subscription sends the message to the Agent (i.e. Advanced Queue).
Note the subscription phase number must be greater than the first message subscription.

BPEL Process Consuming The Business Event

The following example shows the start of a BPEL process that dequeues a message from the subscription to the E-Business Suite example outlined above using the AQ Adapter.

1. An XML Schema matching the payload being transferred in the message from Oracle E-Business Suite is required.

2. Create an Empty BPEL Process.

3. Add an AQ Adapter Partnerlink to the BPEL process to dequeue messages placed in the AQ defined as the Business Events System Agent.

Select the XML Schema defined for the process as the Message Schema.

4. Add a Receive activity into the BPEL process.

5. The remainder of the BPEL process can now be added, deployed and tested.