Configuring the Oracle ERP Cloud Adapter within Oracle Integration Cloud (OIC) for importing bulk data using File Based Data Import (FBDI) into ERP requires many considerations and some important configuration. This post outlines the steps and considerations required for an effective ERP bulk data import integration.

Table Of Contents

Overview
Considerations
Callback
FBDI
Job Properties
Oracle ERP Cloud Adapter Configuration
Connection
Integration
Basic Info
Actions
Operations
Response
Typical ERP Integration
Retrieve/Determine Data
Stage Each Required Data File
Stage Job Properties File
Zip All Files
Callback Integrations
Support Options
ERP
Viewing Import Jobs In ERP
Retrieving Job Properties From ERP
Manually Starting Jobs In ERP
Purge Interface Table Data
View AR Invoices
View AP Invoices
Universal Content Management (UCM)
References

Introduction

Configuring the Oracle ERP Cloud Adapter within Oracle Integration Cloud (OIC) for importing bulk data using File Based Data Import (FBDI) into ERP requires many considerations and some important configuration. Below are the steps and considerations required for an effective ERP bulk data import integration.

This blog will consider the import of Accounts Receivable (AR) and Accounts Payable (AP) invoices as examples.

Some support details will also be highlighted.

Please Note: Hot off the presses from Oracle OpenWorld 2019 last week, we learned that the functionality described below will be provided out-of-the-box sometime in the future.

Considerations

Callback

When using the bulk data import function of the Oracle ERP Cloud Adapter there is an option to have ERP callback to OIC providing details about the import into ERP. These details highlight any issues that the import job had importing the data. This enables the automated handling of success and failure within an OIC integration and may be used to trigger further business processing of the result.

If callback is a feature that is required then it is important that the following is addressed:

  1. If OIC and ERP are in different data centres then an SR needs to be raised to have Oracle enable communications from ERP to OIC.
  2. The callback by ERP implements the SAML Bearer Token policy. The user used by the Oracle ERP Cloud Adapter to connect to ERP is used by ERP as the issuer name and therefore must also be created for access OIC. The password is not important.
  3. There are two certificates that need to be imported into OIC as Message Protection Certificate certificates. These certificates are retrieved from the relevant ERP end point shown below. Navigate to the bottom of the WSDL and copy and paste each certificate into its own file ensuring the to place "-----BEGIN CERTIFICATE-----" at the beginning of the file on its own line and "-----END CERTIFICATE-----" at the end of the file on its own line.
    • ERP R12: https://<<erp hostname>>:<<port>>/fndAppCoreServices/ServiceCatalogService?wsdl
    • ERP R13: https://<<erp hostname>>:<<port>>/fscmService/ServiceCatalogService?wsdl

FBDI

The various FBDI imports are documented by Oracle at File-Based Data Imports. This documentation addresses each supported FBDI.

Of importance for an ERP based integration in OIC is the XSLM Template. Each template comes with a detailed instruction sheet providing details about how to generate the required files for import, provides low level detail about what data each file requires, and provides the ability for the user to fill in the data and generate the resulting data files directly. This gives a great overview of the final file format as the ordering of fields in the sheet is not necessarily a 1:1 mapping of the order of the data in the resulting file. The template is also the starting point for deriving the final business mapping from business data to ERP data.

The following samples of XSLM templates have been attached to this blog:

Job Properties File

Each FBDI based bulk data import into ERP requires a job properties file that provides ERP with details of the data to load. Execute an ERP manual job to determine the contents of the job properties file. Refer to the Using ERP - Retrieving Job Properties From ERP section below to see where to locate the job properties after a manual job execution. These properties are then added to the job properties file in the order indicated by the properties shown for the manual job in ERP.

Oracle ERP Cloud Adapter Configuration

Connection

The screenshot below shows the configuration for an ERP connection. Each field is described below:

  • ERP Services Catalog WSDL URL
    • ERP R12: https://<<erp hostname>>:<<port>>/fndAppCoreServices/ServiceCatalogService?wsdl
    • ERP R13: https://<<erp hostname>>:<<port>>/fscmService/ServiceCatalogService?wsdl
  • ERP Events Catalog URL (optional) - https://<<erp hostname>>:<<port>>
    This field is only relevant if creating integrations that will subscribe to events coming from ERP.
  • Security Policy - Username Password Token
  • Username - username used to connect to ERP
  • Password - password used to connect to ERP

