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

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>