Difference between revisions of "VbzCart/queries/qryStock forOpenOrders"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(using place description instead of bin description)
(place description -> place name)
Line 5: Line 5:
 
* '''History''':
 
* '''History''':
 
** '''2009-02-07''' Using place description instead of bin description
 
** '''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 ...]
 
==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 18: Line 20:
 
   o.ID_Pull,
 
   o.ID_Pull,
 
   CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
 
   CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
   CONCAT_WS(' ',s.BinCode,'in',s.PlcDescr) AS BinText
+
   CONCAT_WS(' ',s.BinCode,'in',s.PlcName) 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
 
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>
 
WHERE (NOT s.isPulled) AND (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql>
 
<section end=sql />
 
<section end=sql />

Revision as of 02:19, 25 April 2009

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 ...]

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 (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>