XMLP Concurrent Program- Revisited with Bursting

Miscellaneous

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

Search Courses

Details: Category: Miscellaneous Technology; Published: 28 April 2008

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.

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 too

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 from within MS Word, using XMLP Desktop Preview option

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
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 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 , this will submit the Bursting process too.

I can now see the emails in my inbox

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

Anil Passi

Comments

0 #1 Ashutosh Raman 2008-04-28 17:08

Anil,

Thanks for the wonderful article.

Sri

<< Apr 2024 >>
Mon	Tue	Wed	Thu	Fri	Sat	Sun
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Search Courses

Miscellaneous Technology