Contact Free trial Login

Oracle E-Business Suite 12.1 Connector - Mule 4

Support Category: Premium

Oracle EBS 12.1 Connector Version 5.1

Anypoint Connector for Oracle E-Business Suite (Oracle EBS Connector) provides real-time connectivity to query and update Oracle EBS using web services and message queues. The connector has been developed for the Oracle EBS v12.1.x.

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

  • XML Gateway: Inbound and Outbound, to send and receive message to and from EBS

  • Business Events: The ability to receive messages when an event is fired in EBS, via the WF_JMS_JMS_OUT topic

Prerequisites

To use this information, you should be familiar with Oracle EBS, Mule runtime engine (Mule), Anypoint Connectors, Anypoint Studio, Mule concepts, elements in a Mule flow, and Global Elements.

You need login credentials to test your connection to your target resource.

To use this connector with Maven, view the pom.xml dependency information in the Dependency Snippets in Anypoint Exchange.

POM File Information

<dependency>
  <groupId>org.mule.connectors</groupId>
  <artifactId>mule-oracle-ebs-connector</artifactId>
  <version>RELEASE</version>
  <classifier>mule-plugin</classifier>
</dependency>

Mule converts RELEASE to the latest version. To specify a version, view Oracle E-Business Suite (EBS) 12.1 Connector in Anypoint Exchange and click Dependency Snippets.

Connector Architecture

Oracle EBS offers different technologies or products to address various types of integrations. The most widely used technologies for integration with enterprise applications includes:

  1. Data-centric integration

  2. Event-driven integration using Oracle Business Event System and XML Gateway queues

  3. Integration through Web Services using Oracle E-Business Suite Integrated SOA Gateway (ISG)

Oracle EBS Connector Architecture

Web Service Endpoints

The supported Oracle Web Service endpoints include the following:

  • Receivables

    • DQM Search Service (/oracle/apps/ar/hz/service/party/DqmSearchService)

      • Find Parties

    • Email Business Object Services (/oracle/apps/ar/hz/service/party/EmailService)

      • Create E-mail

      • Get E-mail

      • Save E-mail

      • Update E-mail

    • Location Business Object Services (/oracle/apps/ar/hz/service/party/LocationService)

      • Create Location

      • Get Location

      • Save Location

      • Update Location

    • Organization Business Object Services (/oracle/apps/ar/hz/service/party/OrganizationService)

      • Create Organization

      • Get Organization

      • Get Organizations Created

      • Get Organizations Updated

      • Save Organization

      • Update Organization

    • Organization Contact Business Object Services (/oracle/apps/ar/hz/service/party/OrgContactService)

      • Create Organization Contact

      • Get Organization Contact

      • Save Organization Contact

      • Update Organization Contact

    • Organization Customer Business Object Services (/oracle/apps/ar/hz/service/account/OrgCustomerService)

      • Create Organization Customer

      • Get Organization Customer

      • Get Organization Customers Created

      • Get Organization Customers Updated

      • Save Organization Customer

      • Update Organization Customer

    • Party Site Business Object Services (/oracle/apps/ar/hz/service/party/PartySiteService)

      • Create Party Site

      • Get Party Site

      • Save Party Site

      • Update Party Site

    • Person Business Object Services (/oracle/apps/ar/hz/service/party/PersonService)

      • Create Person

      • Get Person

      • Get Persons Created

      • Get Persons Updated

      • Save Person

      • Update Person

    • Person Customer Business Object Services (/oracle/apps/ar/hz/service/account/PersonCustomerService)

      • Create Person Customer

      • Get Person Customer

      • Get Person Customers Created

      • Get Person Customers Updated

      • Save Person Customer

      • Update Person Customer

    • Phone Business Object Services (/oracle/apps/ar/hz/service/party/PhoneService)

      • Create Phone

      • Get Phone

      • Save Phone

      • Update Phone

    • Relationship Business Object Services (/oracle/apps/ar/hz/service/party/RelationshipService)

      • Create Relationship

      • Get Relationship

      • Save Relationship

      • Update Relationship

    • Web Business Object Services (/oracle/apps/ar/hz/service/party/WebService)

      • Create Web

      • Get Web

      • Save Web

      • Update Web

