VbzCart/docs/queries/qryStock forOpenOrders: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(New page: ==Details== * '''Returns''': all stock available to fill open orders, along with helpful information about the Bin, Order, and Item * '''Requires''': {{vbzcart/query|v_stk_byItemAndBin_wIn...)
 
m (Woozle moved page VbzCart/VbzCart/queries/qryStock forOpenOrders to VbzCart/docs/queries/qryStock forOpenOrders without leaving a redirect: part 3/5)
 
(4 intermediate revisions by one other user not shown)
Line 1: Line 1:
==Details==
==Details==
* '''Returns''': all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
* '''Returns''': all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
* '''Requires''': {{vbzcart/query|v_stk_byItemAndBin_wInfo}}, {{vbzcart/query|qryOrderLines_notPkgd}}, {{vbzcart/query|qryCbx_Orders}}
* '''Requires''': {{vbzcart/query|qryStk_byItem_byBin_wInfo}}, {{vbzcart/query|qryOrderLines_notPkgd}}, {{vbzcart/query|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.
* '''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 {{vbzcart/query|qryCat_Items}} (used by {{vbzcart/query|qryStk_byItem_byBin_wInfo}})
==SQL==
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS
Line 16: Line 22:
   o.ID_Pull,
   o.ID_Pull,
   CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
   CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
/*  s.BinCode,
   CONCAT_WS(' ',s.BinCode,'in',s.PlcName) AS BinText
  s.BinDescr,  are these ever needed? */
FROM (qryStk_byItem_byBin_wInfo AS s
   CONCAT_WS(' ',s.BinCode,s.BinDescr) AS BinText
  LEFT JOIN qryOrderLines_notPkgd AS oi ON s.ID_Item=oi.ID_Item)
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
  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>
WHERE (o.ID_Pull IS NULL) AND (oi.QtyOpen) AND (s.QtyForShip>0);</mysql>
<section end=sql />
<section end=sql />

Latest revision as of 01:56, 25 February 2024

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.
  • 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 Template:Vbzcart/query (used by Template:Vbzcart/query)

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

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryStock_forOpenOrders&oldid=6825"