XML Data Template and XML Bursting Control file structure

Data Template Definition

Data Template is XML document that consist below basic sections

1. Properties
2. Parameters
3. Triggers
4. Data Query
5. Data Structure

Below is the basic structure of Data Template

Data Template Declaration		This is the root element. It has set of related attributes expressed within tag.
Element Name	Attribute Name	Description
<dataTemplate>	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 this section to set properties to affect the XML output and data engine execution.
Element Name	Attribute Name	Description
<property>	include_parameters	Indicates whether to include parameters in the output. Valid values are: True (default) and False
	include_null_Element	Indicates whether to remove or keep the null elements in the output. Valid values are: True (default) and False
	xml_tag_case	Allows you to set the case for the output XML element names. Valid values are: upper (default) , lower and 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 and off (default)
	include_rowsettag	Allows you to include or exclude the Rowset Tag from the output. Valid values: true (default) and False
	debug_mode	Turns debug mode on or off. Valid values: on and off (default)

Parameter Section		A parameter is a variable whose value can be set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. The Parameters section of the data template is optional.
Element Name	Attribute Name	Description
<parameter>	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”.

Trigger Section		Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.Data triggers are optional
Element Name	Attribute Name	Description
<dataTrigger>	name	The event name to fire this trigger
	source	The PL/SQL < package name >.< function name > where the executable code resides.

Data Query	<dataQuery>	This is the root element. It has set of related attributes expressed within tag.
	<SqlStatement>	Inside this element we can define SQL queries. SQL query is entered in the CDATA section.

Data link		If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.
Element Name	Attribute Name	Description
<link>	name	Required. Enter a unique name for the link.
	parentQuery	Specify the parent query name. This must be the name that you assigned to the corresponding <sqlstatement> element. See How to Define Queries.
	parentColumn	Specify the parent column name.
	childQuery	Specify the child query name. This must be the name that you assigned to the corresponding <sqlstatement> element. See How to Define Queries.
	childColumn	Specify the child column name.
	Example	<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPARTMENTNO"/>

Data Structure <dataStructure>		In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output.
Element Name	Attribute Name	Description
<Group>	name	Specify any unique name for the group. This name will be used as the output XML tag name for the group.
	source	The name of the query that provides the source data for the group. The source must come from the name attribute of the <sqlStatement> element.
<element>	name	Specify any name for the element. This name will be used as the output XML tag name for the element. The name is optional. If you do not specify a name, the source column name will be used as the XML tag name.
	value	The name of the column that provides the source data for the element (from your query).

Sample code of Data Template

<?xml version="1.0" encoding="UTF-8" ?> 
 <dataTemplate name="POData" description="PO Details" 
      Version="1.0" defaultPackage="XXPO_PKG">
 <properties>
  <property name="debug_mode" VALUE="on"/>
 </properties> 
 <parameters>
  <parameter name="P_VENDOR_ID" dataType="number" /> 
 </parameters>
 <dataTrigger name="beforeReport" SOURCE="XXPO_PKG.print_params()"/>
 <dataQuery>
  <sqlStatement name="Q1">
  <![CDATA[ 
   SELECT asp.vendor_name,

          asp.segment1 vendor_no,

          pha.segment1 po_number,

          sum(pla.quantity * pla.unit_price) po_amount,

          asa.email_address
   FROM po_headers_all pha,po_lines_all pla,ap_suppliers asp,ap_supplier_sites_all asa
   WHERE   pha.po_header_id = pla.po_header_id
   AND asp.vendor_id = pha.vendor_id

   AND asp.vendor_id = :p_vendor_id

   AND asp.vendor_id = asa.vendor_id

   AND pha.vendor_site_id = asa.vendor_site_id

   GROUP BY aps.vendor_name,aps.segment1,pha.segment1,asa.email_address
   ]]> 
   </sqlStatement>
  </dataQuery>
  <dataTrigger name="afterReport" SOURCE="XXPO_PKG.AfterReport" />
  <dataStructure>
  <GROUP name="G_PO" SOURCE="Q1">
   <element name="VENDOR_NAME" VALUE="VENDOR_NAME" />    <element name="PO_AMOUNT" VALUE="G_PO1.PO_AMOUNT" 

          FUNCTION="SUM()"/> 
   <element name="VENDOR_NO" VALUE="VENDOR_NO"/> 
   <element name="EMAIL_ADDRESS" VALUE="EMAIL_ADDRESS" /> 
   <GROUP name="G_PO1" SOURCE="Q1">
    <element name="PO_NUMBER" VALUE="PO_NUMBER" />     <element name="PO_AMOUNT" VALUE="PO_AMOUNT" />
   </group>
  </group>
  </dataStructure>
 </dataTemplate>

