Automating End to End Bulk Export from Oracle ERP Cloud using Oracle Integration (Part 1- ERP Cloud Callback)

Background

What are the main methods available for Outbound Integrations within Oracle ERP Cloud?

Oracle ERP Cloud has provided a very useful functionality which empowers business enterprises to extract financials data from Oracle Cloud ERP and integrate it with on-premise systems, legacy systems and other Cloud Applications.

One very common business scenario in an enterprise is to generate extracts of financials data like Journals, Payables Invoices, Payments, Receivable Invoices etc for legacy or third-party partner systems. Let's see the Outbound mechanisms and the purpose of each of them based on the volume of data that needs to be sent to downstream applications

      BIP Extract

        Designed to extract incremental data for scenarios ex:

      –Get the list of invoices that has been approved since last execution

      –Extract payment data since last execution to update downstream or upstream applications to reflect payments

Short BIP Reports

    Designed to extract lookup values/metadata/transaction details of a transaction ex:

      –Get Chart of Accounts values (COA) for a specific ledger etc. The reports are small and can be csv/xml format

      To automate an entire extraction process using Oracle Integration there are couple of ways to do this. Automate the entire process of extraction as per below recommendations: 

       When the report size is greater than 10 MB it is recommended to leverage extract process which pushes the file to a Content Server (UCM) which can be fetched later for further processing. This is purely an asynchronous invocation.

       For smaller data sets less than 10 MB it is recommended to leverage synchronous Invocation of report service which produces the report response instantly. Ideally, client applications which may wanted to show data set values instantly to the end user to take further course of action.

       Irrespective of the method BIP Report needs to exist in ERP Cloud. There are lot of Out of the box financial extracts (Doc ID: 2303683.1) available which is spread across key Financial Business Objects. The extracts can be extended as needed. Additionally, brand new custom extracts can be created.

        

These seeded extracts can be run as an ESS job

High Level Steps to Automate Data Extracts in ERP Cloud using Oracle Integration

1. Invoke ERPIntegration Service -> exportBulkData operation

2. Listen to Callback Event/Message and capture the Document Id

3. Use Document Id and download the file from UCM

Understanding ESS Job & BIP Report related parameters 

It is important to understand few of these parameters as we will use these parameters in Oracle Integration Flow to initiate the extract.

In Oracle ERP Cloud Navigate to Setup and Maintenance and search for setup task "Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications". In our example we will consider Payables Transactions Extract. 

exportBulkData This operation helps to submit an ESS job to export the data from the report and upload the job output file to Universal Content Management (UCM) server with callback and notification Operation which accepts below parameters.

The Following table indicates the notification code to be sent. If Email-Notification is provided mail will be sent to the user invoking the ERPIntegrationService

Edit the Payables Transactions Extract ESS job. Note the below parameters that needs to be leveraged for "Job name" parameter above

Navigate to the Report Path from Reports & Analytics and Edit the associated Payables Transactions Extract Data Model

In this example we wanted to extract Payables Transactions for a specific Business Unit in the period 12-19.

Provide the parameters as appropriate and view the report:

 

Designing the Integration Flow in OIC

     

Flow 1 - Initiate Extract

    1.    Create a Scheduled Based Integration

    2.    Invoke ERPIntegrationService -> exportBulkData operation using ERP Cloud Adapter Connection

3.    Map the values noted in earlier section

     JobName: oracle/apps/ess/financials/commonModules/shared/common/outbound,PayablesTransactionsExtract

       parameterList: 300000046987012,#NULL,#NULL,#NULL,#NULL,#NULL,12-19,N,N,300000046975971,#NULL,#NULL,#NULL,FULL_EXTRACT,#NULL,#NULL,#NULL,PayablesTransactionsExtract,#NULL

       jobOptions: ExtractFileType=ALL

       callbackURL : <End point URL of Flow 2>

       notificationCode: 30

Flow 2 - Callback Integration Flow

      Configure

      1. ERP callback implements SAML bearer token policy. In order for OIC to receive callback, the issuer name (username) used in SAML assertion must exist in OIC identity store. The issuer name is the username used when invoking bulk export service in ERP Cloud through OIC. 

        2. The cloud certificates must be imported in OIC to authenticate SAML assertion from Oracle ERP cloud. Upload the certificates as SAML (Message Protection) type. Raise an SR with Oracle Support requesting Two Certificates from ERP Cloud (oraakey.cer , cloudca.cer)

