Difference between revisions of "VbzCart/queries/qryStock forOpenOrders"
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. | + | 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:
- 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>