VbzCart/queries/qryStock forOpenOrders - Revision history

Woozle: minor SQL layout improvement, and also a field has gone away

2010-12-24T20:14:12Z

minor SQL layout improvement, and also a field has gone away

← Older revision		Revision as of 20:14, 24 December 2010
Line 8:		Line 8:
	*** 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-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 22:		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 (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		FROM (qryStk_byItem_byBin_wInfo AS s
	WHERE ~~(NOT s.isPulled) AND~~ (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql>		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 />

Woozle: naming update

2010-02-23T13:49:01Z

naming update

← Older revision		Revision as of 13:49, 23 February 2010
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''':		* '''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
	==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 22:
	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 (~~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 (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 (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 />

Woozle: place description -> place name

2009-04-25T02:19:14Z

place description -> place name

← Older revision		Revision as of 02:19, 25 April 2009
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 />

Woozle: using place description instead of bin description

2009-02-08T02:29:26Z

using place description instead of bin description

← Older revision		Revision as of 02:29, 8 February 2009
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,
	~~/* s.BinCode,~~		CONCAT_WS(' ',s.BinCode,'in',s.PlcDescr) AS BinText
	~~s.BinDescr, are these ever needed? */~~
	CONCAT_WS(' ',s.BinCode,s.~~BinDescr~~) 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 />

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

2008-12-04T02:36:43Z

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

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_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.
==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,
/* s.BinCode,
s.BinDescr, are these ever needed? */
CONCAT_WS(' ',s.BinCode,s.BinDescr) 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 />