Bursting Functionality in Oracle HCM Fusion
Business Requirement – Business needs report output to be a break in different PDF file. For example Payslip report, Form 16 Report, and any other report which is generating data for all the employees in a single PDF.
This is the same concept we used to use in Oracle EBS in XML Publisher report, in XML Publisher report we used to have a bursting control file that will take care of breaking the PDF in multiple PDF based on how XML data generated by Data Definition.
In oracle fusion we also have the same concept but in place of bursting control file, we called that as bursting query.
Steps to follow to develop a sample report to get an understanding of bursting functionality.
Step1- To Open the BI Publisher you can use the POD link/analytics
Go to Tools > Report and Analytics > Create a new data model
Step2- Write a query to get the data that is needed for the report and also take the email address and any unique key to do the bursting. Below is the attached query that can be used to prepare the data model.
Step3- Click on + button to create a DATA MODEL and choose the data source as HCM for Human Capital queries and Type of sql should be standard SQL.
Step4- Once data model got created, run the query and save the sample output to create a report on that.
Step5- Now when you get the data we can export the xml file to prepare our template.
Your xml file will show like below example
Step6- To create the template first step is to load the xml file downloaded in last step. To do that you should have BI Publisher plugin installed in your Microsoft word software, which can be easily downloaded from oracle software download section.
https://www.oracle.com/middleware/technologies/bi-publisher/downloads.html
Go to BI Publisher tab and click on sample xml which will ask you to load the xml file. Browse the downloaded xml file in your local.
Once loaded you will get below confirmation message.
Step7- Now to design the report template we can click on all fields, it will automatically create template based on xml tags.
Save the template in local, now we can attach the DATA MODEL to the template.
Step7- To attach the template we have to create a report and link data model to template.
Click on Create Report Button and it will pop up another screen. Click Cancel button as we already have template which we have to upload. Select the data model in upper left corner by clicking on search button.
Step8- Click on UPLOAD to load the template created earlier and choose the UPLOAD option.
Step9- Once template attached with Data model give the name of the report and save. Report create with extension of XDO and data model with extension XDM.
Step10- Now we can edit the Data Model and go to bursting and create bursting file here, we have predefined parameters in bursting given by oracle so need to make sure those are getting used properly.
Split by and Delivered by should be based on KEY which is unique.
For bursting file we can use the same query which we have used for Data Model but only thing to make sure is we have all the attributes available which needed to print in the report or needed to break the PDF and email id to send to the people.
For easy understanding we can say it should be like as mentioned below.
Step13- Once load the bursting file enable the bursting check box in report property.
Step14-Once done with the report development save the report and schedule the run to see the output and how it break in multiple PDF.
Click on Submit and give the Flow Name.
Here we can see multiple PDF generated and bursting happened successfully.
Here is the output mail got in mail box.
Requirement -How to make file name and subject name dynamic using bursting report.
For that we have to change the query and use dynamic column in place of static name.
Email output received in mail.
bursting file.sql
SELECT EmployeeNumber KEY,
'bitemplate' template,
'en-us' locale,
'pdf' output_format,
'true' save_output,
'english_hijrah'calendar,
'email' del_channel,
'Employee_detail_report' output_name,
This email address is being protected from spambots. You need JavaScript enabled to view it.' parameter1,
This email address is being protected from spambots. You need JavaScript enabled to view it.' parameter2,
'Email Detail Report' parameter3,
'Email Detail Report' parameter4,
'Email Detail Report' parameter5,
'true' parameter6,
This email address is being protected from spambots. You need JavaScript enabled to view it.' parameter7
FROM ( SELECT papf.person_number EmployeeNumber,
pea.email_address EmployeeEmail,
ppnf.display_name EmployeeName ,
To_char(pps.actual_termination_date, 'MM-DD-YYYY') TerminationDate
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_email_addresses pea,
per_periods_of_service pps ,
per_person_names_f ppnf
WHERE papf.person_id = paam.person_id
AND paam.assignment_type IN ( 'E', 'C' )
AND pps.actual_termination_date IS NOT NULL
AND pea.email_type = 'W1'
AND pea.person_id = papf.person_id
AND paam.primary_flag = 'Y'
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
and papf.person_number in ('4296218',
'2021148',
'4047305')
-- and paam.assignment_status_type like 'ACTIVE'
and paam.period_of_service_id= pps.period_of_service_id
AND Trunc(sysdate) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(sysdate) BETWEEN paam.effective_start_date AND
paam.effective_end_date
AND Trunc(sysdate) BETWEEN ppnf.effective_start_date AND
ppnf.effective_end_date )
Datamodelquery.sql
SELECT papf.person_number EmployeeNumber,
pea.email_address EmployeeEmail,
ppnf.display_name EmployeeName ,
To_char(pps.actual_termination_date, 'MM-DD-YYYY') TerminationDate
FROM per_all_people_f papf,
per_all_assignments_m paam,
per_email_addresses pea,
per_periods_of_service pps ,
per_person_names_f ppnf
WHERE papf.person_id = paam.person_id
AND paam.assignment_type IN ( 'E', 'C' )
AND pps.actual_termination_date IS NOT NULL
AND pea.email_type = 'W1'
AND pea.person_id = papf.person_id
AND paam.primary_flag = 'Y'
AND ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
and papf.person_number in ('4296218',
'2021148',
'4047305')
-- and paam.assignment_status_type like 'ACTIVE'
and paam.period_of_service_id= pps.period_of_service_id
AND Trunc(sysdate) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(sysdate) BETWEEN paam.effective_start_date AND
paam.effective_end_date
AND Trunc(sysdate) BETWEEN ppnf.effective_start_date AND
ppnf.effective_end_date
TemplateBuilder.rtf
Apps2Fusion Articles 