Oracle E-Business Suite Connector

[tab,title="Studio Visual Editor"]
....
Follow these steps to create an Oracle EBS 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: Configuration*.
+
image:oracle_ebs_connector_config.png[oracle_ebs_connector_config]
+
. Click *OK*.
. Configure the parameters according to the table below.
+
[width="100%",cols="50a,50a",options="header",]

|===
|Field|Description
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username to log into Oracle EBS web services.
|*Password* |Password for the username.
|*Host* |Enter the host of the Oracle EBS instance.
|*Port* |Enter the port of the Oracle EBS web services.
|*SSL* |If enabled, will attempt to make web services calls using HTTPS instead of HTTP.
|*TrustStore file name* |Enter the path to a custom TrustStore for HTTPS calls (optional).
|*TrustStore password* |Enter the password for the TrustStore (optional).
|*Disable Common Name Checking* |Disables Common Name (CN) Checking on SSL certificates (optional).
|*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*.
|===
+
image:oracle_ebs_global_element.png[oracle_ebs_global_element]
+
. Configure your custom web services list according to the steps below:
.. Select *Create Object manually* and then click the `...` button next to it.
+
image:create-object-1.png[create-object-1]
+
.. In the pop-up window, select the plus sign (*+*) to add more entries, as needed.
.. Right-click a metadata item and click *Edit the selected metadata field* to enter the values.
+
image:oracle_ebs_enter_metadata.png[oracle_ebs_enter_metadata]
+
.. Click *OK* to save the list.
+
. Click *Test Connection* and confirm receipt of the _Connection Successful_ message.
. Click *OK* to save the global connector configurations.
+
[IMPORTANT]
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.

....
[tab,title="XML Editor"]
....
Ensure you have included the Oracle EBS namespaces in your configuration file.

[source, xml, linenums]
----
<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
<!-- Add your flows and configuration elements here -->
</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.
+
[source, xml, linenums]
----
<oracle-ebs:config name="OracleEBS__Configuration" username="${username}" password="${password}" host="${host}" port="${port}" responsibilityName="${responsibilityName}" responsibilityApplName="${responsibilityApplName}" doc:name="OracleEBS: Configuration" disableCNCheck="true" sslEnabled="true" trustStorePassword="${tsPassword}" trustStorePath="TrustStore.ts"/>
----
+
[cols=","]
|===
|*Parameter* |*Description*
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username to log into Oracle EBS web services.
|*Password* |Password for the username.
|*Host* |Enter the host of the Oracle EBS instance.
|*Port* |Enter the port of the Oracle EBS web services.
|*SSL* |If enabled, will attempt to make web services calls using HTTPS instead of HTTP.
|*TrustStore file name* |Enter the path to a custom TrustStore for HTTPS calls (optional).
|*TrustStore password* |Enter the password for the TrustStore (optional).
|*Disable Common Name Checking* |Disables Common Name (CN) Checking on SSL certificates (optional).
|*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).
|*NLS language* |Enter the NLS language of the Oracle EBS instance (optional).
|*Org. ID* |Enter the organization ID of the Oracle EBS instance (optional).
|===

....

[tab,title="Studio Visual Editor"]
....
Follow these steps to create an Oracle EBS global element for Business Events or XML Gateway Outbound subscriptions:

. 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: JMS Configuration*.
+
image:oracle_ebs_connector_config.png[oracle_ebs_connector_config]
+
. Click *OK*.
. Configure the parameters according to the table below.
+
[width="100%",cols="50a,50a",options="header",]

|===
|Field|Description
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username for the Oracle EBS database.
|*Password* |Enter the password for the username.
|*Host* |Enter the host of the Oracle EBS database.
|*Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|===
+
image:oracle_ebs_jms_global_element.png[oracle_ebs_jms_global_element]
+
. From the same global element properties window, load the required external dependency JARs:
+
image:oracle-ebs-jar-deps.png[req deps]
+
.. Click on the *Modify* button next to *Oracle AQ API*, and select the JAR file corresponding to this library. You can retrieve this file from your EBS instance, at `$ORACLE_HOME/rdbms/jlib/aqapi.jar`.
.. Do the same for the *Oracle Database JDBC Driver*. This file can also be found in your instance, at the following path: `$ORACLE_HOME/jdbc/lib/ojdbc6.jar`.
+
. Click *Test Connection* and confirm receipt of the _Connection Successful_ message.
. Click *OK* to save the global connector configurations.
+


