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
No alt text provided for this image
  • Create the BI report on above data model and set the output to Data (CSV)
No alt text provided for this image
  • Output of the report would like below in csv format.
No alt text provided for this image

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
No alt text provided for this image
  • Enter the Server Details, Test the connection and Apply (Save)
No alt text provided for this image

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

  • Click on Schedule of the report
No alt text provided for this image
  • Verify if data model populated in the Report field in General Tab
No alt text provided for this image
  • Enter the destination details and Select the FTP Server
No alt text provided for this image
  • Enter the Schedule Frequency
No alt text provided for this image
  • Click on Submit
No alt text provided for this image