Fixes issue 21693: Invoices Tax Report shows incorrect data.
authorIoritz Cia <ioritz.cia@openbravo.com>
Fri, 21 Sep 2012 10:51:10 +0200
changeset 18035 41ed8d602a12
parent 18034 84a0b15c82c5
child 18036 29e0d401e2b2
Fixes issue 21693: Invoices Tax Report shows incorrect data.
src-db/database/model/functions/C_CURRENCY_CONVERT_RATE.xml
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
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-db/database/model/functions/C_CURRENCY_CONVERT_RATE.xml	Fri Sep 21 10:51:10 2012 +0200
@@ -0,0 +1,66 @@
+<?xml version="1.0"?>
+  <database name="FUNCTION C_CURRENCY_CONVERT_RATE">
+    <function name="C_CURRENCY_CONVERT_RATE" type="NUMERIC">
+      <parameter name="p_amount" type="NUMERIC" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_curfrom_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_curto_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_convdate" type="TIMESTAMP" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_ratetype" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_client_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_org_id" type="VARCHAR" mode="in">
+        <default/>
+      </parameter>
+      <parameter name="p_convrate" type="NUMERIC" mode="in">
+        <default/>
+      </parameter>
+      <body><![CDATA[/*************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (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 SLU
+ * All portions are Copyright (C) 2012 Openbravo SLU
+ * All Rights Reserved.
+************************************************************************/
+  v_Rate NUMBER;
+BEGIN
+  IF (p_convrate IS NULL) THEN
+    RETURN C_CURRENCY_CONVERT_PRECISION(p_amount, p_curfrom_id, p_curto_id, p_convdate, p_ratetype, p_client_id, p_org_id, 'A');
+  ELSE
+    -- Return Amount
+    IF(p_Amount=0 OR p_curfrom_id=p_curto_id) THEN
+      RETURN p_Amount;
+    END IF;
+    -- Return NULL
+    IF(p_Amount IS NULL OR p_curfrom_id IS NULL OR p_curto_id IS NULL) THEN
+      RETURN NULL;
+    END IF;
+    -- Standard Precision
+    RETURN ROUND(p_amount * p_convrate, C_GET_CURRENCY_PRECISION(p_curto_id, 'A')) ;
+  END IF;
+EXCEPTION
+WHEN OTHERS THEN
+  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
+  RAISE;
+END C_CURRENCY_CONVERT_RATE
+]]></body>
+    </function>
+  </database>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchase.jrxml	Tue Sep 25 08:29:41 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchase.jrxml	Fri Sep 21 10:51:10 2012 +0200
@@ -2,6 +2,9 @@
 <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportTaxInvoicePurchase" pageWidth="535" pageHeight="842" columnWidth="535" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
 	<property name="ireport.scriptlethandling" value="0"/>
 	<property name="ireport.encoding" value="UTF-8"/>
+	<property name="ireport.zoom" value="1.0"/>
+	<property name="ireport.x" value="0"/>
+	<property name="ireport.y" value="0"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -69,13 +72,15 @@
 		<defaultValueExpression><![CDATA["102"]]></defaultValueExpression>
 	</parameter>
 	<queryString>
-		<![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS REFERENCE, 
-      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_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXBASEAMT, 
-      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
+		<![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'Y' AS NATIONAL, COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS REFERENCE,
+      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_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXBASEAMT,
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
+      FROM C_TAX, C_INVOICETAX, C_INVOICE
+        LEFT JOIN C_CONVERSION_RATE_DOCUMENT ICR ON C_INVOICE.C_INVOICE_ID = ICR.C_INVOICE_ID,
+        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
@@ -85,12 +90,12 @@
       AND C_INVOICE.PROCESSED = 'Y'
       AND C_INVOICE.ISACTIVE = 'Y'
       AND C_INVOICE.ISSOTRX = 'N'
-      AND C_INVOICE.DATEINVOICED >= $P{parDateFrom} 
-      AND C_INVOICE.DATEINVOICED < $P{parDateTo} 
+      AND C_INVOICE.DATEINVOICED >= $P{parDateFrom}
+      AND C_INVOICE.DATEINVOICED < $P{parDateTo}
       AND C_LOCATION.C_COUNTRY_ID = $P{cCountryId}
-      AND 1=1 $P!{aux_client} 
-      AND 1=1 $P!{aux_org} 
-      AND 1=1 
+      AND 1=1 $P!{aux_client}
+      AND 1=1 $P!{aux_org}
+      AND 1=1
       GROUP BY COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO), C_INVOICE.DATEINVOICED, C_TAX.NAME, C_BPARTNER.NAME, C_INVOICE.ISSOTRX
       HAVING SUM(C_INVOICETAX.TAXBASEAMT) <> 0
       ORDER BY C_TAX.NAME, C_INVOICE.DATEINVOICED, COALESCE(C_INVOICE.POREFERENCE,  C_INVOICE.DOCUMENTNO)]]>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchaseForeign.jrxml	Tue Sep 25 08:29:41 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoicePurchaseForeign.jrxml	Fri Sep 21 10:51:10 2012 +0200
@@ -2,6 +2,9 @@
 <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportTaxInvoicePurchaseForeign" pageWidth="535" pageHeight="842" columnWidth="535" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
 	<property name="ireport.scriptlethandling" value="0"/>
 	<property name="ireport.encoding" value="UTF-8"/>
+	<property name="ireport.zoom" value="1.0"/>
+	<property name="ireport.x" value="0"/>
+	<property name="ireport.y" value="0"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -72,12 +75,14 @@
 		<defaultValueExpression><![CDATA["102"]]></defaultValueExpression>
 	</parameter>
 	<queryString>
