VbzCart/queries/qryStock containers
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
About
- Purpose: Stock codes used by
- REDIRECT Template:l/vc/table, 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
- Codes:
- P = package
- L = location (bin)
- R = restock shipment
- M = master/multiple item
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 WhenMade
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 SELECT
CONCAT("L.",l.ID) AS IDS,
"L" AS Type,
l.ID AS ID_forType,
l.Code AS Name,
l.WhenCreated AS WhenMade
FROM stk_bins AS l UNION SELECT
CONCAT("R.",r.ID) AS IDS,
"R" AS Type,
r.ID AS ID_forType,
CONCAT(rq.PurchOrdNum,"-",r.ID," (inv",SuppInvcNum,")") AS Name,
IFNULL(WhenReceived,IFNULL(WhenDebited,WhenShipped)) AS WhenMade
FROM rstk_rcd AS r LEFT JOIN rstk_req AS rq ON r.ID_Restock=rq.ID UNION SELECT
CONCAT("M.",i.ID) AS IDS,
"M" AS Type,
i.ID AS ID_forType,
i.CatNum AS Name,
NULL AS WhenMade
FROM cat_items AS i WHERE i.IsMaster;</mysql>