details: /erp/devel/pi/rev/a3d93e831e8a changeset: 11668:a3d93e831e8a user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Tue Apr 26 16:54:48 2011 +0200 summary: Fixed issue 16373.Modified view to avoid usage of pl functions.
diffstat: src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml | 25 ++++++++++-- src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml | 11 +++++ 2 files changed, 32 insertions(+), 4 deletions(-) diffs (47 lines): diff -r 539fccd1b7a4 -r a3d93e831e8a src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml --- a/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml Tue Apr 26 16:56:41 2011 +0200 +++ b/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml Tue Apr 26 16:54:48 2011 +0200 @@ -1,7 +1,24 @@ <?xml version="1.0"?> <database name="VIEW M_PRODUCT_PRICE_WAREHOUSE_V"> - <view name="M_PRODUCT_PRICE_WAREHOUSE_V"><![CDATA[SELECT COALESCE(w.m_warehouse_id, '-') || pp.m_productprice_id AS m_product_price_warehouse_v_id, p.ad_client_id, p.ad_org_id, p.isactive, p.updated, p.updatedby, p.created, p.createdby, p.m_product_id, w.m_warehouse_id, pp.m_productprice_id, m_bom_qty_available(p.m_product_id, w.m_warehouse_id, NULL) AS qty_available, m_bom_qty_onhand(p.m_product_id, w.m_warehouse_id, NULL) AS qty_onhand, m_bom_qty_reserved(p.m_product_id, w.m_warehouse_id, NULL) AS qty_reserved, m_bom_qty_ordered(p.m_product_id, w.m_warehouse_id, NULL) AS qty_ordered, m_bom_pricelist(p.m_product_id, pp.m_pricelist_version_id) AS pricelist, m_bom_pricestd(p.m_product_id, pp.m_pricelist_version_id) AS pricestd, m_bom_pricelimit(p.m_product_id, pp.m_pricelist_version_id) AS pricelimit -FROM m_product p -JOIN m_productprice pp ON p.m_product_id = pp.m_product_id -LEFT JOIN m_warehouse w ON p.ad_client_id = w.ad_client_id]]></view> + <view name="M_PRODUCT_PRICE_WAREHOUSE_V"><![CDATA[SELECT COALESCE(w.m_warehouse_id, '-') || pp.m_productprice_id AS m_product_price_warehouse_v_id, p.ad_client_id, p.ad_org_id, p.isactive, p.updated, p.updatedby, p.created, p.createdby, p.m_product_id, w.m_warehouse_id, pp.m_productprice_id, +CASE +WHEN p.producttype <> 'I' OR p.isstocked = 'N' THEN 99999 +ELSE COALESCE(pwq.qtyonhand - pwq.qtyreserved, 0) +END AS qty_available, +CASE +WHEN p.producttype <> 'I' OR p.isstocked = 'N' THEN 99999 +ELSE COALESCE(pwq.qtyonhand, 0) +END AS qty_onhand, +CASE +WHEN p.producttype <> 'I' OR p.isstocked = 'N' THEN 0 +ELSE COALESCE(pwq.qtyreserved, 0) +END AS qty_reserved, +CASE +WHEN p.producttype <> 'I' OR p.isstocked = 'N' THEN 0 +ELSE COALESCE(pwq.qtyordered, 0) +END AS qty_ordered, pp.pricelist, pp.pricestd, pp.pricelimit +FROM m_productprice pp +LEFT JOIN m_warehouse w ON pp.ad_client_id = w.ad_client_id +LEFT JOIN m_product p ON p.m_product_id = pp.m_product_id +LEFT JOIN m_product_warehouse_qtys_v pwq ON pwq.m_product_id = pp.m_product_id AND pwq.m_warehouse_id = w.m_warehouse_id]]></view> </database> diff -r 539fccd1b7a4 -r a3d93e831e8a src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml Tue Apr 26 16:54:48 2011 +0200 @@ -0,0 +1,11 @@ +<?xml version="1.0"?> + <database name="VIEW M_PRODUCT_WAREHOUSE_QTYS_V"> + <view name="M_PRODUCT_WAREHOUSE_QTYS_V"><![CDATA[SELECT s.m_product_id, s.m_warehouse_id, sum(s.qtyonhand) AS qtyonhand, sum(s.qtyordered) AS qtyordered, sum(s.qtyreserved) AS qtyreserved +FROM (SELECT sd.m_product_id, l.m_warehouse_id, sd.qtyonhand, 0 AS qtyordered, 0 AS qtyreserved +FROM m_storage_detail sd +JOIN m_locator l ON sd.m_locator_id = l.m_locator_id +UNION +SELECT sp.m_product_id, sp.m_warehouse_id, 0 AS qtyonhand, sp.qtyordered, sp.qtyreserved +FROM m_storage_pending sp) s +GROUP BY s.m_product_id, s.m_warehouse_id]]></view> + </database> ------------------------------------------------------------------------------ WhatsUp Gold - Download Free Network Management Software The most intuitive, comprehensive, and cost-effective network management toolset available today. Delivers lowest initial acquisition cost and overall TCO of any competing solution. http://p.sf.net/sfu/whatsupgold-sd _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
