Developing XML Publisher Reports using Data Template

Data template is one of the method of generating reports in XMLP. Reports in XMLP has two components:

Data Definition
Template

Data Definition

Data definition is a component which pulls out data from a data source and generates the data file in an XML format with a user defined structure.

Template

Template is a layout which can be designed using Word of Excel.

XML Publisher engine requires input data in an XML format, XML file is processed by XML Publisher engine and the same data displayed in the layout. We can generate XML file using any kind of program, usually we do using:

Oracle Report Builder (RDF)
PL/SQL Program
Java Program
Data Template

In this article, we will focus on how to generate data in XML file using Data Template.

Data Template

The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML.

Data Template Definition

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

Properties
Parameters
Triggers
Data Query
Data Structure

Below is the basic structure of Data Template:

Data Template Declaration

The element is the root element. It has a set of related attributes expressed within the tag.

Example:-

 <dataTemplate name="EmpData" description="Employee Details" 
      Version="1.0" defaultPackage="xx_emp_pkg">

Below are the different possible attributes:

ATTRIBUTE NAME	DESCRIPTION
name	(Required) Enter the data template name
description	(Optional) Enter a description of this data template
version	(Required) Enter a version number for this data template
defaultPackage	(Optional) This attribute is required if your data template contains lexical references or any other calls to PL/SQL
dataSourceRef	(Optional) The default data source reference for the entire data template

Properties Section

Use the section to set properties to affect the XML output and data engine execution.

Example:-

<properties>
     <property name="debug_mode" value="on"/>
</properties>

The following table shows the supported properties:

PROPERTY NAME	DESCRIPTION
include_parameters	Indicates whether to include parameters in the output. Valid values are: True (default) False
include_null_Element	Indicates whether to remove or keep the null elements in the output. Valid values are: True (default) False
xml_tag_case	Allows you to set the case for the output XML element names. Valid values are: upper (default) lower as_are (The case will follow the definition in the dataStructure section.)
db_fetch_size	Sets the number of rows fetched at a time through the jdbc connection. The default value is 500.
scalable_mode	Sets the data engine to execute in scalable mode. This is required when processing a large volume of data. Valid values: on off (default)
include_rowsettag	Allows you to include or exclude the Rowset Tag from the output. Valid values: true (default) false
debug_mode	Turns debug mode on or off. Valid values: on off (default)

Parameters Section

A parameter is a variable whose value can be set at runtime, usually we get parameter value from the concurrent program parameters. Parameters defined in the data template must be in the same order of the parameters defined in the concurrent program. The Parameters section of the data template is optional.

The element is placed between the open and close tags. The element has a set of related attributes namely

ATTRIBUTE NAME	DESCRIPTION
name (required)	The parameter name that will be referenced in the template.
dataType	Valid values are: “character”, “date”, “number”
defaultValue	Value to use for the parameter if none supplied from the data
include_in_output	Whether this parameter should appear in the XML output or not. The valid values are “true” and “false”.

Parameters can be accessed in the data query part of data template using bind variables. Eg:- :p_deptno

 <parameters>
  <parameter name="P_DEPTNO" dataType="character" /> 
 </parameters>

Triggers Section

Triggers are optional elements of data template which are nothing but a PL/SQL Function or a Procedure which gets executed at specific times during the execution and generation of XML output. Triggers can be used to perform some pre and post execution activities like in financial reports where we update some table columns stating that a particular is row is printed.

Data triggers are optional, and you can have as many elements as necessary.

The element has a set of related attributes. These are expressed within the tag. For example, the name and source attributes are expressed as follows:

<dataTrigger name="beforeReport" SOURCE="xx_emp_pkg.print_params()"/>

ATTRIBUTE NAME	DESCRIPTION
name	The event name to fire this trigger
source	The PL/SQL < package name >.< function name > where the executable code resides.

The location of the trigger indicate at what point the trigger fires:

Place a beforeReport trigger anywhere in your data template before the < dataStructure > section.. A beforeRepot trigger fires before the dataQuery is executed.
Place an afterReport trigger after the < dataStructure > section. An afterReport trigger fires after you exit and after XML output has been generated.

Data Query

Data Query is a mandatory element where we place the SQL statement which gets executed to fetch the information to be printed on the report output. We can have multiple SQL statements in DataQuery differentiated with a ‘name’ for the SQL Statement.

Example:

