VbzCart/procs/Upd CatItems fr StkItems StkBins

About

• Action: updates availability from current stock
• Input: stk_items, stk_bins
• Output: cat_items
• History:
  • 2011-01-24 Corrected i.QtyInStock to i.QtyIn_Stk
  • 2012-02-06 Added "AND (sb.isEnabled)" condition (isEnabled flag is new)

SQL

<mysql>DROP PROCEDURE IF EXISTS `Upd_CatItems_fr_StkItems_StkBins`; CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins()

   UPDATE cat_items AS i LEFT JOIN (
       SELECT
           si.ID_Item,
           SUM(si.Qty) AS QtyInStock
        FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID
        WHERE (sb.isForSale) AND (sb.isEnabled) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved)
        GROUP BY ID_Item
       ) AS sig ON i.ID=sig.ID_Item
     SET
        i.QtyIn_Stk = sig.QtyInStock,
        i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql>