Oracle E-Business Suite 12.2 Connector

Premium

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

Separately, there is a Oracle E-Business Suite (EBS) connector built for the Oracle EBS v12.1.x that supports SOAP web services and message queues.

Read through this user guide to understand how to set up and configure a basic flow using the connector. Track feature additions, compatibility, limitations and API version updates with each release of the connector using the Connector Release Notes. Review the demo applications.

MuleSoft maintains this connector under the Premium support policy.

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.
Integration through Web Services using Integrated SOA gateway.

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

Oracle EBS 12.2 - Connector Architecture

For further information, refer to Oracle E-Business Suite Documentation Web Library.

Before You Begin

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.

Hardware and Software Requirements

For hardware and software requirements, please visit the Hardware and Software Requirements page.

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

Web service configuration in Oracle EBS: Configuring the Oracle EBS connector requires configuring REST 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.
REST Service Locator: The connector requires Oracle EBS to have the REST Service Locator deployed as a REST service.
WADL names in lower case: WADLs corresponding to deployed PL/SQLs must have their Internal Names in lower case as alias. For example, if you wish to deploy PLSQL Interface : Absence Attendance Type, which has HR_ABSENCE_TYPE_API as internal name, its Service Alias should be hr_absence_type_api.
Anypoint Studio

Compatibility

Software	Version
Mule Runtime	3.7
Oracle EBS	12.2.x
Java	7

Software

Version

Mule Runtime

3.7

Oracle EBS

12.2.x

Java

This connector only supports Oracle EBS version 12.2.x. If you require integration with version 12.1.x, refer to Oracle E-Business Suite (EBS) connector.

To Install this Connector

In Anypoint Studio, click the Exchange icon in the Studio taskbar.
Click Login in Anypoint Exchange.
Search for the connector and click Install.
Follow the prompts to install the connector.

When Studio has an update, a message displays in the lower right corner, which you can click to install the update.

Before Using the Connector

Deploy the REST Service Locator

This is the very first task you need to accomplish in order to later expose your PL/SQL services for the connector to consume. The following steps were taken from Oracle’s Developer’s Guide tutorial Using Java APIs as REST Services:

Log in to Oracle E-Business Suite as a user who has the Integration Administrator role.
- Select the Integrated SOA Gateway responsibility and the Integration Repository link from the navigation menu.
In the Integration Repository tab, click Search to access the main Search page.
Click Show More Search Options to display more search fields.
- Enter the following key search values as the search criteria:
  - Category: Interface Subtype
  - Category Value: Java Bean Services
Click Go to execute the search.
- Click the REST Service Locator interface name link to open the interface details page.

In the REST Web Service tab, enter the following information:

Oracle EBS 12.2 - REST Service Locator Un-deployed

Service Alias: restServiceLocator
- The alias will be displayed as the service endpoint in the WADL and schema for the selected method or operation.

Select Desired Service Operations

In the Service Operations region, HTTP method check boxes are preselected.

If a Java method is annotated with a specific HTTP method, then the corresponding HTTP method check box is preselected for that method. The administrator can change the HTTP method check box selection before deploying the service. In this example, the 'getRestInterface' service operation has been annotated with the GET HTTP method; therefore, the GET check box is automatically selected.

Click Deploy to deploy the service to an Oracle E-Business Suite WebLogic environment.
- Once the REST service has been successfully deployed, 'Deployed' appears in the REST Service Status field along with the View WADL link. Click the View WADL link to view the deployed service WADL description.

Deploy a PL/SQL API as a REST Service (WADL)

The following steps describe the procedure to deploy the PL/SQL "Adjustment API" as a REST service. The same guide applies to any PL/SQL.

Log in to Oracle E-Business Suite as a user who has the Integration Administrator role.
- Select the Integrated SOA Gateway responsibility and the Integration Repository link from the navigation menu.
In the Integration Repository tab, click Search to access the main Search page.
Click Show More Search Options to display more search fields.
- Enter the following key search values as the search criteria:
  - Category: Interface Subtype
  - Category Value: PL/SQL
  - Internal Name: FA_ADJUSTMENT_PUB
