Are You Getting the Most Out of Your Oracle E-Business Suite Deployment?

By Sancho Pinto and Stephen Schleifer [Mar-09]

The right imaging system can help drive efficiencies in lines of business throughout the enterprise – including IT.

Today’s ERP applications have enabled organizations to automate many business processes, taking common daily operations out the physical world and bringing them online to achieve greater efficiency. However, paper content still drives many high transaction processes – from procure-to-pay, to expense management, to employee benefits – presenting a tangible obstacle to optimizing process efficiency. In effect, workers still need to manage a single process by toggling between the online and offline worlds, which not only results in higher productivity costs related to slower transaction processing and increasing the likelihood of human error, but also it also significantly increases costs related to paper storage and transportation.

By deploying an imaging solution, organizations can leave the physical world of paper behind, to achieve a higher degree of business process automation that brings measurable productivity gains and cost savings. Oracle Imaging and Process Management (Oracle I/PM), a part of Oracle’s content management suite of products, digitizes paper content and inserts it into application workflows to optimize business operations. It offers an out-of-the-box integration with Oracle E-Business Suite, in a solution that offers business users access to images and workflows directly from their E-Business Suite UIs while minimizing its integration footprint. . The end result augments the existing business process without significantly altering it, so that departments can streamline their operations with minimum user disruption.

It’s a simple concept with clear value for business users, applicable to many different areas of an organization. In fact, the ROI to image-enabling applications is so quickly evident, that its use within a company is viral: one department head speaks to another about how it’s helped to achieve process optimization in their area, and soon that manager will knock on IT’s door asking when imaging can be integrated with their application. Oracle I/PM is well-suited to this type of shared-services deployment model, as it’s designed to be used as an imaging platform, allowing an IT organization to maintain a central imaging and workflow system that can be leveraged organization-wide to support any application without requiring additional infrastructure or administration.

Oracle I/PM’s Unique Approach to Application Integration

From an infrastructure perspective, when it comes to image-enabling business applications, the more customization and coding that exist between an imaging system and a business application, the more complex things become. For example, small changes such as modifying a system-to-system workflow can require additional consulting services. Or, performing a system upgrade to the application environment can end up being a major undertaking, as the imaging system will also need to conform to the updated ERP. In short, when there is too much inter-dependence between the two systems, an IT department can find that it no longer has complete control over its application infrastructure, nor the flexibility that it needs.

Oracle I/PM takes a different approach.The E-Business Suite Adapter for I/PM acts as a plumbing layer between the two systems to make this integration possible. The E-Business Suite Adapter for I/PM is built on a standardized Application Extension Framework (AXF), which manages a list of configured integrations and their association with solution templates that are provided for automating processes such as procure-to-pay or expense management. The AXF manages a list of configured integrations and their association with solution templates that are provided for automating processes such as procure-to-pay or expense management. These best practice templates comprise of Oracle BPEL Process Manager-based workflows with associated approval rules, data entry forms and reports. This effectively eliminates all hard-coded integration mechanisms and provides flexible configuration options to define which additional commands are made available to E-Business Suite users. Once the integration is installed image enabling an additional E-Business Suite module is done through an AXF configuration change without any additional E-Business Suite customizations.

The Oracle I/PM integration approach allows the imaging system to be maintained separately from the E-Business Suite environment, while providing end users access to imaged documents and workflow tasks from within the context of E-Business Suite.

Fusion Matters: Oracle I/PM and the Future of Oracle Applications

For Oracle customers who are looking to simplify their IT infrastructures and image-enable multiple business applications on one infrastructure, there is a lot to look forward to with Oracle I/PM. Not only will the AXF be built out to support other Oracle Application families such as PeopleSoft and JDEdwards over the coming months, but Oracle I/PM provides additional web service APIs and tools to easily integrate imaging and workflow with other applications in your environment. For those who are looking towards a future with Fusion Applications, the story gets even better. Oracle I/PM will also provide the default image repository for Fusion Applications, so it will be able to be leveraged wherever imaging services are needed throughout the enterprise.

