VbzCart/docs/queries/qryRstkItms active: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (used by) |
imported>Woozle (change in qty calculation) |
||
| Line 8: | Line 8: | ||
* '''History''': | * '''History''': | ||
** '''2008-11-18''' Created for new restock process | ** '''2008-11-18''' Created for new restock process | ||
** '''2010-01-04''' now allowing QtyExp to override QtyOrd; resulting field renamed from QtyOrd to QtyExp | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_active AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_active AS | ||
| Line 13: | Line 14: | ||
rrq.ID_Restock, | rrq.ID_Restock, | ||
rrq.ID_Item, | rrq.ID_Item, | ||
SUM(rrq.QtyOrd) AS | SUM(IFNULL(rrq.QtyExp,rrq.QtyOrd)) AS QtyExp | ||
FROM | FROM rstk_req_item AS rrq | ||
LEFT JOIN qryRstks_active AS rq ON rrq.ID_Restock=rq.ID | |||
WHERE rq.ID IS NOT NULL | WHERE rq.ID IS NOT NULL | ||
GROUP BY rrq.ID_Restock, rrq.ID_Item;</mysql> | GROUP BY rrq.ID_Restock, rrq.ID_Item;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 00:34, 5 January 2010
Details
- Returns: Data about items which are in active restocks, i.e. on restock orders but not yet received (or cancelled).
- Requires: Template:Vbzcart/table, Template:Vbzcart/query
- Used by: Template:Vbzcart/query
- Note: This is different from qryRstkLines_en_route (deprecated):
- Grouped by Item instead of Restock Line
- Uses new Restock tables
- History:
- 2008-11-18 Created for new restock process
- 2010-01-04 now allowing QtyExp to override QtyOrd; resulting field renamed from QtyOrd to QtyExp
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_active AS SELECT
rrq.ID_Restock, rrq.ID_Item, SUM(IFNULL(rrq.QtyExp,rrq.QtyOrd)) AS QtyExp
FROM rstk_req_item AS rrq
LEFT JOIN qryRstks_active AS rq ON rrq.ID_Restock=rq.ID
WHERE rq.ID IS NOT NULL GROUP BY rrq.ID_Restock, rrq.ID_Item;</mysql> <section end=sql />