VbzCart/queries/qryStock containers - Revision history

Woozle: codes

2009-12-21T02:25:20Z

codes

← Older revision		Revision as of 02:25, 21 December 2009
Line 3:		Line 3:
	* '''History''':		* '''History''':
	** '''2008-12-27''' Adapting from stored procedure which didn't include packages or restocks because they hadn't been migrated yet		** '''2008-12-27''' Adapting from stored procedure which didn't include packages or restocks because they hadn't been migrated yet
			* '''Codes''':
			** '''P''' = package
			** '''L''' = location (bin)
			** '''R''' = restock shipment
			** '''M''' = master/multiple item
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS

Woozle: working

2008-12-27T18:42:33Z

working

← Older revision		Revision as of 18:42, 27 December 2008
Line 6:		Line 6:
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS
	SELECT		SELECT
	CONCAT("P.",p.ID) AS IDS,		CONCAT("P.",p.ID) AS IDS,
	"P" AS Type,		"P" AS Type,
	p.ID AS ID_forType,		p.ID AS ID_forType,
	CONCAT(o.Number,"-",p.Seq) AS Name,		CONCAT(o.Number,"-",p.Seq) AS Name,
	s.WhenShipped AS ~~TimeStamp~~		s.WhenShipped AS WhenMade
	FROM (ord_pkgs AS p LEFT JOIN core_orders AS o ON p.ID_Order=o.ID) LEFT JOIN ~~Shipments~~ AS s ON p.ID_Shipment=s.ID;		FROM (ord_pkgs AS p LEFT JOIN core_orders AS o ON p.ID_Order=o.ID) LEFT JOIN ord_shipmt AS s ON p.ID_Shipment=s.ID
	UNION ~~/**/~~		UNION
	SELECT		SELECT
	CONCAT("L.",l.ID) AS IDS,		CONCAT("L.",l.ID) AS IDS,
	"L" AS Type,		"L" AS Type,
	l.ID AS ID_forType,		l.ID AS ID_forType,
	l.Code AS Name,		l.Code AS Name,
	l.WhenCreated AS ~~TIMESTAMP~~		l.WhenCreated AS WhenMade
	FROM stk_bins AS l		FROM stk_bins AS l
	UNION		UNION
	/*		SELECT
	~~SELECT~~		CONCAT("R.",r.ID) AS IDS,
	CONCAT("R.",r.ID) AS IDS,		"R" AS Type,
	"R" AS Type,		r.ID AS ID_forType,
	r.ID AS ID_forType,		CONCAT(rq.PurchOrdNum,"-",r.ID," (inv",SuppInvcNum,")") AS Name,
	"po"&r.~~PurchOrdNum&~~"~~/ord~~"~~&SuppOrdNum&~~"~~/inv~~"~~&SuppInvcNum~~ AS Name,		IFNULL(WhenReceived,IFNULL(WhenDebited,WhenShipped)) AS WhenMade
	~~RestockEffDate~~(~~r.ID~~) AS ~~TimeStamp~~		FROM rstk_rcd AS r LEFT JOIN rstk_req AS rq ON r.ID_Restock=rq.ID
	FROM ~~Restocks~~ AS r		UNION
	UNION ~~/**/~~		SELECT
	SELECT		CONCAT("M.",i.ID) AS IDS,
	CONCAT("M.",i.ID) AS IDS,		"M" AS Type,
	"M" AS Type,		i.ID AS ID_forType,
	i.ID AS ID_forType,		i.CatNum AS Name,
	i.CatNum AS Name,		NULL AS WhenMade
	NULL AS ~~TIMESTAMP~~		FROM cat_items AS i WHERE i.IsMaster;</mysql>
	FROM cat_items AS i WHERE i.IsMaster;</mysql>
	<section end=sql />		<section end=sql />

Woozle: in progress

2008-12-27T16:20:57Z

in progress

New page

==About==
* '''Purpose''': Stock codes used by {{vbzcart/table|stk_history}}, and by anything that needs to look up what those codes refer to
* '''History''':
** '''2008-12-27''' Adapting from stored procedure which didn't include packages or restocks because they hadn't been migrated yet
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS
SELECT
CONCAT("P.",p.ID) AS IDS,
"P" AS Type,
p.ID AS ID_forType,
CONCAT(o.Number,"-",p.Seq) AS Name,
s.WhenShipped AS TimeStamp
FROM (ord_pkgs AS p LEFT JOIN core_orders AS o ON p.ID_Order=o.ID) LEFT JOIN Shipments AS s ON p.ID_Shipment=s.ID;
UNION /**/
SELECT
CONCAT("L.",l.ID) AS IDS,
"L" AS Type,
l.ID AS ID_forType,
l.Code AS Name,
l.WhenCreated AS TIMESTAMP
FROM stk_bins AS l
UNION
/*
SELECT
CONCAT("R.",r.ID) AS IDS,
"R" AS Type,
r.ID AS ID_forType,
"po"&r.PurchOrdNum&"/ord"&SuppOrdNum&"/inv"&SuppInvcNum AS Name,
RestockEffDate(r.ID) AS TimeStamp
FROM Restocks AS r
UNION /**/
SELECT
CONCAT("M.",i.ID) AS IDS,
"M" AS Type,
i.ID AS ID_forType,
i.CatNum AS Name,
NULL AS TIMESTAMP
FROM cat_items AS i WHERE i.IsMaster;</mysql>
<section end=sql />