Oracle E-Business Suite Connector

[tab,title="Studio Visual Editor"]
....
image:oracle_ebs_example_flow.png[oracle_ebs_example_flow]

. 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:HTTP-1.png[HTTP-1]
+
.. Configure the following HTTP parameters:
+
image: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: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: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 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.
|*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.
|*Required dependencies* |Select the location in your local file system for the Oracle AQ API and Oracle JDBC Driver. 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:
+
image:oracle_ebs_connector_operation_config.png[oracle_ebs_connector_operation_config]
+
[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]`
|=====
+
. 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:datamapper.png[datamapper]
+
.. In the *Source* field, select *Inbound Property - http.query.params* and click  the
image: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:queryParamsDataMapper.png[queryParamsDataMapper]
+
... Add a name to the structure. In the example it is *queryParams*.
... In the *Type* dropdown select *Element*
... Click the plus sign (*+*) and add a name to the field_0. In the example it is *user*.
... Click the plus sign (*+*) again and add a name to the field_1. In the example it is *password*.
... In the Output section of the DataMapper, specify the applicable output data type (e.g. xml), so the connector may perform the Validate Login operation specified earlier
. 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.
....
[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="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">
----
+
. Do the same, but configure the HTTP endpoint as follows:
+
[source, xml, linenums]
----
<http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>
----
+
. 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__Configuration" type="fnd_web_sec||VALIDATE_LOGIN" doc:name="Testing custom PL/SQL operation"/>
----
+
. 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.7.3"
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>

[tab,title="Studio Visual Editor"]
....
image:oracle_ebs_example_flow_source.png[oracle_ebs_example_flow_source]

. Create a Mule project in your Anypoint Studio.
. Drag a Flow into the canvas.
. Drag the Oracle EBS connector into the Source section of the flow, then configure it according to the steps below:
.. Add a new Oracle EBS Global Element by clicking the plus sign (*+*).
+
image:connectorconfig_source.png[connectorconfig_source]
+
.. 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.
|*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 *None*.
|*Required dependencies* |Select the location in your local file system for the Oracle AQ API and Oracle JDBC Driver. 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
|=====
+
. 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.
....
[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="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"/>
----
+
. Begin the flow with a Oracle EBS connector source, configuring the endpoint as follows:
+
[source, xml, linenums]
----
<oracle-ebs:business-events config-ref="OracleEBS__Configuration" doc:name="OracleEBS (Streaming)"/>
----
+
. Add  a logger component and set message attribute value to _#[payload]_
+
[source, xml, linenums]
----
<logger message="#[payload]" level="INFO" doc:name="Logger"/>
----
+
+
. Save and run the project as a Mule Application.
....

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

  
<?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: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"/>
    <flow name="demo-business-eventsFlow">
        <oracle-ebs:business-events config-ref="OracleEBS__Configuration" doc:name="OracleEBS (Streaming)"/>
        <logger message="#[payload]" level="INFO" doc:name="Logger"/>
    </flow>
</mule>