For PL/SQL functions, the connector supports the following out of the box, along with custom functions:

  • Install Base

    • Manage Item Instances (CSI_ITEM_INSTANCE_PUB)

      • Copy Item Instance

      • Create Item Instance

      • Get Item Instances

      • Get Item Instance Details

      • Update Item Instance

  • Order Management

    • Process Order API (OE_ORDER_PUB)

      • Delete Line

      • Delete Order

      • Get Order

      • ID to Value

      • Lock Order

      • Process Header

      • Process Line

      • Process Order

      • Update Header

      • Update Line

      • Value to ID

    • Purchase Order Acknowledgments Extension Columns API (EC_POAO_EXT)

      • POAO Populate Ext Lev01

      • POAO Populate Ext Lev02

    • Purchase Order Change Acknowledgments Extension Columns API (EC_POCAO_EXT)

      • POCAO Populate Ext Lev01

      • POCAO Populate Ext Lev02

    • Sales Agreement API (OE_BLANKET_PUB)

      • Process Blanket

    • Ship Conformation (OE_SHIP_CONFIRMATION_PUB)

      • Ship Zero

  • Payables

    • Suppliers Package (AP_VENDOR_PUB_PKG)

      • Create Vendor

      • Create Vendor Contact

      • Create Vendor Site

  • Receivables

    • Invoice Creation (AR_INVOICE_API_PUB)

      • Create Invoice

      • Create Single Invoice

  • Trading Community

    • Location (HZ_LOCATION_V2PUB)

      • Create Location

      • Create Location 1

      • Update Location

      • Update Location 1

    • Party Contact (HZ_PARTY_CONTACT_V2PUB)

      • Create Org Contact

      • Create Org Contact Role

      • Update Org Contact

      • Update Org Contact Role

Connect in Design Center

  1. Select a trigger. You can use this connector as the trigger, or an HTTP Listener or Scheduler trigger. For JMS, you need to supply drivers that come with your Oracle software.

    The Oracle EBS Connector provides these operations to trigger an app when information arrives from Oracle EBS:

    • Business Events Source

    • XML Gateway Outbound Source

  2. In Flow Designer, click Click here to set it up, browse for, and select the driver for this connector on your file system, and upload it.
    Alternatively, search for and select a driver that is already uploaded.

  3. You can create different kinds of setups, depending on the operations you want to perform:

    • OracleEBS JMS

    • OracleEBS PL-SQL

    • OracleEBS Web Services

      1. For calling PL/SQL operations, create a PL/SQL Configuration:

        • Username: Username to call PL/SQL Web Services. Make sure it has the correct grants.

        • Password: Password for the username.

        • Host: Host of the Oracle EBS instance.

        • Port: Port of the Oracle EBS Web Services.

        • SSL: If enabled, will make calls using HTTPS instead of HTTP.

        • Responsibility Name: The responsibility name that is needed to execute the operation.

        • Responsibility Application Name: The application short name that is needed to execute the operation.

        • Security Group Name: The security group key of the Oracle EBS instance (optional). Default value is STANDARD.

        • NLS Language: The NLS language of the Oracle EBS instance (optional). Default value is AMERICAN.

        • Org ID: The organization ID of the Oracle EBS instance (optional). Default value is 204.

      2. If you wish to call other types of web services, create a Web Services Configuration:

        • Username: Username to call PL/SQL web services. Make sure it has the correct grants.

        • Password: Password for the username.

        • Host: Host of the Oracle EBS instance.

        • Port: Port of the Oracle EBS web services.

        • SSL: If enabled, will make calls using HTTPS instead of HTTP.

        • TrustStore file name: The custom Trust Store file for HTTPS calls (optional).

        • TrustStore password: The password for the TrustStore (optional).

        • Disable Common Name Checking: Disables common name (CN) checking on SSL certificates (optional).

        • Responsibility Name: The responsibility name that is needed to execute the operation.

        • Responsibility Application Name: The application short name that is needed to execute the operation.

        • Security Group Name: The security group key of the Oracle EBS instance (optional). Default value is STANDARD.

        • NLS Language: The NLS language of the Oracle EBS instance (optional). Default value is AMERICAN.

        • Org ID: The organization ID of the Oracle EBS instance (optional). Default value is 204.

      3. To use the connector to subscribe to Business Events or to the XML Gateway queue, create a JMS configuration. This one supports two connection types: one where you set the database URL as a whole, and one to define it as individual parameters:

        • Username: Username for the Oracle EBS database.

        • Password: The password for the username.

        • URL: The URL of the Oracle EBS database.

        • Read Receive Timeout: The timeout value (in milliseconds) - 30000 in this example.

        • Username: Username for the Oracle EBS database.

        • Password: The password for the username.

        • Host: The host of the Oracle EBS database.

        • Port: The port of the Oracle EBS database. This example uses port 1521.

        • Database SID: The SID of the database.

  4. Select the plus sign to add a component.

  5. Select the connector as a component.

    Select connector as a Design Center Component
  6. Pick an operation, for example, Get Party Site:

  7. Configure the fields:

    1. Id: TCA identifier for the Party site business object.

    2. Orig Sys: Party site original system name.

    3. Orig Sys Ref: Party site original system reference.

