XML Publisher

Overview: Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output in PDF, HTML, RTF, EXCEL (HTML), or even TEXT for use with EFT and EDI transmissions

Basic Need for XML: Consider the following scenarios

We have a RDF report with tabular layout which prints in English

New Requirements:

  1. User1 wants the same Report needs to be printed in Spanish
  2. User2 wants the Same Report needs to be printed in chart format
  3. User3 wants the Same Report output in Excel
  4. User4 wants the Same Report output to be published on intranet or internet
  5. User5 wants the Same Report output eliminating few columns and adding few other

A new RDF needs to be created for each requirement stated above or an existing RDF needs to be modified with huge amount of effort but whereas with XML Publisher it can be done very easily.

XML Publisher separates a reports data, layout and translation components into three manageable pieces at design time; at runtime all the three pieces are brought back together by XML Publisher to generate the final formatted, translated outputs like PDF, HTML, XLS and RTF. In future, if any there is any change in layout we just need to add/modify the Layout file

alt

alt

Data Logic: Data extracted from database and converted into an XML string.

Layout: The layout templates to be used for the final output are stored and managed in the Template Manager.

Translation: The translation handler will manage the translation that is required at runtime

In brief the steps are as follows:-
a.    Create a procedure and register it as Concurrent Program so that we write XML  tags  into output file.
b.    Build a Data Definition & XML Template using XML Publisher.
c.    Create a relation between XML Template & Concurrent Program and run the concurrent program

Requirements for XML Data Object Reports

  1. Oracle XML Publisher Release 5.5 patch 4206181
  2. Template Builder 5.5

Template builder is used to create template/layout for your report. Usually Template builder 5.5 is available in Oracle XML Publisher patch itself but you can also download it from http://edelivery.oracle.com. First select Oracle Application Server Products then select your platform and then locate the Oracle XML Publisher Release 5.6.2 Media Pack v1 for Microsoft Windows, as below:

alt

Download the Desktop edition from the below:

alt

When you download the XML Publisher Desktop edition you get a Zip file containing setup for  XML Publisher Desktop Install Shield, this installs some components into Microsoft Word.

alt

After installing, the Word Add-Ins is attached to the menu bar for the word document. This menu lets you attach an XML data source document, add the XML data to your template, set preferences and preview the output.

alt

In detail along with screenshots:-

A concurrent program is written that spit out an XML file as output such concurrent program can be of type SQL or PL/SQL or Oracle Report or any other supportable type, provided it can produce a XML output.

1. Here I have a very simple PL/SQL procedure, which fetch the records from AR tables and write the output in xml tags.

CREATE OR REPLACE PROCEDURE APPS.Demo_XML_Publisher (errbuf VARCHAR2,retcode NUMBER,v_customer_id VARCHAR2)
AS

/*Cursor to fetch Customer Records*/
CURSOR xml_parent
IS
SELECT   customer_name , customer_id
FROM      ra_customers
WHERE   customer_id = to_number(v_customer_id);

/*Cursor to fetch customer invoice records*/
CURSOR xml_detail(p_customer_id1 NUMBER)
IS
SELECT ra.customer_trx_id customer_trx_id, ra.ship_to_customer_id ship_to_customer_id, ra.trx_number trx_number,aps.amount_due_original ams
FROM  ra_customer_trx_all ra, ar_payment_schedules_all aps
WHERE  ra.ship_to_customer_id = p_customer_id1
AND aps.customer_trx_id = ra.customer_trx_id
AND      ROWNUM<4;

BEGIN
/*First line of XML data should be <?xml version="1.0"?>*/
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0"?>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMERINFO>');
FOR v_customer IN xml_parent
LOOP
/*For each record create a group tag <P_CUSTOMER> at the start*/
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<P_CUSTOMER>');
/*Embed data between XML tags for ex:- <CUSTOMER_NAME>ABCD</CUSTOMER_NAME>*/
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_NAME>' || v_customer.customer_name
|| '</CUSTOMER_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_ID>' || v_customer.customer_id ||
'</CUSTOMER_ID>');

