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
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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"