Difference between revisions of "VbzCart/procs/Upd CatItems fr StkItems StkBins"
Jump to navigation
Jump to search
m (input/output doc) |
(corrected i.QtyInStock) |
||
| Line 3: | Line 3: | ||
* '''Input''': {{vbzcart|table|stk_items}}, {{vbzcart|table|stk_bins}} | * '''Input''': {{vbzcart|table|stk_items}}, {{vbzcart|table|stk_bins}} | ||
* '''Output''': {{vbzcart|table|cat_items}} | * '''Output''': {{vbzcart|table|cat_items}} | ||
| + | * '''History''': | ||
| + | ** '''2011-01-24''' Corrected '''i.QtyInStock''' to '''i.QtyIn_Stk''' | ||
==SQL== | ==SQL== | ||
| − | <section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() | + | <section begin=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 ( | UPDATE cat_items AS i LEFT JOIN ( | ||
SELECT | SELECT | ||
| Line 14: | Line 17: | ||
) AS sig ON i.ID=sig.ID_Item | ) AS sig ON i.ID=sig.ID_Item | ||
SET | SET | ||
| − | i. | + | i.QtyIn_Stk = sig.QtyInStock, |
i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql> | i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 19:06, 24 January 2011
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
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 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>