VbzCart/queries/qryItms to restock

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryItms_to_restock&oldid=10920"