details: https://code.openbravo.com/erp/devel/pi/rev/daca5e5e260e
changeset: 30718:daca5e5e260e
user: Atul Gaware <atul.gaware <at> openbravo.com
date: Fri Dec 02 10:26:25 2016 +0100
summary: Related to issue 34205: Code review improvements
- Fix parenthesis in C_INVOICE_CANDIDATE_V view.
- Fix CreateFrom_InvoiceData.selectFromShipmentSOTrxCombo query, by retrieving
shipments not related to orders or shipments related to orders with invoicerule
<> 'N'.
- Remove unnecessary ic.term <> 'N' checks, as C_INVOICE_CANDIDATE_V view will
not retrieve orders with invoicerule = 'N' anymore.
diffstat:
src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml | 2
+-
src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql | 9
+++------
src/org/openbravo/erpCommon/ad_forms/GenerateInvoicesmanual_data.xsql | 4
++--
3 files changed, 6 insertions(+), 9 deletions(-)
diffs (63 lines):
diff -r 0fba1fd4f076 -r daca5e5e260e
src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
--- a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Fri Dec 02
10:33:48 2016 +0100
+++ b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Fri Dec 02
10:26:25 2016 +0100
@@ -1,4 +1,4 @@
<?xml version="1.0"?>
<database name="VIEW C_INVOICE_CANDIDATE_V">
- <view name="C_INVOICE_CANDIDATE_V"><![CDATA[SELECT o.ad_client_id,
o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered,
o.c_doctype_id, o.totallines AS amountlines, o.grandtotal AS amountlinesgross,
sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS
notinvoicedlines, round(sum(round((l.qtyordered - l.qtyinvoiced) *
l.priceactual, c.stdprecision) * (1 + t.rate / 100)), c.stdprecision) AS
notinvoicedlinesgross, o.invoicerule AS term, sum(round((l.qtydelivered -
l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS pendinglines,
round(sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual,
c.stdprecision) * (1 + t.rate / 100)), c.stdprecision) AS pendinglinesgross,
sum(abs(l.qtyordered)) AS qtyordered, sum(abs(l.qtydelivered)) AS qtydelivered
FROM c_order o JOIN c_doctype dt ON o.c_doctype_id = dt.c_doctype_id JOIN
c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id JOIN c_currency c ON
o.c_currency_id = c.c_currency_id JOIN c_orderl
ine l ON o.c_order_id = l.c_order_id JOIN c_tax t ON t.c_tax_id = l.c_tax_id
LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id =
si.c_invoiceschedule_id WHERE (o.docstatus = 'CO' OR o.docstatus = 'CL' OR
o.docstatus = 'IP') AND (o.invoicerule = 'I' OR o.invoicerule = 'O' OR
o.invoicerule = 'D' OR o.invoicerule = 'S') AND dt.docbasetype = 'SOO' AND
(dt.docsubtypeso <> 'ON' OR dt.docsubtypeso <> 'OB' OR dt.docsubtypeso <> 'WR')
AND (si.invoicefrequency IS NULL OR (si.invoicefrequency = 'D' OR
si.invoicefrequency = 'W' OR si.invoicefrequency = 'T') AND
trunc(o.dateordered) <= (trunc(now(), 'MM') + si.invoicedaycutoff - 1) AND
trunc(now()) >= (trunc(o.dateordered, 'MM') + si.invoiceday - 1) OR
trunc(o.dateordered) <= (trunc(now(), 'MM') + si.invoicedaycutoff + 14) AND
trunc(now()) >= (trunc(o.dateordered, 'MM') + si.invoiceday + 14) OR
si.invoicefrequency = 'M' AND trunc(o.dateordered) <= (trunc(now(), 'MM') +
si.invoicedaycutoff - 1) AND trunc(now()) >= (trunc(o.dateordered, 'M
M') + si.invoiceday - 1)) AND (abs(l.qtyordered - l.qtyinvoiced) <> 0 OR
abs(l.qtydelivered - l.qtyinvoiced) <> 0) GROUP BY o.ad_client_id, o.ad_org_id,
o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id,
o.totallines, o.grandtotal, o.invoicerule, c.c_currency_id,
c.stdprecision]]></view>
+ <view name="C_INVOICE_CANDIDATE_V"><![CDATA[SELECT o.ad_client_id,
o.ad_org_id, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered,
o.c_doctype_id, o.totallines AS amountlines, o.grandtotal AS amountlinesgross,
sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS
notinvoicedlines, round(sum(round((l.qtyordered - l.qtyinvoiced) *
l.priceactual, c.stdprecision) * (1 + t.rate / 100)), c.stdprecision) AS
notinvoicedlinesgross, o.invoicerule AS term, sum(round((l.qtydelivered -
l.qtyinvoiced) * l.priceactual, c.stdprecision)) AS pendinglines,
round(sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual,
c.stdprecision) * (1 + t.rate / 100)), c.stdprecision) AS pendinglinesgross,
sum(abs(l.qtyordered)) AS qtyordered, sum(abs(l.qtydelivered)) AS qtydelivered
FROM c_order o JOIN c_doctype dt ON o.c_doctype_id = dt.c_doctype_id JOIN
c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_id JOIN c_currency c ON
o.c_currency_id = c.c_currency_id JOIN c_orderl
ine l ON o.c_order_id = l.c_order_id JOIN c_tax t ON t.c_tax_id = l.c_tax_id
LEFT JOIN c_invoiceschedule si ON bp.c_invoiceschedule_id =
si.c_invoiceschedule_id WHERE (o.docstatus = 'CO' OR o.docstatus = 'CL' OR
o.docstatus = 'IP') AND dt.docbasetype = 'SOO' AND (dt.docsubtypeso <> 'ON' OR
dt.docsubtypeso <> 'OB' OR dt.docsubtypeso <> 'WR') AND (o.invoicerule = 'I' OR
o.invoicerule = 'O' OR o.invoicerule = 'D' OR o.invoicerule = 'S' AND
(si.invoicefrequency IS NULL OR si.invoicefrequency = 'D' OR
si.invoicefrequency = 'W' OR si.invoicefrequency = 'T' AND trunc(o.dateordered)
<= (trunc(now(), 'MM') + si.invoicedaycutoff - 1) AND trunc(now()) >=
(trunc(o.dateordered, 'MM') + si.invoiceday - 1) OR trunc(o.dateordered) <=
(trunc(now(), 'MM') + si.invoicedaycutoff + 14) AND trunc(now()) >=
(trunc(o.dateordered, 'MM') + si.invoiceday + 14) OR si.invoicefrequency = 'M'
AND trunc(o.dateordered) <= (trunc(now(), 'MM') + si.invoicedaycutoff - 1) AND
trunc(now()) >= (trunc(o.dateordered, 'MM')
+ si.invoiceday - 1))) AND (abs(l.qtyordered - l.qtyinvoiced) <> 0 OR
abs(l.qtydelivered - l.qtyinvoiced) <> 0) GROUP BY o.ad_client_id, o.ad_org_id,
o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id,
o.totallines, o.grandtotal, o.invoicerule, c.c_currency_id,
c.stdprecision]]></view>
</database>
diff -r 0fba1fd4f076 -r daca5e5e260e
src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
--- a/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
Fri Dec 02 10:33:48 2016 +0100
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
Fri Dec 02 10:26:25 2016 +0100
@@ -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-2014 Openbravo SLU
+ * All portions are Copyright (C) 2001-2016 Openbravo SLU
* All Rights Reserved.
* Contributor(s): Cheli Pineda__________________________.
************************************************************************
@@ -149,7 +149,7 @@
AND ic.C_BPartner_ID = ?
AND pl.istaxincluded = ?
AND o.C_Order_Id = ic.C_Order_Id
- AND ((ic.term = 'D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND
exists (SELECT 1 FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = o.C_ORDER_ID group
by ol.c_orderline_id having SUM(ol.QTYORDERED)-SUM(ol.QTYINVOICED)<>0)) OR
(ic.term <> 'N' AND (ic.term IN ('O','S') AND (ic.qtyordered = ic.qtydelivered)
)) )
+ AND ((ic.term = 'D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND
exists (SELECT 1 FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = o.C_ORDER_ID group
by ol.c_orderline_id having SUM(ol.QTYORDERED)-SUM(ol.QTYINVOICED)<>0)) OR
(ic.term IN ('O','S') AND (ic.qtyordered = ic.qtydelivered) ) )
GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal
ORDER BY NAME
]]>
@@ -473,10 +473,7 @@
HAVING ( l.movementqty >= 0 AND l.movementqty >
Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) )
OR ( l.movementqty < 0 AND l.movementqty <
Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) )
)
- AND NOT EXISTS (SELECT 1 FROM C_INVOICE_CANDIDATE_V ic, C_ORDER o
- WHERE o.C_ORDER_ID = s.C_ORDER_ID
- AND o.C_ORDER_ID = ic.C_ORDER_ID
- AND ic.term = 'N')
+ AND NOT EXISTS (SELECT 1 FROM C_ORDER o WHERE o.C_ORDER_ID =
s.C_ORDER_ID AND o.invoicerule = 'N')
AND NOT EXISTS (SELECT 1
FROM C_ORDER o , C_ORDERLINE ol
WHERE o.C_ORDER_ID = s.C_ORDER_ID
diff -r 0fba1fd4f076 -r daca5e5e260e
src/org/openbravo/erpCommon/ad_forms/GenerateInvoicesmanual_data.xsql
--- a/src/org/openbravo/erpCommon/ad_forms/GenerateInvoicesmanual_data.xsql
Fri Dec 02 10:33:48 2016 +0100
+++ b/src/org/openbravo/erpCommon/ad_forms/GenerateInvoicesmanual_data.xsql
Fri Dec 02 10:26:25 2016 +0100
@@ -38,7 +38,7 @@
AND ic.C_DocType_ID=dt.C_DocType_ID
AND l.value = ic.term
AND ord.C_ORDER_ID=ic.C_ORDER_ID
- AND ((ic.term ='D' AND ic.qtydelivered <>0 AND exists (SELECT 1 FROM
C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYDELIVERED-ol.QTYINVOICED <> 0)) OR (ic.term = 'I' AND exists (SELECT 1
FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYORDERED-ol.QTYINVOICED <> 0)) OR (ic.term <> 'N' AND (ic.term IN ('O')
AND (ic.qtyordered = ic.qtydelivered) )) OR (ic.term = 'S' AND exists (SELECT 1
FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND ol.QTYDELIVERED <>
0 AND ol.QTYDELIVERED <> ol.QTYINVOICED)))
+ AND ((ic.term ='D' AND ic.qtydelivered <>0 AND exists (SELECT 1 FROM
C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYDELIVERED-ol.QTYINVOICED <> 0)) OR (ic.term = 'I' AND exists (SELECT 1
FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYORDERED-ol.QTYINVOICED <> 0)) OR (ic.term IN ('O') AND (ic.qtyordered =
ic.qtydelivered)) OR (ic.term = 'S' AND exists (SELECT 1 FROM C_ORDERLINE ol
WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND ol.QTYDELIVERED <> 0 AND
ol.QTYDELIVERED <> ol.QTYINVOICED)))
AND AD_Reference_ID='150'
AND ic.ad_client_id in ('1')
AND ic.ad_org_id in ('1')
@@ -70,7 +70,7 @@
AND ic.C_DocType_ID=dt.C_DocType_ID
AND l.value = ic.term
AND ord.C_ORDER_ID=ic.C_ORDER_ID
- AND ((ic.term ='D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND exists
(SELECT 1 FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYORDERED-ol.QTYINVOICED <> 0)) OR (ic.term <> 'N' AND (ic.term IN ('O')
AND (ic.qtyordered = ic.qtydelivered) )) OR (ic.term = 'S' AND exists (SELECT 1
FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND ol.QTYDELIVERED <>
0 AND ol.QTYDELIVERED <> ol.QTYINVOICED)))
+ AND ((ic.term ='D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND exists
(SELECT 1 FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND
ol.QTYORDERED-ol.QTYINVOICED <> 0)) OR (ic.term IN ('O') AND (ic.qtyordered =
ic.qtydelivered)) OR (ic.term = 'S' AND exists (SELECT 1 FROM C_ORDERLINE ol
WHERE ol.C_ORDER_ID = ord.C_ORDER_ID AND ol.QTYDELIVERED <> 0 AND
ol.QTYDELIVERED <> ol.QTYINVOICED)))
AND AD_Reference_ID='150'
AND ic.ad_client_id in ('1')
AND ic.ad_org_id in ('1')
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
