VbzCart/queries/qryItms to restock union

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 all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in #qryItms_to_restock)
  • Requires:
  1. REDIRECT Template:l/vc/query, qryStkItms_for_sale,
  2. REDIRECT Template:l/vc/table, qryRstkItms_expected
  • Used by: qryItms_to_restock
  • History:
    • 2008-11-18 created for new restocking process (under construction; need to add items-in-transit for restocks)
    • 2008-11-22 in-transit items should never be negative; if we receive something not requested, it goes into stock
    • 2008-12-05 Updated source query name: qryRstkItms_en_route → qryRstkItms_expected

SQL

<mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS SELECT 

 ID_Item,
 QtyOpen,
 NULL AS QtyForSale,
 NULL AS QtyMin_Stk,
 NULL AS QtyOnOrder

FROM qryItms_open UNION SELECT 

 ID_Item,
 NULL AS QtyOpen,
 QtyForSale,
 NULL AS QtyMin_Stk,
 NULL AS QtyOnOrder

FROM qryStkItms_for_sale UNION SELECT 

 ID AS ID_Item,
 NULL AS QtyOpen,
 NULL AS QtyForSale,
 QtyMin_Stk,
 NULL AS QtyOnOrder

FROM cat_items WHERE QtyMin_Stk>0 UNION SELECT 

 ID_Item,
 NULL AS QtyOpen,
 NULL AS QtyForSale,
 NULL AS QtyMin_Stk,
 IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder

FROM qryRstkItms_expected;</mysql>

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