Mashing Oracle: Part 3 Mashing Oracle E-Business Suite

Posted 03/25/2010 - 10:59 by Karthic Thope

Your rating: None

A Developer's Guide to 'Mashing Oracle' Applications

Part 1: Mashing Oracle Siebel
Part 2: Mashing Oracle PeopleSoft
Part 3: Mashing Oracle E-Business Suite 
The complete 'Developer's Guide to Mashing Oracle' includes best-practices for building mashups using Oracle applications. This is a three part series. Part 1 focuses on mashing Oracle Siebel. Part 2 focuses on mashing Oracle PeopleSoft and Part 3 focuses on mashing Oracle E-Business Suite.

1.1 Overview/Architecture

Presto platform allows you to mashups data from multiple sources of information, i.e, combine data from various source in an uniform way, massage and manipulate data to produce and generate meaningful and relevant data sets. The resulting mashups can be visualized as mashups, embeddable UI widgets, and can be published to almost any application including Portals, enterprise web applications, online Web 2.0 portals like iGoogle, NetVibes, etc. EBS applications can be used as a mashable data source to mashup with other mashables, including other EBS data. EBS business component web services provide an easy to integrate into the Presto platform. The picture below highlights the various architectural pieces and interaction between EBS Web services, Presto Mashup server and Portals/Web Apps. 

1.2 Exposing E-Business WebServices

Oracle E-Business suite Application Programming Interfaces (APIs) are stored procedures that enable you to insert and update data in Oracle Applications. For example, using APIs, you can insert a customer record in Oracle Applications. The Oracle AS Adapter for Oracle Applications exposes the Oracle Applications API as a Service via WSDL with JCA binding.

The Oracle AS Adapter for Oracle Applications is a pure JCA 1.5 Resource Adapter and can be deployed in a J2EE container in managed mode. The Adapter Framework (AF) is used for the bidirectional integration of the JCA 1.5 resource adapter with BPEL Process Manager. Adapter FW is based on open standards and employs the Web Service Invocation Framework (WSIF) technology for exposing the underlying JCA Interactions as Web Services. Both the JCA Inbound and Outbound Interactions are exposed as WSDL with JCA bindings.

Using ESB and AS Adapter design-time wizard in Jdeveloper, you can discover the APIs and database objects of E-business suite and generate web services for it. Generated web service must be deployed to an integration server running on SOA suite. Using Enterprise manager, you should be able to verify the deployed web services, like shown in the screenshot here.

1.3 Consuming EBS Web Services in Presto

Presto Service Explorer can be used to register a wide variety of data services including WSDL services. To register a EBS service in Presto, you must first generate the WSDL using the EBS web services administration screen. The generated WSDL can be registered in Presto using the WSDL service registration wizard. After registration and activation, the EBS web service is ready for consumption.

1.4 Creating Mashups

Mashups of EBS data can be created using Presto Wires (visual mashup composition tool) or Enterprise Masuhp Markup Language (EMML), standards based mashup language. Presto Wires allows both business users and developers to create mashups in a simple, graphical way. The features and scope for Wires mashups, however, is only part of the full capabilities and flexibility possible with mashups. Developers who need more robust mashup capabilities can create mashups using the Enterprise Mashup Markup Language (EMML). This XML vocabulary is a very simple, but very powerful way to quickly build mashups from Presto services or any service accessible by URL.

1.4.1 Using Wires

Wires allows you to mashup EBS data with other data services including other EBS services. Response from any WSDL operation can be mashed-up with other data. Mashup supports actions such as join, merge, filter, sort, etc., to manipulate and control the mashups results. Custom actions can also be created for general purpose or specific actions using EMML macros.

The services, mashups and actions listed on the top left side can be dropped on the main canvas to build mashups. Bottom left area can be used to set mashup/service input parameters, filter conditions, action settings, etc. The bottom right side displays real-time results of the mashups which can be visualized in various ways including data grid, chart, RSS feeds and XML. You can view results of any node of mashup by executing that specific node.