-		<![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS REFERENCE, 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_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXBASEAMT, 
-      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
+		<![CDATA[SELECT C_INVOICE.ISSOTRX AS SALES, 'N' AS NATIONAL, COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS REFERENCE, 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_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXBASEAMT,
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
+      FROM C_TAX, C_INVOICETAX, C_INVOICE
+        LEFT JOIN C_CONVERSION_RATE_DOCUMENT ICR ON C_INVOICE.C_INVOICE_ID = ICR.C_INVOICE_ID,
+        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
@@ -90,7 +95,7 @@
       AND C_INVOICE.DATEINVOICED >= $P{parDateFrom}
       AND C_INVOICE.DATEINVOICED < $P{parDateTo}
       AND C_LOCATION.C_COUNTRY_ID <> $P{cCountryId}
-      AND 1=1 $P!{aux_client} 
+      AND 1=1 $P!{aux_client}
       AND 1=1 $P!{aux_org}
       AND 1=1
       GROUP BY COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO), C_TAX.NAME, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME, C_INVOICE.ISSOTRX
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml	Tue Sep 25 08:29:41 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml	Fri Sep 21 10:51:10 2012 +0200
@@ -2,6 +2,9 @@
 <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportTaxInvoiceSale" pageWidth="605" pageHeight="842" columnWidth="605" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
 	<property name="ireport.scriptlethandling" value="0"/>
 	<property name="ireport.encoding" value="UTF-8"/>
+	<property name="ireport.zoom" value="1.0"/>
+	<property name="ireport.x" value="0"/>
+	<property name="ireport.y" value="0"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -59,12 +62,14 @@
 		<defaultValueExpression><![CDATA["102"]]></defaultValueExpression>
 	</parameter>
 	<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_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-       ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXBASEAMT, 
-      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-       ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
+		<![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_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+       ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXBASEAMT,
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+       ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
+      FROM C_TAX, C_INVOICETAX, C_INVOICE
+        LEFT JOIN C_CONVERSION_RATE_DOCUMENT ICR ON C_INVOICE.C_INVOICE_ID = ICR.C_INVOICE_ID,
+        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
@@ -77,9 +82,9 @@
       AND C_LOCATION.C_COUNTRY_ID = $P{cCountryId}
       AND C_INVOICE.DATEINVOICED >= $P{parDateFrom}
       AND C_INVOICE.DATEINVOICED < $P{parDateTo}
-    AND 1=1 $P!{aux_client} 
+    AND 1=1 $P!{aux_client}
 AND 1=1 $P!{aux_org}
-      AND 1=1 
+      AND 1=1
       GROUP BY C_INVOICE.DOCUMENTNO, C_INVOICE.DATEINVOICED, C_TAX.NAME, C_BPARTNER.NAME, C_INVOICE.ISSOTRX
       HAVING SUM(C_INVOICETAX.TAXBASEAMT) <> 0
       ORDER BY C_TAX.NAME, C_INVOICE.DATEINVOICED]]>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSaleForeign.jrxml	Tue Sep 25 08:29:41 2012 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSaleForeign.jrxml	Fri Sep 21 10:51:10 2012 +0200
@@ -2,6 +2,9 @@
 <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ReportTaxInvoiceSaleForeign" pageWidth="535" pageHeight="842" columnWidth="535" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
 	<property name="ireport.scriptlethandling" value="0"/>
 	<property name="ireport.encoding" value="UTF-8"/>
+	<property name="ireport.zoom" value="1.0"/>
+	<property name="ireport.x" value="0"/>
+	<property name="ireport.y" value="0"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -72,12 +75,14 @@
 		<defaultValueExpression><![CDATA["102"]]></defaultValueExpression>
 	</parameter>
 	<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(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXBASEAMT, 
-      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID))
-      ELSE (C_CURRENCY_CONVERT(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
-      FROM C_TAX, C_INVOICETAX, C_INVOICE, C_BPARTNER_LOCATION, C_LOCATION, C_BPARTNER, C_DOCTYPE
+		<![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(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXBASEAMT, C_INVOICE.C_CURRENCY_ID,$P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXBASEAMT,
+      SUM(CASE WHEN DOCBASETYPE IN ('ARC','APC') THEN -1 * (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE))
+      ELSE (C_CURRENCY_CONVERT_RATE(C_INVOICETAX.TAXAMT, C_INVOICE.C_CURRENCY_ID, $P{PARAM_CURRENCY}, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())), NULL, C_INVOICETAX.AD_CLIENT_ID, C_INVOICETAX.AD_ORG_ID, ICR.RATE)) END) AS TAXAMT, '' AS ADTREEID,C_CURRENCY_SYMBOL($P{PARAM_CURRENCY}, 0, 'Y') AS CURRENCY,'' AS SUM_AMOUNT,'' AS Tax_BASE
+      FROM C_TAX, C_INVOICETAX, C_INVOICE
+        LEFT JOIN C_CONVERSION_RATE_DOCUMENT ICR ON C_INVOICE.C_INVOICE_ID = ICR.C_INVOICE_ID,
+        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
@@ -90,7 +95,7 @@
       AND C_INVOICE.DATEINVOICED >= $P{parDateFrom}
       AND C_INVOICE.DATEINVOICED < $P{parDateTo}
       AND C_LOCATION.C_COUNTRY_ID <> $P{cCountryId}
-      AND 1=1 $P!{aux_client} 
+      AND 1=1 $P!{aux_client}
       AND 1=1 $P!{aux_org}
       AND 1=1
       GROUP BY C_INVOICE.DOCUMENTNO, C_TAX.NAME, C_INVOICE.DATEINVOICED, C_BPARTNER.NAME, C_INVOICE.ISSOTRX