....
[tab,title="XML Editor"]
....
Ensure you have included the Oracle EBS namespaces in your configuration file.

[source, xml, linenums]
----
<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
<!-- Add your flows and configuration elements here -->
</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.
+
[source, xml, linenums]
----
<oracle-ebs:jmsconfig name="OracleEBS__JMS_Configuration" username="${username}" password="${password}" host="${host}" port="${port}" sid="${sidd}" doc:name="OracleEBS: JMS Configuration"/>
----
+
[cols=","]
|===
|*Parameter* |*Description*
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username for the Oracle EBS database.
|*Password* |Enter the password for the username.
|*Host* |Enter the host of the Oracle EBS database is located.
|*Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|===

....

  
1
2
3
4
5
6
7
8
9
10
11
12

  
<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">

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

</mule>

[tab,title="Studio Visual Editor"]
....
image:oracle-ebs-plsql-example.png[oracle-ebs-plsql-example]

. Create a Mule project in Anypoint Studio.
. Drag an HTTP connector onto the canvas, then select it to open the properties editor console.
. Add a new HTTP Listener Configuration global element:
.. In *General Settings*, click the plus sign (*+*):
+
image:connector-configuration-dropdown-general.png[connector-configuration-dropdown-general]
+
.. Configure the following HTTP parameters:
+
[width="100%",cols="50a,50a",options="header",]
|======
|Field|Value
|*Display Name* |HTTP_Listener_Configuration
|*Port* |8081
|======
+
.. Reference the HTTP Listener Configuration global element by selecting the configuration created in the preview step, from the *Connector Configuration* dropdown:
. Drag the Oracle EBS connector onto the canvas, then configure it according to the steps below:
.. Add a new "OracleEBS: Configuration" Global Element by clicking the plus sign (*+*).
+
.. Configure the global element according to the table below:
+
[width="100%",cols="50a,50a",options="header",]
|=======
|Field|Description
|*Name* |Enter a name you prefer
|*Username* |Username to log into Oracle EBS web services.
|*Password* |Password for the username.
|*Host* |Enter the host of the Oracle EBS instance.
|*Port* |Enter the port of the Oracle EBS web services.
|*SSL* |If enabled, will attempt to make web services calls using HTTPS instead of HTTP.
|*TrustStore file name* |Enter the path to a custom TrustStore for HTTPS calls (optional).
|*TrustStore password* |Enter the password for the TrustStore (optional).
|*Disable Common Name Checking* |Disables Common Name (CN) Checking on SSL certificates (optional).
|*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).
|*NLS language* |Enter the NLS language of the Oracle EBS instance (optional).
|*Org. ID* |Enter the organization ID of the 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:
+
[cols=",",]
|=====
|*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
|*PL/SQL* |Fnd Web Sec (custom)
|*Function* |Validate Login
|*Input Reference* |From Message `#[payload]`
|=====
+
image:oracle_ebs_connector_operation_config.png[oracle_ebs_connector_operation_config]
. Add a *Transform Message* 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 *Transform Message* component according to the steps below.
+
.. On the left, under the *Inbound Properties* tree, click on *http.query.params* and then click on the *Edit* button.
Enter the following code:
+
----
%dw 1.0
%output application/java
---
{
	"user": "sampleUser",
	"password": "samplePassword"
}
----
+
.. Enter the following DataWeave code into the *Transform Message* text editor:
+
----
%dw 1.0
%output application/xml
%namespace ns0
http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_web_sec/validate_login/
---
{
	ns0#InputParameters: {
		ns0#"P_USER": inboundProperties."http.query.params".user,
		ns0#"P_PWD": inboundProperties."http.query.params".password
	}
}
----
+
. 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:
+
[width="100%",cols="50a,50a",options="header"]
|===
|*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.
+
[width="100%",cols="50a,50a",options="header"]
|===
|*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 by right-clicking the project name in the Package Explorer, selecting *Run As* > *Mule Application*.
.. Open a browser and load the following URL: `http://localhost:8081/?user=<username>&password=<password>`, replacing the _<username>_ and _<password>_ placeholders with a set of credentials.
.. You should see the response of the FND_WEB_SEC PL/SQL operation, informing you if the provided credentials are valid or invalid.

