VbzCart/queries/qryStock containers

About

• Purpose: Stock codes used by

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