Q)What is a Data Template?
The Oracle XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner. 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.
Q)What Functionality is Supported?
The data engine supports the following functionality:
• Schema generation
• Default RTF template generation
• Flexfields
• Single and multiple data queries
• Query links
• Parameters
• Aggregate functions (SUM, AVG, MIN, MAX, COUNT)
• Event triggers
• Multiple data groups
• Unicode for XML Output
Unicode is a global character set that allows multilingual text to be displayed in a single application. This enables you to develop a single multilingual application and deploy it worldwide.
• Canonical format
The data engine generates date elements using the canonical ISO date format: YYYY-MM-DDTHH24:MI:SS.FF3TZH:TZM for a mapped date element, and ######.## for number elements in the data template XML output.
How to Design the Data Template Definition
<data Template name=”data Template name” description=”Template description” default Package = “XX” version =”1.0”>
<parameters>
<parameter name =”xx” dataType =”character/date/number” default Value=”10”/>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name=”Q1_XX”>
Put your Select ename from emp
</ sqlStatement >
<data Trigger name=”before Report” source=”package name.beforeReportTrigger”/>
<dataStructure>
</dataStructure>
<group name=”G_name” source=”Q1_XX”>
<element name=”ename” source=”ENAME” datatype=”varchar2”/>
</group>
</dataQuery>
</data Template>
Q) How to link the Queries in BI publishers reports ?
Ans) link the SQL operator that defines the parent-child relationship
<link name =”Q1_link” parentQuery=”Q1” parentColumn=”depno” childQuery=”Q2” childColumn=”depno”/>
Q) Data Template using Link
Using Link tag
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT DEPTNO,DNAME,LOC from dept where &pwhereclause
order by deptno ]]>
</sqlStatement>
<sqlStatement name="Q2">
<![CDATA[
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL
from EMP ]]>
</sqlStatement>
<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q2" childColumn=“DEPTNO“condition="="/>
</dataQuery>
Q) Data template Passing parameter
<parameters>
<parameter name="p_DeptNo" dataType="character" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT d.DEPTNO,d.DNAME,d.LOC,
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL
from dept d, emp e
where d.deptno=e.deptno
AND d.deptno = nvl(:p_DeptNo,d.deptno) ]]>
</sqlStatement>
</dataQuery>
Q) Using Triggers in Data Template
Data Triggers
Data Triggers can be used
Perform initialization tasks
Allows to build dynamic query
Data Triggers Types
beforeReport trigger: Fires before the dataQuery is executed .
afterReport trigger : Fires after you exit and after XML output has been generated
Example :-
<dataTrigger name="beforeReport" source="employee.beforeReport()"/>
<dataTrigger name="beforeReport" source="employee.beforeReport(:Parameter)"/>
Q) Using Flex Fields in Data Demplate
Flexfield aware
Support all Flexfield features
KFF related lexicals
• oracle.apps.fnd.flex.kff.segments_metadata
• oracle.apps.fnd.flex.kff.select
• oracle.apps.fnd.flex.kff.where
• oracle.apps.fnd.flex.kff.order_by
Example: Flexfield Query
<dataTemplate ...
<parameters ...
</parameters>
<lexicals>
<lexical
type="oracle.apps.fnd.flex.kff.select"
name="FLEX_VALUE_ALL"
comment="Comment"
application_short_name="SQLGL"
id_flex_code="GL#"
id_flex_num=":P_ID_FLEX_NUM"
multiple_id_flex_num="N"
code_combination_table_alias="gcc"
segments="ALL"
show_parent_segments="Y"
output_type="VALUE"
/>
</lexicals>
How to Prevent Excel from suppressin
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?XML_ELEMENT?>
</fo:bidi-override>
How to create Summary Columns in Data Template ?A summary column performs a computation on another column's data. Using the function attribute of the <element> tag, you can create the following summaries: sum, average, count, minimum, and maximum.
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?XML_ELEMENT?>
</fo:bidi-override>
<group name="G_DEPT" source="Q1" groupFilter="empdata.G_EMPFilter(:DEPTSAL)">
<element name="DEPT_NUMBER" value="DEPTNO" />
<element name="DEPT_NAME" value="DNAME"/>
<element name="DEPTSAL" value="G_EMP.SALARY" function="SUM()"/>
<group name="G_EMP" source="Q1">
<element name="DEPT_NUMBER" value="DEPTNO" />
<element name="DEPTSAL" value="G_DEPT.SALARY" function="SUM()"/>
<group name="G_EMP" source="Q2">
<element name="EMPLOYEE_NUMBER" value="EMPNO" />
<element name="NAME" value="ENAME"/>
<element name="JOB" value="JOB" />
<element name="SALARY" value="SAL"/>
</group>
</group>
Q) How to use the lexicla Paramters in Data Templates ?
in ur select statement where &whereaswrite the on package in like below
CREATE OR REPLACE PACKAGE BI_SQL
ASapproved VARCHAR2(10);
buyer varchar2(50);
po_to number;
po_from number;
p_blanket varchar(10);
whereas varchar2(1000);
function po_num return boolean ;
end;
CREATE OR REPLACE PACKAGE body BI_SQL
AS
function po_numreturn boolean
AS
begin
WHEREAS := ' ';
If po_from > po_to then
WHEREAS := ' AND to_number(Poh.SEGMENT1) between '||'('||po_to||') and ('||po_from||') ';
---||po_from ||' AND '|| po_to;elseWHEREAS := ' AND to_number(Poh.SEGMENT1) between '||'('||po_from||') and ('||po_to||') ';
end if;
return true;
end;
END;/
call this package through
before report trigger <dataTrigger name="beforeReport" source="BI_SQL.po_num"/>
How to Prevent Excel from suppressin
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?XML_ELEMENT?></fo:bidi-override>
Drop me a comment if have any doubts and questions.........