Lexical Section In xml Data Template

Lexical Section In xml Data Template

Hi Friends, We are going to discuss about the lexical section in xml data templates. We will share the steps for how to use the lexical section in xml reports data templates and how we can use the lexical parameters in xml data templates. For Lexical parameters we have to use the lexical section in xml data templates. Lexical section and lexical parameters helps to create the dynamic sql queries. We can change the sql query columns , sql query groupings and the sql query order by clauses using the lexical parameters in the sql query. To use the lexical parameters , we need to define the lexical parameters in the sql query. We will try to explain you the lexical section and the lexical parameters by create the new complete xml publisher report using the new data template. Please find below the detail about Lexical Section In xml Data Template.

Steps to use or Define  the Lexical Section In xml Data Template

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 Section In xml Data Template

<dataQuery>

<sqlstatement name="Q1">

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

</sqlstatement>

</dataQuery>

Lexical Section In xml Data Template

Sample Data Template file using the Lexical Section In xml Data Template

<?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>

Lexical Section In xml Data Template

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.

Lexical Section In xml Data Template

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.

Lexical Section In xml Data Template

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

Lexical Section In xml Data Template

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

Lexical Section In xml Data Template

Step 8:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

Lexical Section In xml Data Template

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.

Lexical Section In xml Data Template

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.

Lexical Section In xml Data Template