Add Libraries for This Connector

To subscribe to business events or to the XML Gateway queue, the JMS Configuration requires two external libraries:

  1. Go to the connector’s JMS configuration.

  2. Click Set Up under the message to setup drivers.

  3. Upload and select your libraries.

    1. Oracle AQ API: Can be retrieved from the EBS instance, at $ORACLE_HOME/rdbms/jlib/aqapi.jar.

    2. Oracle Database JDBC Driver: Can also be found in the instance, at $ORACLE_HOME/jdbc/lib/ojdbc6.jar.

      OracleEBS JMS Drivers for the Oracle JDBC driver and AQ API

Add the Connector to a Studio Project

Anypoint Studio provides two ways to add the connector to your Studio project:

  • From the Exchange button in the Studio taskbar

  • From the Mule Palette view

Add the Connector Using Exchange

  1. In Studio, create a Mule project.

  2. Click the Exchange icon (X) in the upper-left of the Studio task bar.

  3. In Exchange, click Login and supply your Anypoint Platform username and password.

  4. In Exchange, search for "ebs".

  5. Select the connector and click Add to project.

  6. Follow the prompts to install the connector.

Add the Connector in Studio

  1. In Studio, create a Mule project.

  2. In the Mule Palette view, click (X) Search in Exchange.

  3. In Add Modules to Project, type "ebs" in the search field.

  4. Click this connector’s name in Available modules.

  5. Click Add.

  6. Click Finish.

Configure in Studio

  1. Drag a connector’s operation to the Studio Canvas.

  2. Configure the example Get Party Site:

    1. Id: TCA identifier for the Party Site business object.

    2. Orig Sys: Party Site original system name.

    3. Orig Sys Ref: Party Site original system reference.

Get Party Site operation configuration

Log PSQL and Web Service Requests and Responses

To log PSQL and Web Service requests and responses when using the connector, configure a logger:

  • For PSQL requests and responses, add this line to the Loggers element of the log4j2.xml configuration file for the Mule app:

    <AsyncLogger name=name="org.mule.service.http.impl.service.HttpMessageLogger"
       level="DEBUG"/>
  • For web service requests and responses, add this line to the Loggers element of the log4j2.xml configuration file for the Mule app:

    <AsyncLogger name="org.apache.cxf.service" level="INFO"/>

    The following example shows the Loggers element with the AsyncLogger line configured to log web service requests and responses:

    <?xml version="1.0" encoding="UTF-8"?>
    <Configuration status="WARN">
        <Appenders>
            <Console name="Console" target="SYSTEM_OUT">
                <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
            </Console>
        </Appenders>
        <Loggers>
            <AsyncLogger name="org.apache.cxf.service" level="INFO" additivity="false">
                <appender-ref ref="Console" level="debug"/>
            </AsyncLogger>
        </Loggers>
    </Configuration>

Use Case: Call and Log an Operation

This sample flow calls an operation every 30 seconds and logs its output.

  1. Drag a Scheduler and set the frequency to 30 seconds.

  2. Select Oracle EBS > Get Party Site from the Mule Palette view and drag it to the flow.

  3. Configure its input.

  4. Drag a Logger and set its message to #[payload].

Studio flow with Scheduler

Use Case: XML

Here is the code for the aforementioned example:

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

<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
	xmlns:oracle-ebs="http://www.mulesoft.org/schema/mule/oracle-ebs"
	xmlns="http://www.mulesoft.org/schema/mule/core"
	xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	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
http://www.mulesoft.org/schema/mule/ee/core
http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">
	<oracle-ebs:web-services-config
		name="OracleEBS_Web_Services"
		doc:name="OracleEBS Web Services" >
		<oracle-ebs:web-services-connection
			username="${username}"
			password="${password}"
			host="${host}"
			responsibilityName="${responsibilityName}"
			responsibilityApplicationName="${responsibilityApplicationName}" />
	</oracle-ebs:web-services-config>
	<flow name="ebs_testFlow" >
		<scheduler doc:name="Scheduler" >
			<scheduling-strategy >
				<fixed-frequency frequency="30" timeUnit="SECONDS"/>
			</scheduling-strategy>
		</scheduler>
		<oracle-ebs:get-party-site
			doc:name="Get party site"
			config-ref="OracleEBS_Web_Services"
			id="1000"/>
		<logger level="INFO" doc:name="Logger" message="#[payload]"/>
	</flow>
</mule>