details: https://code.openbravo.com/erp/devel/pi/rev/a86d3e78d9bc changeset: 30670:a86d3e78d9bc user: Atul Gaware <atul.gaware <at> openbravo.com> date: Sun Nov 13 23:49:39 2016 +0530 summary: Related to issue 34205: Remove OR clause for invoicerule='N'
Remove OR clause for invoicerule='N' from c_invoice_candidate_v query to avoid orders having invoice rule as Do Not Invoice. diffstat: src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml | 2 +- 1 files changed, 1 insertions(+), 1 deletions(-) diffs (9 lines): diff -r 3b6e5e8f3a2c -r a86d3e78d9bc src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml --- a/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Fri Nov 18 14:28:32 2016 +0530 +++ b/src-db/database/model/views/C_INVOICE_CANDIDATE_V.xml Sun Nov 13 23:49:39 2016 +0530 @@ -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 = '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> + <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> </database> ------------------------------------------------------------------------------ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
