VbzCart/queries/qryStock containers
Jump to navigation
Jump to search
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>