Design the Callback Flow

       Once the ESS Job is completed ERP Cloud calls the registered callback Endpoint (Flow1) with the below message structure

        The JSON message in the <resultMessage> element contains all the jobs that ran in ERP and its statues

    <?xml version="1.0" encoding="UTF-8"?> <ns0:onJobCompletion xmlns:ns0="http://xmlns.oracle.com/scheduler"> <requestId>2118936</requestId> <state>SUCCEEDED</state> <resultMessage>{"JOBS":[{"JOBNAME":"Payables Transactions Extract","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/outbound","REQUESTID":"2118936","STATUS":"SUCCEEDED"},{"JOBNAME":"Upload Interface Error and Job Output File to Universal Content Management","JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader","REQUESTID":"2118938","STATUS":"SUCCEEDED"}],"SUMMARYSTATUS":"SUCCEEDED","DOCUMENTID":"2613402","DOCUMENTNAME":"ExportBulkData_PayablesTransactionsExtract_2118936"}</resultMessage> </ns0:onJobCompletion>

    Creating Connection

      1.Create a SOAP adapter connection with the below abstract wsdl interface adhering to the above callback message. Use SAML as Authentication mechanism

    <?xml version="1.0" encoding="UTF-8"?> <definitions xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://oracle.com/erpintegration/" xmlns:wsam="http://www.w3.org/2007/05/addressing/metadata" xmlns:wsp="http://www.w3.org/ns/ws-policy" xmlns:wsp1_2="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://oracle.com/erpintegration/" name="ERPCbkService"> <types> <xsd:schema xmlns:ns0="http://oracle.com/erpintegration/" targetNamespace="http://oracle.com/erpintegration/"> <xsd:complexType name="onJobCompletionType"> <xsd:sequence> <xsd:element name="requestId" type="xsd:int" minOccurs="0" /> <xsd:element name="state" type="xsd:string" minOccurs="0" /> <xsd:element name="resultMessage" type="xsd:string" minOccurs="0" /> </xsd:sequence> </xsd:complexType> <xsd:element name="onJobCompletion" type="ns0:onJobCompletionType" /> </xsd:schema> </types> <message name="onJobCompletionMessage"> <part name="parameters" element="tns:onJobCompletion" /> </message> <portType name="ERPCbkService"> <operation name="onJobCompletion"> <input wsam:Action="http://oracle.com/erpintegration/ERPCbkService/onJobCompletionRequest" message="tns:onJobCompletionMessage" /> </operation> </portType> </definitions>

    2. Create a SOAP Adapter Connection pointing to ERPIntegrationService wsdl. We need a SOAP adapter to accept attachments (ERP extract) from UCM

      3. Create an FTP Connection connecting to OIC File Server

       Creating Callback Integration Flow

       

      1.Use the Callback Interface connection and configure onJobCompletion operation

       

      2. Use Assign activity and create a variable to assign the resultMessage reference key

       

        The <resultMessage> element contains a JSON. Write it to OIC Virtual File System (using decodeBase64ToReference) so that later it can parsed to get the DocumentId.

       3. Orchestrate a Stage Activity and Read the Json. Provide the variable declared as reference

       

       Parse the file with a sample JSON structure above

       4. Use the SOAP adapter connection and configure the operation ERPIntegrationService ->getDocumentForDocumentId which fetches the file as attachment from UCM

       

       Note: SOAP Adapter supports attachments which allows to process files as big as 1 GB

        

       5. Map the DocumentId from parsed JSON to getDocumentByDocumentId-> DocumentId

       

       6. Finally Write the BIP extract file to File Server.

       In the entire flow the reference (key) of the file in the virtual file system is mapped instead of actual content of the File itself. Hence, there is a huge performance benefit in File Processing across different intermediate activities within the flow.

Testing the End to End Flow

            

            1. Activate the Extract Flow (Flow1) and Callback (Flow2) Integration Flow

            2. Run the Extract flow

            3. 2 ESS Jobs is seen

            

            The extract file available in the UCM

            

            4. After successful completion of Extraction process in ERP Cloud a Callback flow is triggered.Observe the trigger message in the Activity Stream

            

            Notice that the Job list (JSON array) is the same set of jobs which is seen in ERP Cloud Scheduled Processes

           In the Part 2 of this Blog series we will see a recommended way to receive events in the Callback Flow instead of registering a concrete endpoint.