VbzCart/docs/queries/qryStock containers: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (in progress) |
m (Woozle moved page VbzCart/VbzCart/queries/qryStock containers to VbzCart/docs/queries/qryStock containers without leaving a redirect: part 3/5) |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 3: | Line 3: | ||
* '''History''': | * '''History''': | ||
** '''2008-12-27''' Adapting from stored procedure which didn't include packages or restocks because they hadn't been migrated yet | ** '''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== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS | ||
SELECT | 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> | |||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 01:56, 25 February 2024
About
- Purpose: Stock codes used by Template:Vbzcart/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
<section begin=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> <section end=sql />