Quick Trick to Export Data from Oracle ERP Cloud

Overview

Most modern applications today integrate and exchange data with each other using file-based approach. This approach involves source application placing the file in a SFTP server in the format the destination application would accept and then the destination application picking up the file and processing accordingly.

Oracle ERP Cloud also has the capability to integrate with other applications. Using in-built BI Publisher tool, reports can be created and scheduled to send data files to SFTP server at the desired frequency. Let’s walkthrough the different steps using a use case of sending the journal details to data warehouse. Journals data will be generated in a CSV format and the file will be placed on an SFTP server (Detailed steps described below). From there, ETL job of Data warehouse will pick the file from SFTP server, process and load data in datawarehouse tables.

Development Steps

1. Create a BI report showing the data in a csv format

Prepare the SQL and create a data model using BI Publisher
Create the BI report on above data model and set the output to Data (CSV)
Output of the report would like below in csv format.

2. Create an FTP Server Connection in BI Publisher

Navigate to FTP Delivery Tab and click on Add Server from Administration Administration -> Manage BI Publisher -> Delivery -> FTP
Enter the Server Details, Test the connection and Apply (Save)

3. Schedule the Report to send the file to FTP Server daily