VbzCart/procs/Upd StkContainers

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | procs
Jump to: navigation, search

About

  • Purpose update (replace/fill) _stk_containers
  • Status: NOT USED
    • if something ever does need to cache container data, just change this so it SELECTs * from
  1. REDIRECT Template:l/vc/query
    • Packages and Restocks were not yet migrated when this was written as it is now, which is why those sections are commented out

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>