VbzCart/queries/qryItms to restock union

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: qryItms_open, qryStkItms_for_sale, cat_items, 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

<section begin=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> <section end=sql />