Difference between revisions of "VbzCart/queries/qryRstkItms expected"
Jump to navigation
Jump to search
(post-move cleanup & doc) |
m (→About: used by: ?) |
||
| Line 2: | Line 2: | ||
* '''Returns''': Restock items requested (ordered), not yet received, but possibly expected because the restock request hasn't been terminated (closed, orphaned, or killed). | * '''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}} | ||
| + | * '''Used by''': | ||
* '''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 | ** '''2008-12-04''' renamed: qryRstkItms_en_route → qryRstkItms_expected | ||
| + | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_expected AS | ||
Revision as of 01:43, 5 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
- Used by:
- 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>