ERP Integration using File Based Data Import: Oracle Integration

The Oracle ERP integration is one of the most common integration requirement in today’s world as most of the big organization is having Enterprise Resource Planning(ERP) to manage enterprise functions including accounting, financial management, project management, and procurement etc. The article is focused on the aspects which is required in order to connect Oracle ERP system with Oracle Integration Cloud (OIC) using File Based Data Import (FBDI).

For this article, we’ll consider the import of Account Receivables (AR) invoices as an example.

Some important link which might help you to extend your knowledge

Web Application in Oracle Visual Builder

Parent Child relationship in Oracle Visual Builder

Dependent/Cascading List Of Values (LOV) in VBCS

Here is the use case which we are trying to sort out here:

• The third party application generates CSV file and keeps the data at SFTP location. The file may be large than 10 MB
• Convert the source file into the pre-defined FBDI format and upload into the ERP
• Once ERP completes the import job, it will send a callback to OIC to notify the status of import job

Below are high level steps to achieve this integration

• Build a Scheduled integration
• List all the files from SFTP
• Iterate over the files and Download file on staging
• Read the file from staging in chunks and create the FBDI file
• Create the Job Properties file and Zip the FBDI and property file
• Use ERP adapter and call the “Import AutoInvoice” Bulk data import process. Register the callback integration identifier and version
• Create a callback integration in order to get callback upon ERP job completion

After the integration is completed, it will look like below

Let’s begin step by step in detail.

Create scheduled integration and list source file using SFTP

• Create a scheduled based integration and name it “ERP_Integration“
• Drop the FTP adapter and loop over the source files from the directory

Iterate over the files and Download file on staging

• Drop the For each activity and loop over the files
• Drop the FTP adapter and choose “Download File” Operation. Doing so we are making sure the files which are greater than 10 MB should be processed successfully. As OIC has limitation that it can’t read file which is greater than 10 MB. So in order to read large files, first we’ll have to stage the file

Read the file from staging in chunks and create the FBDI file

See the required FBDI format for AR invoice here

• Drop the “Stage File” activity and choose “Read File in Segments” option in order to read the staged file into chunks

• Choose the sample file from the next screen of “Stage File” and finish the wizard

• Drop the “Stage File” activity and choose “Write File” operation. In this wizard we’ll create the FBDI file on Stage.

• Choose the sample file from the next screen of “Stage File” and finish the wizard

• Map the required fields

Till this step we are able to read the source file and convert into the FBDI.

Create the Job Properties file and Zip the FBDI and property file

The FBDI bulk data import operation require a job property file that provides ERP with details of data to load. The properties file includes the job definition and package name as well as the job parameters of the object being imported. You must generate and add the Job Properties File as part of the data ZIP file. The job property file must be in csv format as follows:

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

The name of the properties file could be anything but extension must be .properties file. The third parameter of the property file should be as below.

For example: If the zip file name is AR_Invoice_20202402101010.zip and contains RaInterfaceLines.csv and ARInvoice.properties file then the value of third parameter should be AR_Invoice

See the blog on how to get job definition, package name and parameters list.

• Drop the “Stage File” activity and choose “Write File” operation. Select the sample csv in order to form property file.

Note: the FBDI and property file must be in same directory as both the files will go in single zip file.

• Map the property file mapper as per the job package name, definition name and params.
• Drop the “Stage File” activity and choose “Zip Files” operation

Use ERP adapter and call the Bulk data import process

• Drop ERP adapter and choose “Import Bulk Data into Oracle ERP Cloud” operation

• From the next screen, select “Import AutoInvoice” and select next button

• Select Enable Callback and enter Integration Flow Identifier ( ERPCALLBACK) and Integration Flow Version(01) of the integration which will receive the callback

• Open the mapper and map below
  • Zip Files response-File Reference —-> importBulkData-FileReference
  • Zip Files response-filename —-> importBulkData-filename

Save this integration and activate.

Create a callback integration

See the pre-requisite section for Callback Integration here

• Create a “App Driven Orchestration” integration and name it as “ERPCallBack” with version 01.00.0000 and make sure the identifier would be “ERPCALLBACK” as the same has been configured during ERP adapter in previous step.
• Drop the ERP adapter as Trigger and select “Receive Callback message upon completion of FBDI bulk import Job submitted via another Integration” option and select “Always” from dropdown

Activate both the integration and submit the ERP_Integration. Once the ERP_Integration executes successfully and ERP loads all the files into system, ERP will send a callback to OIC. See below screenshot which depicts the OIC has received the callback.