VbzCart/docs/tables/rstk rcd: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (→SQL: 3 "When" fields (how did I manage to leave these out? what else have I forgotten?)) |
m (Woozle moved page VbzCart/VbzCart/tables/rstk rcd to VbzCart/docs/tables/rstk rcd without leaving a redirect: part 4/5) |
||
| (7 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': Master record for a restock (wholesale) shipment received | |||
* ''' | * '''Related''': | ||
* | ** {{vbzcart|table|rstk_rcd_line}} lists the contents of this shipment | ||
** {{vbzcart|table|rstk_req}} has the restock requests, each of which may have one or more restock shipments received against it | |||
* '''Future''': | |||
** Should this table be named '''rstk_shp''', and terminology changed from "restock received" to "restock shipment"? | |||
==Fields== | |||
* '''InvcCondition''': an integer describing the condition of the invoice paperwork -- | |||
** 0 = absent (no invoices found) | |||
** 1 = partial (one or more pages missing/illegible) | |||
** 2 = complete (all pages located and legible) | |||
* '''TrackingCode''': eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web | |||
* '''TotalInvFinal''': Final = Merch + Ship + Adj | |||
==History== | |||
* '''2008-11-07''' Created | |||
* '''2008-12-17''' Added '''InvcEntry''' field | |||
* '''2016-01-06''' Renamed '''ID_Restock''' to '''ID_Request''' | |||
* '''2016-01-13''' Added '''ID_Supplier''' and '''ID_Warehouse''' fields (we previously had to look them up in the Request) because: | |||
** There might be restocks received without a Request that can reasonably be attached to them | |||
*** ...especially when entering old invoices, but possibly during the normal course of business | |||
** A restock might presumably be sent to a different Warehouse than where it was expected. | |||
** Having to look up the Request to get that information adds extra complexity. | |||
==SQL== | ==SQL== | ||
<mysql>CREATE TABLE `rstk_rcd` ( | |||
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
` | `ID_Request` INT COMMENT "rstk_req.ID of restock request fulfilled by this shipment", | ||
`ID_Supplier` INT NOT NULL COMMENT "cat_supp.ID of supplier", | |||
`ID_Warehouse` INT COMMENT "stk_whse.ID of ship-to warehouse", | |||
`SuppInvcNum` VARCHAR(63) COMMENT "supplier's invoice number", | `SuppInvcNum` VARCHAR(63) COMMENT "supplier's invoice number", | ||
`CarrierDescr` VARCHAR(63) COMMENT "shipping carrier (UPS, USPS...)", | `CarrierDescr` VARCHAR(63) COMMENT "shipping carrier (UPS, USPS...)", | ||
`InvcEntry` TEXT DEFAULT NULL COMMENT "invoice lines as parseable text", | |||
`WhenShipped` DATETIME DEFAULT NULL COMMENT "when supplier shipped the package", | `WhenShipped` DATETIME DEFAULT NULL COMMENT "when supplier shipped the package", | ||
`WhenReceived` DATETIME DEFAULT NULL COMMENT "when package was received from supplier", | `WhenReceived` DATETIME DEFAULT NULL COMMENT "when package was received from supplier", | ||
`WhenDebited` DATETIME DEFAULT NULL COMMENT "when charge for order was debited", | `WhenDebited` DATETIME DEFAULT NULL COMMENT "when charge for order was debited", | ||
`TrackingCode` VARCHAR(63) COMMENT "carrier's tracking number", | `TrackingCode` VARCHAR(63) COMMENT "carrier's tracking number", | ||
`TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated from contents of received restock", | `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated from contents of received restock", | ||
/* from supplier invoice */ | |||
`TotalInvMerch` DECIMAL(9,2) COMMENT "total cost of merchandise invoiced", | `TotalInvMerch` DECIMAL(9,2) COMMENT "total cost of merchandise invoiced", | ||
`TotalInvShip` DECIMAL(9,2) COMMENT "total shipping cost invoiced", | `TotalInvShip` DECIMAL(9,2) COMMENT "total shipping cost invoiced", | ||
`TotalInvAdj` DECIMAL(9,2) COMMENT "total invoice adjustments", | `TotalInvAdj` DECIMAL(9,2) COMMENT "total invoice adjustments", | ||
`TotalInvFinal` DECIMAL(9,2) COMMENT "final total on invoice (must match amt paid)", | `TotalInvFinal` DECIMAL(9,2) COMMENT "final total on invoice (must match amt paid)", | ||
`InvcCondition` INT DEFAULT NULL COMMENT "do we have all the paperwork for this invoice?", | |||
`PayMethod` VARCHAR(63) COMMENT "how payment was made, if not same as restock request", | `PayMethod` VARCHAR(63) COMMENT "how payment was made, if not same as restock request", | ||
`Notes` TEXT DEFAULT NULL COMMENT "human-entered notes", | `Notes` TEXT DEFAULT NULL COMMENT "human-entered notes", | ||
| Line 26: | Line 47: | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
Latest revision as of 01:57, 25 February 2024
About
- Purpose: Master record for a restock (wholesale) shipment received
- Related:
- Template:Vbzcart lists the contents of this shipment
- Template:Vbzcart has the restock requests, each of which may have one or more restock shipments received against it
- Future:
- Should this table be named rstk_shp, and terminology changed from "restock received" to "restock shipment"?
Fields
- InvcCondition: an integer describing the condition of the invoice paperwork --
- 0 = absent (no invoices found)
- 1 = partial (one or more pages missing/illegible)
- 2 = complete (all pages located and legible)
- TrackingCode: eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web
- TotalInvFinal: Final = Merch + Ship + Adj
History
- 2008-11-07 Created
- 2008-12-17 Added InvcEntry field
- 2016-01-06 Renamed ID_Restock to ID_Request
- 2016-01-13 Added ID_Supplier and ID_Warehouse fields (we previously had to look them up in the Request) because:
- There might be restocks received without a Request that can reasonably be attached to them
- ...especially when entering old invoices, but possibly during the normal course of business
- A restock might presumably be sent to a different Warehouse than where it was expected.
- Having to look up the Request to get that information adds extra complexity.
- There might be restocks received without a Request that can reasonably be attached to them
SQL
<mysql>CREATE TABLE `rstk_rcd` (
`ID` INT NOT NULL AUTO_INCREMENT, `ID_Request` INT COMMENT "rstk_req.ID of restock request fulfilled by this shipment", `ID_Supplier` INT NOT NULL COMMENT "cat_supp.ID of supplier", `ID_Warehouse` INT COMMENT "stk_whse.ID of ship-to warehouse", `SuppInvcNum` VARCHAR(63) COMMENT "supplier's invoice number", `CarrierDescr` VARCHAR(63) COMMENT "shipping carrier (UPS, USPS...)", `InvcEntry` TEXT DEFAULT NULL COMMENT "invoice lines as parseable text", `WhenShipped` DATETIME DEFAULT NULL COMMENT "when supplier shipped the package", `WhenReceived` DATETIME DEFAULT NULL COMMENT "when package was received from supplier", `WhenDebited` DATETIME DEFAULT NULL COMMENT "when charge for order was debited", `TrackingCode` VARCHAR(63) COMMENT "carrier's tracking number", `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated from contents of received restock",
/* from supplier invoice */
`TotalInvMerch` DECIMAL(9,2) COMMENT "total cost of merchandise invoiced", `TotalInvShip` DECIMAL(9,2) COMMENT "total shipping cost invoiced", `TotalInvAdj` DECIMAL(9,2) COMMENT "total invoice adjustments", `TotalInvFinal` DECIMAL(9,2) COMMENT "final total on invoice (must match amt paid)", `InvcCondition` INT DEFAULT NULL COMMENT "do we have all the paperwork for this invoice?", `PayMethod` VARCHAR(63) COMMENT "how payment was made, if not same as restock request", `Notes` TEXT DEFAULT NULL COMMENT "human-entered notes", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;</mysql>