Oracle E-Business Suite Connector

[tab,title="Studio Visual Editor"]
....
Follow these steps to create an Oracle EBS global element:

. 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*.
+
image:resources/oracle_ebs_connector_config.png[oracle_ebs_connector_config]
+
. Click *Ok*
. Configure the parameters according to instructions below
+
image:resources/oracle_ebs_global_element.png[oracle_ebs_global_element.png]
image:resources/oracle_ebs_global_element.png[oracle_ebs_global_element_2.png]
+
[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 in to 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.
|*DB Username* |Username for the Oracle EBS database.
|*DB Password* |Enter the password for the username.
|*DB Host* |Enter the host of the Oracle EBS database is located.
|*DB Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|*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:
.. Select *Create Object Manually* and then click the button next to it.
+
image:resources/createobject1.png[createobject1]
+
.. On the pop-up window, select the *+* plus button to add more entries.
.. Right-click a metadata item and click *Edit the selected metadata field* to enter the values.
+
image:resources/oracle_ebs_enter_metadata.png[oracle_ebs_enter_metadata]
+
.. Click *OK* to save the list.
. Click *Test Connection* to receive a _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}" dbUser="${dbUser}" dbPassword="${dbPassword}" dbHost="${dbHost}" dbPort="${dbPort}" dbSid="${dbSid}" responsibilityName="${responsibilityName}" responsibilityApplName="${responsibilityApplName}" doc:name="OracleEBS: Configuration"/>
----
+
[cols=",",]
|===
|*Parameter* |*Description*
|*Name* |Enter a name for the configuration with which it can be referenced later.
|*Username* |Username to log in to 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.
|*DB Username* |Username for the Oracle EBS database.
|*DB Password* |Enter the password for the username.
|*DB Host* |Enter the host of the Oracle EBS database is located.
|*DB Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|*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"]
....
image:resources/oracle_ebs_example_flow.png[oracle_ebs_example_flow]

. 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:
.. In *General Settings*, click the *+* plus button:
+
image:resources/HTTP-1.png[HTTP-1]
+
.. Configure the following HTTP parameters:
+
image:resources/oracle_ebs_http_params.png[oracle_ebs_http_params]
+
[width="100%",cols="50a,50a",options="header",]
|======
|Field|Value
|*Port* |8090
|*Host* |localhost
|*Display Name* |HTTP_Listener_Configuration
|======
+
.. Reference the HTTP Listener Configuration global element:
+
image:resources/httpconfig.png[httpconfig]
+
. 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.
+
image:resources/connectorconfig.png[connectorconfig]
+
.. 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 in to 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.
|*DB Username* |Username for the Oracle EBS database.
|*DB Password* |Enter the password for the username.
|*DB Host* |Enter the host of the Oracle EBS database is located.
|*DB Port* |Enter the port of the Oracle EBS database.
|*Database SID* |Enter the SID of the database.
|*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* |Web Sec
|*Function* |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. 
+
image:resources/datamapper.png[datamapper]
+
.. In the *Source* field, select *Inbound Property - http.query.params* and click  the
image:resources/edit_button.png[edit_button](Edit) button located to the right of the *Type* drop down list.
.. In the Type field, select Map (k,v)
.. Select *User Defined* and then click the *Create/Edit Structure* button:
+
image:resources/oracle_ebs_define_map.png[oracle_ebs_define_map]
+
... Add a name to the structure. In the example it is *queryParams*.
... Click the *+* plus button and add a name to the field_0. In the example it is *user*.
... Click the *+* plus button again and add a name to the field_1. In the example it is *password*.
... 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:
+
[width="100%",cols="50a,50a",options="header"]
|===
|*Field* |*Value*
|*Display Name* |Logger (or any other name you prefer)
|*Message* |Output from Trasformer 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.
....
[tab,title="XML Editor"]
....
. Add an oracle:config global element to your project, then configure its attributes as follows:
+
[source, xml, linenums]
----
<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:
+
[source, xml, linenums]
----
<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:
+
[source, xml, linenums]
----
<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.
+
[source, xml, linenums]
----
<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. 
+
[source, xml, linenums]
----
<oracle-ebs:invoke-pl-sql config-ref="OracleEBS" doc:name="Testing custom PL/SQL operation" type="fnd_web_sec#VALIDATE_LOGIN"/>
----
+
. Add  a DOM to XML Transformer.
+
[source, xml, linenums]
----
<mulexml:dom-to-xml-transformer doc:name="DOM to XML"/>
----
+
. Add  a logger component and set message attribute value to _### EBS Test #[payload]_
+
[source, xml, linenums]
----
<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**.**_
+
[source, xml, linenums]
----
<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.

....

  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

  
<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" version="EE-3.6.1"
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="OracleEBS__Configuration" username="${username}" password="${password}" host="${host}" port="${port}" dbUser="${db.username}" dbPassword="${db.password}" dbHost="${db.host}" dbPort="${db.port}" dbSid="${db.sid}" 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-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-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="#[payload]" level="ERROR" doc:name="Logger"/>
    </catch-exception-strategy>
</mule>