DATA INTEGRATION FOR ORACLE CLOUD

Preface:

With increasing number of Enterprises both big and small, moving their IT infrastructure to the cloud, there are multiple challenges for timely and quality data movement and access between legacy systems and the cloud environment.

Industry experts often cite integration as one of the barriers to adoption of cloud services, especially for apps that need to exchange messages and data. Organizations want to avoid rigid point-to-point connections between cloud-based services and on-premises infrastructure, which often ignore well-established integration principles.

Two ways by which we can effectively integrate with cloud applications are

  • ERP Integration Services
  • Integration Platform

External Data Integration Services :

Overview

External Data integration services are used to load data into Oracle Fusion Applications from external sources such as legacy systems and third-party applications.

The following are the components in the overall process:-

  • Templates for structuring, formatting and generating the data file according to the requirements of the target application tables.
  • A File based load process for loading data from the data file into the interface tables.
  • Application-specific data import processes for transferring data from interface tables to the application tables in your Oracle Fusion Applications.

The File Import and Export page accesses allows user to place the external data file to the repository and run the import program to load the external data

The below process flow diagram briefly explains the steps in the flow.

External Data Integration Services image
External Data Integration Services image

Data Integration using Web Service :

A web service for Oracle Fusion is an artifact that provides a standardized way of integrating other web-based applications with Fusion Applications over the web. It allows organizations to communicate with Fusion Applications without the need of intimate knowledge of the applications.

We have numerous web services which are present out of the box but we cannot define any custom ones as of now. We would only need to call using cloud application username and password.

For example “InvoiceInterfaceService” webservice has operations to populate and update the ap invoice interface table and ExternalReportWSSService.

The ERP Integration Service are utility web services that provide external web service operations for automatically performing different tasks. Such as:

  • Managing data files for import
  • Launching Enterprise Scheduling Service jobs
  • Downloading Enterprise Scheduling Service job details

Some available operations are

getESSJobStatus(get ESS job Request Status),

submitESSJobRequest (submit ESS Job Request)

and uploadFileToUcm (Provide base64 converted file data to be transferred and uploaded to UCM)

“ExternalReportWSSService” webservice has operations for submitting any Cloud concurrent request.

“FinancialUtilService” web service provides external web service operations like file upload operations to Oracle Universal Content Management, and service operations to submit ESS jobs from external applications.

Sample SOAP request for Createinvoice operation of InvoiceInterfaceService

Data Integration using Web Service image

Sample response

Data Integration using Web Service image

Data Extracts:-

Oracle Transactional Business Intelligence is built on the OBIEE platform; there are few limitations as compared to the on premise OBIEE features.

OTBI is included as part of the Oracle Cloud Apps licenses and does not need any additional setup. This includes out of the box reports, subject areas which are built to work with the Fusion view objects (VOs). In addition, there are tools for customizations.

When reports have specific requirements like splitting of reports (by person or date or delivery type) or if requirements cannot be delivered using OBIEE, OTBI provides BI Publisher.

Using BI Publisher, we can write custom SQL queries to hit the fusion tables and get the required output. BI Publisher also provides the bursting feature where we can split the reports on particular fields and deliver using delivery devices like Email, FTP, Printer etc.

Data security can be configured using the security view in Fusion tables and passing session variable for user or role name which are in built in BI Publisher.

Custom reports or extracts once built can be easily migrated to different cloud environments as catalogs. Those can be scheduled and ftped to a server directly.

Data Extracts image

Integration Platform:

Overview

Oracle Integration Cloud Service gives you a powerful and intuitive visual designer and broad connectivity so you can:

  • Connect securely and exchange messages between applications and services in the cloud and on premises
  • Point and click to create integrations between your applications with a powerful browser-based visual designer—it even runs on your favorite tablet
  • Publish and subscribe to messages
  • Schedule, monitor, and manage integrations
  • Manage errors

Main use of cloud integration is you should be able to integrate the application in minutes rather than in months. It simplifies connectivity between key enterprise applications like Fusion application, Rightnow, EBS suite and other third party SaaS application.

With ICS you get Automated backup, Pitch updates and upgrades which will leave us to focus on the integration steps. Also you can import standard pre built integrations built by Oracle and use it.

Integration Platform image

We have numerous pre built adaptors in ICS.

Integration Platform image

ICS – EBS Adapter :

The Oracle E-Business Suite Adapter is predefined adapters included with Oracle Integration Cloud allowing you to securely connect and use Oracle E-Business Suite services in integrations in Oracle Integration Cloud.

It significantly simplifies the complexity of typical integration experiences.

ICS – EBS Adapter image

ICS – Oracle database adapter:-

The Oracle Database Adapter enables you to integrate the Oracle database residing behind the firewall of your on-premises environment with Oracle Integration Cloud Service through use of the on-premises connectivity agent.

      1. Support for invocation of stored procedures in the Oracle database.
      2. Support for non-JDBC (PL/SQL) datatypes in outbound invocations of stored procedures.
      3. Support for execution of DML statements and SQL queries: Select, Insert, Update, and Delete.
      4. Support for generating XSD from PureSQL. This feature generates an XSD from a PureSQL statement provided by dynamically querying the table.
      5. Support for polling new and updated records for processing in the Oracle database. The Oracle Database Adapter supports distributed polling and multithreading. Distributed polling helps eliminate duplicate polling of the same records while multithreading provides optimum performance.

 

The Oracle database adapters are recommended when need to invoke all Database operations like insert, update, delete, select, calling a stored procedure, polling records from DB and executing Pure SQL. We can also call Custom packages to do custom validations and insert data into base tables.

