XML Publisher Report from XML Data Template

I used to wonder how to develop XML Publisher reports without using rdf or sql or pl/sql concurrent program. The traditional way is to

  1. Create a RDF or SQL or PL/SQL procedure
  2. Register the RDF or SQL or PL/SQL report as a concurrent program and set the output to XML
  3. Run the above report and get the output and save it as a XML file
  4. Use the XML data in building the Template using Microsoft Word

This seemed to be a little cumbersome for me. So I was searching for alternatives. Then I stumbled upon the following solution. And I hope this helps all of  you. This is part one of a series of blogs. Hope you enjoy this and come up with questions and suggestions. So let us take a look at how we can create xml report from xml data template.

The XML data template is an XML document that consists of four basic sections:

  1. Parameters
  2. Triggers
  3. Data Query
  4. Data Structure

This structure is shown in the following graphic:

XML Template

Step by Step Guide to create a sample XML Data Template:

Decide on the SQL Query

In the example, we are trying to get the following data from the AP_INVOICES_ALL Table

  1. INVOICE_NUM
  2. INVOICE_CURRENCY_CODE
  3. INVOICE_AMOUNT

The query for the same will be like below:

[sql]
SELECT  invoice_num
,invoice_currency_code
,invoice_amount
FROM  ap_invoices_all;
[/sql]

Decide on the parameters

We will be using two parameters to limit the data being retrieved:

  1. ORG_ID
  2. VENDOR_ID

The resulting query will be like:

[sql]
SELECT  invoice_num
,invoice_currency_code
,invoice_amount
FROM  ap_invoices_all
WHERE org_id = :p_OrgId
AND  vendor_id = :p_VendorId;
[/sql]

That’s all we need to construct the XML Data Template.

[xml]
<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name ="invoiceData" description="Invoice Data" Version="1.0" >
<parameters>
<parameter name="p_OrgId" dataType="number" />
<parameter name="p_VendorId" dataType="number" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT invoice_num,invoice_currency_code,invoice_amount
FROM ap_invoices_all
WHERE org_id = :p_OrgId
AND vendor_id = :p_VendorId
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_INV" source="Q1">
<element name="INV_NUMBER" value="invoice_num" />
<element name="CURRENCY_CODE" value="invoice_currency_code" />
<element name="AMOUNT" value="invoice_amount" />
</group>
</dataStructure>
</dataTemplate>
[/xml]

If you see the above XML Data Template, we have specified the parameters that are being used and also the SQL Query that will be fetching the data. The SQL Query is using the parameters that are being defined under the Parameters Section.  I have also included a jpeg image of the XML File Definition. Sometimes when you copy the XML Definition from the blog, you might run into “invalid characters” that can cause issues. So check the jpeg image and make use of it.

xml_template_definition

Save the same as “.xml” file in your local system. After saving the file, you should be able to open it in the browser. Double click the xml file and the file should open. The file will look as shown below:

xml_file

Open Word. Go to Add-Ins. You will be able to see the BI Publisher Menu. Under the Menu, Select Data > Load XML Schema. Select the xml file that we just created.

BI Publisher Word Addon

If the data is loaded successfully, you will get the below message:

Data Loaded Successfully

Click OK and to Continue. Select Insert > Table/Form

You will get the below screen:

Drag and Drop G Inv element from Data Source to the Template Region as shown below

Select Drop All Nodes from the choices. We will be getting the below screen:

Press OK. We will get the below template in the Word Document. Save the same as XXINV_DETAILS.rtf.

Inv Number Currency Amount
F INV_NUMBER CURRENCY AMOUNT E

Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields:

Name – XXINV_DETAILS

Code – XXINV_DETAILS

Application – Provisioning (or your custom application)

Start Date – Automatically Populated

Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here.

Go to Data Templates and create a new Data Template. Give the following details:

Select the Data Definition that we had created prior to this.

Select Language as US English and upload the RTF File that we have created (XXINV_DETAILS.rtf).

Navigate to System Administrator > Concurrent > Program > Define.

Create a new Concurrent Program and give the following details.

The Executable should always be XDODTEXE.

Click on Parameters and give the following details:

Assign the program to the request group and run the program.

You can download the sample output file HERE.

Hope this helps. Let me know your thoughts and feel free to ask any questions that you might have. Also, check out the next blog which has Triggers being called from the XML Template file. You can check it here.

Also check out more advanced topic on XML Templates – XML Template Part 3

 

Tagged

