details: /erp/devel/pi/rev/585692ac6142
changeset: 11738:585692ac6142
user: Ainhoa Pagola <ainhoa.pagola <at> openbravo.com>
date: Wed Apr 27 17:24:50 2011 +0200
summary: Fixes issue 16100. Financial invoices showed in Reports
diffstat:
src-db/database/model/views/C_INVOICELINE_V2.xml | 7 +-
src/org/openbravo/erpCommon/ad_reports/InvoiceEdition_data.xsql | 23
++++++---
src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorJR.jrxml | 2 +-
src/org/openbravo/erpCommon/ad_reports/ReportInvoicesEditJR.jrxml | 2 +-
src/org/openbravo/erpCommon/ad_reports/ReportInvoices_data.xsql | 21
+++++---
5 files changed, 32 insertions(+), 23 deletions(-)
diffs (171 lines):
diff -r 42f99307aeae -r 585692ac6142
src-db/database/model/views/C_INVOICELINE_V2.xml
--- a/src-db/database/model/views/C_INVOICELINE_V2.xml Wed Apr 27 20:18:59
2011 +0200
+++ b/src-db/database/model/views/C_INVOICELINE_V2.xml Wed Apr 27 17:24:50
2011 +0200
@@ -16,7 +16,8 @@
CASE
WHEN il.pricelimit IS NULL THEN 0
ELSE round(i.multiplier * il.linenetamt - il.pricelimit * il.qtyinvoiced, 2)
-END AS lineoverlimitamt
-FROM c_invoice_v2 i, c_invoiceline il, m_product p
-WHERE i.c_invoice_id = il.c_invoice_id AND il.m_product_id =
p.m_product_id]]></view>
+END AS lineoverlimitamt, il.account_id
+FROM c_invoice_v2 i, c_invoiceline il
+LEFT JOIN m_product p ON il.m_product_id = p.m_product_id
+WHERE i.c_invoice_id = il.c_invoice_id]]></view>
</database>
diff -r 42f99307aeae -r 585692ac6142
src/org/openbravo/erpCommon/ad_reports/InvoiceEdition_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/InvoiceEdition_data.xsql Wed Apr
27 20:18:59 2011 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/InvoiceEdition_data.xsql Wed Apr
27 17:24:50 2011 +0200
@@ -12,7 +12,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2008 Openbravo SLU
+ * All portions are Copyright (C) 2001-2011 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -29,7 +29,8 @@
<Sql>
<![CDATA[
SELECT COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS
DOCUMENTNO, C_INVOICE.DATEINVOICED AS DATEINVOICED,
- C_BPARTNER.NAME AS CLIENT_NAME, M_PRODUCT.NAME AS PRODUCT_NAME, SUM(CASE
WHEN C_DOCTYPE.DOCBASETYPE='APC' THEN C_INVOICELINE.QTYINVOICED*-1 ELSE
C_INVOICELINE.QTYINVOICED END) AS QUANTITYORDER,
+ C_BPARTNER.NAME AS CLIENT_NAME, COALESCE(M_PRODUCT.NAME, C_GLITEM.NAME)
AS PRODUCT_NAME,
+ SUM(CASE WHEN C_DOCTYPE.DOCBASETYPE='APC' THEN
C_INVOICELINE.QTYINVOICED*-1 ELSE C_INVOICELINE.QTYINVOICED END) AS
QUANTITYORDER,
C_UOM.NAME AS UOMNAME, C_INVOICELINE.PRICEACTUAL AS PRICEACTUAL,
C_CURRENCY_SYMBOL (C_INVOICE.C_CURRENCY_ID, C_INVOICELINE.PRICEACTUAL,
'Y') AS PRICEACTUALSYM,
C_CURRENCY_CONVERT(C_INVOICELINE.PRICEACTUAL,
C_INVOICE.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())),
NULL, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID) AS CONVPRICEACTUAL,
@@ -43,16 +44,18 @@
C_INVOICELINE.AD_CLIENT_ID AS TRANSCLIENTID,
C_INVOICELINE.AD_ORG_ID AS TRANSORGID
FROM C_INVOICE left join C_DOCTYPE on
C_INVOICE.C_DOCTYPE_ID=C_DOCTYPE.C_DOCTYPE_ID,
- C_INVOICELINE, M_PRODUCT, C_UOM, C_BPARTNER
+ C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID =
M_PRODUCT.M_PRODUCT_ID
+ left join C_GLITEM on C_INVOICELINE.ACCOUNT_ID =
C_GLITEM.C_GLITEM_ID,
+ C_UOM, C_BPARTNER
WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
- AND C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
+ AND (C_INVOICELINE.M_PRODUCT_ID IS NOT NULL OR C_INVOICELINE.ACCOUNT_ID
IS NOT NULL)
AND C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_INVOICE.AD_ORG_ID IN ('1')
AND C_INVOICE.AD_CLIENT_ID IN ('1')
AND 1=1
AND C_INVOICE.ISSOTRX = ?
- GROUP BY C_BPARTNER.NAME, M_PRODUCT.NAME, C_UOM.NAME,
C_INVOICELINE.PRICEACTUAL,
+ GROUP BY C_BPARTNER.NAME, COALESCE(M_PRODUCT.NAME, C_GLITEM.NAME),
C_UOM.NAME, C_INVOICELINE.PRICEACTUAL,
COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO),
C_INVOICE.DATEINVOICED,
C_INVOICE.C_CURRENCY_ID, C_INVOICELINE.AD_CLIENT_ID,
C_INVOICELINE.AD_ORG_ID
ORDER BY C_BPARTNER.NAME, COALESCE(C_INVOICE.POREFERENCE,
C_INVOICE.DOCUMENTNO), C_INVOICE.DATEINVOICED
@@ -74,7 +77,7 @@
<Sql>
<![CDATA[
SELECT COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO) AS
DOCUMENTNO, C_INVOICE.DATEINVOICED AS DATEINVOICED,
- C_BPARTNER.NAME AS CLIENT_NAME, M_PRODUCT.NAME AS PRODUCT_NAME, SUM(CASE
WHEN C_DOCTYPE.DOCBASETYPE='APC' THEN C_INVOICELINE.QTYINVOICED*-1 ELSE
C_INVOICELINE.QTYINVOICED END) AS QUANTITYORDER,
+ C_BPARTNER.NAME AS CLIENT_NAME, COALESCE(M_PRODUCT.NAME, C_GLITEM.NAME)
AS PRODUCT_NAME, SUM(CASE WHEN C_DOCTYPE.DOCBASETYPE='APC' THEN
C_INVOICELINE.QTYINVOICED*-1 ELSE C_INVOICELINE.QTYINVOICED END) AS
QUANTITYORDER,
C_UOM.NAME AS UOMNAME, C_INVOICELINE.PRICEACTUAL AS PRICEACTUAL,
C_CURRENCY_SYMBOL (C_INVOICE.C_CURRENCY_ID, C_INVOICELINE.PRICEACTUAL,
'Y') AS PRICEACTUALSYM,
C_CURRENCY_CONVERT(C_INVOICELINE.PRICEACTUAL,
C_INVOICE.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C_INVOICE.DATEINVOICED, NOW())),
NULL, C_INVOICELINE.AD_CLIENT_ID, C_INVOICELINE.AD_ORG_ID) AS CONVPRICEACTUAL,
@@ -88,10 +91,12 @@
C_INVOICELINE.AD_CLIENT_ID AS TRANSCLIENTID,
C_INVOICELINE.AD_ORG_ID AS TRANSORGID
FROM C_INVOICE left join C_DOCTYPE on
C_INVOICE.C_DOCTYPE_ID=C_DOCTYPE.C_DOCTYPE_ID,
- C_INVOICELINE, M_PRODUCT, C_UOM, C_BPARTNER
+ C_INVOICELINE left join M_PRODUCT on C_INVOICELINE.M_PRODUCT_ID =
M_PRODUCT.M_PRODUCT_ID
+ left join C_GLITEM on C_INVOICELINE.ACCOUNT_ID =
C_GLITEM.C_GLITEM_ID,
+ C_UOM, C_BPARTNER
WHERE C_INVOICE.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND C_INVOICE.C_INVOICE_ID = C_INVOICELINE.C_INVOICE_ID
- AND C_INVOICELINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID
+ AND (C_INVOICELINE.M_PRODUCT_ID IS NOT NULL OR C_INVOICELINE.ACCOUNT_ID
IS NOT NULL)
AND C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_INVOICE.AD_ORG_ID IN ('1')
AND C_INVOICE.AD_CLIENT_ID IN ('1')
@@ -102,7 +107,7 @@
AND il.C_INVOICELINE_ID = ila.C_INVOICELINE_ID
AND ila.C_PROJECT_ID = ?))
AND C_INVOICE.ISSOTRX = ?
- GROUP BY C_BPARTNER.NAME, M_PRODUCT.NAME, C_UOM.NAME,
C_INVOICELINE.PRICEACTUAL,
+ GROUP BY C_BPARTNER.NAME, COALESCE(M_PRODUCT.NAME, C_GLITEM.NAME),
C_UOM.NAME, C_INVOICELINE.PRICEACTUAL,
COALESCE(C_INVOICE.POREFERENCE, C_INVOICE.DOCUMENTNO),
C_INVOICE.DATEINVOICED,
C_INVOICE.C_CURRENCY_ID, C_INVOICELINE.AD_CLIENT_ID,
C_INVOICELINE.AD_ORG_ID
ORDER BY C_BPARTNER.NAME, COALESCE(C_INVOICE.POREFERENCE,
C_INVOICE.DOCUMENTNO), C_INVOICE.DATEINVOICED
diff -r 42f99307aeae -r 585692ac6142
src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorJR.jrxml
--- a/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorJR.jrxml
Wed Apr 27 20:18:59 2011 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceVendorJR.jrxml
Wed Apr 27 17:24:50 2011 +0200
@@ -517,7 +517,7 @@
<textElement>
<font
pdfFontName="Helvetica-Bold" size="8"/>
</textElement>
- <text><![CDATA[Article]]></text>
+ <text><![CDATA[Item]]></text>
</staticText>
<staticText>
<reportElement
diff -r 42f99307aeae -r 585692ac6142
src/org/openbravo/erpCommon/ad_reports/ReportInvoicesEditJR.jrxml
--- a/src/org/openbravo/erpCommon/ad_reports/ReportInvoicesEditJR.jrxml Wed Apr
27 20:18:59 2011 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportInvoicesEditJR.jrxml Wed Apr
27 17:24:50 2011 +0200
@@ -651,7 +651,7 @@
<textElement textAlignment="Center"
verticalAlignment="Middle">
<font
pdfFontName="Helvetica-Bold" size="8"/>
</textElement>
- <text><![CDATA[Concept]]></text>
+ <text><![CDATA[Item]]></text>
</staticText>
<staticText>
<reportElement
diff -r 42f99307aeae -r 585692ac6142
src/org/openbravo/erpCommon/ad_reports/ReportInvoices_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportInvoices_data.xsql Wed Apr
27 20:18:59 2011 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportInvoices_data.xsql Wed Apr
27 17:24:50 2011 +0200
@@ -12,7 +12,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2008 Openbravo SLU
+ * All portions are Copyright (C) 2001-2011 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -34,7 +34,7 @@
TRANSCURRENCYID, TRANSDATE, TRANSCLIENTID, TRANSORGID
FROM (
SELECT B.NAME AS NAMEBPARTNER, I.C_INVOICE_ID AS INVOICEID,
I.DOCUMENTNO AS INVOICEDOCUMENTNO, O.DOCUMENTNO AS DOCNOORDER,
- I.DESCRIPTION AS DESCRIPTIONINVOICE, I.DATEINVOICED AS DATEINVOICED,
C_INVOICELINE.LINE, P.NAME AS NAMEPRODUCT,
+ I.DESCRIPTION AS DESCRIPTIONINVOICE, I.DATEINVOICED AS DATEINVOICED,
C_INVOICELINE.LINE, COALESCE(P.NAME, C_GLITEM.NAME) AS NAMEPRODUCT,
IL.QTYINVOICED AS QTYINVOICED, TO_CHAR(C_UOM.UOMSYMBOL) AS UOMSYMBOL,
IL.PRICEACTUAL AS PRICEACTUAL,
C_CURRENCY_CONVERT(IL.PRICEACTUAL, I.C_CURRENCY_ID, ?,
TO_DATE(COALESCE(I.DATEINVOICED, NOW())), NULL, IL.AD_CLIENT_ID, IL.AD_ORG_ID)
AS CONVPRICEACTUAL,
@@ -47,11 +47,13 @@
IL.AD_CLIENT_ID AS TRANSCLIENTID,
IL.AD_ORG_ID AS TRANSORGID
FROM C_INVOICE I left join C_ORDER O on I.C_ORDER_ID = O.C_ORDER_ID
- right join C_INVOICELINE_V2 IL on IL.C_INVOICE_ID =
I.C_INVOICE_ID,
- C_BPARTNER B, M_PRODUCT P, C_UOM, C_INVOICELINE, C_TAX
- WHERE IL.M_PRODUCT_ID = P.M_PRODUCT_ID
- AND IL.C_BPARTNER_ID = B.C_BPARTNER_ID
+ right join C_INVOICELINE_V2 IL on IL.C_INVOICE_ID =
I.C_INVOICE_ID
+ left join M_PRODUCT P ON IL.M_PRODUCT_ID =
P.M_PRODUCT_ID
+ left join C_GLITEM on IL.ACCOUNT_ID =
C_GLITEM.C_GLITEM_ID,
+ C_BPARTNER B, C_UOM, C_INVOICELINE, C_TAX
+ WHERE IL.C_BPARTNER_ID = B.C_BPARTNER_ID
AND IL.C_INVOICELINE_ID = C_INVOICELINE.C_INVOICELINE_ID
+ AND (C_INVOICELINE.M_PRODUCT_ID IS NOT NULL OR C_INVOICELINE.ACCOUNT_ID
IS NOT NULL)
AND C_INVOICELINE.C_UOM_ID = C_UOM.C_UOM_ID
AND C_INVOICELINE.C_TAX_ID = C_TAX.C_TAX_ID
AND I.AD_Client_ID IN ('1')
@@ -71,11 +73,12 @@
C_INVOICETAX.AD_CLIENT_ID AS TRANSCLIENTID,
C_INVOICETAX.AD_ORG_ID AS TRANSORGID
FROM C_INVOICE I2 left join C_INVOICELINE_V2 IL on IL.C_INVOICE_ID =
I2.C_INVOICE_ID
+ left join M_PRODUCT p on IL.M_PRODUCT_ID =
P.M_PRODUCT_ID
left join C_ORDER O on I2.C_ORDER_ID =
O.C_ORDER_ID,
- C_BPARTNER B, M_PRODUCT P, C_TAX, C_INVOICETAX
- WHERE IL.M_PRODUCT_ID = P.M_PRODUCT_ID
- AND IL.C_BPARTNER_ID = B.C_BPARTNER_ID
+ C_BPARTNER B, C_TAX, C_INVOICETAX
+ WHERE IL.C_BPARTNER_ID = B.C_BPARTNER_ID
AND C_INVOICETAX.C_INVOICE_ID= I2.C_INVOICE_ID
+ AND (IL.M_PRODUCT_ID IS NOT NULL OR IL.ACCOUNT_ID IS NOT NULL)
AND C_INVOICETAX.C_TAX_ID = C_TAX.C_TAX_ID
AND I2.AD_Client_ID IN ('2')
AND I2.AD_ORG_ID IN ('2')
------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today. Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
