Difference between revisions of "VbzCart/tables/rstk rcd"
Jump to navigation
Jump to search
m (tag) |
(added Supplier and Warehouse fields) |
||
(11 intermediate revisions by the same 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", | ||
+ | `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", | `TrackingCode` VARCHAR(63) COMMENT "carrier's tracking number", | ||
− | `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated | + | `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)", | ||
− | `PayMethod` VARCHAR(63) COMMENT "how payment was made, if not same as restock request", | + | `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", | `Notes` TEXT DEFAULT NULL COMMENT "human-entered notes", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
− |
Latest revision as of 00:25, 14 January 2016
About
- Purpose: Master record for a restock (wholesale) shipment received
- Related:
- rstk_rcd_line lists the contents of this shipment
- 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.
- 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>