Click Go to execute the search.
- Click on the Adjustments API link to see the interface details.
  - Click the REST Service Locator interface name link to open the interface details page.

In the REST Web Service tab, enter the following information:

Deploy PL/SQL - Adjustment API Configuration

The alias of the deployed web service MUST be: 1) the Internal Name and 2) be written in lowercase. The alias information can be found under the label Service Alias. In this example, it is fa_adjustment_pub.

Click Deploy to deploy the service to an Oracle E-Business Suite WebLogic environment.
- Once the REST service has been successfully deployed, 'Deployed' appears in the REST Service Status field along with the View WADL link. Click the View WADL link to view the deployed service WADL description.

Configuring the Connector Global Element

To use the Oracle EBS 12.2 connector in your Mule application, configure a global Oracle EBS 12.2 element that can be used by all the Oracle EBS 12.2 connectors in the application. The connector offers the following global configuration:

Configuration: for web services and PL/SQL invocation.

Follow the steps below to create an Oracle EBS 12.2 global element for web services and PL/SQL invocation:

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 12.2: Configuration.

Then, fill in the following arguments:

Field Description

Field	Description
Host	Enter the host of the Oracle EBS instance.
Port	Enter the port of the Oracle EBS web services.
SSL enabled	If checked, will attempt to make web services calls using HTTPS instead of HTTP.
Username	Username to log into Oracle EBS web services.
Password	Password for the username.
Rest Service Locator alias	Name assigned to the web service with Internal Name `oracle.apps.fnd.rep.ws.service.EbsRestLocator` when deployed.
FND Web Sec alias	Name assigned to the PL/SQL web service with Internal Name `FND_WEB_SEC` when deployed.
Responsibility name	Enter the responsibility name that is needed to execute the operation.
Responsibility application 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).

Host

Enter the host of the Oracle EBS instance.

Port

Enter the port of the Oracle EBS web services.

SSL enabled

If checked, will attempt to make web services calls using HTTPS instead of HTTP.

Username

Username to log into Oracle EBS web services.

Password

Password for the username.

Rest Service Locator alias

Name assigned to the web service with Internal Name oracle.apps.fnd.rep.ws.service.EbsRestLocator when deployed.

FND Web Sec alias

Name assigned to the PL/SQL web service with Internal Name FND_WEB_SEC when deployed.

Responsibility name

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

Responsibility application 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).

Using the Connector

Oracle EBS 12.2 connector is and operation-based connector that supports the invocation of any PL/SQL REST service deployed in the Oracle system.

Connector Namespace and Schema

When designing your application in Studio, the act of dragging the connector from the palette onto the Anypoint Studio canvas should automatically populate the XML code with the connector namespace and schema location.

If you are manually coding the Mule application in Studio’s XML editor or other text editor, define the namespace and schema location in the header of your Configuration XML, inside the <mule> tag.

<mule xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:oracle-ebs122="http://www.mulesoft.org/schema/mule/oracle-ebs122"
      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-ebs122
               http://www.mulesoft.org/schema/mule/oracle-ebs122/current/mule-oracle-ebs122.xsd">

      <!-- put your global configuration elements and flows here -->

</mule>

Using the Connector in a Mavenized Mule App

If you are coding a Mavenized Mule application, this XML snippet must be included in your pom.xml file.

<dependency>
  <groupId>org.mule.modules</groupId>
  <artifactId>oracle-ebs-122-connector</artifactId>
  <version>x.x.x</version>
</dependency>

Replace x.x.x with the version that corresponds to the connector you are using.

To obtain the most up-to-date pom.xml file information, access the connector in Anypoint Exchange and click Dependency Snippets.

Demo Mule Applications Using Connector

You can download fully functional demo applications using the Oracle EBS 12.2 connector from this link.