1.-ERPConnectionConfig

Integration

Basic Info

  • What do you want to call your endpoint? - This is the name that will appear as the orchestration step.
  • What does the endpoint do? - Optionally provide a description

2.-IntegrationBasicInfo

Actions

Select Import Bulk Data into Oracle ERP Cloud Application

3.-IntegrationActions

Operations

  • Select Bulk Data Import Process
    • AR Invoice - Import AutoInvoice
    • AP Invoice - Import Payables Invoices
  • Leave all other fields as default

4.-IntegrationOperations

Response

  • Notification Mode - Email Notification. This will send an email to the email address for the user used to connect to ERP should relevant events based on the next field happen while importing the data. This email address should be configured for a support team.
  • Occurrence
    • Send on import failure - will send email notifications for failures.
    • Send on import success - will send email notifications for successes.
    • Send in any case - will send email notifications for failures and successes.
  • Enable Callback - select if call back is required
  • Callback
    • Integration Flow Identifier - The integration flow identifier for the callback integration
    • Integration Flow Version - The major version of the integration flow identified by the integration flow identifier above

5.-IntegrationResponse

Typical ERP Integration

Retrieve/Determine Data

Retrieve/Determine the data required for the import files using whatever mechanism is required e.g. callout to some API.

Stage Each Required Data File

Use separate stage actions to write out each data file. Each data file must be written to the same directory so that all files can be zipped successfully. It is extremely import that each data file is named correctly as per the FBDI specification for the relevant job. This can easily be determined by using the relevant XSLM template to generate the data files and then identifying each relevant file name from the result.

Stage Job Properties File

The name of the job properties file is based on the prefix of the name of the ZIP file.

  • For AR invoices - if the name of the zip file will be AR_20181002.zip then the job properties file will be named AR.properties
  • For AP invoices - if the name of the zip file will be AP_20181002.zip then the job properties file will be named AP.properties

Zip All Files

Use a stage action to zip up all of the generated files. The name of the zip file must conform to <<prefix>>_<<remaining name>>.zip. This is because the prefix portion is used in naming the job properties file. This zip file is then mapped into the Oracle ERP Cloud Adapter call out.

Callback Integrations

When ERP calls to the callback integration, as configured in the integration that calls out to ERP, it will pass details about logs as well as a reference to an attachment that is a ZIP file containing the original files sent to ERP, the results files sent from ERP, and the related job log files. Due to OIC shortcomings that are yet to be fixed by Oracle, this ZIP file cannot be simply unzipped by using the Stage File action. The file must be written to a file system somewhere and the read back to be unzipped. The approach used at ServiceCo to address this was to write to the SFTP server on the localhost of the OIC instance. This SFTP server was exposed only for access on localhost and secured to one specific user.

It is also important that once the file is written to the target file system to force a dehydration using the wait action. This ensures that any failure in the integration can be recovered without loss of the file data. The attachment is lost otherwise thereby rendering the integration instance as useless. It is also important to have the callback integration remove this saved file if the integration instance completes successfully. It is a good idea to leave the file in place if the integration instance fails to enable support fast access to this file. Having said that it is also a good idea to have a separate scheduled integration that will cleanup these files after a given amount of time so that the local OIC file system does not become full.

Support Options

ERP

Viewing Import Jobs In ERP

  1. Log into ERP as the user that OIC uses to connect to ERP

    6.-SupportERPLogin


  2. Navigate to Tools - Scheduled Processes

    7.-SupportERPToolsScheduledProcesses


  3. The list of jobs is shown and can be searched

    8.-SupportERPJobList

Retrieving Job Properties From ERP

  1. Follow the process to view import jobs in ERP as documented above.
  2. Locate the job of interest
    AR Invoice - Import AutoInvoice
    AP Invoice - Import Payables Invoice

    9.-SupportERPChooseJob


  3. At the bottom of the screen expand the parameters to see the properties for the job in argument order. To determine so of what these properties represent either locate the relevant documentation or follow the process below to manually start the same job so as to correlate the properties/parameters.

    10.-SupportERPJobProperties

