Difference between revisions of "VbzCart/queries/qryRstkItms expected"
Jump to navigation
Jump to search
m (VbzCart/queries/qryRstkItms en route moved to VbzCart/queries/qryRstkItms expected: reorganized terminology) |
(post-move cleanup & doc) |
||
| Line 1: | Line 1: | ||
| − | == | + | ==About== |
| − | * '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or | + | * '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed). |
* '''Requires''': {{vbzcart/table|rstk_rcd_line}}, {{vbzcart/table|rstk_rcd}}, {{vbzcart/query|qryRstkItms_active}} | * '''Requires''': {{vbzcart/table|rstk_rcd_line}}, {{vbzcart/table|rstk_rcd}}, {{vbzcart/query|qryRstkItms_active}} | ||
* '''History''': | * '''History''': | ||
** '''2008-11-19''' Created for new restock process | ** '''2008-11-19''' Created for new restock process | ||
** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null | ** '''2008-11-24''' '''QtyRecd''' falls back on '''QtyFiled''' if null | ||
| + | ** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected | ||
==SQL== | ==SQL== | ||
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW | + | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS |
SELECT | SELECT | ||
rqi.ID_Restock, | rqi.ID_Restock, | ||
Revision as of 23:29, 4 December 2008
About
- Returns: Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed).
- Requires:
- REDIRECT Template:l/vc/table, rstk_rcd,
- REDIRECT Template:l/vc/query
- History:
- 2008-11-19 Created for new restock process
- 2008-11-24 QtyRecd falls back on QtyFiled if null
- 2008-12-04 renamed: qryRstkItms_en_route → qryRstkItms_expected
SQL
<mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS SELECT
rqi.ID_Restock, rqi.ID_Item, SUM(rqi.QtyOrd) AS QtyOrd, SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)) AS QtyRecd
FROM
(rstk_rcd_line AS rcl LEFT JOIN rstk_rcd AS rc ON rcl.ID_RstkRcd=rc.ID) LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)
GROUP BY rqi.ID_Restock, rqi.ID_Item HAVING IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0);</mysql>