Integrations and Extensions

Enterprise Resource Planning

Topic

    Jack Desai
    ERP Integration Services - Export Data
    Topic posted July 1, 2016 by Jack DesaiRed Ribbon: 250+ Points, last edited July 14, 2016, tagged Cloud Integration, Export Bulk Data, Extensions, File Based Data Integration - FBDI, Financials, Fusion Applications Integration, Integrated Cloud Services, PaaS - SaaS Extensions, Pre-built Integration, REST Services, SaaS Integration, SOACS, SOAP Services, Transaction Services 
    3829 Views, 6 Comments
    Title:
    ERP Integration Services - Export Data
    Summary:
    Automate ERP Outbound Bulk Export Integration in R11
    Content:

    Introduction

    Oracle Enterprise Resource Planning (ERP) Cloud provides a comprehensive set of modern cloud tools, templates, and pre-packaged integration to cover various scenarios using modern and efficient technologies. One of the patterns is the bulk integration to extract data from the ERP Cloud.

    Outbound bulk export is a powerful tool for extracting data from Oracle ERP Cloud through Business Intelligence Publisher (BIP) tool.

    ERP Cloud implements Oracle WebCenter Content (UCM), a component of Fusion Middleware, to store and secure data files for both inbound and outbound bulk integration patterns.

    This post focuses on how to automate ERP Cloud bulk export integration.

     

    ERP Outbound Flow

    There are several scenarios where data must be exported from ERP cloud for external applications consumption (On-premise and PaaS) such as:

    • Automated payment data extract from ERP Cloud to update downstream external application
    • Seamless launch of payables register, trial balance, and reconciliation reports

    The approach is to automate end-to-end orchestration using ERP integration services and avoid high touch integration scenarios for handling individual transactions.

    This is a typical bulk export pattern:

    preview

     

    Fusion Applications Security

    The content in WebCenter Content (UCM) is secured through users, roles, privileges and accounts. The user could be any valid user with a role such as “Integration Specialist.” The role may have privileges such as read, write and delete. The accounts are predefined by each application. For example, ERP uses fin/payables/import for invoices, fin/generalLedger/import for journals , etc.


    The ERP integration web service is secured through Oracle Web Service Manager (OWSM) using the following policy: oracle/wss11_saml_or_username_token_with_message_protection_service_policy.

    The client must satisfy the message protection policy to ensure that the payload is encrypted or sent over the SSL transport layer.

    A client policy that can be used to meet this requirement is: “oracle/wss11_username_token_with_message_protection_client_policy”

    To use this policy, the message must be encrypted using a public key provided by the server. When the message reaches the server it can be decrypted by the server’s private key. A Keystore is used to import the certificate and it is referenced in the subsequent client code.

    The public key can be obtained from the certificate provided in the service WSDL file (the certificate is Base64 encoded).

     

    Design and Implementation

    These are the following design considerations:

    1. Create BI Publisher (BIP) report and respective Enterprise Scheduler (ESS) job
    2. Invoke ERP Integration Service to initiate respective ESS job.
    3. Upon job completion, ERP Cloud will notify through asynchronous callback.
    4. Callback will provide the status and information to download extracted data file from the ERP Cloud.
    5. Determine errors and take appropriate action such as process data extracted for downstream applications.

     

    Invoke ERP Integration Service

    Once the BIP report and respective ESS job are created, you are ready to invoke ERP integration service to export data from the ERP Cloud.

    In R11, the consolidated and simplified operation is available to export data in a single call avoiding high touch point integration scenarios for handling individual integration.

    Constructing ERP Integration Service End Point URL

    To get the physical end point of any specific instance:

    1. Launch ATK home page and sign in as a functional user.

    Navigate to a dashboard or work area page associated with Payables Service.
    For example, for the ERP Integration service, navigate to Payables.

    In the Payables Invoice workbench, you can see a URL in the browser similar to https://<hostname>.<domainname>/payables/faces/InvoiceWorkbench.

     

    The “<hostname>.<domainname>” may be “https://<pod-name>.<lba>.***.oraclecloud.com”.
    In this example “<pod-name>.<lba>” is hostname and “***.oraclecloud.com” is domainname.
     

    1. In this URL, take the "https://<hostname>.<domainname>".

    For example, for the Payables Invoice workbench URL, use payables.

     

    1. Append the static context root: "/publicFinancialCommonErpIntegration/ErpIntegrationService".

    “https://<hostname>.<domainname>/publicFinancialCommonErpIntegration/ErpIntegrationService” is the WSDL URL for ERP Integration Service.

     

    Operation: exportBulkData

    The exportBulkData operation submits an ESS job to start the BIP report processor and eventually uploads the report output to UCM. The supported output formats are XML and CSV.

    The following table lists the parameters for this operation:

    Parameter

    Name

    Description

    Parameter

    (In/Out)

    Mandatory

    Job Name

    Contain the details of the ESS job and package name.  (Job definition name, job package name)

    IN

    Yes

    Parameter List

    This contains ESS parameters as defined in your ESS job.

    IN

    Yes

    Notification Code

    Notification options upon job completion such as bell and/or e-mail. Please refer the table below for the notification code values.

    IN

    Yes

    Callback URL

    The callback URL of the web service you implemented to receive the job status upon job completion.

    IN

    No

    Job Options

    Optional parameters as provided by Oracle

    IN

    No

    Response Code

    The response code returns the Request Id of the load job only (interface table).

    OUT

     

     

    The following table provides information on the notification codes:

    Digit Position

    Digit Value

    Meaning

    First digit

    1

    E-mail notification

    2

    Bell notification

    3

    Email & Bell notification

    Second digit

    0

    Send in any case (export failed or succeeded)

    1

    Send on export success

    2

    Send on export failure

     

     

    Sample Payload of exportBulkData Operation

    <soap:Body>

    <typ:exportBulkData>
             <typ:jobName>oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader,InterfaceLoaderController</typ:jobName>
             <typ:parameterList>1,21824,N,N</typ:parameterList>
             <typ:jobOptions/>
            <typ:callbackURL>%Your callback Service endpoint%</typ:callbackURL>
             <typ:notificationCode>10</typ:notificationCode>
          </typ:exportBulkData>

    </soap:Body>

     

    Sample Response from exportBulkData Operation

    The response contains the Request Id of the job initiating BIP report.

    <ns0:exportBulkDataResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
             <result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">21137</result>
          </ns0:exportBulkDataResponse>

     

    Callback Web Service

    In practice, customers will create and host a callback web service to optimally leverage the callback capabilities provided by Oracle ERP Integration Service for notification purposes. The callback web service must implement the onJobCompletion() operation. When a job completes, Oracle ERP Integration Service invokes the customer callback web service as defined in the request payload of supported operations with callback capabilities, such as the bulkexportData operation.

    Please refer this post on implementing Callback web service.

     

    This is a sample callback response in JSON string:

    {  
       "JOBS":[  
          {  
             "JOBNAME":"Payables Transactions Extract",
             "JOBPATH":"oracle/apps/ess/financials/commonModules/shared/common/outbound",
             "REQUESTID":"1556",
             "STATUS":"SUCCEEDED"
          },
          {  
             "JOBNAME":"Upload Interface Error and Job Output File to Universal Content Management",
             "JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
             "REQUESTID":"1559",
             "STATUS":"SUCCEEDED"
          }
       ],
       "SUMMARYSTATUS":"SUCCEEDED",
       "DOCUMENTID":"7604",
       "DOCUMENTNAME":"ExportBulkData_PayablesTransactionsExtract_1556"
    }

     

    Get BIP Data Content

    The callback response contains the "DOCUMENTID" which is the document Id of the file uploaded in the UCM. Please refer getDocumentForDocumentId operation to get the content.

    Operation: getDocumentForDocumentId

    The getDocumentForDocumentId operation downloads the job output file generated by bulkExportData. This operation requires application user access and access to the AttachmentsRead role.

    The following table lists the parameters for this operation:

    Parameter

    Name

    Description

    Parameter

    (In/Out)

    Mandatory

    Type

    Document ID

    The UCM document ID from the callback response.

    IN

    Yes

    java.lang.String

    return

    Returns a list of elements, each containing the details of the downloaded files. The details include the document ID, file content, file name, content type, file title, author, security group, and account.

    OUT

     

    List<DocumentDetailsVORowImpl>

    The following sample payload illustrates how to get file from UCM by Document Id:

    <soap:Body>

     <ns1:getDocumentForDocumentId  xmlns:ns1="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">

     <ns1:DocumentId>7604</ns1:DocumentId>

     </ns1:getDocumentForDocumentId>

    </soap:Body>

     

    Conclusion

    This post demonstrates how to implement ERP outbound flow. It illustrates the architecture of exporting data from the ERP Cloud to be consumed by external systems avoiding high touch integration scenarios for handling individual transactions. Integration tools can be further leveraged to automate the orchestration of the integration flow and processing data for downstream applications.

     

    Comment

     

    • Kristopher Neilsen

      What is the benefit of using this approach which requires 3 request/response routes instead of just creating a BIP report and running the External Report webservice to get the data in one call?

      • Juan Miguel Isip

        Hi, I was also wondering the same thing. Did you find out any advantage to this approach? Thank you!

        • Kristopher Neilsen

          Hi Juan,

          I did not find any advantage to this approach for extracting data. I am currently just creating a BIP report and extracting the results using the External Report webservice. If there is more than a certain amount of records being returned in the response, we are breaking it up and doing multiple calls to the External Report webservice by passing parameters to the BI Publisher report. 

          • Juan Miguel Isip

            Thanks Kristopher, would you happen to have links on how to use the External Report WebService? I am also working on creating a custom report and transferring it to a 3rd-Party application.

            • Kristopher Neilsen

              Sure no problem. I've been following the information in this link. http://www.ateam-oracle.com/using-oracle-bi-publisher-to-extract-data-from-oracle-sales-and-erp-clouds/ 

              • Jack Desai

                ExternalReportWSSService is good as long as your report is small. It's not recommended for large reports. Also this service returns report in BASE64 encoded format - MTOM attachment is not supported.

          • Jack Desai

            Advantage is for large extracts and it also callbacks when extract is completed