BI Publisher bursting in Oracle Fusion

3342 Views

SaveSavedRemoved 0

Bi Publisher Bursting is a feature of Oracle Business intelligence publisher which allows you to send the output of the BIP report to different channels like FTP / EMAIL / UCM etc.

The same BI Engine is present in Oracle fusion applications and most of the features of BI Publisher can be used.

The common use cases when you want to use the bursting feature of BI Publisher is :

Send output to an email(s)
Send output with different layouts conditionally to different emails / channels.
Send output to FTP location.

Table of Contents

High Level Steps to do BI Publisher bursting in Fusion

Create BIP Data model
Create Report using the Data model
Add Layout(s) to the BIP Report
Create Bursting Query in Data model
Enable Bursting at Report definition level
Test by scheduling the Report

Lets See the Steps in Detail with an example

Let’s suppose we have 2 Layouts (Layout A and Layout B) for a BIP Report and the requirement is to send Layout A to Email 1 and Layout B to Email 2 along with their associated data.

Write a SQL based data model

SELECT
   arc.cash_receipt_id,
   arc.pay_from_customer,
   arc.status,
   arc.type,
   arc.receipt_number,
   arc.receipt_date,
   rac.trx_number,
   ara.amount_applied,
   ara.org_id,
   decode(sign(arc.cash_receipt_id - 30000), - 1, '[email protected]', '[email protected]') EMAIL 
FROM
   ar_cash_receipts_all arc,
   ar_receivable_applications_all ara,
   ra_customer_trx_all rac 
WHERE
   ara.cash_receipt_id = arc.cash_receipt_id 
   AND ara.applied_customer_trx_id = rac.customer_trx_id 
   and arc.org_id = rac.org_id 
   and arc.pay_from_customer = 300000047394603 
   and ara.org_id = 300000046987012 
   and ara.amount_applied > 1064340

Data model Output

We will split this data by email address and the condition is if the cash receipt id is > 30000 the email should go to [email protected] else it should go to [email protected].

Once the data model is ready, test the data and create a report

Assign two layouts templates in our case it is named BurstingDemo_Layout and BurstingDemo_Layout2

Save the report

Create a Bursting Query and use the data model query in its from clause.

The bursting query has a specific SQL template that lists the parameters for delivery types .

The important values of delivery XML are:

KEY — The Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data.

TEMPLATE — The name of the Layout to apply. Note that the value is the Layout name (for example, ‘Customer Invoice’), not the template file name (for example, invoice.rtf).

LOCALE — The template locale, for example, ‘en-US’.

OUTPUT_FORMAT — The output format.

Parameter List For Delivery Channel : Email

Parameter List For FTP Delivery Channel

SQL Bursting Query

SELECT distinct
   EMAIL KEY,   -- Split by
   IDCLAYOUT TEMPLATE,    -- Catalog
   :xdo_user_report_locale LOCALE,   -- English (es)
   'PDF' OUTPUT_FORMAT,   -- PDF,HTML,CSV,RTF,EXCEL
   'EMAIL' DEL_CHANNEL,   -- EMAIL/FAX/FTP/FILE/SFTP/PRINT/WEBDAV
   'Sales Report',   -- Output Name
   EMAIL parameter1,   -- To
   '' parameter2,  -- cc
   '[email protected]' parameter3,   -- From
   EMAIL || ' Sales Report With Template ' || IDCLAYOUT parameter4,
-- Subject
   'Please See Report Attached' parameter5,   -- Body
   'true' parameter6,   -- Attachment
   '[email protected]' parameter7,   -- Reply to
   '' parameter8 	-- Bcc
From
   (
      SELECT
         arc.cash_receipt_id,
         arc.pay_from_customer,
         arc.status,
         arc.type,
         arc.receipt_number,
         arc.receipt_date,
         rac.trx_number,
         ara.amount_applied,
         ara.org_id,
         decode(sign(arc.cash_receipt_id - 30000), - 1, '[email protected]', '[email protected]') EMAIL,
         decode(sign(arc.cash_receipt_id - 30000), - 1, 'BurstingDemo_Layout2', 'BurstingDemo_Layout') IDCLAYOUT 
      FROM
         ar_cash_receipts_all arc,
         ar_receivable_applications_all ara,
         ra_customer_trx_all rac 
      WHERE
         ara.cash_receipt_id = arc.cash_receipt_id 
         AND ara.applied_customer_trx_id = rac.customer_trx_id 
         and arc.org_id = rac.org_id 
         and arc.pay_from_customer = 300000047394603 
         and ara.org_id = 300000046987012 
         and ara.amount_applied > 1064340 
   )