Next Steps

· Take a look at the Oracle I/PM for E-Business Suite solution sheet and viewlet for an integration overview
· Review the Demonstration Viewlet for a detailed step-by-step run through of Oracle I/PM’s integration with Oracle E-Business Suite Payables
· Download Oracle I/PM 10gR3

Business Event Subscriptions with Custom Payloads

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.

Using R12 TCA Business Object APIs with BPEL Process Manager

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.

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!

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.

BPEL: Migrating from DEV:TEST:PROD Environments

By Anirudh Pandit [Apr-09]

"Ah, to build, to build!
That is the noblest art of all the arts."
-- Henry Wadsworth Longfellow

Deployment is a vital step in developing and implementing any BPEL application, as most applications must be deployed to multiple environments including Dev, Test, QA, and Production. Given these environments are often configured differently from each other, for example Service End points, runtime properties, adapter properties, deployment can often pose numerous challenges if this process is not considered and planned for. This article will highlight the new deployment capabilities in BPEL version 10.1.3.4 and also revisit deployment approaches that can be used when using BPEL version 10.1.3.3.

Deployment in BPEL Version 10.1.3.4

Oracle BPEL Process Manager version 10.1.3.4 introduces a significantly enhanced methodology for application deployment to different environments. In BPEL 10.1.3.4 a single BPEL Suitcase is used to deploy your BPEL application to all of the various deployment environments. The distinct configurations for each environment are maintained in a deployment plan attached to the BPEL Suitcase, and at the time of deployment all respective environment changes are applied to the appropriate BPEL project files. Test, QA, Prod environments each have a deployment plan with assoicated configurations for service endpoint WSDL, XSD etc.

The next few steps describe how to create a deployment plan and associate it with the BPEL Suitcase for deployment to different environments. The following logical steps allow you to simply automate BPEL deployment in 10.1.3.4:
1. Create a deployment plan for each environment
2. Maintain each environments configuration in the respective deployment plan
3. Attach the deployment plan to the build process to generate the BPEL Suitcase
4. Deploy the Suitcase to the Environment

In the example below we are using JDeveloper as the development environment, but any other ANT based development tool can be used.

The screenshot below shows a BPEL process to be deployed to the Test Environment. The Partner link (CreditRatingService) WSDL locations are different in Dev and Test.

In BPEL 10.1.3.4 there are 4 ANT commands provided:

Step 1) Create Deployment Plan

Open the build.xml file and add the following code .

< target name="generatePlanForTestEnv">
	< generateplan planfile="${process.dir}/Test_EnvDeploymentPlan.xml" verbose="true"
		overwrite="true" descfile="${process.dir}/bpel/bpel.xml"/>	
</target>
<target name="generatePlanForDevEnv">
  <generateplan planfile="${process.dir}/Dev_EnvDeploymentPlan.xml" verbose="true"
      overwrite="true" descfile="${process.dir}/bpel/bpel.xml"/>
</target>

The build file should now look something like this:

Now we will run ANT from JDeveloper to create the deployment plans for the Dev and Test environments, you can use any other ANT tool to generate the plans.
Right click the build.xml file and select the Run Ant option.

Now select the generatePlanForDevEnv and generatePlanforTestEnv to the target pane as shown in the screenshot below.

Click Ok.

This should generate the deployment plans for the Dev and Test environments, output from the ANT log file in JDeveloper is shown below:

This should generate the Dev_EnvDeploymentPlan.xml and Test_EnvDeploymentPlan.xml.xml files in the ${process.dir} folder.

Step 2) Maintain each environment's configuration in the respective deployment plan

Open the respective deployment plans to change or add the environment configurations
In this example, we will change the WSDL URL of the PartnerLink to point to the Test environment.

The deployment plan for Test should look similar to the following:

Step 3) Attach the deployment plan to the build process to generate the BPEL Suitcase

Add the following code to the build.xml file:

<target name="AttachTestPlan">
  <echo> ---- Attaching the Test deployment plan file -----</echo>
  <attachplan planfile="${process.dir}/Test_EnvDeploymentPlan.xml" verbose="true"
   overwrite="true"
   suitecase="${process.dir}/output/bpel_BPELProcessDeployment_v2009_03_23__53965.jar"/>
</target>				  

You can also parameterize the suitcase directory.

The build.xml file should look similar to the one shown below:

Right Click Build.xml in JDeveloper and Select the RunAnt command and move AttachTestPlan to Selected Targets and click Ok.

The build process should attach the plan to the BPEL Suitcase. Output from the ANT log file in JDeveloper is shown below:

Note that the attachplan command does not replace the old bpel.xml. Replacement occurs only when the BPEL process is deployed to the server. Similarly, we can extract and validate plans based on the ANT commands that are provided with BPEL 10.1.3.4

Step 4) Deploy the Suitcase to the Test Environment

Deploy the BPEL Suitcase to the server. In our example I will use the deploy (default) ANT target but this can be customized.

Right click build.xml and select deploy (default) and move it to Selected Targets and click OK.
This will deploy the BPEL Suitcase with the Test Environment Configuration for the WSDL location, to the server.

Deployment in BPEL 10.1.3.3

When a BPEL process is developed in JDeveloper, it creates ANT scripts that can be directly deployed from JDeveloper to the Dev environment.
This is not the best approach when applications are deployed to multiple environments because of the need to support different environment variables.

BPEL 10.1.3 provides a “Customize” ANT task where different properties of various environments can be declared in a single file.

Properties can be parameterized for these environments in the bpel.xml file. Different property values can be defined for these environments in the Build properties file.
These are passed to the bpel.xml file parameters in the customize task. The “Customize” task can be run as a subtask of BPELC or as a standalone ANT task.

In BPEL Version 10.1.3.3, a BPEL Suitcase file is generated for every environment the project is deployed to.

Solution Details

The following files can be found in the BPEL Process Directory:
· BPEL Process Folder
· Build.xml
· Build.properties
· BPEL (folder)

The following steps are required to customize the deployment task.

1. Create copies of the build.properties file for each environment, naming them as follows and save them in the same location.
· build_dev.properties
· build_test.properties
· build_qa.properties
· build_prod.properties

2. Each environment property file i.e. build_<env>.property file supply values to parameters that will be defined in a later step.
Make the following changes to build_<env>.properties to reflect the respective environment and application parameters values.

bpel_host=<bpel server host name> # e.g. prod host name in build_prod.properties file
bpel_port=<bpel server port>

admin.user = oc4jadmin #This is default, it can be any other admin user
http.hostname = <hostname> # hostname of the server where we are deploying 

# Change if deploying to domain other than "default"
domain = <bpel domain>

# Change if deploying with process revision other than 1.0
rev = <current revision>

3. Copy the build.xml from <BPEL Process main folder> to the BPEL sub-folder .

4. Define the ANT ‘customize’ task to change the WSDL URL of a PartnerLink.

Add the <partnerLinkBinding > tag and the WSDL location property with respective parameters.

Repeat this step for every partnerlink that needs modification.

a) Find the following piece of XML in the build.xml file under {BPEL Application}/bpel folder

<target name="compile">
    <echo>
      --------------------------------------------------------------
      | Compiling bpel process ${process.name}, revision ${rev}
      --------------------------------------------------------------
    </echo>
   
    <bpelc input="${process.dir}/bpel/bpel.xml" out="${process.dir}/output"
    rev="${rev}" home="${bpel.home}"/>
</target>

Replace the above XML with the following:

