Difference between revisions of "VbzCart/queries/qryStock containers"
Jump to navigation
Jump to search
(in progress) |
(codes) |
||
| (One intermediate revision by the same 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 02:25, 21 December 2009
About
- Purpose: Stock codes used by
- 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>