Example Use Case

The current section describes 4 four common use cases related to the HR Location service. To create the Mule app that will contain these examples, follow the steps below:

Create a new Mule Project in Anypoint Studio.

Set your OracleEBS 12.2 credentials in src/main/resources/mule-app.properties.

oracle122.username=
oracle122.password=
oracle122.host=
oracle122.port=
oracle122.responsibility=
oracle122.restServiceLocatorAlias=
oracle122.fndWebSecAlias=
oracle122.respApplication=
oracle122.securityGroup=
oracle122.nlsLanguage=
oracle122.orgId=

Create a new OracleEBS 12.2 global element configuration and fill in the credentials using placholders:

<oracle-ebs122:config name="OracleEBS_12_2__Configuration"
    host="${oracle122.host}"
    port="${oracle122.port}"
    username="${oracle122.username}"
    password="${oracle122.password}"
    restServiceLocatorAlias="${oracle122.restServiceLocatorAlias}"
    fndWebSecAlias="${oracle122.fndWebSecAlias}"
    responsibility="${oracle122.responsibility}"
    respApplication="${oracle122.respApplication}"
    securityGroup="${oracle122.securityGroup}"
    nlsLanguage="${oracle122.nlsLanguage}"
    orgId="${oracle122.orgId}"
    doc:name="OracleEBS 12.2: Configuration"/>

Click Test Connection to confirm that Mule can connect with the Oracle 12.2 instance. If the connection is successful, click OK to save the configuration. Otherwise, review or correct any invalid parameters and test again.
Create a new HTTP Listener global element configuration and leave it with the default values.

Create a Location

Drag a HTTP endpoint onto the canvas and configure the following parameters:

Parameter Value

Connector Configuration

HTTP_Listener_Configuration

Path

/create
Drag a OracleEBS12.2 Connector component next to the HTTP endpoint and in the Connector Configuration field select the configuration created in the previous section.
Configure the processor with the following values:

Parameter Value

Operation

Invoke PL/SQL REST service

WADL

hr_location_api

Operation

CREATE_LOCATION

Payload Reference

#[payload]

Parameter	Value
Connector Configuration	HTTP_Listener_Configuration
Path	/create

Parameter	Value
Operation	Invoke PL/SQL REST service
WADL	hr_location_api
Operation	CREATE_LOCATION
Payload Reference	#[payload]

Drag a DataWeave component and set the following input parameters:

%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/create_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_EFFECTIVE_DATE: now,
		ns0#P_LOCATION_CODE: "HR- MuleSoft BA",
		ns0#P_DESCRIPTION: "Buenos Aires Office"
	}
}

Deploy the application, open a web browser and make a request to the URL http://localhost:8081/create.

If the location was successfully created, you should receive the following XML response containing the ID and Version Number of the new location:

<?xml version = '1.0' encoding = 'UTF-8'?>
<OutputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.oracle.com/apps/per/rest/hr_location_api/create_location/">
    <P_LOCATION_ID>27545</P_LOCATION_ID>
    <P_OBJECT_VERSION_NUMBER>1</P_OBJECT_VERSION_NUMBER>
</OutputParameters>

Get a Location

Drag a HTTP endpoint onto the canvas and configure the following parameters:

Parameter Value

Connector Configuration

HTTP_Listener_Configuration

Path

/get
Drag a OracleEBS12.2 Connector component next to the HTTP endpoint and in the Connector Configuration field select the configuration created in the previous section.
Configure the processor with the following values:

Parameter Value

Operation

Invoke PL/SQL REST service

WADL

hr_location_record

Operation

GET_LOCATION_DETAILS

Payload Reference

#[payload]

Parameter	Value
Connector Configuration	HTTP_Listener_Configuration
Path	/get

Parameter	Value
Operation	Invoke PL/SQL REST service
WADL	hr_location_record
Operation	GET_LOCATION_DETAILS
Payload Reference	#[payload]

