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.

           

Comments

  1. Perfect blog to know Oracle ERP outbound methods.

    ReplyDelete
  2. This is great !! Congratulations for exploring and taking efforts to document and share with everyone. Much appreciated.
    Are you aware of any mechanism for bulk data extracts ? Extracting all of gl_code_combinations or gl_je_lines tables for entire period ?
    Thank you

    ReplyDelete
  3. Would this use named callback or the event callback?

    ReplyDelete
    Replies
    1. As indicated in the blog it is name callback. Check the part2 (https://kishorekatta.blogspot.com/2020/08/automating-end-to-end-bulk-export-from_26.html) of the blog which is event based

      Delete
  4. Perfect..i missed to configure my connection with SAML (used basic auth) struck for hrs. Again revisited your blog and read carefully and fixed the issue. Yeah..good learning

    Thank you Kishore

    ReplyDelete
  5. It is incredibly handy for me Thank you for taking the time to review this, I feel strongly regarding it and love learning more on this topic. If possible, as you gain knowledge, would you mind upgrading your blog site with more information?
    Miracle II Soap

    ReplyDelete
  6. Thank you for sharing this informative information with us. Its very helpful. ERP for Import Export.

    ReplyDelete
  7. Thank you for sharing this informative information with us. Its very helpful.
    Cloud ERP for Import Export

    ReplyDelete
  8. My callback service is not giving output to Mulesoft in dev envirnoment. Do u know what to check in oracle procurement cloud? The supplier import programs runs successfully but no callback responce is there whereas callback is working fine in production env

    ReplyDelete

  9. The information you discussed above is very useful and interesting. Thanks for sharing such great post

    Things to Consider to Start Import Export Business in India

    ReplyDelete
  10. Great article, I didn't know it was possible to run a BI Report that had more than 10 MB of data , this is a viable solution for those reports where we can't directly run the BI Report using the report soap service.

    ReplyDelete
  11. Thank you for sharing this informative information with us. Its very helpful. Cloud Invoice Tool

    ReplyDelete
  12. Hi. Thanks for sharing the valuable information. I found this article very helpful. Good work.
    I have read a similar article on EPM VS. ERP: DIFFERENT BUT WORK WELL TOGETHER. Please do check it out.

    ReplyDelete
  13. Thanks for the blog article. Your information can help us a lot. Enable your organization to make a better decision with our secure Oracle Cloud for ERP! Our staff will provide you with methods to reduce risks in decisions with effective configuration control.

    ReplyDelete
  14. Nice Blog, Thanks For Posting Visit For More know about Cloud ERP for Import Export - ERP with Invoice With Best Price.

    ReplyDelete
  15. I would appreciate if you can take time to go through some very informative posts on API Integration Services.

    ReplyDelete
  16. Thanks for sharing this informative information with us. ERP for Import Export

    ReplyDelete
  17. We are providing best ERP Cloud Solutions for Consultant. ERP solutions are the backbone of any company. They help businesses in numerous areas like finance, procurement, manufacturing and inventory management.
    Our exclusive focus and long association with Oracle has given us the extra edge of possessing deep knowledge across Oracle’s SaaS, PaaS and IaaS solutions. We bring forth a rich experience of implementation services at more than 150 companies around the world – many of them in the Fortune 500, across a variety of industries – Wholesale, Distribution, Retail, Manufacturing, Trading, Hi-Tech, Utilities and Professional Services.

    ReplyDelete
  18. Very informative blog , thanks for sharing this blog. Best tailormade erp software development company in Chennai.

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. Good one about automated bulk import export! The technology has reduced our efforts on documentation with usage of import export software system!

    ReplyDelete
  21. Advancement in technology has made human life easier and better. Introduction of Document Impexflo Management ERP system has made import export documents easier. It revolutionize their operations, save time, and reduce costs.

    ReplyDelete
  22. If you're looking for an excellent resource on this subject, you've found it! Moreover, don't miss the opportunity to explore the Export Import Data provided in the accompanying link.

    ReplyDelete

Post a Comment