VbzCart/docs/queries/qryItms to restock union: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(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 ...)
 
imported>Woozle
(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|qryRstkItms_en_route}}
* '''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}} &rarr; {{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 qryRstkItms_en_route;</mysql>
FROM qryRstkItms_expected;</mysql>
<section end=sql />
<section end=sql />

Revision as of 23:45, 5 December 2008

Details

SQL

<section begin=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> <section end=sql />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryItms_to_restock_union&oldid=5267"