Best Practices from Oracle Development's A‑Team
-
Architecture
March 29, 2017
Integrating with ERP Cloud using ICS
Introduction
Integration Cloud Service (ICS) is a PaaS cloud offering from Oracle that provides capabilities of integrating applications both on-cloud and on-premise (on-prem). ICS provides rich set of connectors/adapters to connect to Oracle's SaaS and on-prem applications and many third-party cloud and on-prem applications. One such connector that ICS provides is the Oracle ERP Cloud Adapter which is used to connect to Oracle Cloud ERP. Oracle Cloud ERP (ERP) offers the most complete and integrated Enterprise Resource Management solutions for operations, GRC, and performance management.
ERP exposes web-services (mainly SOAP) to integrate with ERP. This blog shows how to use ICS to integrate with ERP. One may wonder if ERP exposed websevices then what's the big deal in integrating with it? Why do we need this blog in the first place? While integration with ERP query services can be done in a simple manner, there are challenges dealing with bulk-load services. So what are these challenges?
- How can a very large file say 500 MB or more be read, transformed and uploaded to ERP?
- How to handle zip files that ERP expects as input?
- How to handle the callback response from ERP?
This blog looks into these challenges and provides a solution design to address these challenges using ICS. This blog takes the example of syncing Journal from a third-party application into ERP.
Journal Import into ERP using ICS
Solution Design
The entire solution is broken in 3 steps - Step1, Step2 and Step3. Each of these steps has an ICS flow associated. The solution design is shown below -
Step 1: Generate FBDI File
The first step is to generate File Based Data Import (FBDI) file for ERP's consumption.Most of the bulk integration with ERP is through FBDI File. There is nothing fancy about FBDI and it just a fancy name for flat file which looks like this -
The source application's data file may not be in the FBDI format always. In this case the source application data format needs to be converted to FBDI format. This can easily achieved using ICS mapper functionality (XSLT) to transform from source format to the destination format. However, there may be cases where the source generated file may be too large, for example, 500 MB. Anyone who has experience dealing with on-premise integration products like Fusion Middleware (FMW) can tell that reading large file in-memory is not a good approach. This is because there are chances of running out of memory reading the large file in-memory. Since ICS is a PaaS offering, there has been checks on the file sizes that can be read with FTP Adapter. ICS limits the largest file that can be read in-memory using FTP Adapter to maximum of 1 MB. With this being the case, how could one read a 500 MB source file, transform to FBDI format and then upload to ERP? Fortunately, ICS has ability to chunk read the large source file. This feature is called the Stage Read. Stage Read is a map reduce feature within ICS. Prior to making use of Stage Read in the ICS flow, one must make sure to download the file locally to ICS where ICS can read the file in chunks. The reason why the file must be downloaded to ICS prior to chunk reading it is because chunk reading from a FTP Server is very expensive.
With the above context, lets look at how the ICS flow to convert from source format to FBDI format looks like the below -
The flow has been marked with logical numbers to explain the different steps involved to generate the FBDI format.
- 1| Read Trigger File
- This is scheduled ICS integration that gets triggered based on a scheduled configured. The first step is to read the trigger file that will be put in FTP location. The trigger file is zero byte file having the same name as the source data file. The trigger file provides the name of the source data file to be read by the downstream activities in this ICS flow. A trigger file is used to make sure the source application can write the data file completely before ICS can pick up the data file to read. FTP Adapter in ICS does support the minimum age property but sometimes the source application may be trying to append to data file if there were many records. So using minimum age may not work in such scenarios and hence the need of having a trigger file.
- 2| Download Source Data File
- The next step is to download the source data file into ICS. As explained earlier the reason behind downloading the source data file is because the chunking of the file on FTP Server is expensive operation. The ICS flow was written with the requirement that the source data file is a compressed file (.zip file) that may contain one or more source data format files.
- 3| Chunk Read and Transform to FBDI
- In this step we make use of the Stage Read feature in ICS to chunk read file. Then every chunk is transformed from source format to FBDI format and then output FBDI file is written. The outer for-loop ensures that if there multiple files as a part of the source zip file is read and transformed to FBDI formatted file.
- 4| List File
- List file is needed for ICS to determine how many FBDI files are written out.
- 5| Generate Property File and Write zip file
- ICS uses ERP Adapter to upload FBDI file to ERP. Although the ERP Adapter makes uses of ERP provided webservices internally, it obfuscates many complexities involved in the raw ERP webservices. The ERP Adapter expects a zip file containing FBDI file and Property file to upload to ERP. The property file is a manifest file that will contain information on Jobname and Parameterlist that ERP needs to upload the FBDI file into ERP.
- 6| Archive Source File
- The source file is archived for the future on a FTP location for re-submission purposes if necessary in the future.
- 7| Archive Trigger File
- The trigger file is archived for the future on a FTP location for re-submission purposes if necessary in the future.
Step 2: Upload to ERP
The second step is to consume the FBDI file generated in Step 1 and upload the file to ERP. ERP exposes webservices (mainly SOAP) to upload data to ERP. While these services can be made use directly using a SOAP call, there are some complexities involved in terms of dealing with binary file like zip file to upload data and receive data. Retrieving and uploading the zip files using a simple SOAP call maybe little cumbersome. Fortunately, ICS has an ERP Adapter that abstracts several of these complexities. So, for the journal upload in this case, the ERP Adapter is used.
Configuring ERP Adapter Connection
Configuring the ERP Adapter is very easy. To configure the ERP Adapter the serviceCatalog wsdl is needed. The service catalog wsdl is https://<ERP Host Name>/fndAppCoreServices/ServiceCatalogService?wsdl. The serviceCatalog WSDL is a catalog of all services exposed by the ERP. The catalog exposes services from all the module such as finance, project management, order management, inventory management and so on.
The ICS flow to upload the journal to ERP is shown below.
- 1| Read FBDI Zip File
- The flow is triggered by a scheduler. When the scheduler kicks off the process, the first activity reads the FBDI zip file created in the previous flow. Since the file is a zip file there is no schema needed to read the file.
- 2| Invoke ERP using ERP Adapter
- To configure the ERP Adapter at Design time follow the steps below -
- The ERP Adapter takes the zip file containing the FBDI file and Property File as input. Since the read activity has read the file, just the file reference and name needs to be passed to the ERP Adapter as shown below.
Step 3: Receive Callback From ERP
In this ICS flow a callback from ERP is received. It is possible to receive a callback on SUCCESS, ERROR or BOTH. See configuration below.
Configuring ERP Adapter for Callback
- 1| Receive Callback from ERP
- The flow is triggered by when ERP asynchronously calls back ICS. The callback was configured when the ERP adapter was configured to invoke (see the screen shot in STEP 2 under section 2| Invoke ERP using ERP Adapter).
- 2| Write Result File
- The callback from ERP contains a zip file that has all the results of the jobs that ran on the ERP. This can be written out to a FTP location.
Data Received From ERP
The ERP Callback has 2 parts.
- 1. JSON Message
- The JSON message contains all the jobs that ran in ERP and its statues. The JSON looks something like this -
-
{
"JOBS":[
{
"JOBNAME":"Load Interface File for Import",
"JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
"DOCUMENTNAME":"JTest1.zip",
"REQUESTID":"603547",
"STATUS":"SUCCEEDED",
"CHILD":[
{
"JOBNAME":"Load File to Interface",
"JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
"REQUESTID":"603549",
"STATUS":"SUCCEEDED"
},
{
"JOBNAME":"Transfer File",
"JOBPATH":"/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader",
"REQUESTID":"603548",
"STATUS":"SUCCEEDED"
}
]
},
{
"JOBNAME":"Import Journals",
"JOBPATH":"/oracle/apps/ess/financials/generalLedger/programs/common",
"REQUESTID":"603550",
"STATUS":"ERROR"
}
],
"SUMMARYSTATUS":"ERROR"
}
- 2. ZIP File
- This zip file sent from the ERP contains the results of all the jobs that ran on ERP. The content of the zip file is explained in the picture below.
Summary
With the help of ICS orchestration pattern, complex integration scenarios can be designed. ICS provided ERP Adapter helps to integrate with Oracle ERP Cloud. The ERP Adapter hides a lot of complexities integrating with ERP especially dealing with batch integration using FBDI. ICS also provides Stage Read feature which is a map reduce feature in ICS. This feature is very critical when large files are to be read by ICS. With rich set of connectors and features, ICS is a service of choice when it comes to integrating between SaaS and SaaS or On-prem and SaaS. ICS offers the depth of integration capabilities dealing with Oracle SaaS and this blog has showed this for Oracle ERP Cloud.