Streamline report management with Oracle Integration Cloud (OIC) by effortlessly downloading Oracle Fusion BIP (Business Intelligence Publisher) reports. Using Oracle OIC, integrate and automate report retrieval processes, optimize efficiency, and enhance decision-making in your organization. We have already run Oracle Fusion BIP report in Oracle OIC and sent output via email in earlier post. Now we will download Oracle Fusion BIP report using Oracle Integration Cloud (OIC) to sFTP. We can re-use most of parts from previously built integration and add some specific functionality for sending output to sFTP. Let us build the solution in step wise approach as follow
Create a BIP report in Fusion ERP
As a first step, please ensure that you have a BI Publisher report created in Oracle Fusion Applications with required data model and layout set for the report. We will need path of the report to pass in while calling the API. Copy the path e.g. /Custom/Financials/Demo/DemoRpt.xdo (you should not prefix the shared folder in the path). This part is similar as we have done it earlier.
Create Multiple Connections in Oracle OIC
We will create two connections here. One for SOAP adapter to call Oracle Fusion BI report service and other for sFTP for transferring file to ftp. Login to Oracle Integration Cloud console using your credentials. Go to Integrations -> Connections and click Create button and choose SOAP adapter and enter WSDL URL as https://ur-erpinstance-here.oraclecloud.com/xmlpserver/services/ExternalReportWSSService?WSDL. Choose security policy and test and save your connection as shown below.
Next we need to define connection for sFTP as follows
Create Integration
We can re-use our previously built integration here. Navigate to Integrations in OIC console and on previous integration, choose clone and fill in details as follows and click clone. You will land in designer page of integration. Switch layout to Horizontal.
In canvas view, we will change some default variables. We will keep report path and output format variables and add new variable for target directory on sFTP (as shown below) so that we don’t need to change these every time by opening the integration code, we can just change these values during a specific schedule. In target directory, OIC will download Oracle Fusion BIP Report. Set these default values according to your environment.
Configure & Map SOAP Endpoint
Now we will add our previously created SOAP connection in canvas view after setting default variables. Please choose runReport operation of standard Fusion ERP SOAP web service as shown below and leave all other settings as default.
Similarly, we will map the runReport operation arguments as follows. The size data chunk download should be set to “-1” since it will not limit the response payload size.
As already encountered in other integration, we know that the response payload will have reportBytes with base64 encoded output of report. Also in order to write the file to target directory on sFTP as the last step of this workflow, we need this base64 encoded reportBytes tag in response payload decoded to base64 reference before we can pass this to sFTP connection mapper in OIC. For this we will use stage file action write file operation and we will also name the report output and attach current date time.
We also need to specify opaque file schema in stage file action. The schema can be downloaded here. Save it in opaque_schema.xsd file and attach it in next step.
Map the stage file response file reference to file reference of sFTP connection along with directory and filename attribute as follows
Our final BI report service integration to sFTP workflow looks like this
Activate and Test Integration
As a last step, set business identifier for tracking as per your requirement. Schedule and activate the integration to perform testing. Start the schedule with default parameters for report path, output format and output path. After the successful run, please check on sFTP server if the Oracle Fusion BIP report output is download.