<target name="compile">

    <bpelc input="${process.dir}/bpel/bpel.xml" out="${process.dir}/output"
        rev="${rev}"home="${bpel.home}">

        <customize>
            <partnerLinkBinding name="TestProcess">      
                <property name="wsdlLocation">  
                     http://${bpel_host}:${bpel_port}/orabpel/${domain}/TestProcess/TestProcess?wsdl
                </property>
            </partnerLinkBinding>
        </customize>        
    </bpelc>     
</target>

b) The second part of this step is to point to the properties file to provide environment parameter values.
To do this, find the following piece of XML in build.xml file under {BPEL Application}/bpel folder

<property file="${process.dir}/build.properties"/> 

Replace the above XML with the following:

<property file="${process.dir}/build_${env}.properties"/> 

The ${env} parameter value will be provided when the ANT script is compiled.

5. The following steps direct the main {BPEL Process}/build.xml to run the {BPEL Process} /bpel/build.xml file that was created.

a) Create a new target to redirect to the {BPEL Process}/bpel/build.xml file

Make the following changes and add the following target to the {BPEL Process}/build.xml file

<target name="compileEnv"> 
    <echo>
      -------------------------------------------------------------- 
      |Compiling bpel process with following ${process.name}/bpel/build.xml file
      ------------------------------------------------------------- 
    </echo> 
    <ant dir="${process.dir}/bpel"/> 
</target>

b) The second part of this step is to modify the process-deploy target to invoke the new compile target that was created.

Find the following piece of XML in the build.xml file under {BPEL Application}/bpel folder:

<target name="process-deploy"      
    depends="validateTask, compile, deployProcess, deployTaskForm, deployDecisionServices" />

Replace it with the following:

<target name="process-deploy"      
    depends="validateTask, compileEnv, deployProcess, deployTaskForm, deployDecisionServices" />

c) Find the following piece of XML in the build.xml file under {BPEL Application}/bpel folder:

<property file="${process.dir}/build.properties"/> 

Replace it with the following:

<property file="${process.dir}/build_${env}.properties"/> 

6. Use ANT and deploy the respective BPEL process.

ANT -Denv=dev -Dadmin.password=<oc4jadmin password>
use env=prod or test or qa depending on environment.

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.

Combining Multiple Worklists into a Single BPEL Tasklist

By Michael Rulf [May-09]

When you start using both BPEL and E-Business Suite, you end up with multiple worklists containing tasks that have been assigned to you. This article will explain how you can collect workflow tasks from E-Business Suite (EBS) and populate them into the BPEL task list. We will be using the Business Event functionality built-in to EBS to invoke a BPEL process whenever Workflow sends a notification message.

First, some assumptions:
1. This exercise assumes that all your userids are the same for both EBS and BPEL. This can either be done by configuring EBS and BPEL to use the same LDAP repository, or you can manually define and manage the users in both systems.
2. For this example, I am assuming that all notifications have approval codes of “Approve” and “Reject”. The code provided collects the approval codes for the
notification and you could extend the example to accommodate additional approval codes.

After creating a new application to contain your work, let’s create an Asynchronous BPEL process to handle workflow notifications.

You will want to set the input schema to match the EBS workflow event structure by selecting the APPS_WF_EVENT_T.xsd file provided by Oracle and selecting the
WF_EVENT_T type.

The first step is to gather some additional information about the workflow notification. I have written a small PL/SQL procedure and two associated data types that
collects the necessary information.

The two data types are used to gather valid response codes for a particular notification. While this example does not deal with multiple sets of response codes,
you could use this code to extend the example based on your response values.

create or replace type xx_bpel_response_record is object(code varchar2(30), meaning varchar2(30))
create or replace TYPE XX_BPEL_RESPONSE_ARRAY AS TABLE OF xx_bpel_response_record

This procedure returns information about the notification that is used by the BPEL process to generate the BPEL notifications.

