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
< VbzCart‎ | queries
Revision as of 21:28, 3 December 2008 by Woozle (talk | contribs) (New page: ==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 ...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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_en_route
  • 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

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_en_route;</mysql>