....
[tab,title="XML Editor"]
....

=== Example Code

[source, xml, linenums]
----
<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:oracle-ebs="http://www.mulesoft.org/schema/mule/oracle-ebs" xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" 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/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.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/oracle-ebs http://www.mulesoft.org/schema/mule/oracle-ebs/current/mule-oracle-ebs.xsd">
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>
    <oracle-ebs:config name="OracleEBS__Configuration" username="${username}" password="${password}" host="${host}" port="${port}" responsibilityName="${responsibilityName}" responsibilityApplName="${responsibilityApplName}" doc:name="OracleEBS: Configuration">
        <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>
    <flow name="oracle-ebs-exampleFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
        <dw:transform-message doc:name="Transform Message">
            <dw:input-inbound-property doc:sample="map_string_string.dwl" propertyName="http.query.params"/>
            <dw:set-payload><![CDATA[%dw 1.0
%output application/xml
%namespace ns0
http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_web_sec/validate_login/
---
{
	ns0#InputParameters: {
		ns0#"P_USER": inboundProperties."http.query.params".user,
		ns0#"P_PWD": inboundProperties."http.query.params".password
	}
}]]></dw:set-payload>
        </dw:transform-message>
        <oracle-ebs:invoke-pl-sql config-ref="OracleEBS__Configuration" 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="Error: #[payload]" level="ERROR" doc:name="Logger"/>
    </catch-exception-strategy>
</mule>
----

....

[tab,title="Studio Visual Editor"]
....
image:oracle-ebs-biz-events-use-case.png[oracle_ebs_example_flow_source]

. Create a new Mule project in Anypoint Studio.
. Drag a new *Flow* scope element onto the canvas.
. Add an Oracle EBS connector as an inbound endpoint at the beginning of the flow.
+
image:oracle_ebs_config_source_2.png[oracle_ebs_config_source_2]
+
.. Add a new "OracleEBS: JMS Configuration" Global Element by clicking the green plus sign (*+*).
.. Configure the global element according to the table below:
+
[width="100%",cols="50a,50a",options="header",]
|=======
|Field|Description
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username for the Oracle EBS database.
|*Password* |Enter the password for the username.
|*Host* |Enter the host of the Oracle EBS database.
|*Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|*Required dependencies* |Select the location in your local file system for the Oracle AQ API and Oracle JDBC Driver JAR files. Both are used in the Test Connection process and subscription to queues and topics.
|=======
+
.. 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:
+
[cols=",",]
|=====
|*Field* |*Value*
|*Display Name* |Business Events (or any other name you prefer)
|*Connector Configuration* |OracleEBS__Configuration (Enter name of the global element you have created)
|*Operation* |Business events
|*Durable Subscription* |If checked, a durable subscription will be created. This means that if the client loses connectivity, all messages uploaded to the topic during this period will be retrieved as soon as it is restablished.
|*Subscription Name* |If you wish to create a durable subscription, you can specify its name. If not, it will default to "muleBusinessEvents".
|=====
+
. Add a *Logger* scope right after the EBS Connector, to print the messages received from the Business Events topic in the Mule Console. Configure the Logger according to the table below:
+
[width="100%",cols="50a,50a",options="header"]
|===
|*Field* |*Value*
|*Display Name* |Logger (or any other name you prefer)
|*Message* |`#[payload]`
|*Level* |INFO (Default)
|===
+
. Save and run the project as a Mule Application by right-clicking the project name in the Package Explorer, selecting *Run As* > *Mule Application*.
.. For every Business Event that is fired, you will see the corresponding message taken from the queue in the console log.
....
[tab,title="XML Editor"]
....

=== Example Code

[source, xml, linenums]
----
<?xml version="1.0" encoding="UTF-8"?>

<mule 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: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/oracle-ebs http://www.mulesoft.org/schema/mule/oracle-ebs/current/mule-oracle-ebs.xsd">
    <oracle-ebs:jmsconfig name="OracleEBS__JMS_Configuration" doc:name="OracleEBS: JMS Configuration" host="${host}" password="${password}" port="${port}" sid="${sid}" username="${username}"/>
    <flow name="demo-business-eventsFlow">
        <oracle-ebs:business-events config-ref="OracleEBS__JMS_Configuration" doc:name="OracleEBS (Streaming)"/>
        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
    </flow>
</mule>
----

....