Difference between revisions of "VbzCart/tables/rstk rcd line"
Jump to navigation
Jump to search
(→SQL: ID_RstkRecd -> ID_RstkRcd) |
(renamed ID_RstkReq -> ID_Parent; tidying) |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
[[category:VbzCart/tables]] | [[category:VbzCart/tables]] | ||
| − | * ''' | + | * '''Purpose''': Items actually received in a restock shipment |
| − | * | + | ==Fields== |
| + | * '''CostActBal''' may be unnecessary anywhere except [[MS Access|Access]], in which it is difficult for continuous form fields to show cumulatively calculated results rather than a value derived from a specific row -- evaluation of field functions for each row may take place in an order that does not correspond with the row's position on the form. | ||
| + | ==History== | ||
| + | * '''2008-11-07''' Created for revised restocking process -- split off from {{vbzcart|table|rstk_lines}} | ||
| + | * '''2008-11-09''' +'''InvcDescr''' field, ID_RstkRecd -> '''ID_RstkRcd''' | ||
| + | * '''2008-11-18''' Changed '''InvcLineNo''' from DECIMAL(3,3) to DECIMAL(6,3) | ||
| + | * '''2008-12-16''' Added '''InvcCatNo''' - can be used to update {{vbzcart/table|cat_items}}.Supp_CatNum, if it wasn't updated during the request entry (requests sometimes get entered by pasting from the supplier's version of the order, which has SCat#s). | ||
| + | * '''2010-11-28''' | ||
| + | ** Added '''isActive''' field to help with data entry process: when invoice text is parsed, all existing lines are deactivated (not deleted), and then updated with new info and reactivated. | ||
| + | ** Changed '''InvcLineNo''' from DECIMAL(6,3) to INT. No fractions found in historical data, and current design makes it unnecessary. (If this is wrong, scenario needs to be documented.) | ||
| + | * '''2016-01-06''' Renamed '''ID_RstkRcd''' to '''ID_Parent''' | ||
==SQL== | ==SQL== | ||
| − | + | <mysql>CREATE TABLE `rstk_rcd_line` ( | |
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
| − | ` | + | `ID_Parent` INT COMMENT "rstk_recd.ID of received restock shipment this line belongs to", |
`ID_Item` INT COMMENT "cat_items.ID of item being restocked", | `ID_Item` INT COMMENT "cat_items.ID of item being restocked", | ||
| − | `InvcLineNo` | + | `isActive` BOOL DEFAULT NULL "FALSE = data in this line is obsolete, so ignore it", |
| + | `InvcLineNo` INT COMMENT "line number on invoice", | ||
| + | `InvcCatNo` VARCHAR(63) COMMENT "catalog number from invoice", | ||
`InvcQtyOrd` INT COMMENT "quantity ordered", | `InvcQtyOrd` INT COMMENT "quantity ordered", | ||
`InvcQtySent` INT COMMENT "quantity shipped to us", | `InvcQtySent` INT COMMENT "quantity shipped to us", | ||
| Line 19: | Line 31: | ||
`CostInvTot` DECIMAL(9,2) COMMENT "invoice line total (CostExpPer x InvcQtySent) for this item", | `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", | `CostActTot` DECIMAL(9,2) COMMENT "actual (best) line total as used for reconciling", | ||
| − | `CostActBal` DECIMAL(9,2) COMMENT "running total, calculated from CostActTot | + | `CostActBal` DECIMAL(9,2) COMMENT "running total, calculated from CostActTot", |
`Notes` VARCHAR(255) COMMENT "human-entered notes on this line item", | `Notes` VARCHAR(255) COMMENT "human-entered notes on this line item", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
| − | |||
Latest revision as of 19:00, 6 January 2016
About
- Purpose: Items actually received in a restock shipment
Fields
- CostActBal may be unnecessary anywhere except Access, in which it is difficult for continuous form fields to show cumulatively calculated results rather than a value derived from a specific row -- evaluation of field functions for each row may take place in an order that does not correspond with the row's position on the form.
History
- 2008-11-07 Created for revised restocking process -- split off from rstk_lines
- 2008-11-09 +InvcDescr field, ID_RstkRecd -> ID_RstkRcd
- 2008-11-18 Changed InvcLineNo from DECIMAL(3,3) to DECIMAL(6,3)
- 2008-12-16 Added InvcCatNo - can be used to update
- REDIRECT Template:l/vc/table.Supp_CatNum, if it wasn't updated during the request entry (requests sometimes get entered by pasting from the supplier's version of the order, which has SCat#s).
- 2010-11-28
- Added isActive field to help with data entry process: when invoice text is parsed, all existing lines are deactivated (not deleted), and then updated with new info and reactivated.
- Changed InvcLineNo from DECIMAL(6,3) to INT. No fractions found in historical data, and current design makes it unnecessary. (If this is wrong, scenario needs to be documented.)
- 2016-01-06 Renamed ID_RstkRcd to ID_Parent
SQL
<mysql>CREATE TABLE `rstk_rcd_line` (
`ID` INT NOT NULL AUTO_INCREMENT, `ID_Parent` INT COMMENT "rstk_recd.ID of received restock shipment this line belongs to", `ID_Item` INT COMMENT "cat_items.ID of item being restocked", `isActive` BOOL DEFAULT NULL "FALSE = data in this line is obsolete, so ignore it", `InvcLineNo` INT COMMENT "line number on invoice", `InvcCatNo` VARCHAR(63) COMMENT "catalog number from invoice", `InvcQtyOrd` INT COMMENT "quantity ordered", `InvcQtySent` INT COMMENT "quantity shipped to us", `InvcDescr` VARCHAR(255) COMMENT "description from invoice",
/* quantities - data from receiving the items */
`QtyRecd` INT COMMENT "quantity actually received", `QtyFiled` INT COMMENT "quantity moved into stock",
/* cost information */
`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", `Notes` VARCHAR(255) COMMENT "human-entered notes on this line item", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;</mysql>