Integrating With Fusion Application Using Services (APEX)

Fusion Applications provides web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and composite services. ADF services are created for a logical business object and provide functionality to access and manipulate these objects. The composite services are mostly process oriented and provide an orchestration of multiple steps.

Information about the web services provided by Fusion Applications is hosted in Oracle Enterprise Repository (OER). The information provided by OER can be used to understand the functionality provided by the service and how the service can be called.

This series of articles describes how one can invoke SOAP web services provided by Fusion Applications using various technologies. In this article we will cover how to invoke a Fusion Application web service using Oracle Application Express (APEX).

Prerequisites

Oracle Application Express (APEX) on-premises environment or Oracle Cloud Database Service needs to be available.

Implementing Web Service Call

Oracle Application Express (APEX) is a software development platform on the database level. Web services can be integrated to APEX applications using "Web Service Reference" objects that work on top of the APEX_WEB_SERVICE API. Details on how to create and use web service references can be found in this presentation. First we need to construct a valid SOAP envelope to be used for the web service call, in this example we will query a employee name based on employee number (replace the username and password from the content):

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/hcm/employment/core/workerService/types/" xmlns:typ1="http://xmlns.oracle.com/adf/svc/types/">

  <soapenv:Header>

      <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

          <wsse:UsernameToken wsu:Id="UsernameToken-2" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

          <wsse:Username>username</wsse:Username>

          <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">password</wsse:Password>

          <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">jwCzGGijT90Wml6eZe4cxg==</wsse:Nonce>

          <wsu:Created>2012-07-04T06:49:48.981Z</wsu:Created>

        </wsse:UsernameToken>

    </wsse:Security>

</soapenv:Header>

   <soapenv:Body>

      <typ:findWorker>

         <typ:findCriteria>

            <typ1:fetchStart>0</typ1:fetchStart>

            <typ1:fetchSize>1</typ1:fetchSize>

            <typ1:filter>

               <typ1:conjunction>And</typ1:conjunction>

               <typ1:group>

                  <typ1:conjunction>And</typ1:conjunction>

                  <typ1:upperCaseCompare>false</typ1:upperCaseCompare>

                  <typ1:item>

                     <typ1:conjunction>And</typ1:conjunction>

                     <typ1:upperCaseCompare>false</typ1:upperCaseCompare>

                     <typ1:attribute>PersonNumber</typ1:attribute>

                     <typ1:operator>=</typ1:operator>

                     <typ1:value>#PERSON_NUMBER#</typ1:value>

                  </typ1:item>

               </typ1:group>

            </typ1:filter>

            <typ1:findAttribute>PersonName</typ1:findAttribute>

           <typ1:excludeAttribute>false</typ1:excludeAttribute>

            <typ1:childFindCriteria>

               <typ1:fetchStart>0</typ1:fetchStart>

               <typ1:fetchSize>1</typ1:fetchSize>

               <typ1:findAttribute>FirstName</typ1:findAttribute>

               <typ1:findAttribute>LastName</typ1:findAttribute>

               <typ1:excludeAttribute>false</typ1:excludeAttribute>

               <typ1:childFindCriteria/>

               <typ1:childAttrName>PersonName</typ1:childAttrName>

            </typ1:childFindCriteria>

         </typ:findCriteria>

         <typ:findControl>

            <typ1:retrieveAllTranslations>false</typ1:retrieveAllTranslations>

         </typ:findControl>

      </typ:findWorker>

   </soapenv:Body>

</soapenv:Envelope>

In your APEX application navigate to "Shared Components -> Web Service References -> Create" and choose Manual on the first screen:

On the Web Service Reference screen we will define the details of how the service will be invoked:

When defining the reference for a Fusion Applications ADFbc service use values such as:

Field	Description	Value
URL	This is the URL for WSDL for the Fusion Applications ADFbc service	https://host:port/hcmEmploymentCoreWorker/WorkerService?wsdl
Action	This is the definition of the service and operation including the namespace	http://xmlns.oracle.com/apps/hcm/employment/core/workerService/getWorker
SOAP Envelope	This defines the content to be passed to the service. The value needs to be complete SOAP envelope to invoke the service with. Bind variables can be defined by having and upper case string with hash pre and post fix. In the example case we have a "#PERSON_NUMBER#" bind variable. The bind variables can be used in the application to obtain input from the user using an input field. The value of the input field would be used to replace the bind variable on the SOAP content when submitting the request to the service	Enter the content we constructed earlier
Store Response in Collection	This is a temporary structure containing the response from the Web Service call. This object can be used to parse the response XML to be used in the application.	Set to any unique value

Next we create UI for the new web service references, click "Create Form & Report on Web Service":

On next screen choose the web service reference that we created:

On the following screens simply accept the defaults until the “Web Service Results” screen:

When defining the results use values such as:

Field	Description	Value
Result Node Path (XPath)	This is a XPath expression that can be used to derive the data we are interested in from the response. In the example the response will contain the results in a element names “PersonName”	/PersonName
Message Namespace	This defines the namespace used with the XPath	http://xmlns.oracle.com/apps/hcm/people/core/personService/

On the next screen add the "LastName" and "FirstName" to be displayed on the resulting report. Elements matching these names must exist under the "Result Node Path" defined in previous step:

For the next screens accept the defaults, once finished navigate to the application page and run the new page:

On the opened report we can query employees using the web service reference:

Summary

In this article we covered an example using APEX to integrate with Fusion Applications using web services. In future articles other technologies for invoking Fusion Applications web services will be covered.