<?xml version="1.0" encoding="UTF-8" ?> 
 <dataTemplate name="EmpData" description="Employee Details" 
      Version="1.0" defaultPackage="xx_emp_pkg">
 <properties>
  <property name="debug_mode" VALUE="on"/>
 </properties> 
 <parameters>
  <parameter name="P_DEPTNO" dataType="character" /> 
 </parameters>
 <dataTrigger name="beforeReport" SOURCE="xx_emp_pkg.print_params()"/>
 <dataQuery>
  <sqlStatement name="Q1">
  <![CDATA[ 
   SELECT d.DEPTNO,d.DNAME,d.LOC,EMPNO,ENAME,JOB,MGR,HIREDATE,
    SAL,nvl(COMM,0) 
   FROM dept d, emp e
   WHERE d.deptno=e.deptno
   AND d.deptno = nvl(:p_deptno,d.deptno)
   ]]> 
   </sqlStatement>
  </dataQuery>
  <dataTrigger name="afterReport" SOURCE="xx_emp_pkg.AfterReport" />
  <dataStructure>
  <GROUP name="G_DEPT" SOURCE="Q1">
   <element name="DEPT_NUMBER" VALUE="DEPTNO" /> 
   <element name="DEPT_NAME" VALUE="DNAME" /> 
   <element name="DEPTSAL" VALUE="G_EMP.SALARY" 
    FUNCTION="SUM()" /> 
   <element name="LOCATION" VALUE="LOC" /> 
   <GROUP name="G_EMP" SOURCE="Q1">
    <element name="EMPLOYEE_NUMBER" VALUE="EMPNO" /> 
    <element name="NAME" VALUE="ENAME" /> 
    <element name="JOB" VALUE="JOB" /> 
    <element name="MANAGER" VALUE="MGR" /> 
    <element name="HIREDATE" VALUE="HIREDATE" /> 
    <element name="SALARY" VALUE="SAL" /> 
   </group>
  </group>
  </dataStructure>
 </dataTemplate>

Example

Step 1 – Define Data Template
Create a data definition with below code:

<?xml version="1.0" encoding="UTF-8" ?> 
 <dataTemplate name="EmpData" description="Employee Details" 
      Version="1.0" defaultPackage="xx_emp_pkg">
 <properties>
  <property name="debug_mode" value="on"/>
 </properties> 
 <parameters>
  <parameter name="P_DEPTNO" dataType="character" /> 
 </parameters>
 <dataTrigger name="beforeReport" source="xx_emp_pkg.print_params()"/>
 <dataQuery>
  <sqlStatement name="Q1">
  <![CDATA[ 
   SELECT d.DEPTNO,d.DNAME,d.LOC,EMPNO,ENAME,JOB,MGR,HIREDATE,
    SAL,nvl(COMM,0) 
   FROM dept d, emp e
   WHERE d.deptno=e.deptno
   AND d.deptno = nvl(:p_deptno,d.deptno)
   ]]> 
   </sqlStatement>
  </dataQuery>
  <dataStructure>
  <group name="G_DEPT" source="Q1">
   <element name="DEPT_NUMBER" value="DEPTNO" /> 
   <element name="DEPT_NAME" value="DNAME" /> 
   <element name="DEPTSAL" value="G_EMP.SALARY" 
    function="SUM()" /> 
   <element name="LOCATION" value="LOC" /> 
   <group name="G_EMP" source="Q1">
    <element name="EMPLOYEE_NUMBER" value="EMPNO" /> 
    <element name="NAME" value="ENAME" /> 
    <element name="JOB" value="JOB" /> 
    <element name="MANAGER" value="MGR" /> 
    <element name="HIREDATE" value="HIREDATE" /> 
    <element name="SALARY" value="SAL" /> 
   </group>
  </group>
  </dataStructure>
 </dataTemplate>

Step 2 – Define Package
Default Package used in data template

Package Specification:

CREATE OR REPLACE PACKAGE XX_EMP_PKG
AS
   -- Package variables
   p_deptno     VARCHAR2 (50);
 
   -- used to print end of log statements
   FUNCTION AfterReport
      RETURN BOOLEAN;
 
   --used to print input parameters to the report
   FUNCTION print_params
      RETURN BOOLEAN;
 
END XX_EMP_PKG;
/
SHOW ERRORS;
EXIT;

Package Body:

CREATE OR REPLACE
PACKAGE BODY XX_EMP_PKG
AS
--
--
FUNCTION afterreport
  RETURN BOOLEAN
IS
BEGIN
  FND_FILE.put_line (fnd_file.LOG, '*****  End of Log  *****');
  RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
  FND_FILE.put_line (fnd_file.LOG, SQLERRM);
  RETURN FALSE;
END afterreport;
--
--
FUNCTION print_params
  RETURN BOOLEAN
IS
BEGIN
  --print parameter information
  FND_FILE.put_line ( fnd_file.LOG, 'Input Parameters :                             Date and Time : ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  FND_FILE.put_line (fnd_file.LOG, 'Dept No.   : ' || p_deptno);
  RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
  FND_FILE.put_line (fnd_file.LOG, SQLERRM);
  RETURN FALSE;
END print_params;
END XX_EMP_PKG;
/
SHOW errors;
EXIT;

Note: In Data template when you define default Package, its mandatory to declare all the parameters of concurrent program as global variable in the default package.