VbzCart/docs/queries/qryStock forOpenOrders: Difference between revisions
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...) |
imported>Woozle (using place description instead of bin description) |
||
| Line 3: | Line 3: | ||
* '''Requires''': {{vbzcart/query|v_stk_byItemAndBin_wInfo}}, {{vbzcart/query|qryOrderLines_notPkgd}}, {{vbzcart/query|qryCbx_Orders}} | * '''Requires''': {{vbzcart/query|v_stk_byItemAndBin_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 | |||
==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 18: | ||
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,s. | |||
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:29, 8 February 2009
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
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.PlcDescr) 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> <section end=sql />