details: https://code.openbravo.com/erp/devel/pi/rev/a9ab872b6dee
changeset: 30564:a9ab872b6dee
user: Atul Gaware <atul.gaware <at> openbravo.com>
date: Thu Oct 27 12:14:29 2016 +0200
summary: Fixes issue 34205: Improve c_invoice_candidate_v view performance
diffstat:
src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml | 2 +-
1 files changed, 1 insertions(+), 1 deletions(-)
diffs (9 lines):
diff -r 02c7d99b53e9 -r a9ab872b6dee
src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml
--- a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Wed Sep 21
16:39:13 2016 +0200
+++ b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Thu Oct 27
12:14:29 2016 +0200
@@ -1,4 +1,4 @@
<?xml version="1.0"?>
<database name="VIEW C_INVOICE_CANDIDATE_V">
- <view name="C_INVOICE_CANDIDATE_V"><![CDATA[SELECT sq.ad_client_id,
sq.ad_org_id, sq.c_bpartner_id, sq.c_order_id, sq.documentno, sq.dateordered,
sq.c_doctype_id, sq.amountlines, sq.amountlinesgross, sq.notinvoicedlines,
round(sq.notinvoicedlinesgross, c.stdprecision) AS notinvoicedlinesgross,
sq.term, sq.pendinglines, round(sq.pendinglinesgross, c.stdprecision) AS
pendinglinesgross, sq.qtyordered, sq.qtydelivered FROM (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_1.stdprecision)) AS
notinvoicedlines, sum(round((l.qtyordered - l.qtyinvoiced) * l.priceactual,
c_1.stdprecision) * (1 + t.rate / 100)) AS notinvoicedlinesgross, o.invoicerule
AS term, sum(round((l.qtydelivered - l.qtyinvoiced) * l.priceactual,
c_1.stdprecision)) AS pendinglines, sum(round((l.qtydelivered - l.qtyinvoiced)
* l.pricea
ctual, c_1.stdprecision) * (1 + t.rate / 100)) AS pendinglinesgross,
sum(abs(l.qtyordered)) AS qtyordered, sum(abs(l.qtydelivered)) AS qtydelivered,
o.m_pricelist_id, c_1.c_currency_id FROM c_order o JOIN m_pricelist p_1 ON
o.m_pricelist_id = p_1.m_pricelist_id JOIN c_currency c_1 ON p_1.c_currency_id
= c_1.c_currency_id JOIN c_orderline l ON o.c_order_id = l.c_order_id JOIN
c_bpartner bp ON o.c_bpartner_id = bp.c_bpartner_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 IN ('CO', 'CL', 'IP')) AND
(o.c_doctype_id IN (SELECT c_doctype.c_doctype_id FROM c_doctype WHERE
c_doctype.docbasetype = 'SOO' AND (c_doctype.docsubtypeso NOT IN ('ON', 'OB',
'WR')))) AND (o.invoicerule = 'I' OR o.invoicerule = 'O' OR o.invoicerule = 'N'
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' AN
D 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, o.m_pricelist_id, c_1.c_currency_id)
sq JOIN m_pricelist p ON sq.m_pricelist_id = p.m_pricelist_id JOIN c_currency c
ON sq.c_currency_id = c.c_currency_id]]></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 (o.invoicerule = 'I' OR o.invoicerule = 'O' OR
o.invoicerule = 'N' 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, '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>
------------------------------------------------------------------------------
The Command Line: Reinvented for Modern Developers
Did the resurgence of CLI tooling catch you by surprise?
Reconnect with the command line and become more productive.
Learn the new .NET and ASP.NET CLI. Get your free copy!
http://sdm.link/telerik
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
