XMLP Concurrent Program- Revisited with Bursting

Thanks to the wonderful XML Bursting article written by Prasad CP.
Following his explanations in this link, I have re-created the old tutorial for XML Publisher Concurrent Program that was written in Year 2006.
Old tutorial has now been further enhanced to burst the report output to each email address in the manager table.
This article simply shows the steps, however for detailed explanation of concepts bursting control file etc, please read Prasad CP's articles, where he has explained each node of the Bursting Control File.

You will also be able to download each of the component used in this demo.

Overview of steps in this article are

Create table which will provide data for Bursting Report
Create a report name XX_MANAGERS.rdf
Run the report in preview mode in reports designer
Add user parameters to this report and FTP to $AR_TOP/reports/US
Register this report as concurrent program of type XML
Add this report to Receivables group
Run this report and save the xml file as xx_manager_output.xml
Download & install XML Publisher desktop software latest version[as yet]
Import xx_manager_output.xml in MS Word, and create default layout
Save the default layout as rtf file MANAGER_XML_TEMPLATE.rtf
Preview the report output within MS Word using XMLP Preview option
Define data definition for the XMLP Report
Prepare a simple bursting file, with help of XML xx_manager_output.xml
Upload the bursting control file to data definition
Create XMLP Template XX_MANAGERS, and upload MANAGER_XML_TEMPLATE.rtf to this template.
Create pl/sql procedure xx_submit_bursting in apps schema, that submits fnd_request for XDOBURSTREP, with debug parameter= Y
Call XDOBURSTREP from After report trigger, passing it current request_id
Ensure that srw.user_exit('FND SRWINIT') has been called from before report trigger
FTP the XX_MANAGERS.rdf againt to $AR_TOP/reports/US
Run the report, and you will see the Bursted Output in mailbox.

Ensure that you have the desired patches, as per your EBS Level, as mentioned in Prasad's article.

select application_short_name, bug_number, to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') dated
from apps.ad_bugs
where bug_number = '5968876';

Run the below in non-apps schema [but for demo I am using APPS]
--Create the tables as below
create table xx_managers ( manager_no integer, manager_name varchar2(200) ) ;
insert into xx_managers ( manager_no, manager_name ) values ( 1000, 'Anil Passi') ;
insert into xx_managers ( manager_no, manager_name ) values ( 1001, 'Martin') ;
commit;
--add email address column to the table

commit;
Note:- To each record in the table, we will Burst the output via Email.
Please note, the intention is just to show the steps, and not necessary the breakdown of data structure.

Hence only a simple single repeating frame based layout is being used.

In control file you need to specify the level at which data should be broken down while bursting.

Create a new report as shown below, and name it XX_MANAGERS

Add the user parameters[i.e. P_CONC_REQUEST_ID, P_RESPONSIBILITY_ID, P_USER_ID] as shown above.

Preview the report in reports builder itself, by running the same in Reports Builder

FTP XX_MANAGERS.rdf to $AR_TOP/reports/US
Silly it is, but for demo I will use standard Application [forgive me, but I am making it easier for you to run the source code].

Create Concurrent program executable for XMLP report, as if it is a normal report

Create a concurrent program for the executable. Note that I have kept the shortname and the program name both as XX_MANAGERS.
IMPORTANT:- Note that format is XML

Register this program with Receivables request group.
Now, make the report available to default request set of “Receivables Manager” responsibility.

DECLARE
BEGIN
fnd_program.add_to_group(program_short_name => 'XX_MANAGERS'
,program_application => 'AR'
,request_group => 'Receivables All'
,group_application => 'AR');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Object already exists');
END;
/

Login to receivables Manager, and Run the report from Submit Request window, as a concurrent program submission.

Output will appear as shown below

IMPORTANT NOTE - Save the above XML outpur as xx_manager_output.xml on your PC.

Ensure that you have installed the latest version of XML Publisher desktop software

