Oracle E-Business Suite Connector

Premium

Anypoint Connector for Oracle E-Business Suite (EBS) provides real-time connectivity to query and update Oracle EBS using web services. The connector has been developed for the Oracle EBS v12.1.x. For more information about Oracle EBS, visit Oracle site.

Oracle EBS connector provides integration to the following:

Financial Management and Order Management Modules: Full API coverage for Financials and Order Management services.
PL/SQL Integration: Both standard PL/SQL or user-defined PL/SQL can be invoked.

Prerequisites

Because of how closely the connector is coupled with Oracle EBS, this document assumes you have:

A working knowledge of Oracle EBS.
Read the relevant documentation on Web Services and Integration Scenarios for your Oracle EBS version.

A definition and detailed explanation of the Oracle EBS terms and concepts used here are freely available in the Oracle documentation set of your Oracle installation.

This document assumes you are familiar with Mule,Anypoint Connectors, and Anypoint Studio Essentials To increase your familiarity with Studio, consider completing one or more Anypoint Studio Tutorials. Further, this page assumes that you have a basic understanding of Mule flows andMule Global Elements.

To use the Oracle EBS connector, you must have the following:

Java 7 : Oracle EBS connector does not work unless you are using Java 1.7.0_x as your JRE.
Web service configuration in Oracle EBS: Configuring the Oracle EBS connector requires configuring web services in Oracle EBS and exposing them via the Integration Repository.
FND_WEB_SEC PL/SQL Service: The connector requires Oracle EBS to have the FND_WEB_SEC PL/SQL service deployed.
Anypoint Studio.

Compatibility

Oracle EBS Connector 1.0 is compatible with:

Application/Service

Version

Mule Runtime

3.6 or higher

Anypoint Studio

March 2015

Oracle EBS

12.1.x or higher

Java

1.7.0_x

Connector Architecture

Oracle EBS offers different technologies or products to address various types of integrations. Listed below are the most widely used for integration with enterprise applications:

Data Centric integration.
Event Driven integration using Oracle Business Event System.
Integration through Web Services using Integrated SOA gateway.

The following illustration shows the integration architecture of the MuleSoft Oracle EBS connector:

See the Oracle E-Business Suite Documentation Web Library for more information.

Installing and Configuring

Installing

You can install a connector in Anypoint Studio using the instructions in To Install a Connector from Anypoint Exchange.

To use the Oracle EBS connector in a production environment, you must have either:

An Enterprise license to use Mule
A CloudHub Starter, Professional, or Enterprise account

Contact the MuleSoft Sales Team to obtain either of these.

Creating a New Project

To use the Oracle EBS connector in a Mule Application, first create a new Mule project in Anypoint Studio:

In Studio, click File > New > Mule Project.
Enter a name for your new project and leave the remaining options with their default values.
If you plan to use Git, select Create a .gitignore file for the project with default ignores for Studio Projects, and then click Next.
Click Next to verify that Java 1.7 is set as your default JRE.
Click Finish to create the project.

Configuring a Global Element

In Anypoint Studio, click the Global Elements tab at the base of the canvas.
On the Global Mule Configuration Elements screen, click Create.
In the Choose Global Type wizard, expand Connector Configuration, and then select OracleEBS: Connection Management.

