VbzCart/docs/procs/Upd StkContainers

About

• Purpose update (replace/fill) Template:Vbzcart
• Status: in progress; Packages and Restocks were not yet migrated when this was designed, but they are now -- this should probably just select * from Template:Vbzcart/query (to be documented), but I'm going to save it once for reference purposes...

SQL

<section begin=sql /><mysql>CREATE PROCEDURE Upd_StkContainers()

 REPLACE INTO _stk_containers
 /*
 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 (Packages AS p LEFT JOIN 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>

<section end=sql />