--- /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