Oracle Application's Blog: xml publisher report using data template in oracle apps

xml publisher report using data template in oracle apps

Hi friends, we are going to discuss about the XML Publisher report using data template in Oracle apps. We will share the complete steps and the detail informations to develop the XML publisher reports using the data template. Data template is the new types of repotting option in oracle apps r12. We can also say that this is the new reporting tool in oracle apps. In data template xml reports we don't need the external report builder tool like reports 10g to develop the 'rdf' reporting. This new reporting tool itself build inside the oracle apps application. We just need to prepare the data template to develop the xml reports. In this data template , We do write the complete logic of the report. In the data template , We do write the complete sql of the report with columns details to extract the data. We do create the report triggers too in the data template as per our Requirment. We can create the report parameters too inside that data template also we can create the formula as well the summary columns too for the xml reports.

We need to develop this complete data template in the xml format. In this xml format data template file , we have written the complete report logic as well the report source code too. Please find below the complete detail about the XML Publisher report using data template in Oracle apps.

Features of XML publisher Data Template report in Oracle Apps

1. Using Data Template , We don't need to use the Oracle Report Builder(RDF) Tool.

2.We can write the sql queries itself in the Data Templates.

3.We can write the Reports triggers , Define Parameters of the report itself in the Data Template.

4.Data Template is XML file which contains the complete logic of the report.

5.If we want to hide the report source code , then we can use the Data Template feature of the xml reports to develop the report in oracle apps.

6. Data Template XML reports reporting runs very fast. It has good performance as compared to Report Builder (RDF) reports.

xml publisher report using data template in oracle apps

How to Create the XML Publisher report in RDF.

If you want to learn the XML Publisher report with RDF(Report Builder) tool then please refer my below post.

https://rpforacle.blogspot.com/2020/07/xml-reports-in-oracle-apps-with-rdf.html

Step by Step to create xml publisher report using data template in oracle apps

Here below , we will describe you in more detail about how to develop the xml publisher report using data template in oracle apps.

Step 1:- Here below is the sample of Data Template we have created for XML publisher report.
This report we have created for Supplier Invoice Extraction.

<Parameters>
This Tag helps to define the Parameter for XML reports. We define the xml report parameters under this tag.

<DataQuery>
This tag helps to define the sql query to extract the data from Oracle Apps data base. We do write the sql query here for XML report.

< Group Name>

This Tag helps to define the XML reports data group. Under the group we define the Column/Attributes of the SQL Query. We can decide which data columns of sql query we want to put in which group.

<Element Name> This Tag helps to define the sql query data columns. We need to define those columns which we want to shows in the report. Elena we do define under the Group Tag.

xml publisher report using data template in oracle apps

Step 2:- Here below is the sample code we have written for data template file. We need to save this code in as an xml file in .xml extension.

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="SupInvData" description="Supplier Invoice Details"
Version="1.0" >
<properties>
<property name="debug_mode" value="on"/>
</properties>
<parameters>
<parameter name="P_VENDOR_ID" dataType="character" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
select A2.VENDOR_NAME,A1.INVOICE_NUM,A1.INVOICE_DATE,A1.INVOICE_AMOUNT from ap_invoices_all a1,po_vendors a2
where a1.vendor_id=a2.vendor_id
and a2.vendor_id=:P_VENDOR_ID
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_SUPP" source="Q1">
<element name="VENDOR_NAME" value="VENDOR_NAME" />
<element name="VENDOR_INV_AMT" value="G_INV.INVOICE_AMOUNT"
function="SUM()" />
<group name="G_INV" source="Q1">
<element name="INVOICE_NUM" value="INVOICE_NUM" />
<element name="INVOICE_DATE" value="INVOICE_DATE" />
<element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" />
</group>
</group>
</dataStructure>
</dataTemplate>

xml publisher report using data template in oracle apps

Step 3:- Once we have save this above in xml file , then we can start the XML Publisher report creation part in oracle apps as below.

Step 4:- We first need to go to the Oracle Application to register this report as an xml publisher report in oracle apps r12.

xml publisher report using data template in oracle apps

Step 5:- Here below we are creating the "XX SUPPLIER INVOICE DETAIL' report.
Navigation :Concurrent ==> Concurrent Program.

Important Note : In Executable , We need to define 'XDODTEXE' .

Output Type : XML
Click on the Parameters button to create report parameter.

xml publisher report using data template in oracle apps

Step 6:-
As below , Copying the Report parameter for Concurrent Program from Data Template.

xml publisher report using data template in oracle apps

Step 7:- As below , P_VENDOR_ID column got created.

xml publisher report using data template in oracle apps

Step 8:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

xml publisher report using data template in oracle apps

Step 9:-

Here below , We need to create the report Data Definition.

Note : We need to use the Short Name of Concurrent Program in Step 5 in Data Definition code field as below.

Click Apply.

xml publisher report using data template in oracle apps

Step 10:- Here below , We need to upload the Data template file created in Step 2 & 3.In Data Template as highlighted below and Click on add file.

xml publisher report using data template in oracle apps

Step 11:-

As of now , we are not creating the XML report layout , We will first generate the xml output from report to create the template/layout for the xml publisher report.

Step 12:-

Here below , we are running the Concurrent Program . Once the request will be complete then we need to click on the 'View Output' button.

As we have not attached any Layout/Template , Then output will be xml based.

We need to save the xml output as an .xml file.

xml publisher report using data template in oracle apps

Step 13:- This is the XML output from the report in step 12.

xml publisher report using data template in oracle apps

Step 14:-
Now we need to design the layout for the report. Go to Add-ins in the Word Doc.

xml publisher report using data template in oracle apps

Step 15:- In Add-ins ==> Go to Load XML Data

xml publisher report using data template in oracle apps

Step 16:- Then , If we want to auto generate the layout based on all the report columns.

Click on Insert ==> All Fields

xml publisher report using data template in oracle apps

Step 17:- As below , Report layout has been designed.

xml publisher report using data template in oracle apps

Step 18:-

After saving the xml output file, We need to create the report Templates as below.
We need to attach the report Data Definition created in Step 9 in Report Templates.