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:

1. REDIRECT Template:l/vc/query

• 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

<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>