Difference between revisions of "VbzCart/queries/qryItms to restock union"
Jump to navigation
Jump to search
(New page: ==Details== * '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined ...) |
(updated source query name) |
||
| Line 1: | Line 1: | ||
==Details== | ==Details== | ||
* '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in [[#qryItms_to_restock]]) | * '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in [[#qryItms_to_restock]]) | ||
| − | * '''Requires''': {{vbzcart/query|qryItms_open}}, {{vbzcart/query|qryStkItms_for_sale}}, {{vbzcart/table|cat_items}}, {{vbzcart/query| | + | * '''Requires''': {{vbzcart/query|qryItms_open}}, {{vbzcart/query|qryStkItms_for_sale}}, {{vbzcart/table|cat_items}}, {{vbzcart/query|qryRstkItms_expected}} |
* '''Used by''': {{vbzcart/query|qryItms_to_restock}} | * '''Used by''': {{vbzcart/query|qryItms_to_restock}} | ||
* '''History''': | * '''History''': | ||
** '''2008-11-18''' created for new restocking process (under construction; need to add items-in-transit for restocks) | ** '''2008-11-18''' created for new restocking process (under construction; need to add items-in-transit for restocks) | ||
** '''2008-11-22''' in-transit items should never be negative; if we receive something not requested, it goes into stock | ** '''2008-11-22''' in-transit items should never be negative; if we receive something not requested, it goes into stock | ||
| + | ** '''2008-12-05''' Updated source query name: {{vbzcart/query|qryRstkItms_en_route}} → {{vbzcart/query|qryRstkItms_expected}} | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS | ||
| Line 38: | Line 39: | ||
NULL AS QtyMin_Stk, | NULL AS QtyMin_Stk, | ||
IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder | IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder | ||
| − | FROM | + | FROM qryRstkItms_expected;</mysql> |
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 23:45, 5 December 2008
Details
- Returns: List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in #qryItms_to_restock)
- Requires:
- REDIRECT Template:l/vc/query, qryStkItms_for_sale,
- REDIRECT Template:l/vc/table, qryRstkItms_expected
- Used by: qryItms_to_restock
- History:
- 2008-11-18 created for new restocking process (under construction; need to add items-in-transit for restocks)
- 2008-11-22 in-transit items should never be negative; if we receive something not requested, it goes into stock
- 2008-12-05 Updated source query name: qryRstkItms_en_route → qryRstkItms_expected
SQL
<mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS SELECT
ID_Item, QtyOpen, NULL AS QtyForSale, NULL AS QtyMin_Stk, NULL AS QtyOnOrder
FROM qryItms_open UNION SELECT
ID_Item, NULL AS QtyOpen, QtyForSale, NULL AS QtyMin_Stk, NULL AS QtyOnOrder
FROM qryStkItms_for_sale UNION SELECT
ID AS ID_Item, NULL AS QtyOpen, NULL AS QtyForSale, QtyMin_Stk, NULL AS QtyOnOrder
FROM cat_items WHERE QtyMin_Stk>0 UNION SELECT
ID_Item, NULL AS QtyOpen, NULL AS QtyForSale, NULL AS QtyMin_Stk, IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder
FROM qryRstkItms_expected;</mysql>