details: https://code.openbravo.com/erp/devel/pi/rev/ef154325c6cf changeset: 29577:ef154325c6cf user: Atul Gaware <atul.gaware <at> openbravo.com> date: Wed Jun 01 18:31:57 2016 +0530 summary: Fixes issue 32949: Improve performance in Return to Vendor Pick and Execute
In order to avoid unnecessary joins to M_InoutLine table, replace ol.goodsShipmentLine = iol with ol.goodsShipmentLine.id = iol. details: https://code.openbravo.com/erp/devel/pi/rev/f3f815904ffd changeset: 29578:f3f815904ffd user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Fri Jun 03 15:32:03 2016 +0200 summary: Related to issue 32949: Code review improvements - Use inner join instead of left join between OrderLine and Order tables. - Avoid unnecessary join with Tax table. - Add index in M_MatchPO table to M_InoutLine_Id column. details: https://code.openbravo.com/erp/devel/pi/rev/083683ac0038 changeset: 29579:083683ac0038 user: Atul Gaware <atul.gaware <at> openbravo.com> date: Wed May 25 00:58:03 2016 +0530 summary: Fixes issue 32032: ReportOrderNotShipped report query improved -Replace IN clause for locator using EXISTS -Remove unwanted C_Order from subquery which compares quantities -Rearrange the query -Get stock from m_storage_detail using subquery instead of left join details: https://code.openbravo.com/erp/devel/pi/rev/a68ba952787a changeset: 29580:a68ba952787a user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Fri Jun 03 14:56:32 2016 +0200 summary: Related to issue 32032: Code review improvements diffstat: modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql | 67 ++++----- src-db/database/model/tables/M_MATCHPO.xml | 3 + src-db/database/sourcedata/AD_TABLE.xml | 10 +- src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java | 26 +- 4 files changed, 53 insertions(+), 53 deletions(-) diffs (173 lines): diff -r 5c3ae208fb2c -r a68ba952787a modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql --- a/modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql Fri Jun 03 13:58:10 2016 +0200 +++ b/modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql Fri Jun 03 14:56:32 2016 +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) 2009-2015 Openbravo SLU + * All portions are Copyright (C) 2009-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -29,46 +29,43 @@ PR.NAME || ' ' || COALESCE(TO_CHAR(ASI.DESCRIPTION), '') AS PRODNAME, OL.M_ATTRIBUTESETINSTANCE_ID, OL.QTYORDERED || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS ORDEREDQTY, (OL.QTYORDERED-OL.QTYDELIVERED) || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS PENDINGQTY, - SUM(SD.QTYONHAND) || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS QTYINSTOCK - FROM AD_ORG ORG, C_BPARTNER BP, C_BPARTNER_LOCATION BPADD, - M_PRODUCT PR, C_UOM UO, - C_ORDER O - LEFT JOIN AD_REF_LIST_V REFLISTV - ON REFLISTV.VALUE = O.DELIVERYRULE - LEFT JOIN C_BPARTNER_LOCATION DLOC - ON DLOC.C_BPARTNER_LOCATION_ID = O.DELIVERY_LOCATION_ID, - C_ORDERLINE OL - LEFT JOIN M_ATTRIBUTESETINSTANCE ASI - ON OL.M_ATTRIBUTESETINSTANCE_ID = ASI.M_ATTRIBUTESETINSTANCE_ID - LEFT JOIN M_STORAGE_DETAIL SD - ON OL.M_PRODUCT_ID = SD.M_PRODUCT_ID - AND OL.C_UOM_ID = SD.C_UOM_ID - AND COALESCE(OL.M_ATTRIBUTESETINSTANCE_ID, SD.M_ATTRIBUTESETINSTANCE_ID, '0') = COALESCE(SD.M_ATTRIBUTESETINSTANCE_ID, '0') - AND COALESCE(OL.M_PRODUCT_UOM_ID, '-1') = COALESCE(SD.M_PRODUCT_UOM_ID,'-1') - AND SD.M_LOCATOR_ID IN (SELECT LOC.M_LOCATOR_ID - FROM M_LOCATOR LOC - WHERE LOC.M_WAREHOUSE_ID = OL.M_WAREHOUSE_ID) - WHERE O.AD_ORG_ID = ORG.AD_ORG_ID - AND O.C_BPARTNER_ID = BP.C_BPARTNER_ID - AND O.C_BPARTNER_LOCATION_ID = BPADD.C_BPARTNER_LOCATION_ID - AND O.C_ORDER_ID = OL.C_ORDER_ID - AND O.DOCSTATUS = 'CO' + ( + SELECT SUM(SD.QTYONHAND) + FROM M_STORAGE_DETAIL SD + WHERE SD.M_PRODUCT_ID = OL.M_PRODUCT_ID + AND SD.C_UOM_ID = OL.C_UOM_ID + AND COALESCE(SD.M_ATTRIBUTESETINSTANCE_ID, '0') = COALESCE(OL.M_ATTRIBUTESETINSTANCE_ID, SD.M_ATTRIBUTESETINSTANCE_ID, '0') + AND COALESCE(SD.M_PRODUCT_UOM_ID,'-1') = COALESCE(OL.M_PRODUCT_UOM_ID, '-1') + AND EXISTS ( + SELECT 1 + FROM M_LOCATOR LOC + WHERE LOC.M_LOCATOR_ID = SD.M_LOCATOR_ID + AND LOC.M_WAREHOUSE_ID = OL.M_WAREHOUSE_ID + ) + ) || ' ' || COALESCE(TO_CHAR(UO.UOMSYMBOL), '') AS QTYINSTOCK + FROM C_ORDER O + JOIN AD_ORG ORG ON O.AD_ORG_ID = ORG.AD_ORG_ID + JOIN C_BPARTNER BP ON O.C_BPARTNER_ID = BP.C_BPARTNER_ID + JOIN C_BPARTNER_LOCATION BPADD ON O.C_BPARTNER_LOCATION_ID = BPADD.C_BPARTNER_LOCATION_ID + JOIN AD_REF_LIST_V REFLISTV ON O.DELIVERYRULE = REFLISTV.VALUE + LEFT JOIN C_BPARTNER_LOCATION DLOC ON O.DELIVERY_LOCATION_ID = DLOC.C_BPARTNER_LOCATION_ID + JOIN C_ORDERLINE OL ON O.C_ORDER_ID = OL.C_ORDER_ID + JOIN M_PRODUCT PR ON OL.M_PRODUCT_ID = PR.M_PRODUCT_ID + JOIN C_UOM UO ON OL.C_UOM_ID = UO.C_UOM_ID + LEFT JOIN M_ATTRIBUTESETINSTANCE ASI ON OL.M_ATTRIBUTESETINSTANCE_ID = ASI.M_ATTRIBUTESETINSTANCE_ID + WHERE O.DOCSTATUS = 'CO' AND O.ISSOTRX = 'Y' - AND OL.M_PRODUCT_ID = PR.M_PRODUCT_ID - AND OL.C_UOM_ID = UO.C_UOM_ID - AND EXISTS (SELECT 1 - FROM C_ORDER ORD, C_ORDERLINE ORDL - WHERE ORD.C_ORDER_ID = O.C_ORDER_ID - AND ORD.C_ORDER_ID = ORDL.C_ORDER_ID - AND ORDL.QTYORDERED <> ORDL.QTYDELIVERED) AND REFLISTV.AD_REFERENCE_ID = '151' AND REFLISTV.AD_LANGUAGE = ? AND O.AD_CLIENT_ID IN ('1') AND O.AD_ORG_ID IN ('1') + AND EXISTS ( + SELECT 1 + FROM C_ORDERLINE ORDL + WHERE ORDL.C_ORDER_ID = O.C_ORDER_ID + AND ORDL.QTYORDERED <> ORDL.QTYDELIVERED + ) AND 1=1 - GROUP BY ORG.NAME, BP.NAME, O.DOCUMENTNO, O.POREFERENCE, O.DATEORDERED, O.DATEPROMISED, - REFLISTV.NAME, DLOC.NAME, BPADD.NAME, PR.NAME, ASI.DESCRIPTION, OL.M_ATTRIBUTESETINSTANCE_ID, - OL.QTYORDERED, UO.UOMSYMBOL, OL.QTYDELIVERED ORDER BY ORGNAME, BPNAME, DOCUMENTNO]]></Sql> <Parameter name="adLanguage"/> <Parameter name="adUserClient" type="replace" optional="true" after="O.AD_CLIENT_ID IN (" text="'1'"/> diff -r 5c3ae208fb2c -r a68ba952787a src-db/database/model/tables/M_MATCHPO.xml --- a/src-db/database/model/tables/M_MATCHPO.xml Fri Jun 03 13:58:10 2016 +0200 +++ b/src-db/database/model/tables/M_MATCHPO.xml Fri Jun 03 14:56:32 2016 +0200 @@ -87,6 +87,9 @@ <foreign-key foreignTable="M_PRODUCT" name="M_MATCHPO_M_PRODUCT"> <reference local="M_PRODUCT_ID" foreign="M_PRODUCT_ID"/> </foreign-key> + <index name="M_MATCHPO_INOUT" unique="false"> + <index-column name="M_INOUTLINE_ID"/> + </index> <index name="M_MATCHPO_POSTED" unique="false"> <index-column name="POSTED"/> </index> diff -r 5c3ae208fb2c -r a68ba952787a src-db/database/sourcedata/AD_TABLE.xml --- a/src-db/database/sourcedata/AD_TABLE.xml Fri Jun 03 13:58:10 2016 +0200 +++ b/src-db/database/sourcedata/AD_TABLE.xml Fri Jun 03 14:56:32 2016 +0200 @@ -12096,16 +12096,16 @@ iol.shipmentReceipt.businessPartner as businessPartner, iol.shipmentReceipt.documentNo as shipmentNumber, iol.shipmentReceipt.movementDate as movementDate, attributeSetValue as attributeSetValue, (@returnedOthersLeftClause@)*(-1) as returnQtyOtherRM, -(case when (select ('Y') from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) is null then false else true end) as obSelected, +(case when (select ('Y') from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) is null then false else true end) as obSelected, (@returnedLeftClause@)*(-1) as returned, -(select ol.returnReason from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) as returnReason, -coalesce((select ol.tax from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol) , +(select ol.returnReason from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) as returnReason, +coalesce((select ol.tax.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol) , @taxSubQuery@) as tax, (case when @priceIncludeTaxSubQuery@ = true then - coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), + coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), @grossUnitPriceSubQuery@) else - coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), + coalesce((select ol.@unitPriceProperty@ from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), @unitPriceSubQuery@) end) as unitPrice, @orderNoSubQuery@ as orderNo diff -r 5c3ae208fb2c -r a68ba952787a src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java --- a/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java Fri Jun 03 13:58:10 2016 +0200 +++ b/src/org/openbravo/common/datasource/ReturnToFromCustomerVendorHQLTransformer.java Fri Jun 03 14:56:32 2016 +0200 @@ -31,28 +31,28 @@ private static final String returnToVendorTabId = "5A5CCFC8359B4D79BA705DC487FE8173"; - private static final String rtv_unitPriceLeftClause = "(case when (select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol) = true then coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)) else coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)) end)"; - private static final String rtv_orderNoLeftClause = " coalesce ((select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol), '')"; - private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (iol.salesOrderLine.grossUnitPrice)) else coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), (coalesce(iol.salesOrderLine.unitPrice,0))) end)"; + private static final String rtv_unitPriceLeftClause = "(case when (select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol) = true then coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)) else coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)) end)"; + private static final String rtv_orderNoLeftClause = " coalesce ((select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol), '')"; + private static final String rfc_unitPriceLeftClause = "(case when (iol.salesOrderLine.salesOrder.priceList.priceIncludesTax) = true then coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (iol.salesOrderLine.grossUnitPrice)) else coalesce((select ol.unitPrice from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), (coalesce(iol.salesOrderLine.unitPrice,0))) end)"; private static final String rfc_orderNoLeftClause = " coalesce((select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol), '')"; - private static final String returnedLeftClause = " coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol),0)"; - private static final String returnedOthersLeftClause = " coalesce((select sum(ol.orderedQuantity) from OrderLine as ol left join ol.salesOrder as o where ol.goodsShipmentLine = iol and o.processed = true and o.documentStatus <> 'VO'), 0)"; - private static final String returnReasonLeftClause = " coalesce((select ol.returnReason.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), '')"; - private static final String returnReasonLeftClause2 = " coalesce((select ol.returnReason.name from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine = iol), '')"; + private static final String returnedLeftClause = " coalesce((select ol.orderedQuantity from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol),0)"; + private static final String returnedOthersLeftClause = " coalesce((select sum(ol.orderedQuantity) from OrderLine as ol join ol.salesOrder as o where ol.goodsShipmentLine.id = iol and o.processed = true and o.documentStatus <> 'VO'), 0)"; + private static final String returnReasonLeftClause = " coalesce((select ol.returnReason.id from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), '')"; + private static final String returnReasonLeftClause2 = " coalesce((select ol.returnReason.name from OrderLine as ol where ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine.id = iol), '')"; private static final String returnReasonCountQuery = " select count(distinct e.name) from ReturnReason as e where exists (select distinct ol.returnReason from OrderLine as ol where ol.returnReason = e and ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine is not null) "; private static final String returnReasonDataQuery = " select distinct e, e.name from ReturnReason as e where exists (select distinct ol.returnReason from OrderLine as ol where ol.returnReason = e and ol.salesOrder.id = :salesOrderId and ol.goodsShipmentLine is not null) "; private static final String unitPriceProperty = "unitPrice"; private static final String grossUnitPriceProperty = "grossUnitPrice"; - private static final String rtv_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)"; + private static final String rtv_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)"; private static final String rfc_orderNo = "(select e.salesOrderLine.salesOrder.documentNo from MaterialMgmtShipmentInOutLine as e where e.id = iol)"; - private static final String rtv_unitPrice = "(select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)"; + private static final String rtv_unitPrice = "(select e.salesOrderLine.unitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)"; private static final String rfc_unitPrice = "(select e.salesOrderLine.unitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)"; - private static final String rtv_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)"; + private static final String rtv_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)"; private static final String rfc_grossUnitPrice = "(select e.salesOrderLine.grossUnitPrice from MaterialMgmtShipmentInOutLine as e where e.id = iol)"; - private static final String rtv_tax = "(select e.salesOrderLine.tax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)"; - private static final String rfc_tax = "(select e.salesOrderLine.tax from MaterialMgmtShipmentInOutLine as e where e.id = iol)"; - private static final String rtv_priceIncludeTax = "(select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine = iol)"; + private static final String rtv_tax = "(select e.salesOrderLine.tax.id from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)"; + private static final String rfc_tax = "(select e.salesOrderLine.tax.id from MaterialMgmtShipmentInOutLine as e where e.id = iol)"; + private static final String rtv_priceIncludeTax = "(select e.salesOrderLine.salesOrder.priceList.priceIncludesTax from ProcurementPOInvoiceMatch as e where e.goodsShipmentLine.id = iol)"; private static final String rfc_priceIncludeTax = "(select e.priceList.priceIncludesTax from Order as e where e.id = :salesOrderId)"; @Override ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
