details: https://code.openbravo.com/erp/devel/pi/rev/c05369b23139 changeset: 33230:c05369b23139 user: David Miguelez <david.miguelez <at> openbravo.com> date: Mon Jan 29 11:38:22 2018 +0100 summary: Fixes Issue 37742. Fixes performance in Return Material Receipt P&E
In view m_rm_receipt_pick_edit a new column has been added with the Business Partner from the Order. This new column is used in the filter of the Return Material P&E Tab. By doing so, additional cross joins with OrderLine and Order Tables are avoided. Also, the planner in Postgres is able to properly use the Business Partner index. diffstat: src-db/database/model/views/M_RM_RECEIPT_PICK_EDIT.xml | 2 +- src-db/database/sourcedata/AD_COLUMN.xml | 41 +++++++++++++++++- src-db/database/sourcedata/AD_TAB.xml | 2 +- 3 files changed, 42 insertions(+), 3 deletions(-) diffs (79 lines): diff -r 83aa5e45d4c8 -r c05369b23139 src-db/database/model/views/M_RM_RECEIPT_PICK_EDIT.xml --- a/src-db/database/model/views/M_RM_RECEIPT_PICK_EDIT.xml Sat Dec 16 18:40:56 2017 +0100 +++ b/src-db/database/model/views/M_RM_RECEIPT_PICK_EDIT.xml Mon Jan 29 11:38:22 2018 +0100 @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW M_RM_RECEIPT_PICK_EDIT"> - <view name="M_RM_RECEIPT_PICK_EDIT"><![CDATA[SELECT ol.c_orderline_id AS m_rm_receipt_pick_edit_id, ol.ad_client_id, COALESCE(iol.ad_org_id, ol.ad_org_id) AS ad_org_id, ol.isactive, ol.created, ol.createdby, COALESCE(iol.updated, ol.updated) AS updated, ol.updatedby, iol.m_inout_id, CASE WHEN iol.m_inoutline_id IS NOT NULL THEN 'Y' ELSE 'N' END AS ob_selected, ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, ol.m_attributesetinstance_id, (-1) * ol.qtyordered AS returned, (-1) * CASE WHEN uom_pref.property IS NOT NULL AND ol.c_uom_id IS NOT NULL THEN m_get_converted_aumqty(ol.m_product_id, ol.qtyordered - ol.qtydelivered, ol.c_aum) ELSE ol.qtyordered - ol.qtydelivered END AS pendingqty, (-1) * COALESCE(iol.aumqty, iol.movementqty) AS receivingqty, iol.m_condition_goods_id, iol.m_locator_id, o.documentno AS returnorderno, o.issotrx, ol.line, iol.m_inoutline_id, ol.c_aum, (-1) * ol.aumqty AS aumqty, (-1) * CASE WHEN uom_pref.property IS NOT NULL AND ol.c_uom_id IS NOT NULL THEN ol.qtyordered - ol.qtydelivered ELSE m_get_converted_aumqty(ol.m_product_id, ol.qtyordered - ol.qtydelivered, ol.c_aum) END AS pendingaumqty, CASE WHEN iol.m_inoutline_id IS NULL THEN COALESCE(ol.c_aum, ol.c_uom_id) ELSE COALESCE(iol.c_aum, iol.c_uom_id) END AS returneduom FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id JOIN m_product p ON ol.m_product_id = p.m_product_id JOIN c_doctype dt ON dt.c_doctype_id = o.c_doctypetarget_id AND dt.isreturn = 'Y' LEFT JOIN (SELECT il2.m_inoutline_id, il2.ad_client_id, il2.ad_org_id, il2.isactive, il2.created, il2.createdby, il2.updated, il2.updatedby, il2.line, il2.description, il2.m_inout_id, il2.c_orderline_id, il2.m_locator_id, il2.m_product_id, il2.c_uom_id, il2.movementqty, il2.isinvoiced, il2.m_attributesetinstance_id, il2.isdescription, il2.quantityorder, il2.m_product_uom_id, il2.m_condition_goods_id, il2.c_aum, il2.aumqty FROM m_inoutline il2 JOIN m_inout i2 ON il2.m_inout_id = i2.m_inout_id AND i2.processed = 'N') iol ON iol.c_orderline_id = ol.c_orderline_id LEFT JOIN (SELECT pref.property FROM ad_preference pref WHERE pref.property = 'UomManagement' AND to_char(pref.value) = 'Y' AND pref.ad_client_id = '0' AND pref.ad_org_id = '0' AND pref.visibleat_client_id IS NULL AND pref.visibleat_org_id IS NULL AND pref.visibleat_role_id IS NULL AND pref.ad_user_id IS NULL) uom_pref ON 1 = 1 WHERE (ol.qtyordered - ol.qtydelivered) <> 0 AND o.docstatus = 'CO']]></view> + <view name="M_RM_RECEIPT_PICK_EDIT"><![CDATA[SELECT ol.c_orderline_id AS m_rm_receipt_pick_edit_id, ol.ad_client_id, COALESCE(iol.ad_org_id, ol.ad_org_id) AS ad_org_id, ol.isactive, ol.created, ol.createdby, COALESCE(iol.updated, ol.updated) AS updated, ol.updatedby, iol.m_inout_id, CASE WHEN iol.m_inoutline_id IS NOT NULL THEN 'Y' ELSE 'N' END AS ob_selected, ol.c_orderline_id, ol.m_product_id, ol.c_uom_id, ol.m_attributesetinstance_id, (-1) * ol.qtyordered AS returned, (-1) * CASE WHEN uom_pref.property IS NOT NULL AND ol.c_uom_id IS NOT NULL THEN m_get_converted_aumqty(ol.m_product_id, ol.qtyordered - ol.qtydelivered, ol.c_aum) ELSE ol.qtyordered - ol.qtydelivered END AS pendingqty, (-1) * COALESCE(iol.aumqty, iol.movementqty) AS receivingqty, iol.m_condition_goods_id, iol.m_locator_id, o.documentno AS returnorderno, o.issotrx, ol.line, iol.m_inoutline_id, ol.c_aum, (-1) * ol.aumqty AS aumqty, (-1) * CASE WHEN uom_pref.property IS NOT NULL AND ol.c_uom_id IS NOT NULL THEN ol.qtyordered - ol.qtydelivered ELSE m_get_converted_aumqty(ol.m_product_id, ol.qtyordered - ol.qtydelivered, ol.c_aum) END AS pendingaumqty, CASE WHEN iol.m_inoutline_id IS NULL THEN COALESCE(ol.c_aum, ol.c_uom_id) ELSE COALESCE(iol.c_aum, iol.c_uom_id) END AS returneduom, o.c_bpartner_id FROM c_orderline ol JOIN c_order o ON o.c_order_id = ol.c_order_id JOIN m_product p ON ol.m_product_id = p.m_product_id JOIN c_doctype dt ON dt.c_doctype_id = o.c_doctypetarget_id AND dt.isreturn = 'Y' LEFT JOIN (SELECT il2.m_inoutline_id, il2.ad_client_id, il2.ad_org_id, il2.isactive, il2.created, il2.createdby, il2.updated, il2.updatedby, il2.line, il2.description, il2.m_inout_id, il2.c_orderline_id, il2.m_locator_id, il2.m_product_id, il2.c_uom_id, il2.movementqty, il2.isinvoiced, il2.m_attributesetinstance_id, il2.isdescription, il2.quantityorder, il2.m_product_uom_id, il2.m_condition_goods_id, il2.c_aum, il2.aumqty FROM m_inoutline il2 JOIN m_inout i2 ON il2.m_inout_id = i2.m_inout_id AND i2.processed = 'N') iol ON iol.c_orderline_id = ol.c_orderline_id LEFT JOIN (SELECT pref.property FROM ad_preference pref WHERE pref.property = 'UomManagement' AND to_char(pref.value) = 'Y' AND pref.ad_client_id = '0' AND pref.ad_org_id = '0' AND pref.visibleat_client_id IS NULL AND pref.visibleat_org_id IS NULL AND pref.visibleat_role_id IS NULL AND pref.ad_user_id IS NULL) uom_pref ON 1 = 1 WHERE (ol.qtyordered - ol.qtydelivered) <> 0 AND o.docstatus = 'CO']]></view> </database> diff -r 83aa5e45d4c8 -r c05369b23139 src-db/database/sourcedata/AD_COLUMN.xml --- a/src-db/database/sourcedata/AD_COLUMN.xml Sat Dec 16 18:40:56 2017 +0100 +++ b/src-db/database/sourcedata/AD_COLUMN.xml Mon Jan 29 11:38:22 2018 +0100 @@ -256870,6 +256870,45 @@ <!--2CA46C14D66F4B7AA6550531F6D329E7--> <ALLOWED_CROSS_ORG_LINK><![CDATA[N]]></ALLOWED_CROSS_ORG_LINK> <!--2CA46C14D66F4B7AA6550531F6D329E7--></AD_COLUMN> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--><AD_COLUMN> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_COLUMN_ID><![CDATA[2CB6F5E1AE1543B5B7F740A0D6D918D4]]></AD_COLUMN_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <NAME><![CDATA[Business Partner]]></NAME> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <DESCRIPTION><![CDATA[Anyone who takes part in daily business operations by acting as a customer, employee, etc.]]></DESCRIPTION> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <HELP><![CDATA[A Business Partner is anyone with whom you transact. This can include a customer, vendor, employee or any combination of these.]]></HELP> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <COLUMNNAME><![CDATA[C_Bpartner_ID]]></COLUMNNAME> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_TABLE_ID><![CDATA[09C5EA393D0D41CEBB522DEC67534574]]></AD_TABLE_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_REFERENCE_ID><![CDATA[30]]></AD_REFERENCE_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_REFERENCE_VALUE_ID><![CDATA[800057]]></AD_REFERENCE_VALUE_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <FIELDLENGTH><![CDATA[32]]></FIELDLENGTH> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISKEY><![CDATA[N]]></ISKEY> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISPARENT><![CDATA[N]]></ISPARENT> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISMANDATORY><![CDATA[N]]></ISMANDATORY> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISUPDATEABLE><![CDATA[Y]]></ISUPDATEABLE> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISIDENTIFIER><![CDATA[N]]></ISIDENTIFIER> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <SEQNO><![CDATA[320]]></SEQNO> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISTRANSLATED><![CDATA[N]]></ISTRANSLATED> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISENCRYPTED><![CDATA[N]]></ISENCRYPTED> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISSELECTIONCOLUMN><![CDATA[N]]></ISSELECTIONCOLUMN> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_ELEMENT_ID><![CDATA[187]]></AD_ELEMENT_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISSESSIONATTR><![CDATA[N]]></ISSESSIONATTR> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISSECONDARYKEY><![CDATA[N]]></ISSECONDARYKEY> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <POSITION><![CDATA[28]]></POSITION> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISAUTOSAVE><![CDATA[Y]]></ISAUTOSAVE> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <IMAGESIZEVALUESACTION><![CDATA[N]]></IMAGESIZEVALUESACTION> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ISUSEDSEQUENCE><![CDATA[N]]></ISUSEDSEQUENCE> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ALLOWSORTING><![CDATA[Y]]></ALLOWSORTING> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ALLOWFILTERING><![CDATA[Y]]></ALLOWFILTERING> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--> <ALLOWED_CROSS_ORG_LINK><![CDATA[N]]></ALLOWED_CROSS_ORG_LINK> +<!--2CB6F5E1AE1543B5B7F740A0D6D918D4--></AD_COLUMN> + <!--2CC98C761A3F460BB9D88F4E43DD3588--><AD_COLUMN> <!--2CC98C761A3F460BB9D88F4E43DD3588--> <AD_COLUMN_ID><![CDATA[2CC98C761A3F460BB9D88F4E43DD3588]]></AD_COLUMN_ID> <!--2CC98C761A3F460BB9D88F4E43DD3588--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -261051,7 +261090,7 @@ <!--3AE052A78717426CBCB4F828CC76A87E--> <ISDESENCRYPTABLE><![CDATA[N]]></ISDESENCRYPTABLE> <!--3AE052A78717426CBCB4F828CC76A87E--> <DEVELOPMENTSTATUS><![CDATA[RE]]></DEVELOPMENTSTATUS> <!--3AE052A78717426CBCB4F828CC76A87E--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> -<!--3AE052A78717426CBCB4F828CC76A87E--> <POSITION><![CDATA[28]]></POSITION> +<!--3AE052A78717426CBCB4F828CC76A87E--> <POSITION><![CDATA[27]]></POSITION> <!--3AE052A78717426CBCB4F828CC76A87E--> <ISTRANSIENT><![CDATA[N]]></ISTRANSIENT> <!--3AE052A78717426CBCB4F828CC76A87E--> <ISAUTOSAVE><![CDATA[Y]]></ISAUTOSAVE> <!--3AE052A78717426CBCB4F828CC76A87E--> <VALIDATEONNEW><![CDATA[Y]]></VALIDATEONNEW> diff -r 83aa5e45d4c8 -r c05369b23139 src-db/database/sourcedata/AD_TAB.xml --- a/src-db/database/sourcedata/AD_TAB.xml Sat Dec 16 18:40:56 2017 +0100 +++ b/src-db/database/sourcedata/AD_TAB.xml Mon Jan 29 11:38:22 2018 +0100 @@ -20229,7 +20229,7 @@ <!--FEFB495F59894AD6A1163D13ABE88833--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> <!--FEFB495F59894AD6A1163D13ABE88833--> <UIPATTERN><![CDATA[STD]]></UIPATTERN> <!--FEFB495F59894AD6A1163D13ABE88833--> <HQLWHERECLAUSE><![CDATA[COALESCE(e.goodsShipment.id, @MaterialMgmtShipmentInOut.id@) = @MaterialMgmtShipmentInOut.id@ -AND @MaterialMgmtShipmentInOut.businessPartner@ = e.orderLine.salesOrder.businessPartner.id]]></HQLWHERECLAUSE> +AND @MaterialMgmtShipmentInOut.businessPartner@ = e.businessPartner.id]]></HQLWHERECLAUSE> <!--FEFB495F59894AD6A1163D13ABE88833--> <HQLORDERBYCLAUSE><![CDATA[obSelected DESC, rMOrderNo, lineNo]]></HQLORDERBYCLAUSE> <!--FEFB495F59894AD6A1163D13ABE88833--> <SHOWPARENTBUTTONS><![CDATA[Y]]></SHOWPARENTBUTTONS> <!--FEFB495F59894AD6A1163D13ABE88833--> <DISABLE_PARENT_KEY_PROPERTY><![CDATA[N]]></DISABLE_PARENT_KEY_PROPERTY> ------------------------------------------------------------------------------ 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
