Email BIP Report using Oracle OIC

Streamline report distribution with Oracle Integration Cloud (OIC) by emailing Oracle Fusion BIP (Business Intelligence Publisher) report outputs. Integrate and automate email functionalities, optimize report delivery processes, and enhance communication efficiency in your organization. In this post, we will develop a new Oracle OIC integration to run and email BIP report output. Let us build the solution in step wise approach as follow.

Create a BIP report in Oracle Fusion ERP

Ensure that you already have a BIP report 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).

Create Connection in Oracle OIC

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.

Create Integration

Navigate to Integrations in Oracle OIC console and click create button and choose Schedules Orchestration and fill in details as follows and click create. You will land in designer page of integration. Switch layout to Horizontal and choose our previously created connection in start activity.

In canvas view, we will first add assign action to set default variables for report path, output format and delivery email address (To email BIP report from Oracle OIC) 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. 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.

Now 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 explained in post, the response payload will have reportBytes with base64 encoded output of report. Also in order to send email 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 email notification action 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.

Attach the stage file response file reference to attachment of email notification action as follows

The final BI report service integration workflow looks like this

Activate and Test Integration

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 delivery email. After the successful run, please check your inbox for Oracle Fusion BIP report output delivered as an attachment.