details: https://code.openbravo.com/erp/devel/pi/rev/66b7801263ab changeset: 25773:66b7801263ab user: Atul Gaware <atul.gaware <at> openbravo.com> date: Thu Jan 22 23:38:18 2015 +0530 summary: Fixes Issue 25421:Stock availability not updated when a product has no previous stock transaction
If product does not have records in m_storage_detail then they are skipped in the view as m_storage_pending come after left join. A solution implements a union of m_storage_detail and m_storage_pending and grouping is done for qtyonhand, qtyordered, qtyreserved based on m_product_id and m_warehouse_id diffstat: src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml | 2 +- 1 files changed, 1 insertions(+), 1 deletions(-) diffs (9 lines): diff -r 5ef45cfcb9a5 -r 66b7801263ab src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml --- a/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml Thu Jan 29 17:22:12 2015 +0100 +++ b/src-db/database/model/views/M_PRODUCT_WAREHOUSE_QTYS_V.xml Thu Jan 22 23:38:18 2015 +0530 @@ -1,4 +1,4 @@ <?xml version="1.0"?> <database name="VIEW M_PRODUCT_WAREHOUSE_QTYS_V"> - <view name="M_PRODUCT_WAREHOUSE_QTYS_V"><![CDATA[SELECT sd.m_product_id, sd.m_warehouse_id, sd.qtyonhand, COALESCE(s.qtyordered, 0) AS qtyordered, COALESCE(s.qtyreserved, 0) AS qtyreserved FROM (SELECT sdd.m_product_id, l.m_warehouse_id, sum(sdd.qtyonhand) AS qtyonhand FROM m_storage_detail sdd JOIN m_locator l ON sdd.m_locator_id = l.m_locator_id GROUP BY sdd.m_product_id, l.m_warehouse_id) sd LEFT JOIN (SELECT sp.m_product_id, sp.m_warehouse_id, sum(sp.qtyordered) AS qtyordered, sum(sp.qtyreserved) AS qtyreserved FROM m_storage_pending sp GROUP BY sp.m_product_id, sp.m_warehouse_id) s ON sd.m_product_id = s.m_product_id AND sd.m_warehouse_id = s.m_warehouse_id]]></view> + <view name="M_PRODUCT_WAREHOUSE_QTYS_V"><![CDATA[SELECT sd.m_product_id, sd.m_warehouse_id, sum(sd.qtyonhand) AS qtyonhand, sum(sd.qtyordered) AS qtyordered, sum(sd.qtyreserved) AS qtyreserved FROM (SELECT sdd.m_product_id, l.m_warehouse_id, sum(sdd.qtyonhand) AS qtyonhand, 0 AS qtyordered, 0 AS qtyreserved FROM m_storage_detail sdd JOIN m_locator l ON sdd.m_locator_id = l.m_locator_id GROUP BY sdd.m_product_id, l.m_warehouse_id UNION SELECT sp.m_product_id, sp.m_warehouse_id, 0 AS qtyonhand, sum(sp.qtyordered) AS qtyordered, sum(sp.qtyreserved) AS qtyreserved FROM m_storage_pending sp GROUP BY sp.m_product_id, sp.m_warehouse_id) sd GROUP BY sd.m_product_id, sd.m_warehouse_id]]></view> </database> ------------------------------------------------------------------------------ Dive into the World of Parallel Programming. The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