http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5887917

Open MS Word, you will see the menu’s as shown

Load the xml output of concurrent program into XMLP Desktop tool

Now, click on insert and click on Table/Form

Within Insert Table/Form, as shown below, drag the List G Manager No to Template section

The default Layout is created as below

You can preview the output as below from within MS Word, using XMLP Desktop Preview option

Preview is as shown below

Now, lets do the real bit in attaching the Concurrent Program definition to XML
Publisher. Logon to responsibility “XML Publisher Administrator”.

Click on “Data Definition” menu as above…so that we can create the XMLs source data Definition. Effectively we are saying that output of the Concurrent program becomes the data definition for XMLP.

Lets create “Data Definition” with the values in below fields
Name : XX_MANAGERS
Application : Receivables
Code : XX_MANAGERS [Note: This is the short name of the Concurrent program]
Start Date : Leave it to default Sysdate
Click on “Apply”

As soon as you create a Data Definition, you will then be presented with Bursting Control File option as shown below.
Click on the button for uploading Bursting Control File

Ensure that a bursting file created as below has been uploaded

Sample definition of bursting control file is

Ensure that temp directory is set, for this, navigate to Administration in XML Publisher Administrator

In this case, I am setting the Temporary directory to /tmp

Ensure that the XML Template file is there on the server. It can be anywhere on server, we simply note down the path to use it in bursting control file.

In the above example, the RTF template file[MANAGER_XML_TEMPLATE.rtf] is in /tmp
Hence in bursting control file, we will mention the path /tmp/MANAGER_XML_TEMPLATE.rtf
VERY IMPORTANT NOTE- In real life, this rtf file will not be in /tmp directory
If your concurrent managers run on DB Tier itself, then ensure that rtf template file has been FTP’ed to /tmp on DB Tier.

Create template as below, by uploading the rtf file during template creation
Name : XX_MANAGERS [this is the name of the template]
Code : XX_MANAGERS [short name of conc program]
Application : Receivables
Data Definition : XX_MANAGERS [the one that we created in prior step 12]
Start Date : Leave this default.
Type : RTF
Click on browse and select the RTF file[MANAGER_XML_TEMPLATE.rtf] that you had saved using MS-Word/

Click on Apply, and you will get the below record.

Your template has now been created

Run the below procedure in APPS Schema

CREATE OR REPLACE PROCEDURE xx_submit_bursting(p_request_id IN INTEGER) AS
n_request_id NUMBER;
BEGIN
n_request_id := fnd_request.submit_request('XDO'
,'XDOBURSTREP'
,NULL
,NULL
,FALSE
,p_request_id
,'Y'
);
END xx_submit_bursting;
/

Ensure that the procedure has been created

Open report XX_MANAGERS in reports editor, and in the After Report Trigger & Before Report Triggers, write code below

FUNCTION afterreport RETURN BOOLEAN IS
BEGIN
xx_submit_bursting(:p_conc_request_id);
RETURN(TRUE);
END;

FUNCTION beforereport RETURN BOOLEAN IS
BEGIN
srw.user_exit('FND SRWINIT');
RETURN(TRUE);
END;

Next, ftp the file XX_MANAGERS.rdf again to the $AR_TOP/reports/US. This is so, because, now we are calling the bursting process from the After Report Trigger

Run the XX_MANAGERS as below, this will submit the Bursting process too.

I can now see the emails in my inbox, as below

First email is as shown below

Second email is as shown below

Downloads
The rdf file can be downloaded from here
Click here to download bursting control file in this example
Click here to download xml output of concurrent program
Click here to download rtf file used in this example

Set as favorite

Bookmark

Email This

Comments (74)

Subscribe to this comment's feed

XMLP Concurrent Program- Revisited with Bursting
written by Srihari Balla , April 28, 2008

Anil,

Thanks for the wonderful article.

Sri

report abuse

vote down

vote up