`
Click Ok

Configure the parameters according to instructions below

Field

Description

Name

Enter a name for the configuration with which it can be referenced later.

Username

Enter a username to log in to Oracle EBS.

Password

Enter the password for the username.

Host

Enter the URL of the Oracle EBS instance.

Port

Enter the port of the Oracle EBS instance.

Responsibility Name

Enter the responsibility name that is needed to execute the operation.

Responsibility Appl. Name

Enter the application short name that is needed to execute the operation.

Security Group Name

Enter the security group key of the Oracle EBS instance (optional). Default value is STANDARD.

NLS language

Enter the NLS language of the Oracle EBS instance (optional). Default value is AMERICAN.

Org. ID

Enter the organization id of the Oracle EBS instance (optional). Default value is 204.

Configure your custom web services list according to the steps below:
1. Select Create Object Manually and then click the button next to it.
2. On the pop-up window, select the button to add more entries.
3. Right-click a metadata item and click Edit the selected metadata field to enter the values.
4. Click OK to save the list.
Keep the Pooling Profile and the Reconnection tabs with their default entries.
Click Test Connection to receive a Connection Successful message.
Click OK to save the global connector configurations.

Sometimes, it is necessary to create more than one global configuration. It depends on how the Oracle EBS instance is configured. One possible scenario would be to have one configuration for executing web service operations and another one for PL/SQL operations.

XML Editor

Ensure you have included the Oracle EBS namespaces in your configuration file.

<mule xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:oracle-ebs = " http://www.mulesoft.org/schema/mule/oracle-ebs"
xsi:schemaLocation="
http: //www.mulesoft.org/schema/mule/core
http: //www.mulesoft.org/schema/mule/core/current/mule.xsd
 http://www.mulesoft.org/schema/mule/oracle-ebs http://www.mulesoft.org/schema/mule/oracle-ebs/current/mule-oracle-ebs.xsd
<!-- here go your flows and configuration elements -->
</mule>

Follow these steps to configure a Oracle EBS connector in your application:

Create a global Oracle EBS configuration outside and above your flows, using the following global configuration code.

<oracle-ebs:config-name name="OracleEBS__ConnectionManagement"
 username="${oracle.username}" password="${oracle.password}"
 host="${oracle.host}" port="${oracle.port}"
 responsibilityName="${oracle.responsibilityName}"
 responsibilityApplName="${oracle.responsibilityApplName}"
 doc:name="OracleEBS: ConnectionManagement">

Parameter

Description

Name

Enter a name for the configuration with which it can be referenced later.

Username

Enter a username to log in to Oracle EBS.

Password

Enter the password for the username.

Host

Enter the URL of the Oracle EBS instance.

Port

Enter the port of the Oracle EBS instance.

Responsibility Name

Enter the responsibility name that is needed to execute the operation.

Responsibility Appl. Name

Enter the application short name that is needed to execute the operation.

Org. ID

Enter the organization id of the Oracle EBS instance (optional).

Using the Connector

Oracle EBS connector is a operation based connector, which means that when you add the connector to your flow, you need to configure a specific web service for the connector to perform. After you select the web service, you can use the Type field to select a method that you want to execute.

Use Cases

The following is a common use case for the Oracle EBS connector:

Execute a custom PL/SQL operation from the custom PL/SQL web service.

Adding to a Flow

Create a new Mule project in Anypoint Studio.
Add a suitable Mule Inbound endpoint, such as the HTTP listener or File endpoint, to begin the flow.
Drag the Oracle EBS connector onto the canvas, then select it to open the properties editor.

Configure the connector’s parameters according to the table below.

Field

Description

Display Name

Enter a unique label for the connector in your application.

Connector Configuration

Connect to a global element linked to this connector. Global elements encapsulate reusable data about the connection to the target resource or service. Select the global Oracle EBS connector element that you just created.

Operation

Select operation from the drop-down. You can select any of the operations displayed or the Invoke PL/SQL operation, which allows you to execute a web service published from PL/SQL.

Params

Complete the parameters needed for the operation selected. If Invoke PL/SQL operation is selected, you need to select the Type parameter with a PL/SQL operation to be executed.

Save your configurations.
If you select the Invoke PL/SQL operation, you need to add a DataMapper transformer to map the values from the flow to the input parameters for the PL/SQL operation.

Example Use Case

Execute a custom PL/SQL operation from the custom PL/SQL web service.

Create a Mule project in your Anypoint Studio.
Drag an HTTP connector into the canvas, then select it to open the properties editor console.
Add a new HTTP Listener Configuration global element:
1. In General Settings, click the plus button:
2. Configure the following HTTP parameters:
  
  Field Value
  
  Port
  
  8090
  
  Host
  
  localhost
  
  Display Name
  
  HTTP_Listener_Configuration
3. Reference the HTTP Listener Configuration global element:

Field	Value
Port	8090
Host	localhost
Display Name	HTTP_Listener_Configuration

Drag the Oracle EBS connector, then configure it according to the steps below:

Add a new Oracle EBS Global Element by clicking the plus sign next to the Connector Configuration field.

Configure the global element according to the table below:

Field

Description

Name

Enter a name you prefer

Username

Enter your Oracle EBS username

Password

Enter your Oracle EBS password

Host

Enter the URL of your Oracle EBS instance.

Port

Enter the port of your Oracle EBS instance.

Responsibility Name

Enter the responsibility name that is needed to execute the operation.

Responsibility Appl. Name

Enter the application short name that is needed to execute the operation.

Security Group Name

Enter the security group key of your Oracle EBS instance.

NLS language

Enter the NLS language of your Oracle EBS instance (optional)

Org. ID

Enter the organization id of your Oracle EBS instance (optional).

Custom PL/SQL web services

Select Create Object manually, click the button on the right and add all custom PL/SQL web services you want to execute. In the example we use FND_WEB_SEC.

Click Test Connection to confirm that Mule can connect with the Oracle EBS instance. If the connection is successful, click OK to save the configurations. If unsuccessful, revise or correct any incorrect parameters, then test again.

Back in the properties editor of the Oracle EBS connector, configure the remaining parameters according to the table below:

Field

Value

Display Name

Testing custom PL/SQL operation (or any other name you prefer)

Connector configuration

Oracle (Enter name of the global element you have created)

Operation

Invoke PL/SQL

Type

Web Sec – Validate Login (custom)

Input reference

From Message #[payload]

Add a DataMapper transformer between the HTTP endpoint and the Oracle EBS connector to map the data in the HTTP endpoint to the structure required by the Oracle EBS connector.
Configure the Input properties of the DataMapper according to the steps below.
1. In the Source field, select Inbound Property - http.query.params and click on the (Edit) button located to the right of the Type drop down list.
2. In the Type field, select Map (k,v)
3. Select User Defined and then click the Create/Edit Structure button:
  1. Add a name to the structure. In the example it is queryParams.
  2. Click the button and add a name to the field_0. In the example it is user.
  3. Click the button again and add a name to the field_1. In the example it is password.
  4. Do the mapping from your definition to the input parameters for the operation selected.
Add a DOM to XML transformer after the Oracle EBS Connector.
Add a Logger scope right after the DOM to XML Transformer, to print the data that is being received from the Oracle EBS connector in the Mule Console. Configure the Logger according to the table below:

Field

Value

Display Name

Logger (or any other name you prefer)

Message

Output from Transformer is # EBS Test #[payload]

Level

INFO (Default)
Add a Catch Exception Strategy and add a logger component inside it. Configure the logger message attribute with [payload], and set the level to ERROR.

Field

Value

Display Name

Logger (or any other name you prefer)

Message

Error: #[payload]

Level

ERROR
Save and run the project as a Mule Application.

XML Editor

Add an oracle:config global element to your project, then configure its attributes as follows:

<oracle-ebs:config-name name="OracleEBS__ConnectionManagement" username="${oracle.username}" password="${oracle.password}" host="${oracle.host}" port="${oracle.port}" responsibilityName="${oracle.responsibilityName}" responsibilityApplName="${oracle.responsibilityApplName}" doc:name="OracleEBS: ConnectionManagement">

Configure your Oracle EBS custom PL/SQL web services in the Mule application. To do so, find the <oracle-ebs:custom-pl-sql-name-list> internal tag and replace it with the following:
```
<oracle-ebs:custom-pl-sql-name-list>
            <oracle-ebs:custom-pl-sql-name-list>FND_WEB_SEC</oracle-ebs:custom-pl-sql-name-list>
    </oracle-ebs:custom-pl-sql-name-list>
