Woozle: New page: ==Details== * '''Returns''': List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items current...

2008-12-03T21:30:10Z

New page: ==Details== * '''Returns''': List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items current...

New page

==Details==
* '''Returns''': List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items currently in stock.
* '''Requires''': {{vbzcart/query|qryItms_to_restock_union}}
* '''Used by''':
* '''History''':
** '''2008-11-18''' created for new restocking process
** '''2008-11-19''' added additional fields from revised union query
** '''2008-11-22''' various quantities need to be SUMmed, else we got the wrong answer (probably just the first matching row)
* '''Notes''': It might be a good diagnostic to run this query without the final "HAVING" clause (or with QtyToGet<0) and look for anything that doesn't make sense. Hopefully most of the negative numbers are overstocked items and unclosed restock requests (they should be closed).
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock AS
SELECT
ID_Item,
COUNT(ID_Item) AS DupCount,
SUM(iru.QtyOpen) AS QtyOpen,
SUM(iru.QtyForSale) AS QtyForSale,
SUM(iru.QtyMin_Stk) AS QtyMin_Stk,
SUM(iru.QtyOnOrder) AS QtyOnOrder,
SUM(IFNULL(QtyOpen,0))
-SUM(IFNULL(QtyForSale,0))
+SUM(IFNULL(QtyMin_Stk,0))
-SUM(IFNULL(QtyOnOrder,0))
AS QtyToGet
FROM qryItms_to_restock_union AS iru
GROUP BY ID_Item
HAVING QtyToGet>0;</mysql>
<section end=sql />

VbzCart/queries/qryItms to restock - Revision history

Woozle: New page: ==Details== * '''Returns''': List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items current...