create or replace
procedure xx_bpel_GetSummary (notification_id in varchar2,
                      is_url out nocopy varchar2,
                      msg_subject out nocopy varchar2,
                      msg_body out nocopy varchar2,
                      msg_url out nocopy varchar2,
                      msg_role out nocopy varchar2,
                      response_codes out nocopy xx_bpel_response_array,
                      p_ret_array_size OUT NOCOPY NUMBER,
                      p_result     OUT NOCOPY NUMBER,
                      p_error_code OUT NOCOPY NUMBER,
                      p_error_msg  OUT NOCOPY VARCHAR2) is

/* declare */
C_RESULT_SUCCESS            CONSTANT NUMBER(2)    := 0;
C_RESULT_ERROR              CONSTANT NUMBER(2)    := 1;
C_INVALID_PARAM_ERROR       CONSTANT NUMBER(2)    := 2;
C_INVALID_ACTION_ERROR      CONSTANT NUMBER(2)    := 4;
resp_msgtype varchar2(100); 
resp_msgname varchar2(100);
resp_priority varchar2(100);
resp_duedate varchar2(100);
resp_status varchar2(100);
 
cursor c1 is
select lookup.lookup_code code, lookup.meaning meaning
from wf_lookups lookup, wf_activities activities
where activities.message=resp_msgname
and activities.item_type=resp_msgtype
and activities.end_date is null
and lookup.lookup_type= activities.result_type;


begin
/* Get the subject line of the notification*/
msg_subject:= wf_notification.GetSubject(notification_id);
/* Get the body of the notification */
msg_body:= wf_notification.GetBody(notification_id);
/*See if there was a URL embedded in the notification*/
is_url:= wf_notification.isFwkBody(notification_id);
/* If there was a URL in the notification, retrieve it */
msg_url:= wf_notification.GetFwkBodyURL(notification_id,'text/plain');
/* Get the details about the notification*/
wf_notification.GetInfo(notification_id, msg_role, resp_msgtype, resp_msgname, resp_priority, resp_duedate, resp_status);
/*See if there was a URL embedded in the notification*/
is_url:= wf_notification.isFwkBody(notification_id);


/* Retrieve the response codes used by the notification */
response_codes:=xx_bpel_response_array();
for results in c1
loop
dbms_output.put_line(results.code);
response_codes.extend;
response_codes(response_codes.count):=xx_bpel_response_record(results.code, results.meaning);
end loop;
/* The number of response code values */
p_ret_array_size := response_codes.count;
p_result := C_RESULT_SUCCESS;

EXCEPTION
  WHEN OTHERS THEN
  p_ret_array_size := 0;
    p_result := C_RESULT_ERROR;
    p_error_msg := sqlerrm;

end xx_bpel_GetSummary;

Once we have the data types and procedure compiled in the Database, we can call the procedure from the BPEL process through the definition of a Database (DB) Adapter.

Select the Database connection to your EBS instance:

Specify that you are going to call a stored procedure.

Specify the procedure we just defined.

Select “Finish” then accept the partner link that was created.

The next step is to create an “Invoke” action to call our DB Adapter and generate the input and output variables.

Add an “Assign” action to copy data from the input variable to the input variable for your Invoke action.
You will need to modify the default “from” xpath to specify you want the first value entry (PARAMETER_LIST_ITEM[1]) in the parameter list as shown below.

Your BPEL process should now look like the following:

Now that we have collected data about an invoking EBS workflow, we need to add a “Human Task” to the process and create a new task definition.
This “Human Task” will add your EBS notification to the BPEL work queue.

You will need to add a parameter to the notification payload so you can pass the notification recipient as defined by the EBS workflow to the BPEL work queue.

Then add an assignment by expression that uses this payload parameter to the participant list used by BPEL.

Your Human Task should now look like this:

The last step for setting up the Human Task is to populate the payload parameter you just added.
Select the assignment within the Human Task scope and add the recipient. In order to do so, you need to update the schema used by the BPEL
process to define the human workflow task. If you look at the Application Navigator, you will see there currently are two WorkflowTask.xsd files.

