VbzCart/docs/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: Template:Vbzcart/query, Template:Vbzcart/query, Template:Vbzcart/table, Template:Vbzcart/query
• Used by: Template:Vbzcart/query
• 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: Template:Vbzcart/query → Template:Vbzcart/query

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_expected;</mysql> <section end=sql />