2 Replies Latest reply on Nov 15, 2018 2:56 PM by cormaco

    XML Query in oracle

    sahar.zd

      Dear All,

      im trying to write this query in XML but it gives me error.

       

      can you please check -  From_GL_Date  is a dynamic variable will be given when i run the report. (the error is the report isnt take it

       

       

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

      <dataTemplate name="INV"

      description="Supplier Balance XML" version="1.0"

      defaultPackage="">

      <properties></properties>

      <dataQuery>

      <sqlStatement name="Q1">

      <![CDATA[

      select

      SUP.VENDOR_ID,

      SUP.VENDOR_NAME,

      SUP.SEGMENT1 VENDOR_NUMBER,

      sup.VAT_REGISTRATION_NUM VAT,

      ( nvl(sum(dist.amount) ,0))  balance

      from

      AP_INVOICE_DISTRIBUTIONS_ALL dist,

      AP_INVOICES_ALL INV,

      AP_SUPPLIERS SUP,

      AP_SUPPLIER_SITES_ALL SUPA,

      gl_code_combinations acct

      where

      And INV.accts_pay_code_combination_id=acct.code_combination_id

      and  SUP.VENDOR_ID = SUPA.VENDOR_ID

      AND dist.INVOICE_ID(+) = INV.INVOICE_ID

      AND UPPER(inv.INVOICE_TYPE_LOOKUP_CODE) <> UPPER('Prepayment')

      AND UPPER(dist.LINE_TYPE_LOOKUP_CODE) <> UPPER('PREPAY')

      and INV.VENDOR_SITE_ID = SUPA.VENDOR_SITE_ID

      and inv.gl_date between TO_DATE($From_GL_Date ,'YYYY-MM-DD')   and TO_DATE('2018/09/30','YYYY-MM-DD')

      and SUPA.org_id =105

      and sup.enabled_flag = 'Y'

      and ( sup.pay_group_lookup_code   not in ('EMPLOYEE' )  or sup.pay_group_lookup_code is null )

      GROUP BY

      SUP.VENDOR_ID,

      SUP.VENDOR_NAME,

      SUP.SEGMENT1 ,

      sup.VAT_REGISTRATION_NUM

      ]]>

      </sqlStatement>

      </dataQuery>

      <dataStructure>

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

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

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

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

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

      </group>

      </dataStructure>

       

       

      </dataTemplate>

       

       

      thank you in advance ..