Place file in FTP Server from BIP using OIC

In this article, I’ll explain how to use Oracle Integration cloud to place file from BI Publisher report to FTP server.

I’ll develop a scheduled integration that will run a BIP report in fusion and place the file in SFTP location.

This use case requires five simple steps to complete:

Create BI Publisher report
Create SOAP connection
Create FTP connection
Create an Integration
Activate the Integration

Lets say we have created a simple data model and a report for that data model in fusion instance.
Now to invoke that report from OIC we will need to have a SOAP connection. Oracle has provided a webservice ExternalReportWSSService with runReport Operation which helps to run a report which is on BIP of Oracle ERP Cloud and get the output in base64 format. So we create a connection using this WSDL and fusion instance credentials.

Service WSDL URL: https://servername/xmlpserver/services/ExternalReportWSSService?WSDL and Operation: runReport

In order to place the BIP report output in SFTP location we need to create the FTP connection. Using the FTP host address, port, username and password we can create an FTP connection.

Lets start by creating the scheduled integration. Give some name to the integration like INVOKE_BIP.

Now the first step will be to invoke the BIP report and for that we need to use the connection that we have created in step 2.

Enter “RunBIPReport” in “What do you want to call your endpoint” then click on next and select the “RunReport” operation.

Click on Next. leave the values as default and click on Next again and then click on Done and finish the wizard.

Now you will see a mapper for this SOAP connection. Click edit on the mapper.

As per the sample input payload map the following columns:

reportAbsolutePath : Absoulute path of the BI Publisher report. From custom till the end (.xdo)
sizeOfDataChunkDownload : -1. This will return all the report bytes
In case the report has parameters, map the name and value under parameterNameValues. Name will be the name of the parameter that you have used in the BI Publisher report and the value will be the parameter that we add in the scheduled parameter.

Once the request payload is completed. Click on validate and then close. So now you have configured the REST connection and we have the file in base64 encoded format. Now we have to invoke the SFTP connection and pass this file reference after decoding it. Search for the FTP connection that you have created in step 3.

Enter WriteFileToSFTP in What do you want to call your endpoint then click on next and select the operation as WriteFile, trasfter mode as “Binary“, specify the output file name and file directory in SFTP here or we can configure the same in the mapper. Click on next and in Do you want to specify the structure for the content of the File? select No and click on Next and then Done.

Now open the mapper for WriteFileToSFTP and map the reportBytes BIPReport Response ICSFile FileReference of WriteFileToSFTP Request using decodeBase64ToReference function.

Now click on validate and close. On the top right (below start button) click on Tracking and add the startTime in the tracking field and click on save.