VbzCart/docs/queries/qryStock forOpenOrders

Details

• Returns: all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
• Requires: Template:Vbzcart/query, Template:Vbzcart/query, Template:Vbzcart/query
• Note: There's a possibility this query will return wrong information when there are multiple stock lines corresponding to the items needed; further testing would be a good idea.

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS SELECT

 s.ID_Bin,
 s.ID_Item,
 oi.ID_Order,
 oi.QtyOpen,
 s.QtyForSale,
 s.QtyForShip,
 o.WhenNeeded,
 o.Descr AS OrdText,
 o.ID_Pull,
 CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,

/* s.BinCode,

 s.BinDescr,  are these ever needed? */
 CONCAT_WS(' ',s.BinCode,s.BinDescr) AS BinText

FROM (v_stk_byItemAndBin_wInfo AS s LEFT JOIN qryOrderLines_notPkgd AS oi ON s.ID_Item=oi.ID_Item) LEFT JOIN qryCbx_Orders AS o ON oi.ID_Order=o.ID WHERE (NOT s.isPulled) AND (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql> <section end=sql />