Define Package

Default Package used in Data Template

Package Specification

CREATE OR REPLACE PACKAGE XXPO_PKG
AS
   -- Package variables
   p_vendor_id     number;
 
   -- 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 XXPO_PKG;

Package Body

CREATE OR REPLACE PACKAGE BODY XXPO_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, 'Vendor Id   : ' || p_vendor_id);
  RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
  FND_FILE.put_line (fnd_file.LOG, SQLERRM);
  RETURN FALSE;
END print_params;
END XX_EMP_PKG;

XML Bursting Control File

XML bursting can be used to split one XML file into multiple XML blocks. Bursting control is used to identify How to split XML file/Data, How to deliver the report,Select Template Dynamically.

Below sample code help you to deliver the report on email.

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

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

  <xapi:request select="/PODATA/LIST_G_PO/G_PO">

     <xapi:delivery>

       <xapi:email id="${VENDOR_NO}" reply-to="abc@oraapps.com" from="ap@oraapps.com" port="25"
server="ora.apps.com">

         <xapi:message subject="PO Details" attachment="true" to="${EMAIL_ADDRESS}" id="${VENDOR_NO}">

          Please find your electronically formatted PO Details.

         </xapi:message>

       </xapi:email>

     </xapi:delivery>

     <xapi:document delivery="${VENDOR_NO}" output-type="pdf"
output="/data/test1/interfaces/out/${VENDOR_NO}.pdf">

       <xapi:template type="rtf" location="xdo://APPS.XXSUPPO.en.00/?getSource=true" />

     </xapi:document>

  </xapi:request>

</xapi:requestset>

Below are the details of XML Tags

xapi:request - give details of how to split the XML file/Data.

xapi:email - give details of the email

id- This is a value that identifies each group. I out case it is Vendor No.
reply-to - This is the reply to email address
from - This is from email address that will be used while sending the email.
port - The port number of email server. Check with your DBA for this value.
server - The details of email server.

xapi:message - Give details of the email message

subject - Subject of the Email
attachment - We are attaching the report output with email,so the value should be "true"
to - The email id of the Vendor,which is the recipients email address
id - This is a unique that identifies each group. In our case it is the Vendor No

xapi:document - Here we define output documents details

output-type -The output type of the report output
output - The folder in which the output file will be saved

xapi:template - Give details of RTF template

type - Give the details for Template type.
location - Location of rtf template.

You can use below query to get RTF template location value

SELECT    xtb.application_short_name
       || '.'
       || xtb.template_code
       || '.'
       || xtb.default_language
       || '.'
       || xtb.default_territory
  FROM apps.xdo_templates_b xtb
 WHERE xtb.template_code = '<Template Code>';

After creating XML Bursting Control file you have to attach it under data definition.

Submitting Bursting Process

Attaching the bursting file will not burst the report output. You need to submit “XML Publisher Report Bursting Program” program after XML Publisher Report is completed. You can manually submit the program or you can use below code in after report trigger to submit bursting program.

 DECLARE
   l_conc_id      NUMBER;
   g_request_id   NUMBER;
BEGIN
   l_conc_id :=
      fnd_request.submit_request (application   => 'XDO',
                                  program       => 'XDOBURSTREP',
                                  description   => NULL,
                                  start_time    => SYSDATE,
                                  sub_request   => FALSE,
                                  argument1     => NULL,
                                  argument2     => g_request_id ---Request ID of XML Publisher Report
                                                               ,
                                  argument3     => 'Y'           -- debug Flag
                                                      );

   COMMIT;
END;