XML Report Using Source as Data Template

Problem – Generate XML source for BI publisher report.

Solution – There is several options available to generate XML tags, intend as source for BI/XML publisher reports in EBS. One among those is use of data template.

Data Template is an xml file having predefined set of tags. Create a data template file and attach file at (XML Publisher Administrator (R) >> Data Definition >> Add Files >> Data Template)

Header	<?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1"   defaultPackage=”T1” version="1.0" >
Data Trigger	<dataTrigger name="beforeParameter" source="T1.beforeParameterFunction"/>
Parameter Section	<Parameters>             <parameter name="p_param1" datatype="number"/>       </Parameters>
Data Trigger	<dataTrigger name="afterParameter" source="T1.afterParameterFunction"/> <dataTrigger name="beforeReport" source="T1.beforeReportFunction"/>
Query Section : We can use multiple query here	<dataQuery>             <sqlstatement name="Q_EMP">                 <![CDATA[SELECT empname From EMP WHERE empid=: p_param1]]>             </sqlstatement>      </dataQuery>
Data Structure : XML tag generation.	<dataStructure>                      <group name="G_EMP" source="Q_EMP">                 <element name="EMPNAME" value="empname"/>            </group>     </dataStructure>
Data Trigger	<dataTrigger name="aftreReport" source="T1. aftreReportFunction "/>

  

</dataTemplate>

Sample Data Template File

<?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="XXXOALEARNING"  dataSourceRef="ORCL_DB1" defaultPackage="T1"   version="1.0" >      

<Parameters>

<parameter name="p_user_id" datatype="number"/>

</Parameters>

<dataQuery>

<sqlstatement name="Q_USER">

<![CDATA[

SELECT user_id,

                User_name,

       FROM FND_USER

       where

        user_id=:p_user_id

   ]]>

</sqlstatement>

</dataQuery>

<dataStructure>

<group name="G_USER" source="Q_USER">

                <element name="USERID" value="user_id"/>

<element name="USERNAME" value="user_name"/>

  </group>

</dataStructure>

</dataTemplate>

Sample generated XML -

<?xml version="1.0" encoding="UTF-8" ?>

< XXXOALEARNING >

<P_CUSTOMER/>100 </P_CUSTOMER/>

<LIST_G_USER>

<G_USER>

  <USERID>100</USERID>

  <USERNAME>Sandeep</USERNAME>

</G_USER>

</LIST_G_USER>

</ XXXOALEARNING >