Newly created mashups can be published and activated from within wires. You can also invoke "Mashlet Maker" directly from within wires to publish a mashlet, an embeddable UI widget, for mashup visualization.

1.4.2 Using EMML

EMML mashups can be created using the Presto Mashup Studio for Eclipse. Mashup Studio can organize mashups in projects, includes several levels of assistance to help with syntax both for EMML and for XPath, for provides robust, familiar debugging features, etc. Presto platform also includes utility scripts for running and publishing mashups from the command-line.

Using EMML, you can invoke E-Business suite webservices very easily. E-Business suite web services are wrappers to Oracle PL/SQL apis. Mashups can also invoke database api or SQL statements directly, so you may choose to by pass web services layer and directly access the DB layer.

The sample below shows a simple SQL select issued from an EMML mashup. Oracle database credentials, in this sample, are accessed from Presto global extended attributes store. You can easily extend to call to complex SQL statements and function calls inside SQL.

<mashup xmlns:xsi=""
    <operation name="getCancellations">
        <output name="result" type="document"/>

        <!-- EBS 11i datasource -->
        <datasource name="EBS11i_DS0"
            password="$global.oracle_apps_pass" />

        <!-- Select customer contacts for a given customer name -->
        <sql query="SELECT * from OEBG_ORDER_CANCELLATIONS"


Here is another sample showing parameter passing between the mashup and DB SQL calls. In this case, the customer_name mashup input parameter is passed to SQL statement to query contacts for a given customer.

<mashup xmlns:xsi=""
    <operation name="getCustomerContacts">
        <input name="customer_name" type="string" default="%"/>
        <output name="result" type="document"/>

        <!-- EBS 11i datasource -->
        <datasource name="EBS11i_DS2"
            password="$global.oracle_apps_pass" />

        <!-- Select customer contacts for a given customer name -->
        <sql query="SELECT C.*, K.*
                    WHERE C.CUSTOMER_ID = K.CUSTOMER_ID(+) and
                    C.customer_name LIKE :customer_name"
             name="EBS11i_DS2" outputvariable="result"/>

1.4.3 Custom Macros for EBS

Macros are basically snippets of mashup logic that can accept input parameters and produce output. They can do anything that you can do in a mashup, except call another macro. Macros allows for building modular functions and promotes reuse of business logic across mashups. In the previous section, the EMML contains this constructor to create the service request.

To avoid replicating code in different mashups, you could create an EMML macro and reuse it across multiple mashups, in different context. This allows you to abstract specific business or complex logic and provide a cleaner, simpler and domain specific call. A sample macro below allows you to extract a subset of fields from a given response.