You need to update the one under “taskservice” to include the payload specification you just created in the EBSNotification and change the task payload type
to “payloadType”. When complete, your schema should look like this:

After making these changes, refresh the variable definitions via the structure pane to ensure your BPEL process sees the new payload definition.

You are now ready to add the EBS recipient to the Human Task by updating the first pre-populated assignment action in the Human Task scope.

First, you need to remove the default XML Fragment assignment created by the wizard process as highlighted below.
This default assignment overwrites the payload data structure which we now need in order to populate our recipient information.

Once that is deleted, add the recipient information.

The final steps take the result returned by the Human Task and pass them back to the EBS Workflow engine.
First, add a DB Adapter to populate the result code back to EBS via the “wf_notification.SetAttrText” procedure.

Set the DB Connection info to your R12 instance.

You are going to want to call a stored procedure to update.

Once the DB Adapter is created, you need to call it for each of the Human workflow results.
This is done by adding assignment and invoke activities to each of the case outcomes.

The assignment activity sets three parameters required by the DB procedure:
1. The ID of the notification you wish to update.
2. The workflow notification attribute you will be assigning a value. In this case is the “RESULT” attribute.
3. The value you will be assigning to the attribute. In this case it the result code returned by the human workflow.

Repeat for the other explicit case outcome value. Your BPEL process is now complete and should look as follows:

Now that we have created a BPEL process to coordinate the EBS workflow notifications, we need to implement an ESB service to
collect the necessary Business Events from EBS. When ever a workflow notification is sent in EBS, it raises the “oracle.apps.wf.notification.send”
business event. By creating an Oracle Applications Adapter for this business event, JDeveloper will automatically generate and execute the necessary code
to register your event listener. Create a new ESB project in your application and add and Applications Adapter to the project.

You will want to create a new WSDL file which launches the EBS Adapter Wizard. After selecting your connection

You may be asked to create the iRep file which discovers all of the EBS services available to the adapter. If this is your first EBS adapter, go ahead and build the file. You are now ready to select the necessary service which is under “Other Interfaces->Business Events->Outbound(Listen to Business Events from Oracle Apps)”.

JDeveloper then populates the Operation Objects for you.

You will want to specify a schema. Use the APPS_WF_EVENT_T.xsd schema provided by Oracle and complete the adapter definition.

The wizard automatically adds a routing service for you and you will see the following:

The next step is to add a routing rule so your BPEL process is called every time the notification event is raised.
You should browse the service explorer and select the initiate action for your BPEL process from the registered BPEL processes.

This completes your ESB configuration which should look as follows:

You are now ready to start receiving EBS workflow notifications in your BPEL work queue.

Service Oriented Approach for E-Business Suite Integrations

By Peeyush Tugnawat [Sep-08]

Oracle E-Business Suite (EBS) is one of the most widely used enterprise applications. EBS customers are often challenged with the requirements for more collaboration between traditionally separated business functions within and outside the enterprise. This can be multiplied if the enterprise consists of business functions served by diverse best of breed applications.

Traditional enterprise application integration mechanisms may deliver on short-term goals but it has many shortcomings and often results in tightly coupled integrations and vendor lockdown. Adopting a Service Oriented approach for E-Business Suite Integration can deliver on fundamental business needs. Besides addressing the immediate requirements, a service oriented approach underscores and addresses the fundamental reasons for doing the integration. Let’s explore some of the things to consider to ensure the success of SOA based integration solution for EBS.

Deliver on Fundamentals

Business process flexibility and collaboration are the key forces that drive on-going requirements for integrating with EBS and for that matter any enterprise application. A Service Oriented Architecture (SOA) approach for EBS integrations should try to deliver on the fundamental and implicit business requirements of business process agility, simplicity, enhanced visibility, efficiency, and reusability. When the fundamentals are addressed, it becomes easy over time for businesses to create new business processes that are composed of the existing business functions and processes.

Understand Built-in EBS Integration Mechanisms