Votes: +3

XML Publisher Report Bursting Program is erroring out
written by A. Swain , April 29, 2008

Hi Anil,

Thanks a lot for such beautiful article...

I am having some problem i.e. the standard 'XML Publisher Report Bursting Program' is erroring out . Can you please have a look at the bellow log message?

Exception in thread "main" java.lang.NullPointerException
at java.util.Hashtable.put(Hashtable.java:396)
at oracle.apps.xdo.oa.cp.CPUtil.getSessionProperties(CPUtil.java:42)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:80)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)
oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine
Program exited with status 1

Regards,
Swain

report abuse

vote down

vote up

Votes: +1

...
written by Anil Passi , April 29, 2008

Hi Swan

This error might be related to your org_id not being set.
Are you on R12 using MOAC?
If so, raise SR with Oracle, this is a bug. They will give you a patch.

Thanks,
Anil Passi

report abuse

vote down

vote up

Votes: +0

...
written by A. Swain , April 29, 2008

Hi Anil,
Thanks you very much. yes I am in R12. Yes I will raise SR for it.

Regards,
Swain

report abuse

vote down

vote up

Votes: +0

...
written by TUAN , May 15, 2008

Hi Anil,
Our Check printing process is failing because of the character recognition test and its signal level as new regulation from the Bank industry for Canada
required a specific font and a higher level of iron in the toner. In order to meet this requirement, we need to send the check to our printer using RTF and NOT PDF so the printer can use its character set and increase the amount of iron on the check. (from the printer people). However, in 11i I do not know the style or type to set for the printer to print with RTF file. Our process is working fine when sending a PDF to the printer using PDF Publisher print type. CAn you give me some insight on format ( PDF, RTF...) as well as printer style and type.

report abuse

vote down

vote up

Votes: +0

...
written by TUAN , May 19, 2008

Anil,
any advise on this?

report abuse

vote down

vote up

Votes: +0

XML Publisher Report Bursting Program going into error...
written by Kamlesh , May 26, 2008

Hi ,
I follow the above mentioned procedure but the program going into error. Plz. help me.

error---
XML/BI Publisher Version : 5.6.3
--Exception
null
java.lang.NullPointerException
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.getSystemTempDirectory(JCP4XDOBurstingEngine.java:413)
at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:106)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)

Regards,
Kamlesh Nikhade

report abuse

vote down

vote up

Votes: +1

...
written by Anil Passi- , May 26, 2008

Hi Kamlesh

Ensure that temp directory is set, for this, navigate to Administration in XML Publisher Administrator and set this up.
Also, ensure that this directory has write permissions for concurrent managers.

Thanks,
Anil Passi

report abuse

vote down

vote up

Votes: +1

XML Publisher Report Bursting Output going into error
written by Kamlesh , May 27, 2008

Hi , Anil

Thanx..it is done.

Now the program running fine. But when I select the range of employees for my payslip report the bursted output should email to the respective employee but it is going to the last employee from the range.And the attachment contains all employees data.

I confused , where I mistook.

Plz. help as asap.

Regards,
KamleshN.

report abuse

vote down

vote up

Votes: +1

...
written by Prasad-CP , May 28, 2008

Kamlesh ,
Could you please copy paste section of your report xml file and the control file ?
"request select "tag defines the group at which your report data should break , please check if its set to the employee group name as in the report .

report abuse

vote down

vote up

Votes: +0

...
written by Ivan , June 16, 2008

Anil, we have set up bursting in a test environment. The email can be sent with attachment only when we hard code the "to" email add***s. If we enter to = "${EMAIL_ADd***s}" the bursting fails with a no data in required field error. Also, the email subject and body are not being populated properly with the data in the report. Can you tell me what we need to do? It seems that the bursting is not picking up the xml data correctly. Here is the bursting file.

${DESCRIPTION} is a hard worker

report abuse

vote down

vote up

Votes: +0