BIP report email bursting with multiple email recipients

WITH clause using PL/SQL procedure and function

BIP report email bursting with multiple email recipients

1 - BIP Bursting

Bursting is a feature of splitting a single report based on an element in the data model and deliver the report documents to one or more destinations based on the second element in the data model.

The data for the report is generated by executing a query once and then splitting the data based on a “Key” value.

2 - Bursting Definition

Bursting definition is a component of the data model.

Split By: it is an element from the data that will govern how the data is split.
Deliver By it is the element from the data that will govern how delivery options are applied.
SQL Query: It is a SQL query that you define for BIP Publisher to construct the delivery XML data file. BI Publisher uses the results from the bursting query to create the delivery XML.

The bursting engine uses the delivery XML as a mapping table and delivers the split outputs to one or multiple delivery channels.

Examples of delivery channels are given below.

– EMAIL

– FAX

– FTP

– OBJECT STORAGE

– ODCS

– PRINT

– WCC

3. Email Bursting

In this example, we would send an email with a custom message along with the supplier invoice file as an attachment.

Before we start, Lets create a BIP Data model and Report to fetch the supplier invoices from Oracle Cloud ERP.

Now Let’s follow the below steps to add Bursting definition which will send emails to the supplier with their respective invoice information.

Report Output: Our report output has two suppliers’ invoices.

Step-1 Create Bursting SQL query

Edit the Data Model > Select Bursting > Click on “+” icon.

Split By: Select supplier name from list. In our case it is: /DATA_DS/G_1/SUPPLIER_NAME. This list displays all columns present your data set.
Deliver By: Select supplier name same as above from list.
SQL query: Create the Bursting SQL query as shown below.