<macro name="ExtractFields">
      <input name="source" type="document" />
      <input name="path" type="string">
        <presto-meta name="type" variable="path" reference="">datapath</presto-meta>
      <input name="fields" type="string" />
      <input name="headers" type="string" />
      <output name="out" type="document">
      <presto-meta name="macrotype">user</presto-meta>
      <presto-meta name="help">
        <description>Extracts a set of fields from the source document</description>
          <parameter name="source">The path to the section of the document to extract</parameter>
          <parameter name="path">XPath to the section of the source containing the fields</parameter>
          <parameter name="fields">List of columns to extract from the document</parameter>
          <parameter name="headers">List of new column names to use in result set</parameter>
      <variable name="res-empty" type="document">
      <foreach variable="itm" items="{$path}">
        <assign fromvariable="res-empty" tovariable="res"/>
        <foreach variable="field" items="tokenize($fields, ',')">
          <assign fromexpr="index-of(tokenize($fields, ','), $field)" tovariable="idx"/>
         <annotate variable="res" expr=".">
            element {tokenize($headers, ',')[$idx]} {$itm//*[local-name() = $field]/string()}
        <appendresult outputvariable="out">

This macro can now be used in any mashup extract subset of response results. It also supports renaming column headers which can be extremely useful when joining results from different sources. A sample invocation of the above macro will look like below:

<macro:ExtractFields label="ExtractFields" outputvariable="$out" source="$result"
                       fields="Column1,Column2" headers="COL1,COL2"/>  
1.4.4 Virtualizing Complex WebServices

WebService inputs are typically complex and EBS web services are not different. The EBSAccountQueryByExample is fairly complex and will be quite unpleasant for users to interact directly. The EMML below builds a simple wrapper to expose a new findByStatus operation for finding accounts by their status.

Note: Need to include a sample web service invocation here

1.5 Building Mashlets

Mashlets are embeddable UI components/widgets attached to one or more mashups. It visualizes the mashups result in a format appropriate for the data returned by the mashup. Presto provides different ways to build mashlets, wires, mashlet maker, presto portal connector, etc. It's typically trivial compelling grids, charts, maps, etc view using the various tools. Mashlets requiring custom functionality can be build by writing a custom one.

The mashlet below shows a web 2.0 grid visualization of the customer accounts data. Such a mashlet can be displayed in web applications, mobile phones, in standard portals as JSR-168/WSRP portlet.

The mashlet below shows a custom chart mashlet showing order cancellations summary. Such a mashlet can be displayed in web applications, mobile phones, in standard portals as JSR-168/WSRP portlet.

Complex charts and visualizations can be built by creating custom views in the Presto portal connector. Custom views can be created using javascript, extending one of the default view or creating a totally new one. The custom chart shown here was created by extending the default BarChart view to produce a combination chart. See Presto portal documentation for more information on custom views.

// Presto portal connector custom view
  // Produces a combi chart, bar and line chart with
  // trendlines using FusionCharts library
  1 Ema.namespace("Oracle.Portlet.Renderer");
  2 Oracle.Portlet.Renderer.ComboChart = Class.create(Presto.Portlet.Renderer.BarChart, {
  3       getChart: function() {
  4           return this.mashlet.container.resourcesBase + "/common/js/FusionCharts/Charts/ScrollCombi2D.swf";
  5       },
  6       generateXML: function() {
  7         var data = this.getResponseRoot(), label = this.config['X-Axis'];
  8         var value = this.config['Y-Axis'], title = this.config['Chart Title'];
  9         var xml = "<chart caption='" + this.escapeSC(title) +
 10                  "' xAxisName='" + label +
 11                  "' yAxisName='" + value[0] +
 12                  "' numVDivLines='" + data.length*2 +
 13                  "' showLegend='" + ((value.length > 1) ? 1 : 0) +
 14                  "' showAlternateVGridColor='1' labelDisplay='ROTATE' slantLabels='1' " +
 15                  " rotateLabels='1' rotateValues='1' useRoundEdges='1' showBorder='0' " +
 16                  " bgAlpha='0,0' adjustDiv='0' showValues='0' yAxisMinValue='0' pYAxisMaxValue='300'>";
 17         xml += "<categories>";
 18         this.getProperties(data, label).each(function(cat) {
 19          xml += "<category name='" + this.escapeSC(cat) + "'/>";
 20         }, this);
 21         xml += "</categories>";
 22         var trends = [];
 23         value.each(function(val, idx) {
 24             var avg = 0;
 25             xml += "<dataset seriesName='" + this.escapeSC(val);
 26             xml +=  idx > 0 ? "' renderAs='line'>" :  "'>";
 27             this.getProperties(data, val).each(function(d) {
 28                 xml += "<set value='" + this.escapeSC(d) + "'/>";
 29                 avg += parseInt(d, 10);
 30             }, this);
 31             xml += "</dataset>";
 32             trends.push({ "name": val, value: avg/data.length});
 33         }, this);
 34         xml += "<trendlines>";
 35         trends.slice(0,1).each(function(trend){
 36           xml += "<line dashed='1' startValue='" + trend.value + "' displayValue='Avg'/>";
 37         }, this);
 38         xml += "</trendlines>";
 39         xml += "</chart>";
 40         return xml;
 41       }
 42     }
 43 );