44 Responses

  8. Hi Sarunraj, — i tried your example step by step and I’m facing a weird issue. the SQL Statment is not getting invoked in the DataTemplate.I’m not sure, if there is something wrong with the environment.

    i’m a newbie to oracle apps.. can you please help.

    Even tried a Simple DataTemplate as below.

    Output:

    –

    Reply

  9. hello..
    select ph.po_header_id,segment1,po_line_id,item_id,quantity,authorization_status from po_headers_all ph,po_lines_all pl where ph.po_header_id=pl.po_header_id and rownum<=10 and authorization_status=:p_status

    above mentioned query is my data query..i tried to fetch record for paramter column…in pdf output,authorization_status values are coming same as the first column header_id…how to pass the values of parameter column i pdf output

    Reply

    1. Hi Nischal,

      If my understanding is correct, you are trying to display the value of the parameter (“:p_Status”) passed by the user to the report.
      Is this correct?

      If so, the parameters will be part of the XML output. If you have defined the Data Template correctly, this value will be under .. tag.
      If this is not what you looking for, please let me know more details and send me the Data Template and sample XML output to arunraj@quest4apps.com.

      Thanks,
      Arun

      Reply

  10. Hi can you please suggest that if we have too large data as the output of XML report and it is not getting exported in XLS file, as XLS having limit of only 64k in 97-2003 format.. can you please suggest how can we split query in XML data definition?

    Reply

    1. Hi Riddhi,

      Do you want to limit the number of records returned by the report (sql)? If so, you can add parameters to the report (xml data template).
      Apologize if I didnt understand the question correctly.

      If you still have questions or if you need more details, reply to the comment.

      Thanks,
      Arun

      Reply

  12. Hi

    I tried your example step by step and I’m facing a issue.

    Here is my LOG File.

    Can u please help Me …

    +—————————————————————————+
    General Ledger: Version : 12.0.0

    Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

    XXBAP_DETAILS module: XX BAP Test Report
    +—————————————————————————+

    Current system time is 30-NOV-2016 14:27:23

    +—————————————————————————+

    XDO Data Engine Version No: 5.6.3
    Resp: 50893
    Org ID : 1307
    Request ID: 19107660
    All Parameters: P_PERIOD_NAME=MAR-16-16
    Data Template Code: XXBAP_DETAILS
    Data Template Application Short Name: SQLGL
    Debug Flag: N
    {P_PERIOD_NAME=MAR-16-16}
    Calling XDO Data Engine…
    java.lang.NullPointerException
    at oracle.apps.xdo.oa.util.DataTemplate.getDataTemplate(DataTemplate.java:379)
    at oracle.apps.xdo.oa.util.DataTemplate.(DataTemplate.java:226)
    at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:283)
    at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

    +—————————————————————————+
    Start of log messages from FND_FILE
    +—————————————————————————+
    +—————————————————————————+
    End of log messages from FND_FILE
    +—————————————————————————+

    +—————————————————————————+
    Executing request completion options…

    Output file size:
    0

    Finished executing request completion options.

    +—————————————————————————+
    Concurrent request completed
    Current system time is 30-NOV-2016 14:27:37

    +—————————————————————————+

    Reply

    1. Hi Shanker,

      Please send me the below to arunraj(at)quest4apps.com
      – XML Data Template
      – Screen shots of Data Definition, Data Template and Concurrent Program Definition

      Thanks,
      Arun

      Reply

  14. Hi your blog provided information is very impressive. I had got some doubt.I loaded the xml template into the xml sample it loaded successfully but when I click the Table/Form button It is not showing the data source contains columns…
    Please can u check once.
    I will send screenshot what I got.
    Please provide email id..

    Reply

      1. Thanks. You are correct.
        Venkat and I have been exchanging emails and I asked him to save the file as “.xsd” and use that.

        Cheers,
        Arun

        Reply

        1. Do Data template file need any file changes for saving it as .xsd?
          While Data definition creation, which file should be uploaded? XML or XSD?

          Reply

  15. Hi Arun,

    I tried copying XML data template and saved it as .xml but I get weird in the browser.None was right. Could you please help.

    Reply

  18. hi arunraj

    we are facing a wierd issue in Production env only, where the OPP issue pops up for XML publisher report

    i have .rtf
    .xml – data template for the same.

    need your help at the earliest

    regards
    janani

    Reply

  19. Hi Arun,

    I am facing another issue could You please suggest if
    ::
    oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.xdo.XDOException: No corresponding LOB data found :SELECT L.FILE_DATA

    Reply

  20. Sir, I am using a before report trigger to update the parameter (scenario: if no value is passed to parameter a default parameter to be assigned). The updated parameter value is not reflecting in data template. Please reply what to do

    Reply

  22. Hi, When I run the request, I am getting an error ora 00911 invalid character. Can you tell me how can i solve this error.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *