xml publisher data template with lexical parameter

xml publisher data template with lexical parameter

Hi Friends, We are going to discuss about the xml publisher data template with lexical parameter. We will share the method to use the lexical parameters in xml publisher data templates. Using lexical parameters , we can create the dynamic sql queries in xml publisher data template to extract the data. We will share the detail steps to create the data template and to use the lexical parameters in data templates. Lexical parameters one of the great feature in xml reports to create or develop the dynamic sql queries in oracle apps. Please find below the detail steps to write the xml publisher data template with lexical parameter.

Step by step to create the xml publisher data template with lexical parameter

Step 1:- We can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.

Lexical Parameter Syntax

Define the lexical parameters as follows:

&lexicalparamtername

We need to use the Lexical Parameter in xml publisher data template as below.

1.Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters. 

2.Create your query containing lexical references.

For example: 

Package employee AS 

where_clause varchar2(1000);

 ..... Package body employee AS .

.... where_clause := 'and org_id=10';

--------------

Steps to Use the Lexical Parameter in xml publisher data template

<dataQuery>

<sqlstatement name="Q1">

<![CDATA[SELECT ENAME, SAL FROM EMP &where_clause]]>

</sqlstatement>

</dataQuery>

xml publisher data template with lexical parameter

Sample Data Template file using the Lexical Parameter

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

 <dataTemplate name="SupInvData" description="Supplier Invoice Details" 

   Version="1.0" defaultPackage="employee">

 <properties>

  <property name="debug_mode" value="on"/>

 </properties> 

 <parameters>

  <parameter name="P_VENDOR_ID" dataType="character" /> 

 </parameters>

 <dataTrigger name="beforeReport" SOURCE="employee()"/>

 <dataQuery>

  <sqlStatement name="Q1">

  <![CDATA[ 

   select a1.invoice_id,A2.VENDOR_NAME,A1.INVOICE_NUM,A1.INVOICE_DATE,A1.INVOICE_AMOUNT from ap_invoices_all a1,po_vendors a2

where a1.vendor_id=a2.vendor_id

and a2.vendor_id=:P_VENDOR_ID

&where_clause

   ]]> 

   </sqlStatement>

  </dataQuery>

  <dataStructure>

  <group name="G_SUPP" source="Q1">

   <element name="VENDOR_NAME" value="VENDOR_NAME" /> 

   <element name="VENDOR_INV_AMT" value="G_INV.INVOICE_AMOUNT" 

    function="SUM()" /> 

   <group name="G_INV" source="Q1">

    <element name="INVOICE_NUM" value="INVOICE_NUM" /> 

    <element name="INVOICE_DATE" value="INVOICE_DATE" /> 

    <element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" /> 

   </group>

  </group>

  </dataStructure>

 </dataTemplate>

xml publisher data template with lexical parameter

Step 3:- Once we have save this above in xml file , then we can start the XML Publisher report creation part in oracle apps as below.


Step 4:- We first need to go to the Oracle Application to register this report as an xml publisher report in oracle apps r12.

xml publisher data template with lexical parameter

Step 5:-  Here below we are creating the "XX SUPPLIER INVOICE DETAIL' report.
Navigation :Concurrent ==> Concurrent Program.

Important Note : In Executable , We need to define 'XDODTEXE' .

Output Type : XML
Click on the Parameters button to create report parameter.

xml publisher data template with lexical parameter

Step 6:-
As below , Copying the Report parameter for Concurrent Program from Data Template.

xml publisher data template with lexical parameter

Step 7:- As below , P_VENDOR_ID column got created.

xml publisher data template with lexical parameter

Step 8:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

xml publisher data template with lexical parameter

Step 9:- 

Here below , We need to create the report Data Definition.

Note : We need to use the Short Name of Concurrent Program in Step 5 in Data Definition code field as below.


Click Apply.

xml publisher data template with lexical parameter

Step 10:- Here below , We need to upload the Data template file created in Step 2 & 3.In Data Template as highlighted below and Click on add file.

xml publisher data template with lexical parameter

Step 11:-

As of now , we are not creating the XML report layout , We will first generate the xml output from report to create the template/layout for the xml publisher report.

Step 12:- 

Here below , we are running the Concurrent Program . Once the request will be complete then we need to click on the 'View Output' button.

xml publisher data template with lexical parameter

xml publisher data template with lexical parameter