Data template is one of the method of generating reports in XMLP. Reports in XMLP has two components:
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 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:
In this article, we will focus on how to generate data in XML file using 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.
The data template is an XML document that consists of 5 basic sections:
Below is the basic structure of Data Template:
The element is the root element. It has a set of related attributes expressed within the tag.
Example:-
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 |
section to set properties to affect the XML output and data engine execution.
Example:-
> name="debug_mode" value="on"/> >
The following table shows the supported properties:
| Property Name | Description |
|---|---|
| include_parameters | Indicates whether to include parameters in the output. Valid values are: |
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.
element is placed between the open and close
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 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 . where the executable code resides. |
The location of the trigger indicate at what point the trigger fires:
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>
Step 1 – Define Data Template
Create a data definition with below code:
version="1.0" encoding="UTF-8" ?> name="EmpData" description="Employee Details" Version="1.0" defaultPackage="xx_emp_pkg"> > name="debug_mode" value="on"/> > > name="P_DEPTNO" dataType="character" /> > name="beforeReport" source="xx_emp_pkg.print_params()"/> > name="Q1"> 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) ]]> > > > name="G_DEPT" source="Q1"> name="DEPT_NUMBER" value="DEPTNO" /> name="DEPT_NAME" value="DNAME" /> name="DEPTSAL" value="G_EMP.SALARY" function="SUM()" /> name="LOCATION" value="LOC" /> name="G_EMP" source="Q1"> name="EMPLOYEE_NUMBER" value="EMPNO" /> name="NAME" value="ENAME" /> name="JOB" value="JOB" /> name="MANAGER" value="MGR" /> name="HIREDATE" value="HIREDATE" /> name="SALARY" value="SAL" /> > > > >
Step 2 – Define Package
Default Package used in data template
Package Specification:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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;
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
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;
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.
Step 3 – Define concurrent program
Step 4 – Define Data Definition and Template
Step 6 – Add Concurrent Program to Request Group
Step 7 – Creating RTF Template
Run Concurrent program to generate XML data file which can be used to build XML template.
Create a blank rtf file with name ‘XX_EMP_DEPT.rtf’ and Load generated XML Data file to RTF
User insert all fields option in XMLP addon to insert all fields in rtf layout
Preview to see the output:
Click here to download RTF, Data template and package used in this article.
