New Development Report Using XML Data Template
Introduction
XML Publisher (BI Publisher) provides multiple ways to generate reports in Oracle E-Business Suite. One important and commonly used approach is creating reports using Data Templates.
A Data Template is an XML-based definition that contains the SQL queries, parameters, data structure, and grouping logic required to generate report data. Unlike standard SQL-based data definitions, Data Templates allow more control over data extraction, complex joins, multiple queries, conditional logic, and XML structure.
Using Data Templates helps developers:
- Handle complex reporting requirements
- Combine multiple SQL queries in one report
- Define hierarchical data easily
- Improve performance and maintainability
The Data Template Definition
A Data Template in XML Publisher (BI Publisher) is an XML-based file that defines how data is extracted from the database and structured into XML format for reporting.
Data Template Format:
Parameters Section:
The Parameter Section in a Data Template is used to define input values that users provide while running the XML Publisher report. These parameters help filter and control the report data based on user requirements.
How to Define Parameters:
How to Pass Parameters:
To pass parameters, (for example, to restrict the query), use bind variables in your query. For example:
SELECT
aia.invoice_id,
aia.invoice_num,
aia.invoice_currency_code,
aia.invoice_amount,
aia.payment_currency_code,
ap.vendor_name,
aia.org_id,
aps.vendor_site_id,
aps.vendor_site_code
FROM
apps.ap_invoices_all aia,
apps.ap_suppliers ap,
apps.ap_supplier_sites_all aps
WHERE
aia.vendor_id = ap.vendor_id
AND aps.vendor_site_id = aia.vendor_site_id
AND ap.vendor_id = aps.vendor_id
AND aia.vendor_id =:p_vendor_id
AND aia.invoice_num = nvl(:p_invoice_num,aia.invoice_num)
AND aia.org_id = nvl(:p_org_id,aia.org_id)
Data Query Section:
The SQL Query Section in an XML Publisher Data Template is used to retrieve data from the Oracle database based on business requirements and input parameters.
Lexical References:
Use a lexical reference when you want the parameter to replace multiple values at runtime.
Create your query containing lexical references.
</dataQuery>
Data Structure Section:
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.
Navigation of XML Publisher Administrator:
Go to the XML Publisher Administrator
Click the Data Definition
you must first register your data template in the Template Manager as a Data Definition. After that you attached the RTF template to that data definition, After creating the data definition and creating the Template
Navigation of Concurrent Registration
Go to Application Developer
Click Concurrent
Select the Program
Executable Name Enter the XML Publisher data engine executable: XDODTEXE
Output Format Select “XML” as the output format.
Note: We do not need to create a concurrent executable as we use a seeded executable XDODTEXE
Assign the Concurrent Program Request Group
Navigation of Request Group:
Go to System Administrator
Press the Security
Select the Responsibility
Click the Request Form
Assign the Concurrent Program Respective Request Group
Navigation of View Request:
Go to Payables Vision Operations (USA)
Select View
Click the Request
Submitting a new request.
Give Concurrent Program name: XXAP Invoice Details Report
Enter the Parameter Value click the Submit Button
Once Submit the request the program gets ends with complete normal and Output has generated.
