Generate an File-Based Data Import (FBDI) File

The first step is to generate File Based Data Import (FBDI) file for consumption by Oracle Cloud ERP.

Most bulk integrations with ERP use FBDI files. An FBDI file is a flat data file with contents similar to the following:

NEW,1,2018/03/01,Balance Transfer,Remote Transfer,USD,2018/03/01,A,01,000,1212,0000,000,,,,,,,,,,,,,,,,,,,,,,,,,,99.00,,,,Team A Team Batch5,,,Orange Journal5,,,,,,,,,,,,,,,,,,,,,123,,,,,,,,,,,,,,,,,,,,,,,,,,END
NEW,1,2018/03/01,Balance Transfer,Remote Transfer,USD,2018/03/01,A,01,000,4111,0000,000,,,,,,,,,,,,,,,,,,,,,,,,, ,99.00,,,,Team A Team Batch5,,,Orange Journal5,,,,,,,,,,,,,,,,,,,,,123,,,,,,,,,,,,,,,,,,,,,,,,,,END

If the source application's data file is not in the FBDI format you must convert it to the FBDI format using the Oracle Integration mapper functionality (XSLT) to transform it from the source format to the destination format. However, there may be cases where the source generated file is too large, for example, 500 MB.

Reading a large file in-memory can cause it to run out of memory. Since Oracle Integration is a PaaS offering, there are checks on the file sizes that can be read with the FTP Adapter. Oracle Integration limits the largest file that can be read in-memory using the FTP Adapter to a maximum of 1 MB. However, Oracle Integration can read large source files in a series of chunks using a feature called Stage Read. Stage Read is a map reduce feature within Oracle Integration.

Because chunk reading from an FTP Server is process and bandwidth expensive, Oracle recommends that you first download the file locally to Oracle Integration before using Stage Read in the Oracle Integration flow.

Use a File-Based Data Import (FBDI) Orchestration Pattern

The following shows an example Oracle Integration orchestration pattern to convert one or more files in a source ZIP file to the FBDI format.

The logical flow to convert from a source format to the FBDI format follows these steps:

1. Read the trigger file.
2. Download the source data file.
3. Chunk read and transform the data to FBDI format. The outer loop ensures that multiple data files are each processed.
4. Create a list file.
5. Generate one or more property files and write the ZIP file.
6. Archive the source file.
7. Archive the trigger file.

For example:

Description of the illustration journal-entry-1.png

Read the Trigger File

Reading the trigger file is a scheduled Oracle Integration integration.

The trigger file is accessed based on a configured schedule. The first step is to read the trigger file that you place on an FTP server. 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 Oracle Integration flow. A trigger file is used to in order to make sure the source application has written the data file completely before Oracle Integration tries to access the data file to read. The FTP Adapter in Oracle Integration does support the minimum age property, but sometimes the source application may be trying to append to a data file if there were many records. Since minimum age is not available for such scenarios, instead a trigger file is used.

1. Start the upload of your data file to your FTP server.
2. Wait for the data file to be completely written.
3. Write a trigger file to the same location on the FTP server.
   When the scheduled Oracle Integration flow sees the trigger file on the server, it begins downloading the data file of the same name.

Download the Source Data File

The next step is to download the source data file into Oracle Integration.

The Oracle Integration flow was written with the requirement that the source data file is a compressed ZIP file that may contain one or more source data format files.

1. In the Oracle Adapter Endpoint Configuration Wizard, Configure the Operation Parameters for the Target FTP Endpoint, enter your Basic Info and then click Next to get to the Operations step.
2. In Select Operation, select Download to ICS.
3. In Download Directory Path, enter /fbdi/.
   The path is on the Oracle Integration server, which you cannot access. Oracle Integration creates this folder when needed, and then cleans up the folder after it's done.
4. Check the Unzip the file box.
5. Click Next and complete the remaining steps of the wizard.

Chunk Read and Transform the Data File

Use the Stage Read feature in Oracle Integration to read a data file in smaller chunks.

Each chunk is transformed from the source format to the FBDI format, and then creates an output FBDI file. The outer for-loop shown in the diagram ensures that if there are multiple files as a part of the source ZIP file, each one is read and transformed to a separate FBDI-formatted file.

Generate the List File

Generate a list file for Oracle Integration to determine how many FBDI files were created.

Create the Property and ZIP Files

In this step, generate a property file and write the ZIP file.

Oracle Integration uses the Oracle Cloud ERP Adapter to upload FBDI files to Oracle Cloud ERP. Although the Oracle Cloud ERP Adapter makes use of Oracle Cloud ERP-provided web services internally, it obfuscates many complexities involved in the raw web services. The Oracle Cloud ERP Adapter expects a ZIP file containing FBDI and Property files to upload to Oracle Cloud ERP. The property file is a manifest file that contains information on Jobname and Parameterlist that the Oracle Cloud ERP Adapter needs to upload the FBDI file into Oracle Cloud ERP.

Archive the Source and Trigger Files

The source and trigger files are archived for future reference on an FTP location.

For example, you can use these files for re-submission purposes.