Oracle XML Publisher
It is a Template Based Publishing Solution delivered with Oracle EBS.
At Run time, it merges the custom templates with the concurrent Request Data Extract
to generate output in PDF, HTML, RTF, EXCEL
or even Text for use with EFT and EDI Transmission.
What is Data Template
Data Template is one form of Data source which is used to extract the required data from the
Data base and produces the output in the form of XML.
The use of Data Template is, Data template is a XML based document which consists of the
Elements like Data Query, Parameters, Data Structure, and Triggers. These elements will
Communicate with the Data engine and produces the output in the form of XML file.
The XML file which in turn is used by the template to produce the required output.
The below list shows that what kind of communication between the elements and Data engine
Will be:-
- The SQL Query containing Bind parameter
- How the output structure of the XML is defined
- The triggers to be executed before or after the Query Statement
The following flow will give the idea on the flow of the data template execution:
Data Template Declaration:
Data template element includes the set of attributes expressed with in the tags. The Attributes of the data template are shown in the below table
Example:-
<dataTemplate name="EmpData" description="Employee Details" Version="1.0" defaultPackage="xx_emp_pkg">
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>
Parameter Sections
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
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>
Parameters are of 2 types. They are Bind Variables and Lexical Parameters.
- Bind Variables will replace the value directly as shown in the above example.
- Lexical parameters replace the condition while executing the query.
Trigger 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.
Example
<dataTrigger name="beforeReport" SOURCE="xx_emp_pkg.print_params()"/>
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:
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()"/>
<dataTrigger name="afterReport" SOURCE="xx_emp_pkg.AfterReport" />
<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>
|
IF ELSE Statement
<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>
Choose Statement
<?choose:?>
<?when:INVOICE_AMOUNT>5000?>
Higher
<?when:INVOICE_AMOUNT<3000?>
Lower
<?otherwise:?>
Equal
<?end otherwise?>
<?end choose?>
Conditionally Highlighting A Row
<?if@row:position() mod 2=0?>
<xsl:attribute name="background-color"
xdofo:ctx="incontext">lightgray</xsl:attribute>
<?end if?>
Cell Highlighting
<?if@cell:position() approved_amount >0 ?>
<xsl:attribute name="background-color"
xdofo:ctx="block">lightgray</xsl:attribute>
<?end if?>
Position() In RTF Template
Position() acts like a rownum of the particular group. Position() is always placed after the group
is opened.
For example if you need to print the serial number or row number in an rtf template we can use
the position() function.
Inserting Page Breaks
<?split-by-page-break:?>
Inserting An Initial Page Number
<?initial-page-number:pagenumber?>
where pagenumber is the XML element or parameter That holds the numeric value.
BI Publisher also supports continuing the page number from a previous section.
The default behaviour of a new section in a document is to reset the page numbering.
However, if the report requires that the page numbering continue into the next section, use the following command
<?initial-page-number:'auto'?>
