src/org/openbravo/erpCommon/ad_reports/ReportTaxInvoiceSale.jrxml
changeset 18035 41ed8d602a12
parent 11848 e900de861372
child 22717 8f8910c3f90c
--- 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]]>