Pros:

  1. We can invoke the same operation for example call the same package same time to process different orders a t high speed. It also avoids in duplicating orders while processing.
  2. Provide a connectivity platform for integrating complex business processes: Adapters integrate mainframe and legacy applications with enterprise resource planning (ERP), customer relationship management (CRM), databases, and messaging systems. Oracle provides adapters to connect various packaged applications, such as SAP and Siebel, and databases.
  3. Use native APIs: Adapters support multiple ways of interfacing with the back-end system and provide various deployment options. Using native APIs, adapters communicate with the back-end application and also translate the native data to standard XML, which is provided to the client.
  4. Provide easy-to-use design-time tools: Adapters use design-time tools that provide a graphical user interface (GUI) to configure and administer adapters for fast implementation and deployment. In addition, the tools let you to browse, download, and configure back-end schemas.

ICS – Webservice Adapter

The Oracle Webservice adapters are recommend when we need to make Remote Procedure Call (RPC) requests using Simple Object Access Protocol (SOAP). This adapter does not require a configuration node. However, you must configure the adapter in Grid Manager. For example A company has automated its order shipping process. After an inventory check, the address of delivery must be validated before the order is packaged and shipped. Another external company provides address verification Web Service. So here will use webservice adapter to invoke the address verification process.

The Web Services adapter can use a standard HTTP proxy server for establishing outbound HTTP connections. The Web Services adapter supports basic, NTLMv1, and NTLMv2 authentications for the proxy server that is used.

The Web Services adapter supports uploading attachments to the outgoing SOAP request and downloading attachments embedded in the SOAP response received from the web server.

       Pros:  The adapter can be configured and used in an entirely point-and-click fashion, without having to write any program code.

       Cons: For some webservices establishing proxy server connection is little bit hard.Also the output data format will be in different forms.

SOA Suite or SOA Cloud Service:-

It provides a comprehensive suite of components for developing, securing, and monitoring service-oriented architecture to integrate the data between two applications. While ICS is designed for LOB and IT, this is designed to used by IT. Traditional developers will find it easier to work with SOA Suite. This can be used with On premise and Cloud as well.

ICS – EBS Adapter image

Business Case Studies:-

Project: Integration with Oracle Fusion Applications web services

  1. Requirement: To import the data from Oracle fusion application to external application.
  2. OTBI report approach recommended: As in Oracle fusion we don’t have an option to customise the web services so we had created OTBI reports to extract the data from Fusion application. Then we need to expose these reports as web services to extract data from applications
  3. Challenges: If the output of the OTBI reports is set as XML, then also the output will in binary/base64 format so additional wrapper needed to convert that data into XML format.
  4. Pros: Invoking the web services is fast.
  5. Cons: These web services can’t be customized.

Project: Creating and Updating a supplier in On premise ERP using Cloud data

The business flow is as follows: When an Supplier is created/updated in third party tool it needs to be transmitted to the on-premise ERP system for further processing; whenever the Supplier is created in ERP, the details like vendor id needs to be synchronized back with P4T.

Challenges and Solution Architecture

Integrating different systems is usually complex, because the APIs of the applications to be integrated are often quite technical and use complex data models that are also challenging to translate. In addition, the integration of cloud with on-premise applications makes things even worse, since the communication between those applications has to pass a company’s firewall. Security exceptions, such as opening application-specific ports in the firewall, have had to be defined to deal with this issue—usually preceded by exhausting and long-term discussions with the internal security department.

ICS provides a rich palette of different connectors that connect to any system easily, without a need to know about the technical details of the targeted systems. In addition, support for handling complex data models and the needed translations is provided (e.g., through pre-defined data mappings available through the Cloud marketplace or recommendations given while implementing the mappings).

By introducing the concept of the connectivity agent, Oracle ICS puts an end to connectivity challenges. The agent is hosted on the on-premise side behind a company’s firewall and registers with ICS.Communication is done between ICS and the on-premise agent using HTTPS, where the agent always initiates interaction with ICS. There is no direct communication between ICS and the on-premise systems.

The monitoring of the agent can be done via the ICS UI, so there is no need to set up additional monitoring capabilities on the on-premise side:

ics image

Tasks in ICS:

ERP Connection: The integration requires a Oracle ERP connection to exchange messages with P4T. In general, ICS supports both inbound and outbound communication. We just need to provide the details as shown below.

ics image

P4T Connection:The integration also requires P4Tool connection in order to send the messages to P4t. We just need to provide the details as shown below.

ics image

Create Integration:

With both connections configured, We have choosen three different patterns as the P4Tool will publish the data to ICS and as soon as it gets published we need to pick the data from ICS and create/update suppliers.

Create Integration image

Steps:

1. We will Publish the data from P4T to ICS .

2. As soon as we publish the details automatically it will call Subscription to ICS integration which will automatically invoke the Orchestration Integration shown in below image.

3. This integration takes the data details and based on the P4TID if it is null the process will create the supplier in EBS and then pass the Vendorid to p4tool . If the P4TID has value the it will call the update supplier webservice in EBS and then send the return_status to P4tool webservice.

Create Integration image

Mapping of the data between P4t and ERP.

Mapping of the data between P4t and ERP

Now the integration is ready to be used and can be easily tested by Publishing data to ICS. Using the monitoring capabilities of ICS, any instance that has been executed can also be tracked.

Mapping of the data between P4t and ERP

When one such instance is selected, additional information is shown. In this example everything has worked perfectly, so the integration from SFDC to SAP is completed. Anew record has been created in the ERP:

Mapping of the data between P4t and ERP

When you click on the View Activity stream you can see the payload information.

Mapping of the data between P4t and ERP
admin

About admin

Leave a Reply