VbzCart/queries/qryStock containers

About

 * Purpose: Stock codes used by, 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
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;