Bursting in Oracle Fusion With Example

Bursting: Interesting word, wont you say it? but what does it have to do in Oracle Fusion? just like the literal meaning of the word, Oracle Fusion Bursting splits the output file into many pieces ( don’t worry, it will happen as you wish it to be) and deliver the output as required. Lets say you created 10 POs and a report to fetch them, now next logical step will be to send these POs to the vendor. But at the same time you wouldn’t want all the POs to go to every vendor either. so what do you do? You enable the bursting and send only those POs to the vendors which belong to them.

Oracle fusion provides us 3 ways to send the files:

1. Email
2. SFTP server
3. Printer

let’s discuss the email bursting here.

so let’s begin with assumption that your basic report is up and ready. for example we will consider the report query to be:

Report Query

select hp.party_name supplier, po.segment1 PO_NUM
from po_headers_all po,
poz_suppliers supp,
hz_parties hp
where po.vendor_id = supp.vendor_id
and hp.party_id = supp.party_id and po.creation_date>= sysdate -1
and hp.party_name = NVL(:supplier_name, hp.party_name)

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Bursting Concept

As you can see that this query fetches the POs and their supplier created in last 1 day. To enable the bursting, go to your data model and click on edit. Go to bursting and click on “+” sign. Give it a name and choose the data source depending on the product you are implementing. Now comes the prestige ( in a magic trick where the OOH comes out of the mouth). you will find two fields:

1. Split by
2. Deliver by

Now since the query returns the POs and corresponding supplier, you would want to provide the details of their own POs and not the POs of other suppliers. So you have to choose the “Deliver By” value as “Supplier”. Again, there can be two scenarios. Either you want to send all the details in a single file or you want to send one email for each PO. In case you need to send 1 file ( of course having the details of their own) to every supplier, choose the “Split By” value to “Supplier”. Just in case your suppliers need 1 email for each PO, choose the value of “Split By” to “PO_NUM”.

Now you have to develop a query to determine the recipients, subject line etc. Point to keep in mind is that for every distinct supplier in the output of the query, your bursting query must return at least one row. ( More rows means more emails per supplier). Query should look like:

Bursting Query

select distinct
hp.party_name as “KEY”, — value of this column must match the value of the field “Deliver By”.. NO match will mean no email
‘test’ TEMPLATE, –– this is the template name.. and not the RTF or .xsl etc
‘en-EN’ LOCALE, — Locale for which you have developed the template
‘PDF’ OUTPUT_FORMAT, — output format you desire
‘EMAIL’ DEL_CHANNEL, — This is the delivery channel. For email it has to be EMAIL, for other 2 bursting destinations we will discuss later
‘POs’||’_’||to_char(sysdate,’mm/dd/yyyy’) OUTPUT_NAME, — Name of the file
‘donotreply@mycompany.com’ PARAMETER1, — To field of the email
‘MOHIT_Saxena@XYZ.com’ PARAMETER2, — CC field of the email
‘bipublisher-report@oracle.com’ PARAMETER3, — this value will show up as “From” in the email
‘PO Document’||’-‘||po.SEGMENT1 PARAMETER4, — subject of the email
‘Hi’||chr(13)||’,’|| ‘Please find attached your PO Print Document.’ PARAMETER5, –– Body of the email
‘true’ PARAMETER6, — attachment flag. for pdf, excel keep it as true
‘donotreply@mycompany.com’ PARAMETER7 –Reply To
from po_headers_all po,
poz_suppliers supp,
hz_parties hp
where po.vendor_id = supp.vendor_id
and hp.party_id = supp.party_id
and hp.party_name = NVL(:supplier_name, hp.party_name)
AND PO.CREATION_DATE>SYSDAte-10

Related posts

Bursting – SFTP
OTBI vs BI Reporting in Oracle Fusion
OTBI Report Creation in Oracle Fusion
BI Report Development In Oracle Fusion
RTF Date Formatting With Example
RTF Number Formatting With Examples
Looping Finite Number Of Times in RTF

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com