It is important to understand the different integration components available within EBS to make an informed decision about using one or more for your SOA integration project. Your selection depends upon the integration requirements and the interaction pattern determined to be the best fit.

The following integration mechanisms are available within E-Business Suite.

Use IREP to Discover and Catalog Available Business Services

To plan your SOA based integrations, the architects and business users need to know about the services available within EBS that can be leveraged to be a part of your integration service(s). The first step when planning and designing your integrations should be to use Oracle IREP. This acts as single source of truth for the available business services within EBS and also the details of service end-points. IREP lets users easily discover the appropriate business service interface for integration with any system, application, or business partner.

To access IREP go to http://irep.oracle.com/. If you are working on EBS R12, from the Navigator menu, select the Integration Repository responsibility, then click the Integration Repository link that appears.

Adopt SOA Principles for Integration Architecture

Use SOA principles of abstraction, loose coupling, discoverability, and composition for service-oriented integrations with Oracle EBS. Using Oracle SOA Suite for service-oriented integrations can offer great advantage as it provides a complete set of service infrastructure components for building, deploying, and managing SOA's. Lets go over some details and things to keep in mind while coming up with the architecture for EBS integrations.

Use Standards

Using standards-based technologies for your service-oriented integrations will help eliminate lock-down with products and companies. This will enhance easy evolution, enhancement, and composition of business processes that may use services related to integrations.

Service Enable Enterprise Application Functions

Once you explore IREP and know the service or interface that you want to use, the next step is to make the function or service participate in your service oriented integration architecture as a web service. To use one of the integration functions such as business events or PL/SQL APIs in a SOA-based solution (integration or composite process) is relatively simple, with the help of Oracle Applications Adapter by exposing them as Web services. This enhances re-usability, extensibility, and delivers a faster design to deploy time. It exposes existing EBS Integration interfaces as Web services. The adapter inherently uses and leverages open standards, including J2CA, XML, WSIF, WSIL, and WSDL. Most importantly, it dramatically reduces the time to design and develop a SOA based integration that interfaces with web service based integration interface for EBS.

Business Semantics for Message Payloads

Business objects contained in the message payloads for integration services should share common view and semantics across the enterprise and should be architecturally neutral. For example, there should be only one standard view of Customer or Sales Order within an enterprise. This provides common vocabulary and enables reuse, extensibility, and cross pillar/application interoperability. The concept of a common view of business information is commonly known as a Canonical Data Model (CDM) pattern. Oracle has implemented this pattern in the AIA foundation pack and it is termed Enterprise Business Object (EBO). The majority of messages delivered with XML Gateway are mapped using the Open Application Group (OAG) standard.

Classify Integration Requirements

EBS requirements can be broadly classified as real time / near real time and batch integrations. Identify message exchange patterns for both types of integrations. Event based asynchronous MEPs can be used for modeling both type of integrations. Use synchronous services only when absolutely required and if the SLA cannot be fulfilled by other integration options.

Asynchronous Integration Patterns

Enable asynchronous near real-time business interactions using event based integration services. Business events can serve as the basic components of event driven architecture and facilitate loosely coupled asynchronous service oriented integration processes. Business events and AQ components provide the mechanisms that can be used with the use of application and AQ adapters respectively. For example, outbound integration services can be invoked in real time by an employee create event when new hire information is entered into EBS.

Batch Integrations

Batch integration requirements should be carefully examined for factors such as volume, size, and other transaction requirements. Typical batch integration requirements include reading from a file from a secure location, transforming data, and writing to database. Such requirements can be satisfied by using file adapter with ESB. For large file sizes consider debatching feature supported by File/FTP adapters. If the batch integration includes a high volume of data with complex data integrity requirements, ODI should be considered.

Service Layers

It is a good idea to adopt a layered approach when designing your services. This promotes clear separation within service stack and promotes enhanced reuse of services. Think about the following layers:

Application Services Layer: These are the application specific services like PL/SQL APIs or business events related to a particular business function. It also includes PL/SQL Web services.