Drag a DataWeave component and set the following input parameters:

%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_record/get_location_details/
---
{
	ns0#InputParameters: {
		ns0#P_QUERY_OPTIONS: {
			ns0#LOCATION_ID: "27545"
		}
	}
}

Deploy the application, open a web browser and make a request to the URL http://localhost:8081/get.

If the location exists, you should receive the following XML response containing the complete details of the location:

<?xml version = '1.0' encoding = 'UTF-8'?>
<OutputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.oracle.com/apps/per/rest/hr_location_record/get_location_details/">
    <P_LOCATIONS>
        <P_LOCATIONS_ITEM>
            <LOCATION_ID>27545</LOCATION_ID>
            <LOCATION_CODE>HR- Mule BA</LOCATION_CODE>
            <LOCATION_USE>HR</LOCATION_USE>
            <BUSINESS_GROUP_ID xsi:nil="true"/>
            <DESCRIPTION>Buenos Aires Office</DESCRIPTION>
            <SHIP_TO_LOCATION_ID>27545</SHIP_TO_LOCATION_ID>
            ...
        </P_LOCATIONS_ITEM>
    </P_LOCATIONS>
</OutputParameters>

Update a Location

Drag a HTTP endpoint onto the canvas and configure the following parameters:

Parameter Value

Connector Configuration

HTTP_Listener_Configuration

Path

/update
Drag a OracleEBS12.2 Connector component next to the HTTP endpoint and in the Connector Configuration field select the configuration created in the previous section.
Configure the processor with the following values:

Parameter Value

Operation

Invoke PL/SQL REST service

WADL

hr_location_api

Operation

UPDATE_LOCATION

Payload Reference

#[payload]

Parameter	Value
Connector Configuration	HTTP_Listener_Configuration
Path	/update

Parameter	Value
Operation	Invoke PL/SQL REST service
WADL	hr_location_api
Operation	UPDATE_LOCATION
Payload Reference	#[payload]

Drag a DataWeave component and set the following input parameters:

%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/update_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_EFFECTIVE_DATE: now,
		ns0#P_LOCATION_ID: "27545",
		ns0#P_LOCATION_CODE: "HR- MuleSoft BA",
		ns0#P_DESCRIPTION: "Second MuleSoft's Buenos Aires Office",
		ns0#P_OBJECT_VERSION_NUMBER: 1
	}
}

Deploy the application, open a web browser and make a request to the URL http://localhost:8081/update.

If the location was successfully udated, you should receive the following XML response containing the new Version Number of the location:

<?xml version = '1.0' encoding = 'UTF-8'?>
<OutputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.oracle.com/apps/per/rest/hr_location_api/update_location/">
    <P_OBJECT_VERSION_NUMBER>2</P_OBJECT_VERSION_NUMBER>
</OutputParameters>

Delete a Location

Drag a HTTP endpoint onto the canvas and configure the following parameters:

Parameter Value

Connector Configuration

HTTP_Listener_Configuration

Path

/delete
Drag a OracleEBS12.2 Connector component next to the HTTP endpoint and in the Connector Configuration field select the configuration created in the previous section.
Configure the processor with the following values:

Parameter Value

Operation

Invoke PL/SQL REST service

WADL

hr_location_api

Operation

DELETE_LOCATION

Payload Reference

#[payload]

Parameter	Value
Connector Configuration	HTTP_Listener_Configuration
Path	/delete

Parameter	Value
Operation	Invoke PL/SQL REST service
WADL	hr_location_api
Operation	DELETE_LOCATION
Payload Reference	#[payload]

Drag a DataWeave component and set the following input parameters:

%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/delete_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_LOCATION_ID: "27545",
		ns0#P_OBJECT_VERSION_NUMBER: 2
	}
}

Deploy the application, open a web browser and make a request to the URL http://localhost:8081/delete.
If the location was successfully deleted, you should receive an empty response.

Example Use Case - XML