```

Begin the flow with a HTTP endpoint, configuring the endpoint as follows:

<http:listener config-ref="HTTP_Listener_Configuration" path="/get" allowedMethods="GET" doc:name="HTTP"/>

Add a Data Mapper to the flow to map the data in the input parameters for the custom PL/SQL operation.

<data-mapper:transform config-ref="Map_To_Xml_InputParameters_"
          input-ref="#[message.inboundProperties[&quot;http.query.params&quot;]]"
          doc:name="Map To Xml&lt;InputParameters&gt;"/>

Add the oracle:invoke-operation element now to validate login in your Oracle EBS instance.

<oracle-ebs:invoke-p-l-s-q-l config-ref="OracleEBS" doc:name="Testing custom PL/SQL operation" type="FND_WEB_SEC#VALIDATE_LOGIN"/>

Add a DOM to XML Transformer.

<mulexml:dom-to-xml-transformer doc:name="DOM to XML"/>

Add a logger component and set message attribute value to # EBS Test #[payload]
```
<logger message="### EBS Test #[payload]" level="INFO" doc:name="Logger"/>
```

Add a Catch Exception Strategy and add logger inside it. Configure the logger message attribute with # [payload], and set the level to ERROR.

<catch-exception-strategy doc:name="Catch Exception Strategy">
  <logger message="Error: #[payload]" level="ERROR" doc:name="Logger"/>
</catch-exception-strategy>

Save and run the project as a Mule Application.

Example Code

<mule xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" xmlns:data-mapper="http://www.mulesoft.org/schema/mule/ee/data-mapper" xmlns:oracle-ebs="http://www.mulesoft.org/schema/mule/oracle-ebs" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core
http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http
http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/oracle-ebs http://www.mulesoft.org/schema/mule/oracle-ebs/current/mule-oracle-ebs.xsd
http://www.mulesoft.org/schema/mule/ee/data-mapper http://www.mulesoft.org/schema/mule/ee/data-mapper/current/mule-data-mapper.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.xsd">
    <http:listener-config name="HTTP_Listener_Configuration" host="localhost" port="8081" doc:name="HTTP Listener Configuration"/>
    <oracle-ebs:config-name name="OracleEBS__ConnectionManagement" username="${oracle.username}" password="${oracle.password}" host="${oracle.host}" port="${oracle.port}" responsibilityName="${oracle.responsibilityName}" responsibilityApplName="${oracle.responsibilityApplName}" doc:name="OracleEBS: ConnectionManagement">
        <oracle-ebs:custom-pl-sql-name-list>
            <oracle-ebs:custom-pl-sql-name-list>FND_WEB_SEC</oracle-ebs:custom-pl-sql-name-list>
        </oracle-ebs:custom-pl-sql-name-list>
    </oracle-ebs:config-name>
    <data-mapper:config name="Map_To_Xml_InputParameters_" transformationGraphPath="map_to_xml_inputparameters_.grf" doc:name="Map_To_Xml_InputParameters_"/>
    <flow name="oracle-ebs-exampleFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
        <data-mapper:transform config-ref="Map_To_Xml_InputParameters_" input-ref="#[message.inboundProperties[&quot;http.query.params&quot;]]" doc:name="Map To Xml&lt;InputParameters&gt;"/>
        <oracle-ebs:invoke-p-l-s-q-l config-ref="OracleEBS__ConnectionManagement" type="FND_WEB_SEC#VALIDATE_LOGIN" doc:name="OracleEBS"/>
        <mulexml:dom-to-xml-transformer doc:name="DOM to XML"/>
        <logger message="### EBS Test #[payload]" level="INFO" doc:name="Logger"/>
    </flow>
    <catch-exception-strategy name="oracle-ebs-exampleCatch_Exception_Strategy">
        <logger message="#[payload]" level="ERROR" doc:name="Logger"/>
    </catch-exception-strategy>
</mule>

Magic Quadrant Leader

Whitepaper

Training course

CIOs Unfiltered

Magic Quadrant Leader

Whitepaper

Training course

CIOs Unfiltered

Oracle E-Business Suite Connector

Prerequisites

Compatibility

Connector Architecture

Installing and Configuring

Installing

Creating a New Project

Configuring a Global Element

XML Editor

Using the Connector

Use Cases

Adding to a Flow

Example Use Case

XML Editor

Example Code

See Also

Magic Quadrant Leader

Whitepaper

Training course

CIOs Unfiltered

Dreamforce to You

Magic Quadrant Leader

Whitepaper

Training course

CIOs Unfiltered

Dreamforce to You

Oracle E-Business Suite Connector

Prerequisites

Compatibility

Connector Architecture

Installing and Configuring

Installing

Creating a New Project

Configuring a Global Element

XML Editor

Using the Connector

Use Cases

Adding to a Flow

Example Use Case

XML Editor

Example Code

See Also