Fixes bug 0002791 : Credit memo invoices
authorEduardo Argal <eduardo.argal@openbravo.com>
Wed, 18 Jun 2008 15:34:13 +0000
changeset 1271 762533f2593a
parent 1270 c9b59ec0d21a
child 1272 0a07b59e3924
Fixes bug 0002791 : Credit memo invoices
Modifies tax reports to take into account credit memo amounts as negative amounts
src/org/openbravo/erpCommon/ad_process/CreateTaxReport_data.xsql
src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchase.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchaseForeign.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSaleForeign.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoice_data.xsql
--- a/src/org/openbravo/erpCommon/ad_process/CreateTaxReport_data.xsql	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_process/CreateTaxReport_data.xsql	Wed Jun 18 15:34:13 2008 +0000
@@ -28,9 +28,10 @@
       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 TAXAMT, C_TAX_ID, ISSOTRX
-      FROM C_INVOICETAX, C_INVOICE
+      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'
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchase.jrxml	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchase.jrxml	Wed Jun 18 15:34:13 2008 +0000
@@ -147,14 +147,17 @@
 	<parameter name="DETAIL" isForPrompting="false" class="java.lang.String"/>
 	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
 	<parameter name="LOCALE" isForPrompting="false" class="java.util.Locale"/>
-	<queryString><![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+	<queryString><![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, 
+      C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
       AND C_INVOICE.ISSOTRX = 'N'
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchaseForeign.jrxml	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchaseForeign.jrxml	Wed Jun 18 15:34:13 2008 +0000
@@ -148,13 +148,15 @@
 	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
 	<parameter name="LOCALE" isForPrompting="false" class="java.util.Locale"/>
 	<queryString><![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
       AND C_INVOICE.ISSOTRX = 'N'
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml	Wed Jun 18 15:34:13 2008 +0000
@@ -148,13 +148,15 @@
 	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
 	<parameter name="LOCALE" isForPrompting="false" class="java.util.Locale"/>
 	<queryString><![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
       AND C_INVOICE.ISSOTRX = 'Y'
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSaleForeign.jrxml	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSaleForeign.jrxml	Wed Jun 18 15:34:13 2008 +0000
@@ -151,13 +151,15 @@
 	<parameter name="NUMBERFORMAT" isForPrompting="false" class="java.text.DecimalFormat"/>
 	<parameter name="LOCALE" isForPrompting="false" class="java.util.Locale"/>
 	<queryString><![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
       AND C_INVOICE.ISSOTRX = 'N'
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoice_data.xsql	Wed Jun 18 15:06:07 2008 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoice_data.xsql	Wed Jun 18 15:34:13 2008 +0000
@@ -25,13 +25,15 @@
     <Sql>
     <![CDATA[
       SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_LOCATION.C_COUNTRY_ID = TO_NUMBER(?)
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
@@ -72,14 +74,17 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
     <![CDATA[
-      SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
-      SUM(C_INVOICETAX.TAXBASEAMT) AS TAXBASEAMT, SUM(C_INVOICETAX.TAXAMT) AS TAXAMT, '' AS ADTREEID
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER
+      SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, C_INVOICE.DOCUMENTNO AS DOCUMENTNO, C_INVOICE.DATEINVOICED, 
+      C_BPARTNER.NAME AS BPARTNER, LTRIM(RTRIM(C_TAX.NAME, ' '), ' ') AS TAXNAME, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXBASEAMT ELSE C_INVOICETAX.TAXBASEAMT END) AS TAXBASEAMT, 
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * C_INVOICETAX.TAXAMT ELSE C_INVOICETAX.TAXAMT END) AS TAXAMT, '' AS ADTREEID
+      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
       WHERE C_TAX.C_TAX_ID=C_INVOICETAX.C_TAX_ID
       AND C_INVOICETAX.C_INVOICE_ID=C_INVOICE.C_INVOICE_ID
       AND C_INVOICE.C_BPARTNER_LOCATION_ID = C_BPARTNER_LOCATION.C_BPARTNER_LOCATION_ID
       AND C_BPARTNER_LOCATION.C_LOCATION_ID = C_LOCATION.C_LOCATION_ID
       AND C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+      AND C_INVOICE.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_LOCATION.C_COUNTRY_ID <> TO_NUMBER(?)
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'