Business Services Layer: The services in this layer will encapsulate a particular business function like creating a sales order. The service in this layer will be a course-grained Web service that can be used by external entities to perform a business function such as creating a sales order in EBS or notifying other systems about a new hire. The services in this layer can be implemented using resource adapters (Application, DB, AQ) and ESB.

Orchestration Services Layer: The process services in this layer are long-lived and cross-functional processes that are composed of multiple services from the business services layer and/or any other external Web service. These services will provide a layer or abstraction for the actual business service and the interactions it includes for incorporating a business process. Services in this layer can be implemented using BPEL that defines a model and a grammar for describing the behavior of a business process based on interactions between the process and its partners.

Inject Resiliency into SOA Components: Build resiliency into the individual integration processes. This may be easy to miss, even with the best architecture in place. Always think about all the “what if” scenarios and try to inject process level resiliency into the individual integration processes.

For example, you can use partnerLinkBinding configuration properties for implementing resilience to failures in endpoints on which BPEL or ESB processes depend. Use retryMaxCount and retryInterval.

Exception Handling: Despite all the forward thinking there can be things that might and will go wrong. Define reusable, extensible, and agile approach to handle exceptions at process level and other unknown exceptions. Using a common exception handler service with extensible interface can provide the flexibility, re-usability, and extensibility. Such common service can be implemented as a BPEL service.

Simplify Support Functions: Anyone who has worked with application integration can relate to the great deal of time and energy involved when troubleshooting integration issues. With asynchronous messaging and multiple services, the idea should be to ease the pains of traditional EAI support functions.

In the case of EBS, people often jump to the conclusion to create custom tables for handling integration exceptions. Instead, try to leverage built-in human workflow capabilities and the worklist application within BPEL. Notification mechanisms can be used to notify support personnel in case of exception and let them view and analyze the details in an easy to read format within the worklist application. This use of the human workflow feature has proved to be very helpful for support persons.

Human interaction and intervention: Business processes inevitably will involve human interaction in some or other form. If your integration process involves such role based people interaction, plan ahead and use standards-based mechanisms to have human workflows. Oracle BPEL provides standards-based human workflow capability that can be used for modeling human interactions for your integration services.

Separate Business Rules: The integration process is not a good place to embed and hard code business rules. Avoid creating a custom layer in PL/SQL for applying business rules or performing data validations. Identify the rules and use Oracle Business Rules to provide loose coupling between your integration service and rules. This will provide the flexibility for business users to change business rules, without needing developers to modify PL/SQL or redeploy integration services.

Business Process Visibility: Plan to provide visibility into your integration or business process. This is very important because today, with heterogeneous systems and applications, and with integrations spanning multiple systems, it becomes very hard to have visibility at run-time. Use Oracle BAM to enable users (IT and Business) to to monitor and have visibility into business processes and integration points in real-time.

SOA Governance:In simple terms, plan for the capability to manage and apply policies for the services within the service portfolio of your integration services. This is critical for SOA and needs to be planned well to ensure better management and control of services. Use Oracle Web Services Manager for managing and applying policies for your Web services.

Review AIA Offerings: Before diving head-on into creating service oriented integrations for EBS be sure to review the Oracle AIA offering. Oracle Application Integration Architecture (AIA) provides an open standards-based framework for creating cross-application business processes and accelerates time-to-value. AIA offers Process Integration Packs (PIPs) for delivering prepackaged, end-to-end business process integrations across Oracle Applications. It also offers the Foundation Pack which provides a reference architecture and reusable Web service components that can accelerate your SOA initiative for EBS integrations.

Conclusion

A service oriented approach for E-Business Suite integrations offers tremendous advantages over other traditional EAI approaches. Enterprise integrations can evolve into reusable and implementation agnostic useful services by applying very basic principles of flexibility, agility, and extensibility in all the components of service oriented integration architecture.

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;
/