VbzCart/queries/qryItms to restock

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:
 * 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
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;