VbzCart/queries/qryStock forOpenOrders

Details

• Returns: all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
• Requires:

1. REDIRECT Template:l/vc/query, qryOrderLines_notPkgd, qryCbx_Orders

• 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.
• History:
  • 2009-02-07 Using place description instead of bin description
  • 2009-04-24 Using place name instead of place description
    • ultimately, should probably calculate a more complete description from name hierarchy - place @ parent [ @ parent ...]
  • 2010-02-23 v_stk_byItemAndBin_wInfo was renamed to qryStk_byItem_byBin_wInfo
  • 2010-12-24 s.isPulled doesn't exist anymore; not sure why it was ever included in the query stream, as any records where it was TRUE should have been filtered out at qryCat_Items (used by qryStk_byItem_byBin_wInfo)

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,
 CONCAT_WS(' ',s.BinCode,'in',s.PlcName) AS BinText

FROM (qryStk_byItem_byBin_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 (o.ID_Pull IS NULL) AND (oi.QtyOpen) AND (s.QtyForShip>0);</mysql>