details: https://code.openbravo.com/erp/devel/pi/rev/381ee761e739 changeset: 35036:381ee761e739 user: Nono Carballo <nonofce <at> gmail.com> date: Fri Nov 16 12:39:03 2018 +0100 summary: Fixes issue 35702: Performance improvements in Purchase Order Report PDF format
1. Last "Group By" was removed 2. Unnecessary coalesces in mandatory columns were removed 3. Remove duplicated columns dateordered and transdate 4. Line net amount and price actual currency conversion are run in outer query 5. Names of business partner, product and uom retrieved via joins 6. The alternativeUom is calculated with a left join details: https://code.openbravo.com/erp/devel/pi/rev/2e096148d8cb changeset: 35037:2e096148d8cb user: Sandra Huguet <sandra.huguet <at> openbravo.com> date: Mon Nov 19 11:03:46 2018 +0100 summary: Related to issue 35702 Remove duplicated columns transdate diffstat: src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml | 84 +++++---- 1 files changed, 47 insertions(+), 37 deletions(-) diffs (99 lines): diff -r b08cf6ab13a4 -r 2e096148d8cb src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml --- a/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml Mon Nov 19 12:28:51 2018 +0100 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportPurchaseOrder.jrxml Mon Nov 19 11:03:46 2018 +0100 @@ -102,48 +102,58 @@ <defaultValueExpression><![CDATA[org.openbravo.materialmgmt.UOMUtil.isUomManagementEnabled()]]></defaultValueExpression> </parameter> <queryString language="SQL"> - <![CDATA[SELECT DOCUMENTNO, DATEORDERED, CLIENT_NAME, SUM(QUANTITYORDER) AS QUANTITYORDER, PRICEACTUAL, CONVPRICEACTUAL, - SUM(PRICELIST) AS PRICELIST, SUM(CONVPRICELIST) AS CONVPRICELIST, PRODUCT_NAME, UOMNAME, + <![CDATA[SELECT DOCUMENTNO, + DATEORDERED, + C_BPARTNER.NAME AS CLIENT_NAME, + QUANTITYORDER, + PRICEACTUAL, + C_CURRENCY_CONVERT(PRICEACTUAL, TRANSCURRENCYID, $P{aux_Currency}, TO_DATE(DATEORDERED), NULL, TRANSCLIENTID, TRANSORGID) AS CONVPRICEACTUAL, + PRICELIST, + C_CURRENCY_CONVERT(PRICELIST, TRANSCURRENCYID, $P{aux_Currency}, TO_DATE(DATEORDERED), NULL, TRANSCLIENTID, TRANSORGID) AS CONVPRICELIST, + M_PRODUCT.NAME AS PRODUCT_NAME, + C_UOM.NAME AS UOMNAME, C_CURRENCY_SYMBOL(TRANSCURRENCYID, '0', 'Y') AS TRANSSYM, C_CURRENCY_SYMBOL($P{aux_Currency}, '0', 'Y') AS CONVSYM, C_CURRENCY_ISOSYM($P{aux_Currency}) AS CONVISOSYM, - ALTERNATIVEQTY AS AUMQTY, ALTERNATIVEUOM AS AUM + ALTERNATIVEQTY AS AUMQTY, + CUOM.NAME AS AUM FROM ( - SELECT C_ORDER.DOCUMENTNO AS DOCUMENTNO, C_ORDER.DATEORDERED AS DATEORDERED, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS CLIENT_NAME, - SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL, - C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{aux_Currency}, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICEACTUAL, - SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST, - C_CURRENCY_CONVERT(SUM(C_ORDERLINE.LINENETAMT), COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), $P{aux_Currency}, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICELIST, - M_PRODUCT.NAME AS PRODUCT_NAME, C_UOM.NAME AS UOMNAME, - COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYID, - TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATE, - C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, - C_ORDERLINE.AD_ORG_ID AS TRANSORGID, - C_ORDERLINE.AUMQTY AS ALTERNATIVEQTY, - (CASE WHEN C_ORDERLINE.C_AUM IS NULL THEN NULL ELSE (SELECT NAME FROM C_UOM WHERE C_UOM_ID = C_ORDERLINE.C_AUM) END ) AS ALTERNATIVEUOM - FROM C_ORDER, C_ORDERLINE, C_BPARTNER, M_PRODUCT, C_UOM - WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID - AND C_ORDERLINE.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID - AND 1=1 - $P!{aux_organization} - $P!{aux_partner} - $P!{aux_warehouse} - $P!{aux_project} - $P!{aux_DateFrom} - $P!{aux_DateTo} - $P!{aux_Status} - AND C_ORDER.ISSOTRX = 'N' - AND C_ORDER.AD_CLIENT_ID = ($P{Current_Client_ID}) - AND C_ORDER.AD_ORG_ID IN ($P!{Readable_Organizations}) - GROUP BY C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME, C_ORDERLINE.PRICEACTUAL, M_PRODUCT.NAME, C_UOM.NAME, C_ORDER.DOCUMENTNO, - C_ORDER.DATEORDERED, C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID, - C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID, C_ORDERLINE.AUMQTY, C_ORDERLINE.C_AUM + SELECT C_ORDER.C_BPARTNER_ID AS BPARTNER_ID, + C_ORDERLINE.M_PRODUCT_ID AS PRODUCT_ID, + C_ORDER.DOCUMENTNO AS DOCUMENTNO, + C_ORDER.DATEORDERED AS DATEORDERED, + C_ORDERLINE.C_UOM_ID AS OLUOMID, + SUM(C_ORDERLINE.QTYORDERED) AS QUANTITYORDER, + C_ORDERLINE.PRICEACTUAL AS PRICEACTUAL, + SUM(C_ORDERLINE.LINENETAMT) AS PRICELIST, + C_ORDERLINE.C_CURRENCY_ID AS TRANSCURRENCYID, + C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTID, + C_ORDERLINE.AD_ORG_ID AS TRANSORGID, + C_ORDERLINE.AUMQTY AS ALTERNATIVEQTY, + C_ORDERLINE.C_AUM + FROM C_ORDER, C_ORDERLINE + WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID + AND 1=1 + $P!{aux_organization} + $P!{aux_partner} + $P!{aux_warehouse} + $P!{aux_project} + $P!{aux_DateFrom} + $P!{aux_DateTo} + $P!{aux_Status} + AND C_ORDER.ISSOTRX = 'N' + AND C_ORDER.AD_CLIENT_ID = ($P{Current_Client_ID}) + AND C_ORDER.AD_ORG_ID IN ($P!{Readable_Organizations}) + GROUP BY C_ORDER.C_BPARTNER_ID, C_ORDERLINE.PRICEACTUAL, C_ORDERLINE.M_PRODUCT_ID, + C_ORDERLINE.C_UOM_ID, C_ORDER.DOCUMENTNO, + C_ORDER.DATEORDERED, C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID, + C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID, C_ORDERLINE.AUMQTY, C_ORDERLINE.C_AUM ) ZZ - GROUP BY CLIENT_NAME, PRICEACTUAL, CONVPRICEACTUAL, PRODUCT_NAME, UOMNAME, DOCUMENTNO, DATEORDERED, - TRANSCURRENCYID, TRANSDATE, ALTERNATIVEQTY, ALTERNATIVEUOM - ORDER BY CLIENT_NAME, DATEORDERED, DOCUMENTNO]]> + INNER JOIN C_BPARTNER ON ZZ.BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID + INNER JOIN M_PRODUCT ON ZZ.PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID + INNER JOIN C_UOM ON ZZ.OLUOMID = C_UOM.C_UOM_ID + LEFT JOIN C_UOM CUOM ON ZZ.C_AUM = CUOM.C_UOM_ID + ORDER BY CLIENT_NAME, DATEORDERED, DOCUMENTNO, PRODUCT_NAME]]> </queryString> <field name="DOCUMENTNO" class="java.lang.String"/> <field name="DATEORDERED" class="java.util.Date"/> _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
