Integrations and Extensions

Enterprise Resource Planning

Topic

    Jack Desai
    ERP Integration Services - Import Data
    Topic posted May 26, 2016 by Jack DesaiRed Ribbon: 250+ Points, last edited June 26, 2017, tagged Cloud Integration, Extensions, File Based Data Integration - FBDI, Financials, Fusion Applications Integration, Import Bulk Data, Integrated Cloud Services, PaaS - SaaS Extensions, Pre-built Integration, SaaS Integration, SOACS, SOAP Services, Transaction Services 
    6886 Views, 10 Comments
    Title:
    ERP Integration Services - Import Data
    Summary:
    Automate ERP Inbound Bulk Import Integration in R11 – Part I
    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 load and extract data to/from the cloud.

    Inbound bulk import is a powerful tool for loading data from any source in to Oracle ERP Cloud.  It supports one-time data migration and incremental load for replacing legacy ERP or hybrid solution with external applications.

    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 import integration.

    Note: Please refer the following post to optionally encrypt the ZIP file.

     

    ERP Inbound Flow

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

    • On-premise / PaaS based applications require to import recurring billing transactions into ERP Cloud
    • On-premise insurance claim processing applications creates Payables Invoices for handling payments

    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 import 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).

    Important Note: User must import root/intermediate Fusion Applications SSL certificate in their keystore to avoid downtime when a specific Fusion Applications SSL certificate is renewed. This specific certificate is renewed yearly and hence you must configure clients to trust root/intermediate certificate to avoid SSL trust failures.

     

    Design and Implementation

    These are the following design considerations:

    • Generate data file of the respective object being imported
    • Generate respective Import Job Property File (optional)
    • Invoke ERP Integration Service to load and import data into ERP Cloud

    Generate Data File

    The ERP File Based Data Import guides in the Oracle Help Center (http://docs.oracle.com) include integration templates to help you prepare external data for loading and importing. Each template includes table-specific instructions, guidelines, formatted spreadsheets, and best practices for preparing the data file for upload. Use the templates to ensure that your data conforms to the structure and format of the target application tables.

    In this example we have created “AP Invoices” using respective template from ERP Cloud.

    Generate Import Job Property  File

    The Job Details parameter in importBulkData operation includes the job definition and package names as well as the job parameters of the object being imported. To get job package and definition name, please refer to Viewing Details about Predefined Scheduled Processes. The following advanced options may be used to specify the Job Details data associated with the importBulkData operation:

    • Specify the Job Details parameter directly in the request payload 
    • Generate and add the Job Properties File as part of the data ZIP file. For example, your zip file will contain GlInterface.csv and jobDetails.properties. The jobDetails is the sample name – it could be any name, but must have “.properties” extension
    • Generate and upload the Job Properties File to UCM applicable account for reusability. There are two options:
      • The parameter file naming convention is as follows:
        • For example: Zip file name is GL_1234.678.ABC_12345678.zip
        • Job Parameter file name must be GL.1234.678.ABC.properties
      • You can give any name and in <jobOptions> of the request payload add the following:
        • jobParameterFile=<my_parameter_file_name>

    The job property file is in CSV format as follows:

    <job package name>,<job definition name>, <ZIP file prefix>,<Param1>,....<ParamN>

     

    Invoke ERP Integration Service

    Once the data file and job parameters are created respectively, you are ready to invoke ERP integration service to load and import data into ERP Cloud.

    In R11, the consolidated and simplified operation is available to import 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: importBulkData

     

    The importBulkData operation uploads a file to the UCM server based on the document specified and submits an ESS job to load and import the uploaded files to an application table.

     

    The following table lists the parameters for this operation:

     

    Parameter

    Name

    Description

    Parameter

    (In/Out)

    Mandatory

    Document

    Two mandatory document attributes:

    • Content: File content in Base64 encoding. 
    • FileName: Name of the zip file

    IN

    Yes

    Job Details

    Job details include the job name (Job definition name, job package name) and ParameterList.

    IN

    No, if parameter file is provided

    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

    These are the following supported values for ExtractFileType for callback option:

     

    ALL (default):

    All the following output files will be zipped together and uploaded to a respective UCM account.

    ERROR:

    Extract info from interface tables only

    LOG:

    Include ESS logs of load and import jobs

    OUT:

    Include ESS out of load and import jobs

    NONE:

    Do not include any information - file is not generated and not uploaded to UCM

    Example: 

    <jobOptions>ExtractFileType=ALL</jobOptions>

    IN

    No

    Response Code

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

    OUT

     

     

    Note: When a file upload to the UCM server fails, the remaining ESS jobs aren’t executed and a response code of zero (0) appears.

    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 (import failed or succeeded)

    1

    Send on import success

    2

    Send on import failure

     

     

    Sample Payload of importBulkData Operation

    The following sample payload illustrates the AP invoice import process request payload:

    <soap:Body>

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

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

      <ns2:Content>UEsDBBQAAAAIAHSsJUWkOHOwEwEAAHgDAAAXAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3bV0LFOwzAQBuAdiXewOjBdU5/ts+ 6EffAAtSBmEVFhjJKyqHN4AqoeszLP7HID0HEmmljRk5RMU67V7aergIGa8IHE/x1NxPhwngsBjBBdG8jlCAn+XizI/OvPX3wLjuvQJQSwECFAAUAAAACAB0rCVFpDhzsBMBAAB4AwAAFwAAAAAAAAAAACAAAAAAAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3ZQSwECFAAUAAAACAAze2pIXdvnRHQAAACpAAAAEQAAAAAAAAAAACAAAABIAQAAQVBURVNULlBST1BFUlRJRVNQSwUGAAAAAAIAAgCEAAAA6wEAAAAA</ns2:Content>

      <ns2:FileName>GL_1234.678.ABC_12345678</ns2:FileName>

      </ns1:document>

      <ns1:jobDetails></ns1:jobDetails>

      <ns1:notificationCode>30</ns1:notificationCode>

      <ns1:callbackURL>http://hostname:port/myCallbackService</ns1:callbackURL>

      <ns1:jobOptions></ns1:jobOptions>

      </ns1:importBulkData>

    </soap:Body>

     

    The following is the sample job property file of the payable invoices generated through template:

    oracle/apps/ess/financials/payables/invoices/transactions,APXIIMPT,GL_1234.678.ABC,#NULL,Vision Operations,#NULL,#NULL,#NULL,#NULL,#NULL,INVOICE GATEWAY,#NULL,#NULL,#NULL,1,#NULL

     

    Sample Response from importBulkData Operation

    The bulkIMportData response contains the Request Id of the job loading data into the interface table.

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

      <ns0:importBulkDataResponse 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/">2529</result>

      </ns0:importBulkDataResponse>

    </env:Body>

     

    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 bulkImportData operation.

     

    This is a sample callback response in JSON string:

    {

        "JOBS":

                    [

                        {"JOBNAME":"Load Interface File for Import",

                         "JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",

                         "DOCUMENTNAME":"apinvoiceimport.zip",

                         "REQUESTID":"2529",

                         "STATUS":"SUCCEEDED",

                         "CHILD":[

                                           {"JOBNAME":"Transfer File",

                                            "JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",

                                            "REQUESTID":"2530",

                                            "STATUS":"SUCCEEDED"},

                                           {"JOBNAME":"Load File to Interface",

                                            "JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",

                                            "REQUESTID":"2531",

                                            "STATUS":"SUCCEEDED"}

                                        ]

                        },

                        {"JOBNAME":"Import Invoices",

                         "JOBPATH":"/oracle/apps/ess/financials/payables/invoices/transactions",

                         "REQUESTID":"2532",

                         "STATUS":"SUCCEEDED",

                         "CHILD":[

                                           {"JOBNAME":"Import Invoices Report",

                                            "JOBPATH":"/oracle/apps/ess/financials/payables/invoices/transactions",

                                            "REQUESTID":"2533",

                                            "STATUS":"SUCCEEDED"}

                                        ]

                        }                      

                    ],

        "SUMMARYSTATUS":"SUCCEEDED",

        "DOCUMENTID":"23456"

    }

     

    Conclusion

    This post demonstrates how to implement ERP inbound flow. It illustrates the architecture of loading data into the ERP cloud from external systems avoiding high touch integration scenarios for handling individual transactions. Integration tools can be further leveraged to automate data file generation including enrichment capabilities to transform source data to respective template. 

    Comment

     

    • Storm

      Where can I find the GenerateImportProcessDetails.xlsm file to find out the job names for scheduling the ESS jobs while invoking the ERPIntegration  WebService.

      I am trying to find out the different job names to be used during the web service call to upload file , load and import data .

      For Ex. for Journal Import the Job name used while invoking the web service is JournalImportLauncher.

      Need to find the job names for all the different Finance and HCM data imports while using the web service.

      • Chris Thom

        In the FSM(Functional Setup Manager) you can find the job names by using the "Define Custom Enterprise Scheduler Jobs for <Product>".  The Name column has the value you are looking for.

    • Jack Desai

      Thanks Chris for sharing this information

    • Ravi Huggi

      None of the Value Chain Planning Jobs are listed under "Define Custom Enterprise Scheduler Jobs for Value Chain Planning". Is this approach not available for VCP Data Import process?

    • Joe Siers

      Thanks for posting, this is helpful. When I follow the steps to search 'Manage Custom Enterprise Scheduler Jobs'  I'm not finding the Journal import job definition. We are on Release 11.  Is there some other job that needs to be loaded?  Or did the process change in Release 11? 

      Thank you, 

      JSiers

    • Srinivas J

      Hi 

      Post mentions "The following is the sample job property file of the payable invoices generated through template:"

      Where can I get this job property file template? Can you please help?

    • Siva S

      Thanks for the detailed post on this API.

      I'm working on a similar requirement to load and kick off "Import Costs" in PPM. And I would like to know if there is a programmatic way of handling the following scenario.

      "Load Interface File for Import" is successfully loading records and"Import Costs" is unable to process the records due to some missing setups.

      In this case, Import Costs returned "SUCCESS" status. But later I realized that none of the records are imported, by looking at the generated summary report. Also, I wasn't able to find those records in PA Transaction Interface table either.

      Appreciate if you can provide suggestions about handling this.

      Thank you in advance!

      -Tej

      • Monish Munot

        Can you check if Import Base Tables process is triggered and completed successfully? If this process is triggered download and check using ADF DI, if you see any errors.

    • Milind Dalvi

      Do we have XSDs for callback service?

    • Isaiah Inuwa

      +1 to previous comment
      Where can we find the service definition for the Callback Service? I see several posts on how to do it using ICS, but we want to implement it without ICS.