Paste this into Anypoint Studio to interact with the example use case application discussed in this guide.

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:oracle-ebs122="http://www.mulesoft.org/schema/mule/oracle-ebs122" 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-ebs122 http://www.mulesoft.org/schema/mule/oracle-ebs122/current/mule-oracle-ebs122.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">
    <oracle-ebs122:config name="OracleEBS_12_2__Configuration" host="${oracle122.host}" port="${oracle122.port}" username="${oracle122.username}" password="${oracle122.password}" restServiceLocatorAlias="${oracle122.restServiceLocatorAlias}" fndWebSecAlias="${oracle122.fndWebSecAlias}" responsibility="${oracle122.responsibility}" respApplication="${oracle122.respApplication}" securityGroup="${oracle122.securityGroup}" nlsLanguage="${oracle122.nlsLanguage}" orgId="${oracle122.orgId}" doc:name="OracleEBS 12.2: Configuration"/>
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>

    <flow name="Create_HR_Location_Flow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/create" doc:name="HTTP"/>
        <dw:transform-message doc:name="Input params">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/create_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_EFFECTIVE_DATE: now,
		ns0#P_LOCATION_CODE: "HR- Mule BA",
		ns0#P_DESCRIPTION: "Buenos Aires Office"
	}
}]]></dw:set-payload>
        </dw:transform-message>
        <oracle-ebs122:invoke-pl-sql-rest-service config-ref="OracleEBS_12_2__Configuration" wadlOperation="hr_location_api||CREATE_LOCATION" doc:name="Create Location"/>
    </flow>
    <flow name="Get_HR_Location_Flow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/get" doc:name="HTTP"/>
        <dw:transform-message doc:name="Input params">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_record/get_location_details/
---
{
	ns0#InputParameters: {
		ns0#P_QUERY_OPTIONS: {
			ns0#LOCATION_ID: "27545"
		}
	}
}]]></dw:set-payload>
        </dw:transform-message>
        <oracle-ebs122:invoke-pl-sql-rest-service config-ref="OracleEBS_12_2__Configuration" wadlOperation="hr_location_record||GET_LOCATION_DETAILS" doc:name="Get Location"/>
    </flow>
    <flow name="Update_HR_Location_Flow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/update" doc:name="HTTP"/>
        <dw:transform-message doc:name="Input params">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/update_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_EFFECTIVE_DATE: now,
		ns0#P_LOCATION_ID: "27545",
		ns0#P_LOCATION_CODE: "HR- MuleSoft BA",
		ns0#P_DESCRIPTION: "Second MuleSoft's Buenos Aires Office",
		ns0#P_OBJECT_VERSION_NUMBER: 1
	}
}]]></dw:set-payload>
        </dw:transform-message>
        <oracle-ebs122:invoke-pl-sql-rest-service config-ref="OracleEBS_12_2__Configuration" wadlOperation="hr_location_api||UPDATE_LOCATION" doc:name="Update Location"/>
    </flow>
    <flow name="Delete_HR_Location_Flow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/delete" doc:name="HTTP"/>
        <dw:transform-message doc:name="Input params">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/xml
%namespace ns0 http://xmlns.oracle.com/apps/per/rest/hr_location_api/delete_location/
---
{
	ns0#InputParameters: {
		ns0#P_VALIDATE: 0,
		ns0#P_LOCATION_ID: "27545",
		ns0#P_OBJECT_VERSION_NUMBER: 2
	}
}]]></dw:set-payload>
        </dw:transform-message>
        <oracle-ebs122:invoke-pl-sql-rest-service config-ref="OracleEBS_12_2__Configuration" wadlOperation="hr_location_api||DELETE_LOCATION" doc:name="Delete Location"/>
    </flow>
</mule>

Resources

Access the Oracle E-Business Suite 12.2 Connector Release Notes.
Refer Oracle’s blog article A Primer on Oracle E-Business Suite REST Services.
Oracle E-Business Suite 12.2 Connector on Exchange