Difference between revisions of "VbzCart/queries/qryRstkItms unsent for order"
Jump to navigation
Jump to search
(adapted from Access version, using new restock tables) |
(Added cat_ittyps) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | == | + | ==About== |
* '''Purpose''': This should have enough information in it that we can use it to generate a restock order in whatever format the supplier needs it to be in, with minimal hand-massaging. | * '''Purpose''': This should have enough information in it that we can use it to generate a restock order in whatever format the supplier needs it to be in, with minimal hand-massaging. | ||
| − | * '''Requires''': {{vbzcart/ | + | * '''Requires''': {{vbzcart/query|qryRstkItms_unsent}}, {{vbzcart/query|qryCat_Items}}, {{vbzcat/table|cat_ittyps}} |
* '''Used by''': eventually, the restock order generation form (currently working on this) | * '''Used by''': eventually, the restock order generation form (currently working on this) | ||
* '''History''': | * '''History''': | ||
| − | ** '''2008-12-04''' Created for new restock process | + | ** '''2008-12-04''' Created for new restock process; renamed |
| + | ** '''2008-12-05''' Added cat_ittyps | ||
==SQL== | ==SQL== | ||
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW | + | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_unsent_for_order AS |
SELECT | SELECT | ||
ri.ID_Restock, | ri.ID_Restock, | ||
| Line 18: | Line 19: | ||
ci.ItOpt_Sort, | ci.ItOpt_Sort, | ||
ci.Supp_CatNum, | ci.Supp_CatNum, | ||
| + | it.NameSng, | ||
| + | IFNULL(it.NamePlr,it.NameSng) AS NamePlr, | ||
ri.QtyOrd, | ri.QtyOrd, | ||
ri.QtyExp, | ri.QtyExp, | ||
| Line 23: | Line 26: | ||
ri.CostExpPer, | ri.CostExpPer, | ||
ci.PriceSell | ci.PriceSell | ||
| − | FROM | + | FROM |
| + | (qryRstkItms_unsent AS ri LEFT JOIN | ||
| + | qryCat_Items AS ci ON ci.ID=ri.ID_Item) LEFT JOIN | ||
| + | cat_ittyps AS it ON ci.ID_ItTyp=it.ID | ||
ORDER BY ci.CatNum;</mysql> | ORDER BY ci.CatNum;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 18:38, 5 December 2008
About
- Purpose: This should have enough information in it that we can use it to generate a restock order in whatever format the supplier needs it to be in, with minimal hand-massaging.
- Requires:
- REDIRECT Template:l/vc/query, qryCat_Items, Template:vbzcat/table
- Used by: eventually, the restock order generation form (currently working on this)
- History:
- 2008-12-04 Created for new restock process; renamed
- 2008-12-05 Added cat_ittyps
SQL
<mysql>CREATE OR REPLACE VIEW qryRstkItms_unsent_for_order AS SELECT
ri.ID_Restock, ri.ID_Item, ci.CatNum, ci.Title_CatNum, ci.Title_Name, ci.ID_ItTyp, ci.ID_itOpt, ci.ItOpt_Descr, ci.ItOpt_Sort, ci.Supp_CatNum, it.NameSng, IFNULL(it.NamePlr,it.NameSng) AS NamePlr, ri.QtyOrd, ri.QtyExp, ci.QtyMin_Stk, ri.CostExpPer, ci.PriceSell
FROM
(qryRstkItms_unsent AS ri LEFT JOIN qryCat_Items AS ci ON ci.ID=ri.ID_Item) LEFT JOIN cat_ittyps AS it ON ci.ID_ItTyp=it.ID
ORDER BY ci.CatNum;</mysql>