src/org/openbravo/erpCommon/ad_process/CreateTaxReport_data.xsql
author Eduardo Argal <eduardo.argal@openbravo.com>
Wed, 18 Jun 2008 15:34:13 +0000
changeset 1271 762533f2593a
parent 1173 3b528c8e84a7
child 1605 8a0fe0193bef
permissions -rw-r--r--
Fixes bug 0002791 : Credit memo invoices
Modifies tax reports to take into account credit memo amounts as negative amounts
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2001-2008 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->
<SqlClass name="CreateTaxReportData" package="org.openbravo.erpCommon.ad_process">

  <SqlMethod name="select" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT ? AS NUMBER_ROWS, ? AS PARENT, TR.C_TAX_REPORT_ID AS ID, TR.NAME, TR.DESCRIPTION, TR.ISSUMMARY, TR.ISSHOWN, 
      AD_TREENODE.NODE_ID, COALESCE((CASE       
      TR.ISSUMMARY WHEN 'N' THEN      (CASE ISNEGATIVE WHEN 'Y' THEN SUM(A.TAXAMT)*(-1) WHEN 'N' THEN SUM(A.TAXAMT) END) ELSE 0 END),0) AS TOTAL, 
      '' AS VALUE, ? AS LEVEL_TAX, (CASE TO_NUMBER(?) WHEN 0 THEN       
      '' ELSE '' END) AS CLASS_TAX, 'TEXT-INDENT: '||TO_CHAR(TO_NUMBER(?)*10)||'pt' AS CLASS_STYLE
      FROM C_TAX_REPORT TR LEFT JOIN (SELECT CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * TAXAMT ELSE TAXAMT END AS TAXAMT, C_TAX_ID, C_INVOICE.ISSOTRX
      FROM C_INVOICETAX, C_INVOICE, C_DOCTYPE
      WHERE C_INVOICETAX.C_INVOICE_ID = C_INVOICE.C_INVOICE_ID
      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
      AND C_INVOICETAX.AD_CLIENT_ID IN ('1')
      AND C_INVOICETAX.AD_ORG_ID IN ('1')
      AND C_INVOICE.PROCESSED='Y'
      AND 1=1) A ON TR.C_TAX_ID = A.C_TAX_ID AND A.ISSOTRX = TR.ISSOTRX,
      AD_TREE, AD_TREENODE
      WHERE AD_TREE.AD_TREE_ID = AD_TREENODE.AD_TREE_ID
      AND AD_TREENODE.NODE_ID = TR.C_TAX_REPORT_ID
      AND TR.C_TAX_REPORT_ID = TO_NUMBER(?)
      AND AD_TREE.TREETYPE = 'TR'
      GROUP BY TR.C_TAX_REPORT_ID, TR.NAME, TR.DESCRIPTION, TR.ISSUMMARY, TR.ISSHOWN,
      AD_TREENODE.NODE_ID, TR.ISNEGATIVE
      ]]>
    </Sql>
    <Parameter name="numberRows"/>
    <Parameter name="parent"/>
    <Parameter name="level"/>
    <Parameter name="level"/>
    <Parameter name="level"/>
    <Parameter name="adClientId" optional="true" type="replace" after="C_INVOICETAX.AD_CLIENT_ID IN (" text="'1'"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="C_INVOICETAX.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="periodFrom" optional="true" after="AND 1=1"><![CDATA[ AND C_INVOICE.DATEACCT >= TO_DATE(?)]]></Parameter>
    <Parameter name="periodTo" optional="true" after="AND 1=1"><![CDATA[ AND C_INVOICE.DATEACCT < TO_DATE(?)]]></Parameter>
    <Parameter name="cTaxReportId"/>
  </SqlMethod>

  <SqlMethod name="set" type="constant" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql></Sql>
  </SqlMethod>

  <SqlMethod name="selectChild" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT TR.C_TAX_REPORT_ID AS ID, TR.NAME, TR.DESCRIPTION, TR.ISSUMMARY, TR.ISSHOWN, AD_TREENODE.NODE_ID
      FROM AD_TREE, AD_TREENODE, C_TAX_REPORT TR
      WHERE AD_TREE.AD_TREE_ID = AD_TREENODE.AD_TREE_ID
      AND AD_TREENODE.NODE_ID =  TR.C_TAX_REPORT_ID
      AND TR.AD_CLIENT_ID IN ('1')
      AND TR.AD_ORG_ID IN ('1')
      AND AD_TREENODE.PARENT_ID = TO_NUMBER(?)
      AND AD_TREE.TREETYPE = 'TR'
      GROUP BY TR.C_TAX_REPORT_ID, TR.NAME, TR.DESCRIPTION, TR.ISSUMMARY, TR.ISSHOWN, AD_TREENODE.NODE_ID, AD_TREENODE.SEQNO
      ORDER BY AD_TREENODE.SEQNO
      ]]>
    </Sql>
    <Parameter name="adClientId" optional="true" type="replace" after="TR.AD_CLIENT_ID IN (" text="'1'"/>
    <Parameter name="adOrgId" optional="true" type="replace" after="TR.AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="cTaxReportId"/>
  </SqlMethod>

  <SqlMethod name="selectOrg" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT AD_ORG.AD_ORG_ID AS ID, AD_ORG.NAME, AD_ORG.ISSUMMARY, AD_TREENODE.NODE_ID
      FROM AD_CLIENTINFO, AD_TREENODE, AD_ORG
      WHERE AD_CLIENTINFO.AD_TREE_ORG_ID = AD_TREENODE.AD_TREE_ID
      AND AD_TREENODE.NODE_ID = AD_ORG.AD_ORG_ID
      AND AD_TREENODE.PARENT_ID = TO_NUMBER(?)
      AND AD_CLIENTINFO.AD_CLIENT_ID = TO_NUMBER(?)
      ORDER BY ISSUMMARY DESC
      ]]>
    </Sql>
    <Parameter name="adOrgId"/>
    <Parameter name="adClientId"/>
  </SqlMethod>

  <SqlMethod name="processId" type="preparedStatement" return="String">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT AD_PROCESS.AD_PROCESS_ID AS ID
      FROM AD_PROCESS
      WHERE VALUE = ?
      ]]>
    </Sql>
    <Parameter name="processId"/>
  </SqlMethod>

  <SqlMethod name="selectFormat" type="preparedStatement" return="String">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      select TO_CHAR(TO_DATE(?), TO_CHAR(?)) from dual
      ]]>
    </Sql>
    <Parameter name="quarter"/>
    <Parameter name="dateFormat"/>
  </SqlMethod>

  <SqlMethod name="selectTax" type="preparedStatement" return="String">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT C_TAX_ID AS ID
      FROM C_TAX_REPORT
      WHERE C_TAX_REPORT_ID = TO_NUMBER(?)
      ]]>
    </Sql>
    <Parameter name="cTaxReportId"/>
  </SqlMethod>

  <SqlMethod name="selectC_TAX_REPORT" type="preparedStatement" return="multiple">
    <SqlMethodComment></SqlMethodComment>
    <Sql>
      <![CDATA[
      SELECT C_TAX_REPORT_ID AS ID, NAME
      FROM C_TAX_REPORT
      WHERE AD_ORG_ID IN ('1')
      AND AD_CLIENT_ID IN ('1')
      AND ISREPORT='Y'
      AND (isActive = 'Y' OR C_TAX_REPORT_ID = TO_NUMBER(?) )
      ]]>
    </Sql>
    <Parameter name="adOrgClient" type="replace" optional="true" after="WHERE AD_ORG_ID IN (" text="'1'"/>
    <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'1'"/>
    <Parameter name="cTaxReportId"/>
  </SqlMethod>
</SqlClass>