FOR v_details IN xml_detail(v_customer.customer_id)
LOOP
/*For  customer invoices create a group tag <P_INVOICES> at the start*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<P_INVOICES>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_TRX_ID>' ||
v_details.customer_trx_id || '</CUSTOMER_TRX_ID>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER_ID>' ||
v_details.ship_to_customer_id || '</CUSTOMER_ID>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INVOICE_NUMBER>'||
v_details.trx_number||'</INVOICE_NUMBER>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<AMOUNT_DUE_ORIGINAL>'||
v_details.trx_number||'</AMOUNT_DUE_ORIGINAL>');

/*Close the group tag </P_INVOICES> at the end of customer invoices*/
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</P_INVOICES>');
END LOOP;

/*Close the group tag </P_CUSTOMER> at the end of customer record*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</P_CUSTOMER>');
END LOOP;

/*Finally Close the starting Report tag*/
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</CUSTOMERINFO>');
exception when others then
FND_FILE.PUT_LINE(FND_FILE.log,'Entered into exception');
END Demo_XML_Publisher;
/

2. Create an executable SampleXmlReport for the above procedure Demo_XMML_Publisher.

Go to Application Developer Responsibility->Concurrent->Executable

alt

3. Create a new concurrent program SampleXmlReport that will call the SampleXmlReport executable declared above. Make sure that output format is placed as XML.

Go to Application Developer Responsibility -> Concurrent ->Program

alt

4. Make sure we declare the parameters for the procedure.

alt

5. Add this new concurrent  program with Receivables request group. Either using the following code or through below application screen.

DECLARE
BEGIN
FND_PROGRAM.add_to_group
(
PROGRAM_SHORT_NAME  =>'CUST_XML_SAMPLE'
,PROGRAM_APPLICATION =>'AR'
,REQUEST_GROUP       => 'Receivables All'
,GROUP_APPLICATION   =>'AR'
) ;
commit;
exception
when others then
dbms_output.put_line('Object already exists');
END ;
/

Go to System Administrator Responsibility ->Security ->Responsibility->Request

alt

6. From the receivables responsibility (depends on which responsibility we added our concurrent program here it is receivables)

From the menu View->Requests->Submit A New Request->Single Request

alt

Note: The layout field is blank as we haven’t attached any Template or layout to this concurrent program yet.

By submitting the above request we get the output in xml (depending on procedure) as follows:

<?xml version=”1.0″ ?>

 

- <CUSTOMERINFO>

- <P_CUSTOMER>

<CUSTOMER_NAME>UNIV OF CHICAGO HOSP</CUSTOMER_NAME>

<CUSTOMER_ID>1119</CUSTOMER_ID>

- <P_INVOICES>

<CUSTOMER_TRX_ID>929476</CUSTOMER_TRX_ID>

<CUSTOMER_ID>1119</CUSTOMER_ID>

<INVOICE_NUMBER>2484403</INVOICE_NUMBER>

<AMOUNT_DUE_ORIGINAL>8000</AMOUNT_DUE_ORIGINAL>

</P_INVOICES>

- <P_INVOICES

<CUSTOMER_TRX_ID>929374</CUSTOMER_TRX_ID>

<CUSTOMER_ID>1119</CUSTOMER_ID>

<INVOICE_NUMBER>2484267</INVOICE_NUMBER>

<AMOUNT_DUE_ORIGINAL>380.68</AMOUNT_DUE_ORIGINAL>

</P_INVOICES>

- <P_INVOICES>

<CUSTOMER_TRX_ID>806644</CUSTOMER_TRX_ID>

<CUSTOMER_ID>1119</CUSTOMER_ID>

<INVOICE_NUMBER>2421373</INVOICE_NUMBER>

<AMOUNT_DUE_ORIGINAL>615.96</AMOUNT_DUE_ORIGINAL>

</P_INVOICES>

</P_CUSTOMER>

</CUSTOMERINFO>

7. Save the above code as SampleXmlReport.xml

