# HG changeset patch
# Parent ef8f777a6d72308cca2768d6633e167f80d7cb03
re 16373 - Reimplement m_product_price_warehouse_v to remove use of stored procedures in select

diff --git a/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml b/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml
--- a/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml
+++ b/src-db/database/model/views/M_PRODUCT_PRICE_WAREHOUSE_V.xml
@@ -1,7 +1,8 @@
 <?xml version="1.0"?>
   <database name="VIEW M_PRODUCT_PRICE_WAREHOUSE_V">
-    <view name="M_PRODUCT_PRICE_WAREHOUSE_V"><![CDATA[SELECT 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 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, pwq.qtyonhand - pwq.qtyreserved AS qty_available, pwq.qtyonhand AS qty_onhand, pwq.qtyreserved AS qty_reserved, pwq.qtyordered 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 --git a/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml b/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml
new file mode 100644
--- /dev/null
+++ b/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml
@@ -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>