e enjte Maj 26, 2016

ERP Integration Services - Import Data

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.

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

Design and Implementation

These are the following design considerations:

  • Generate data file of the respective object being imported
  • Generate respective Import Parameter File
  • 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 Parameter File

Starting R11, ERP integration is further simplified to provide job parameters to load and import data. The new options are to generate parameter file from “GenerateImportProcessDetails.xlsm” template in the Oracle Help Center (http://docs.oracle.com). These are the following options:

  1. Include the parameter file in your zip file
    • For example, your zip file will contain GlInterface.csv and jobDetails.properties
    • The jobDetials is the sample name – it could be any name, but must have “.properties” extension
  2. Upload parameter file to respective UCM account and use the following 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>

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>.xxx.oraclecloud.com”.
In this example “<pod-name>.<lba>” is hostname and “xxx.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

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

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 parameter 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. 

e enjte Shk 18, 2016

Oracle HCM Cloud – Bulk Integration Automation Using SOA Cloud Service

http://www.ateam-oracle.com/fusion-hcm-cloud-bulk-integration-automation-using-soa-cloud-service/

HCM Atom Feed Subscriber using SOA Cloud Service

http://www.ateam-oracle.com/hcm-atom-feed-subscriber-using-soa-cloud-service/

HCM Atom Feed Subscriber using Node.js

http://www.ateam-oracle.com/hcm-atom-feed-subscriber-using-node-js/

Fusion HCM Cloud – Bulk Integration Automation Using Managed File Transfer (MFT) and Node.js

http://www.ateam-oracle.com/fusion-hcm-cloud-bulk-integration-automation-using-managed-file-transfer-mft-and-node-js/

Invoke Fusion Cloud Secured RESTFul Web Services

http://www.ateam-oracle.com/invoke-fusion-cloud-secured-restful-web-services/

Node.js – Invoking Secured REST Services in Fusion Cloud – Part 1

http://www.ateam-oracle.com/node-js-invoking-secured-rest-services-in-fusion-cloud-part-1/

Fusion HCM Cloud Bulk Integration Automation

http://www.ateam-oracle.com/fusion-hcm-cloud-bulk-integration-automation/

Fusion Applications WebCenter Content Integration – Automating File Import/Export

http://www.ateam-oracle.com/fusion-applications-webcenter-content-integration-automating-file-importexport/

e enjte Pri 15, 2010

OSB 10gR3 High Availability

[Read More]
About

bocadmin_ww

Search

Categories
  • Oracle
Archives
« maj 2016
DieHënMarMërEnjPreSht
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
27
28
29
30
31
    
       
Today