Manually Starting Jobs In ERP

  1. Follow the process to view import jobs in ERP as documented above.
  2. Select "Schedule New Process".

    11.-SupportERPManuallyStartingJob


  3. Select the process from the Name drop down.

    12.-SupportERPManuallyStartingJobSelectProcess


  4. If the process is not in the name drop down then select "Search..." at the bottom of the list.

    1. Type in all or part of the name of the job and select Search.

      13.-SupportERPManuallyStartingJobEnterNameSearch


    2. Locate and select the job and select OK.

      14.-SupportERPManuallyStartingJobEnterNameSelectJob




  5. The job is now selected. Select OK.

    15.-SupportERPManuallyStartingJobJobSelected


  6. Fill in the parameters as required and select Submit. Click OK on the resulting popup.

    16.-SupportERPManuallyStartingJobJobParameters


  7. The job runs. The log/s for the job can be viewed by selecting the job and then selecting the appropriate attachment in the details for the job at the bottom of the screen.

    17.-SupportERPManuallyStartingJobJobRunResult

Purge Interface Table Data

  1. Follow the process to manually start the "Purge Interface Tables" job in ERP as documented above.
  2. For the job parameters select the following:
    • Import Process -
      AR Invoice interface table data - Import AutoInvoice
      AP Invoice interface table data - Import Payables Invoices
    • Select "Enable load request ID ranges
    • Start Load Request ID - starting process ID to remove related interface table data
    • End Load Request ID - ending process ID to remove related interface table data

      18.-SupportERPPurgeInterfaceTableDataParameters



View AR Invoices

  1. Log into ERP as the user that OIC uses to connect to ERP

    19.-SupportERPViewARInvoicesLogin


  2. Navigate to Receivables - Billing

    20.-SupportERPViewARInvoicesReceivablesBilling


  3. On the right side menu select the Search magnifying glass and enter the transaction number details you wish to search for (e.g. enter the invoice prefix) and select Search

    21.-SupportERPViewARInvoicesReceivablesBillingSearch


  4. The invoices are shown in the results screen. The results can be sorted by selecting the ascending or descending arrows above the relevant column.

    22.-SupportERPViewARInvoicesReceivablesBillingSearchResults

View AP Invoices

  1. Log into ERP as the user that OIC uses to connect to ERP

    23.-SupportERPViewAPInvoicesLogin


  2. Navigate to Payables - Invoices

    24.-SupportERPViewAPInvoicesPayablesInvoices


  3. A list of current invoices is shown. Further searching can be performed by selecting the Search magnifying glass in the right side menu.

    25.-SupportERPViewAPInvoicesPayablesInvoicesList

Universal Content Management (UCM)

After ERP processes data import jobs, in some cases it writes the results of the job into a zip file and stores it in Oracle Universal Content Management (UCM). This is the same zip file that is sent to the callback integration should one have been configured.

If the payload trace for a given integration instance is not available and the callback file has been removed from where it was stored then the only place to get the job files from is UCM. The steps below detail how to use UCM to access these job files. The steps will relate to AR and AP invoice import jobs.

  1. Ensure UCM credentials have been configured so as to enable access the relevant files in UCM.
  2. Log into UCM

    26.-SupportUCMLogin


  3. Choose the Standard Search

    27.-SupportUCMStandardSearch


  4. Change the Title field to Substring and enter Import (change this if not searching for AR or AP invoices) as the search text. Click Search

    28.-SupportUCMStandardSearchFields


  5. In the search results screen there will be results for both AR and AP invoices. The ImportBulkData_APXIIMPT_<<job number>> results are for AP invoice imports. The ImportBulkData_AutoInvoiceImportEss_<<job number>> results are for AR invoice imports.

    29.-SupportUCMStandardSearchResults


  6. Click the relevant file to download it.

References

Following is a list of locations that proved invaluable to create working OIC integrations with ERP: