Manual File Based Data Integration in Oracle ERP Cloud

File Based Data Integration (FBDI) - a famous and simplified way of loading bulk data into Oracle ERP (Enterprise Resource Planning) cloud. This process allows one to load large data files into the tables of ERP.

In this blog, we will see how to manually upload data to ERP through FBDI process. Similar to on-premise EBS, Oracle ERP cloud also has many modules like Finance, Sales, SCM, etc. Each of them are different clouds with subscriptions. We will take one template from Oracle Financial Cloud as example.

For any kind of FBDI updates, templates have been predefined in Cloud and the data load can be done only and only in those formats.

1. Download FBDI template and Generate ZIP file:

1. First step is to download the required template. Go to google and type "Financials Fusion FBDI". Open the first link which says "File Based Data Import for Oracle Financials Cloud".

Note: For other clouds, you can search as "SCM Fusion FBDI", etc

2. You can see different FBDI templates for different operations like Invoice, Billing Data, Revenue, Receivables, etc. For our example, lets use AutoInvoice Import. 

1.1 FBDI Import Modules

3. Click on the link for AutoInvoice Import and down load the XLSM template - AutoInvoiceImportTemplate.xlsm
4. This template has different tabs. Ignore the Instructions tab for now. Rest of the tabs will to help load data into different tables.
5. Enter the data that you need to load in the excel template. In my example, I just need to load invoice data into Interface lines table. Hence, I entered data in just RA_INTERFACE_LINES_ALL tab.

1.2 FBDI Template with data

6. After the data has been prepared, go back to Instructions and CSV Generation tab and click Generate CSV File.
7. A zip file with name ArAutoinvoiceImport.zip is generated and a message "CSV and ZIP file have been generated" will appear on screen. 
8. If you extract the zip and see, you will find many CSV files. If data is added in all tabs of AutoInvoiceImportTemplate.xlsm, the zip will contain 4 CSV files. If data is added to one tab, only one CSV will be seen in the zip folder.

In my example, as I added data only in one tab, hence, I can see one CSV file with name RaInterfaceLinesAll.csv.

1.3 Generated ZIP and CSV

Now save the zip at some place. We will use this zip with data in ERP cloud console.

2. Load ZIP file into ERP Cloud:

Before we get into the technical aspects of how to load data into ERP cloud, lets first understand the concept of two tables in ERP - Interface tables and base tables. 

Interface tables are kind of staging tables. Data is present in Interface tables only until they are processed further and moved to base tables.

Base tables are the ones in which you can find the final data loaded into cloud. The same data will be visible in all screens of cloud.

1. Login to Oracle ERP Cloud console. Go to Tools -> Scheduled Processes.

2.1 ERP Console Scheduled Processes

2. Click on New Process. Search for job - "Load Interface File for Import".

2.2 Load Interface File for Import Job

Load Interface File for Import - This job allows any kind of imports into Cloud.

3. In the Import Process, enter name Import AutoInvoice. This job specifically loads Invoice data into cloud tables.

4. In the Data File, upload the ArAutoinvoiceImport.zip zip file that you saved in the above section and click on Submit. 

2.3 Load Interface File for Import Job Details

Job has been successfully created and Job Id will be shown on screen. Keep a note of this job id.

The above job extracts the data from the zip file and loads them into Interface tables.  You can see the log of the job and the file that has been loaded.

2.4 Load Interface File for Import Job Log

There is yet another step to move data from interface to base tables. 

Import Auto-Invoice: This job takes care of moving invoice data from interface tables to base tables. When we are loading data manually, we need to submit 2 jobs. But when submitting through a service, there is one service which takes care of the above two steps. Service details are not covered as part of this blog.

5. Go to Scheduled Processes again -> Click New Process and search for "Import AutoInvoice". This job will ask for many parameters like BU, Transaction Source, etc. Enter whatever is mandatory/applicable and click submit. 

2.5 Import Auto-Invoice Job Input

This job will do the following internally. If the interface table has 50 records, the job will filter the records based on the parameters submitted above, fetch the applicable records from interface table and load into the base table.

For eg: If the interface two records, one with Business Unit Name - US and other with UK. And in Import AutoInvoice job, you give the parameter as US, it will fetch and load only US record into base tables.

You can check the status of the job as follows - 

2.6 Import AutoInvoice Job Status

For the submitted job, you can also check the parameters that are passed. Keep a note of these parameters. When we try to trigger a service to create this job, we would need to pass all the below parameters to the service (including nulls).

2.7 Import AutoInvoice Submitted Job Parameters

This way, FBDI can be done manually. 

Before implementing FBDI process by calling the associated service in your relevant technologies, I would highly recommend the developer to perform this process once manually, so that you are aware of the steps that take place in the process and the parameters that need to be passed.