details: https://code.openbravo.com/erp/devel/pi/rev/c2133a701c72 changeset: 14020:c2133a701c72 user: Javier Etxarri <javier.echarri <at> openbravo.com> date: Tue Oct 18 17:31:40 2011 +0200 summary: Fixes issue 18464: Not possible to create a goods receipt for a product that is not stocked. The problem was that the previos query was working correctly but it has a performance problem because the UNION sql statement spent much time sorting and trying to find some duplicate rows. So I change UNION to UNION ALL is becasue both queries return different rows every time. The column stocked is going to be different.
diffstat: src-db/database/model/views/M_PRODUCT_STOCK_V.xml | 21 +++++++++------------ 1 files changed, 9 insertions(+), 12 deletions(-) diffs (31 lines): diff -r b85624412f8d -r c2133a701c72 src-db/database/model/views/M_PRODUCT_STOCK_V.xml --- a/src-db/database/model/views/M_PRODUCT_STOCK_V.xml Tue Oct 18 17:00:33 2011 +0200 +++ b/src-db/database/model/views/M_PRODUCT_STOCK_V.xml Tue Oct 18 17:31:40 2011 +0200 @@ -1,18 +1,15 @@ <?xml version="1.0"?> <database name="VIEW M_PRODUCT_STOCK_V"> - <view name="M_PRODUCT_STOCK_V"><![CDATA[SELECT p.m_product_id || COALESCE(sd.m_storage_detail_id, '') AS m_product_stock_v_id, p.ad_client_id, COALESCE(sd.ad_org_id, p.ad_org_id) AS ad_org_id, COALESCE(sd.isactive, p.isactive) AS isactive, p.created, p.createdby, p.updated, p.updatedby, p.m_product_id, COALESCE(sd.qtyonhand, 0) AS qtyonhand, sd.qtyorderonhand, COALESCE(sd.preqtyonhand, 0) AS preqtyonhand, sd.preqtyorderonhand, p.c_uom_id, sd.m_product_uom_id, COALESCE(sd.m_attributesetinstance_id, + <view name="M_PRODUCT_STOCK_V"><![CDATA[SELECT p.m_product_id || sd.m_storage_detail_id AS m_product_stock_v_id, l.ad_client_id, l.ad_org_id, l.isactive, sd.created, sd.createdby, sd.updated, sd.updatedby, sd.m_product_id, sd.qtyonhand, sd.qtyorderonhand, sd.preqtyonhand, sd.preqtyorderonhand, sd.c_uom_id, sd.m_product_uom_id, sd.m_attributesetinstance_id, sd.m_locator_id, 'Y' AS stocked +FROM m_storage_detail sd +JOIN m_locator l ON sd.m_locator_id = l.m_locator_id +JOIN m_product p ON sd.m_product_id = p.m_product_id AND p.isactive = 'Y' +WHERE sd.qtyonhand <> 0 OR COALESCE(sd.qtyorderonhand, 0) <> 0 +UNION ALL +SELECT p.m_product_id AS m_product_stock_v_id, p.ad_client_id, p.ad_org_id, p.isactive, p.created, p.createdby, p.updated, p.updatedby, p.m_product_id, 0 AS qtyonhand, NULL AS qtyorderonhand, 0 AS preqtyonhand, NULL AS preqtyorderonhand, p.c_uom_id, NULL AS m_product_uom_id, CASE p.attrsetvaluetype WHEN 'D' THEN p.m_attributesetinstance_id ELSE NULL -END) AS m_attributesetinstance_id, sd.m_locator_id, -CASE -WHEN sd.m_storage_detail_id IS NOT NULL THEN 'Y' -ELSE 'N' -END AS stocked -FROM m_product p -LEFT JOIN (SELECT p.m_product_id || sdd.m_storage_detail_id AS m_product_stock_v_id, sdd.m_storage_detail_id, l.ad_client_id, l.ad_org_id, l.isactive, sdd.created, sdd.createdby, sdd.updated, sdd.updatedby, sdd.m_product_id, sdd.qtyonhand, sdd.qtyorderonhand, sdd.preqtyonhand, sdd.preqtyorderonhand, sdd.c_uom_id, sdd.m_product_uom_id, sdd.m_attributesetinstance_id, sdd.m_locator_id, 'Y' AS stocked -FROM m_storage_detail sdd -JOIN m_locator l ON sdd.m_locator_id = l.m_locator_id -JOIN m_product p ON sdd.m_product_id = p.m_product_id AND p.isactive = 'Y' -WHERE sdd.qtyonhand <> 0 OR COALESCE(sdd.qtyorderonhand, 0) <> 0) sd ON p.m_product_id = sd.m_product_id]]></view> +END AS m_attributesetinstance_id, NULL AS m_locator_id, 'N' AS stocked +FROM m_product p]]></view> </database> ------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2d-oct _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