Note: Before saving the XML string in notepad remove the dashes –

8. Create Template/Layout for the report using Template Builder. Here is a sample template.

alt

Note the following:

The data fields that are defined on the template

For example: Customer Name, Customer Id

The elements of the template that will repeat when the report is run.

For example, Customer trx id , Invoice Number and Original Amount Due. All these fields on the template will repeat for each Employee that is reported.

9. Mark up your template layout.

Like a mail-merge document there’s placeholders for the data you’re going to add, and then you can add whatever formatting you like.

10. Now the next step is to select Data > Load XML Data from the toolbar menu, then pick up the XML data a file ie.SampleXmlReport.xml. Once the data is loaded, a “Data Loaded Successfully” dialog box comes up and you can then start adding data items to the template.

alt

11. To add data items from the XML file into your report, you locate in the document the placeholder for the field you’re going to add, highlight it and then select Insert > Field from the toolbar. A dialog box comes up with all of the available data items, you select the one you want and click insert as shown below:

alt

12. You can add repeating rows into your document by selecting Insert > Table/Form from the toolbar. This brings up a different dialog box that lets you drag a parent node – in this case, “P Invoices” – into the middle section, which becomes your repeating rows.

alt

13. Calculate the average for amount due original. Select Insert->Field from the Add Ins toolbar. Then select the tag that calculates the average for that particular field.

alt

14. Once we are done with adding up all the fields in the template save it as an rtf which looks as below:

alt

To confirm the output from the template we build.  Click on preview and select the type in which format the output is required.

15. Adding the Template to ORACLE Application.

In order to add the template to application the user should have the responsibility XML Publisher Administrator assigned.

In this step we do 2 processes, registering concurrent program as Data Definition in template manager

And register the template using the data definition created.

alt

Go to XML Publisher Administrator->Data Definitions->Create Data definition.

Here we fill all the details to create data definition

alt

NOTE: Make sure the code of the data definition must be the same as the short name of the Concurrent Program we registered for the procedure. So that the concurrent manager can retrieve the templates associated with the concurrent program

We can add our xml file SampleXmlReport.xml in data definition here:

alt

16. Now create the template with the help of template manager

At the runtime the concurrent managers request interface will present the list of available templates with respect to the data definition registered. We will upload the rtf template file created here. We will select the language and territory.

alt

We can upload different templates for different languages and territories.

17. Now run the concurrent program to get the desired output.

From the receivables responsibility, use the submit request form to run the concurrent request.

The default layout is displayed which we can change as per the requirement. For changing the template click on options to look for all templates register with that concurrent program.

alt

Here in the above example my template is SampleXmlReport which is displayed in layout field.

alt

And once the request is submitted we will get the output in PDF as

alt

This is the final output as desired in the PDF format.

 

 

