Difference between revisions of "VbzCart/tables/rstk req item"

Latest revision as of 15:52, 28 December 2019

About

Purpose: Stores requested restock contents by item; planned distribution to customers is in rstk_ord_line
Future: Rename QtyOrd field to QtyReq (quantity requested).
History:
- 2008-11-07 Created for new restock process
- 2008-12-04 Added QtyCust field to make it easier to tell how much immediate revenue a restock is likely to generate
- 2014-12-14 Changed isGone from BOOL (which apparently equates to TINYINT) to BIT.
- 2016-01-07 Changed ID_Restock to ID_Parent
- 2019-12-28 changed ENGINE from MyISAM to InnoDB
Notes:
- replaces part of rstk_lines

SQL

CREATE TABLE `rstk_req_item` (
   `ID_Parent`   INT COMMENT "rstk_req.ID of request to which this item belongs",
   `ID_Item`     INT COMMENT "cat_items.ID of item being restocked",
   `Descr`       VARCHAR(255) DEFAULT NULL COMMENT "Item description as given at time of shopping",
/* timestamps */
   `WhenCreated` DATETIME DEFAULT NULL COMMENT "when this line was added",
   `WhenVoided`  DATETIME DEFAULT NULL COMMENT "when this line was voided; not NULL = ignore this line",
/* quantities - data from creation of restock */
   `QtyNeed`     INT COMMENT "quantity needed, either for an order or to keep stock at desired level",
   `QtyCust`     INT COMMENT "quantity needed just to fill customer orders",
   `QtyOrd`      INT COMMENT "quantity actually ordered from supplier",
   `QtyExp`      INT COMMENT "quantity actually expected, if supplier doesn't have enough available to fill the order",
   `isGone`      BIT COMMENT "YES = item discontinued, no more available (if info from source other than invoice)",
/* cost information */
   `CostExpPer`  DECIMAL(9,2) COMMENT "expected per-item cost",
   `Notes`       VARCHAR(255) COMMENT "human-entered notes on this line item",
   PRIMARY KEY(`ID_Restock`,`ID_Item`)
 )
 ENGINE = InnoDB;

@@ Line 1: / Line 1: @@
+[[category:VbzCart/tables]]
 ==About==
-[[category:VbzCart/tables]]
+* '''Purpose''': Stores requested restock contents by item; planned distribution to customers is in {{vbzcart|table|rstk_ord_line}}
-* '''Created''': 2008-11-07
+* '''Future''': Rename '''QtyOrd''' field to '''QtyReq''' (quantity requested).
-* To replace old [[../rstk_lines]] after data is migrated
+* '''History''':
-* Records restock contents by item; planned distribution to customers is in [[../rstk_ord_lines]]
+** '''2008-11-07''' Created for new restock process
+** '''2008-12-04''' Added '''QtyCust''' field to make it easier to tell how much immediate revenue a restock is likely to generate
+** '''2014-12-14''' Changed '''isGone''' from BOOL (which apparently equates to TINYINT) to BIT.
+** '''2016-01-07''' Changed '''ID_Restock''' to '''ID_Parent'''
+** '''2019-12-28''' changed ENGINE from MyISAM to InnoDB
+* '''Notes''':
+** replaces part of {{vbzcart|table|rstk_lines}}
 ==SQL==
-<section begin=sql /><mysql>CREATE TABLE `rstk_lines` (
+<source lang=mysql>CREATE TABLE `rstk_req_item` (
-    `ID`          INT NOT NULL AUTO_INCREMENT,
+    `ID_Parent`   INT COMMENT "rstk_req.ID of request to which this item belongs",
-   `ID_Restock`  INT COMMENT "core_restocks.ID of restock this item belongs to",
     `ID_Item`     INT COMMENT "cat_items.ID of item being restocked",
     `Descr`       VARCHAR(255) DEFAULT NULL COMMENT "Item description as given at time of shopping",
 /* timestamps */
-    `WhenCreated` DATETIME DEFAULT NULL "when this line was added",
+    `WhenCreated` DATETIME DEFAULT NULL COMMENT "when this line was added",
-    `WhenVoided`  DATETIME DEFAULT NULL "when this line was voided; not NULL = ignore this line",
+    `WhenVoided`  DATETIME DEFAULT NULL COMMENT "when this line was voided; not NULL = ignore this line",
 /* quantities - data from creation of restock */
     `QtyNeed`     INT COMMENT "quantity needed, either for an order or to keep stock at desired level",
-    `QtyOrd`      INT COMMENT "quantity actually ordered",
+   `QtyCust`     INT COMMENT "quantity needed just to fill customer orders",
+    `QtyOrd`      INT COMMENT "quantity actually ordered from supplier",
     `QtyExp`      INT COMMENT "quantity actually expected, if supplier doesn't have enough available to fill the order",
-    `isGone`      BOOL COMMENT "YES = item discontinued, no more available", /* data from invoice */
+    `isGone`      BIT COMMENT "YES = item discontinued, no more available (if info from source other than invoice)",
-   `InvcLineNo`  DECIMAL(3,3) COMMENT "line number (use decimals for multiple restock lines as single invoice line)",
-   `InvcQtyOrd`  INT COMMENT "quantity ordered",
-   `InvcQtySent` INT COMMENT "quantity shipped to us",
-/* quantities - data from receiving the items */
-   `QtyRecd`     INT COMMENT "quantity actually received",
-   `QtyFiled`    INT COMMENT "quantity moved into stock",
 /* cost information */
     `CostExpPer`  DECIMAL(9,2) COMMENT "expected per-item cost",
-   `CostInvPer`  DECIMAL(9,2) COMMENT "invoiced per-item cost",
-   `CostInvTot`  DECIMAL(9,2) COMMENT "invoice line total (CostExpPer x InvcQtySent) for this item",
-   `CostActTot`  DECIMAL(9,2) COMMENT "actual (best) line total as used for reconciling",
-   `CostActBal`  DECIMAL(9,2) COMMENT "running total, calculated from CostActTot; may be unnecessary anywhere except Access",
     `Notes`       VARCHAR(255) COMMENT "human-entered notes on this line item",
-    PRIMARY KEY(`ID`)
+    PRIMARY KEY(`ID_Restock`,`ID_Item`)
   )
-  ENGINE = MYISAM;</mysql>
+  ENGINE = InnoDB;</source>
-<section end=sql />

Difference between revisions of "VbzCart/tables/rstk req item"

Latest revision as of 15:52, 28 December 2019

About

SQL

Navigation menu

Search