Oracle Application's Blog: xml publisher data template multiple queries

xml publisher data template multiple queries

Hi Friends, We are going to discuss about xml publisher data template multiple queries. We will try to develop the xml report using data templates having multiple quires. We will share the detail steps to develop the xml publisher reports with data template. In Data template , We do write the logic of the report. We do write the sql statement in data template to extract the data for reports. Some time we have an complex business requirements for which we have to write the multiple sql queries inside data templates and then join with the each others itself in the data template of the xml reports. We can write any number of multiple queries in the xml reports data templates. Multiple queries possible in the xml publisher data templates. We will share the real time example to use the multiple queries in xml publisher. Here below is the detail about xml publisher data template multiple queries.

Steps to use the xml publisher data template with multiple queries

Here below is the sample Data Template xml file in which we are using the multiple queries. We are trying to extract the supplier Invoice and Payments details with these multiple queries.

xml publisher data template multiple queries

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.

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 data template multiple queries

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 data template multiple queries

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 data template multiple queries

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

xml publisher data template multiple queries

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

xml publisher data template multiple queries

Step 8:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

xml publisher data template multiple queries

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 data template multiple queries

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 data template multiple queries

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 data template multiple queries

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 data template multiple queries

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

xml publisher data template multiple queries

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

Click on Insert ==> All Fields

xml publisher data template multiple queries

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

xml publisher data template multiple queries

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.