50 Comments

  1. raja
    May 19, 2011 @ 14:12:03

    great work..

    Reply

    • Babaiah
      May 20, 2011 @ 23:33:07

      Have done a good job

      Reply

  2. Mohd.Ibrahim
    May 24, 2011 @ 12:59:54

    Hi,
    I Really impressed with ur work

    Reply

  3. Babaiah
    May 24, 2011 @ 23:25:54

    very good job

    Reply

  4. Ganesh
    Jun 04, 2011 @ 12:24:00

    good artical……….!!

    Reply

  5. Darshan
    Jun 08, 2011 @ 10:16:50

    :) :(

    Reply

  6. Ijaz Asim
    Aug 07, 2011 @ 17:48:11

    it is very good artical.

    Reply

  7. rajesh
    Aug 21, 2011 @ 20:00:14

    send me some notes about profiles and multi orgs with screen shots

    Reply

  8. Thomas Paul
    Aug 24, 2011 @ 13:10:59

    Dear Admin ,

    i am having one RDF and n number of RTF files . how will i do that programatically as i am passign the rtf name as one parmater . i defined templates for all RTF under one data definition . Please help .

    Reply

  9. prashant salve
    Sep 20, 2011 @ 15:48:59

    very helpful…

    Reply

  10. pavanchand
    Sep 22, 2011 @ 12:57:10

    Hi Prudhvi,

    This is Pavanchand.I need some help regarding about the Terms & Conditions has to print on the back side of eachand every page.For that it was printing for each and every page but the issue is in the last it was printing a blank page.I have tried to resolve but it was not resolved.I need some help for this .

    Thanks & Regards
    Pavanchand

    Reply

    • Sudheer
      Feb 06, 2012 @ 11:50:31

      Hi Pavan,
      I am looking for printing terms and conditions at back side of the page. Can u pls provide the solution to achieve this.

      Thanks in advance,
      Sudheer

      Reply

  11. Waqas Hassan
    Oct 14, 2011 @ 15:57:38

    Hi,

    Is BI Publisher and XML Publisher the same?

    Regards,
    Waqas Hassan

    Reply

    • Kamalakar
      Oct 21, 2011 @ 18:23:00

      BI Publisher is new name for XML Publisher

      Reply

  12. Abhishek
    Oct 27, 2011 @ 11:07:35

    Hi,

    Thats a nice article. Do we have any command to download rtf layouts. Actually I have a requirement where I have to download all the layouts for a bunch of Xml Reports.

    Do we have any scripts we can run to download these files or We need to manually download them from Application?

    Please advice.

    Thank You.

    Reply

  13. Tooran
    Nov 15, 2011 @ 01:48:50

    how do you set BI publisher or Concurrent program to be defaulted to a certain language for example Spanish. I have created .xlf translation file for spanish but I want to be able to have report to be in spanish without me going to set the language on concurrent program side to Spanish, I need spanish to be defaulted. Is this possible? please advise.
    thanks
    -Tooran

    Reply

  14. Ranaprathap
    Nov 21, 2011 @ 17:23:22

    What are the effected tables in Back End for Both Data definition and Template Definitions in XML Publisher.

    Reply

  15. ufp1000
    Nov 30, 2011 @ 10:38:45

    Good work Sir..
    I am starting my career in Oracle Apps and this site seems to be very helpful. XML pub demo was a great. Will try it today itself.
    Thanks a Ton.

    Reply

  16. Malini Ashok
    Dec 11, 2011 @ 22:38:00

    superb article with detailed explanation:)

    Reply

  17. kirankumar
    Dec 20, 2011 @ 11:38:10

    nice work from ur side keep it up

    regards
    kirankumar

    Reply

  18. damodar
    Dec 20, 2011 @ 15:49:01

    very nice

    Reply

  19. nalini
    Dec 22, 2011 @ 13:34:29

    very clear

    Reply

  20. Durgarao
    Dec 30, 2011 @ 14:37:46

    Nice article

    Reply

  21. Manoj Sharma
    Feb 14, 2012 @ 03:39:30

    Excellent document !

    Reply

  22. Bharath Kumar.P
    Mar 07, 2012 @ 13:14:08

    Very Good Article

    Reply

  23. Jayaprakash
    Mar 17, 2012 @ 16:25:23

    Article was good.
    Which is very useful for me.

    Reply

  24. Ravindran
    Mar 19, 2012 @ 15:57:15

    Outstanding Elucidation…!

    Reply

  25. gangadhar
    Mar 24, 2012 @ 18:55:55

    can i use xsl and fsl-fo syntaxes in rtf templates

    Reply

  26. gangadhar
    Mar 24, 2012 @ 18:59:15

    can any one explain how can i achieve no of records per page
    if possible send me that rtf template
    gangadhar.apps@ymail.com
    Thanks
    Gangadhar

    Reply

  27. ravendra
    Mar 30, 2012 @ 12:39:25

    if there is 1 concurrent program–under which there are 3 templates
    while running I have parameters like terms and condition1,terms and condition2,terms and condition3,
    Is there any option to pick the specific template according to selected parameters
    ?

    Reply

  28. Sonal agrawal
    Apr 23, 2012 @ 11:58:44

    Hi,

    Can anyone plz tell me how to get concurrent program name in xml report dynamically.

    Thanks in advance.

    Reply

  29. athide
    Apr 27, 2012 @ 09:55:23

    Hi Prudhi

    Please let me know is there any word count limit/no.of pages in .rtf file which we use to upload in xml publisher template — oracle apps 11i

    Thank you
    athide

    Reply

  30. siri
    Jun 04, 2012 @ 12:37:21

    Hi Prudhi..
    i have a package.procedure..
    how to build the rdf file by using package.procedure..
    procedure have the four parameters also..pls help me

    Reply

  31. bheema reddy
    Sep 06, 2012 @ 11:43:56

    superb document

    Reply

  32. Thamizhan
    Oct 09, 2012 @ 20:44:04

    Really Nice Article for Beginners. Appreciate your effort on this.

    Reply

  33. Ana Claudia Barreto
    Dec 27, 2012 @ 02:03:53

    Hello,

    Someone can tell me if it is possible to create a report in bi-xml to excel, displaying comments?

    Thanks.

    Reply

  34. Roma
    Jun 22, 2013 @ 01:00:56

    Hello! Quick question that's totally off topic. Do you know how to make your site mobile friendly? My weblog looks weird when viewing from my apple iphone. I'm trying to find
    a template or plugin that might be able to fix this issue.
    If you have any suggestions, please share. Appreciate it!

    Reply

  35. Best Practices in XML Publisher
    Jul 04, 2013 @ 01:39:00

    […] basic values of XML reports can be explored by contexts as well as by having inactive parameters. Any X Path expression can be employed for showing an output. This can also be used when users need alternative colors in the XML reports, then they can get […]

    Reply

  36. coupon
    Jul 06, 2013 @ 17:54:18

    I go to see every day a few blogs and information sites to read content, except
    this weblog gives quality based posts.

    Reply

  37. Simple Deployment and Scheduling in XML Data
    Jul 31, 2013 @ 22:19:50

    […] in relation to information that are needed for creating report. There are also possibilities for querying against information in absence of schemes. The schemes directly thrust information to convert into a form, which help in developers and […]

    Reply

  38. pavan
    Sep 23, 2013 @ 15:34:26

    What is the basic difference between BI Publisher and XML Publisher?

    Reply

    • prudhvi
      Sep 23, 2013 @ 18:34:09

      It is the same product. As part of the R12 EBS release oracle has upgraded few features and renamed XML Publisher as BI Publisher.

      Reply

  39. chinna
    Oct 18, 2013 @ 13:27:15

    Good work….

    Reply

  40. Shadrack Kioko
    Apr 08, 2014 @ 14:50:41

    This article is just awesome. It has helped me create my first Supplier Information Report in R12. Its very detailed and guides you step by step to achieve the output

    Reply

  41. Kevin
    Apr 09, 2014 @ 01:39:40

    I created template with default output type = EXCEL, but the program does not open the excel output.
    The window opens up and closes right away.
    If I do with PDF as default output type, the program output works fine.
    Please advise.
    Thanks,
    Kevin

    Reply

  42. Ramu
    Apr 25, 2014 @ 18:56:43

    Hi,

    above article is very nice. Please suggest me, how can i found the RTF template file location in database.
    Template it is showing in XDO_LOBS Table. But no one table is providing the File location path details.
    And also i search in FND_LOBS Table. In this table the RTF Templates are not showing. Please suggest me.

    Regards,
    Ramu

    Reply

  43. surekha basireddy
    May 28, 2014 @ 13:04:28

    Hi Prudhvi,

    Can you help me Inbound interface(RMA) how to do.

    Really it is very help full of your erp schools

    Reply

  44. MD ABUL BASHER
    Aug 25, 2014 @ 10:53:02

    We could not available matrix report in bi publisher. so You should Matrix report facility in bi publisher, then we can matrix report available .

    Reply

  45. MD ABUL BASHER
    Aug 25, 2014 @ 10:59:38

    How Can we use Repeating Frame in Bi Publisher please!

    Reply

Leave a Reply