Difference between revisions of "VbzCart/queries/qryStock forOpenOrders"
Jump to navigation
Jump to search
(place description -> place name) |
(minor SQL layout improvement, and also a field has gone away) |
||
| (One intermediate revision by the same 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| | + | * '''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''': | * '''History''': | ||
| Line 7: | Line 7: | ||
** '''2009-04-24''' Using place name instead of place 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 ...] | *** 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 21: | Line 23: | ||
CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText, | CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText, | ||
CONCAT_WS(' ',s.BinCode,'in',s.PlcName) AS BinText | CONCAT_WS(' ',s.BinCode,'in',s.PlcName) AS BinText | ||
| − | FROM ( | + | FROM (qryStk_byItem_byBin_wInfo AS s |
| − | WHERE | + | 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 /> | <section end=sql /> | ||
Latest revision as of 20:14, 24 December 2010
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 ...]
- 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>