Grouping in xml publisher data template

Grouping in xml publisher data template

Hi Friends , We will be share the example of using grouping in the xml publisher data templates. We will be create the data template , in which we will be try to use the feature of grouping in xml publisher to sum the invoice amount based on supplier and then in the below group we will add the invoice detail in oracle apps. We can create multiple data groups as per our requirement itself in the xml publisher data template report. We will share the detail steps to create the xml publisher report , using data template in which we will also use the multiple data groups. Please find below the detail steps to use the Grouping in xml publisher data template.

Steps to use the Grouping in xml publisher data template

Step 1:- 

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.

Grouping in xml publisher data template

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>

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

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

Grouping in xml publisher data template

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

Grouping in xml publisher data template

Step 8:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

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

Grouping in xml publisher data template

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

Grouping in xml publisher data template

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

Grouping in xml publisher data template

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

Click on Insert ==> All Fields

Grouping in xml publisher data template

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

Grouping in xml publisher data template

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.

Grouping in xml publisher data template

Step 19:- 

Here below , we need to attach the Template Layout RTF file under XML Template.

Grouping in xml publisher data template

Step 20:- Once we have created the Template and attach with Report XML template , then we are ready to run this XML report in oracle apps.

Grouping in xml publisher data template