ORACLE BURSTING QUERIES

 

                            Oracle Bursting Query

What is Bursting:

Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.

We use bursting to trigger a mail for multiple people at the same time. The data for the report is generated by executing a query once and then splitting the data based on a Key value. For each block of the data, a separate document is generated and delivered.

What is Bursting Defenition :

A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions.

When you set up a bursting definition, you define the following:

• The Split By element is an element from the data that governs how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.

• The Deliver By element is the element from the data that governs how formatting and delivery options are applied. In the invoice example, it is likely that each invoice has delivery criteria determined by the customer; therefore, the Deliver By element would also be CUSTOMER_ID.

• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

SQL Query for the Bursting Query :

---------------------------------------------------------------------------------------------------------------------

SELECT 

PAPF.PERSON_NUMBER  KEY, --- MUST BE SAME AS DELIVER BY

'NMC_ACCRUAL_NOTIFICATION' TEMPLATE, --  REPORT TEMPLATE NAME

'EN-US' LOCALE, -- TEMPLATE FORMAT

'HTML' OUTPUT_FORMAT, -- OUTPUT FORMAT PDF RTF TEXT ETC..

PAPF.PERSON_NUMBER||' ' OUTPUT_NAME, --PDF OUTPUT NAME

'EMAIL' DEL_CHANNEL, -- DELIVERY CHANNEL NAME EMAIL,FAX,PRINT ETC..

PEA.EMAIL_ADDRESS PARAMETER1, -- TO MAIL ADDRESS PEA.EMAIL_ADDRESS

'' PARAMETER2, -- CC MAIL ADDRESS

'DONOTREPLY@GMAIL.COM' PARAMETER3,  -- FROM MAIL ADDRESS

PAPF.PERSON_NUMBER||'This mail is triggered by bursting' PARAMETER4 -- SUBJECT

FROM 

PER_PERSON_SECURED_LIST_V PAPF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_EMAIL_ADDRESSES PEA

WHERE 1 = 1

AND PAAF.PERSON_ID = PAPF.PERSON_ID

AND PEA.PERSON_ID= PAPF.PERSON_ID

AND PAPF.PRIMARY_EMAIL_ID= PEA.EMAIL_ADDRESS_ID

AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE  AND PAPF.EFFECTIVE_END_DATE

AND SYSDATE  BETWEEN PAAF.EFFECTIVE_START_DATE  AND PAAF.EFFECTIVE_END_DATE

AND PAAF.ASSIGNMENT_TYPE = 'E'

AND PAAF.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')

--AND PAAF.DATE_PROBATION_END<SYSDATE

--AND (PAPF.PERSON_NUMBER =:P_PERSON_NUMBER OR :P_PERSON_NUMBER IS NULL ) --Parameter

-------------------------------------------------------------------------------------------------------------------------------------

This is the bursting which we use to burst the letters in the PDF formation or in the HTML format and etc.

There are many types of formats to burst the report. Please find below the formates.

Bursting Formats and their values to be entered in the bursting queries:

Output Format	Value to Enter in Bursting Query	Template Types That Can Generate This Output Format
Interactive	N/A	Not supported for bursting
HTML	html	BI Publisher, RTF, XSL Stylesheet (FO)
PDF	pdf	BI Publisher, RTF, PDF, Flash, XSL Stylesheet (FO)
RTF	rtf	BI Publisher, RTF, XSL Stylesheet (FO)
Excel (mhtml)	excel	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (html)	excel2000	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (*.xlsx)	xlsx	BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (mhtml)	ppt	BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (.*pptx)	pptx	BI Publisher, RTF, XSL Stylesheet (FO)
MHTML	mhtml	BI Publisher, RTF, Flash, XSL Stylesheet (FO)
PDF/A	pdfa	BI Publisher, RTF, XSL Stylesheet (FO)
PDF/X	pdfx	BI Publisher, RTF, XSL Stylesheet (FO)
Zipped PDFs	pdfz	BI Publisher, RTF, PDF, XSL Stylesheet (FO)
FO Formatted XML	xslfo	BI Publisher, RTF, XSL Stylesheet (FO)
Data (XML)	xml	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), Etext, XSL Stylesheet (HTML XML/Text)
Data (CSV)	csv	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), XSL Stylesheet (HTML XML/Text), Etext
XML	txml	XSL Stylesheet (HTML XML/Text)
Text	text	XSL Stylesheet (HTML XML/Text), Etext
Flash	flash	Flash

Oracle referred documents:

Basics of the ORacle Bursting query :

https://docs.oracle.com/middleware/12213/bip/BIPDM/GUID-A926D588-426F-47C7-8A9A-B0A7959C5814.htm#BIPDM350

-------------------------------------------------------------------------------------------------------------------

                                                    Thank You

                            Please comment for more information