Difference between revisions of "VbzCart/tables/rstk req"
Jump to navigation
Jump to search
(New page: ==About== category:VbzCart/tables * '''Modified''': 2008-11-07; several fields moved to rstk_recd; to replace ../core_restocks ==SQL== <section begin=sql /><mysql>CREATE TABLE `cor...) |
(→SQL: warehouse now has its own table) |
||
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
[[category:VbzCart/tables]] | [[category:VbzCart/tables]] | ||
| − | + | '''Purpose''': restock requests (i.e. wholesale orders) sent to Suppliers | |
| + | ===History=== | ||
| + | * '''2008-11-07''' several fields moved to <s>rstk_recd</s> {{vbzcart|table|rstk_rcd}} | ||
| + | * '''2008-11-19''' +WhenOrphaned | ||
| + | * '''2008-11-20''' +WhenClosed | ||
| + | * '''2010-01-02''' +SuppPONum so we can reliably sort by our assigned PO #s | ||
| + | ===Related=== | ||
| + | * {{vbzcart|table|rstk_req_item}}: detail table | ||
| + | * {{vbzcart|table|rstk_rcd}}: every restock received points to a restock request | ||
| + | ===Fields=== | ||
| + | * '''WhenKilled''' indicates that the order was either cancelled or never sent to the supplier, so no shipments are expected. | ||
| + | * '''WhenOrphaned''' indicates that there probably was at least one shipment received in response, but we don't have any data. This is for old restock records where paperwork is missing, so we don't know what date to fill in but we also don't want to keep the restock request active. | ||
| + | * '''WhenClosed''' indicates that we are no longer expecting any responses to the restock request. This happens when the order has generated at least one received package and there are no items remaining on backorder with the supplier. | ||
| + | * '''CarrierDescr''' will eventually be replaced or supplemented by a more rigorous method of tracking delivery carriers. For now, it lets us keep a record without having to do that. | ||
==SQL== | ==SQL== | ||
| − | + | <mysql>CREATE TABLE `rstk_req` ( | |
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
`ID_Supplier` INT NOT NULL COMMENT "cat_supp.ID of supplier", | `ID_Supplier` INT NOT NULL COMMENT "cat_supp.ID of supplier", | ||
| − | `ID_Warehouse` INT COMMENT " | + | `ID_Warehouse` INT COMMENT "stk_whse.ID of ship-to warehouse", |
`PurchOrdNum` VARCHAR(63) COMMENT "our purchase order number", | `PurchOrdNum` VARCHAR(63) COMMENT "our purchase order number", | ||
`SuppOrdNum` VARCHAR(63) COMMENT "supplier's order number", | `SuppOrdNum` VARCHAR(63) COMMENT "supplier's order number", | ||
| + | `SuppPONum` VARCHAR(63) COMMENT "purchase order number assigned by supplier", | ||
`CarrierDescr` VARCHAR(63) COMMENT "shipping carrier requested (UPS, USPS...)", | `CarrierDescr` VARCHAR(63) COMMENT "shipping carrier requested (UPS, USPS...)", | ||
/* eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web */ | /* eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web */ | ||
| Line 18: | Line 32: | ||
`WhenOrdered` DATETIME DEFAULT NULL COMMENT "when order was placed with supplier", | `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when order was placed with supplier", | ||
`WhenConfirmed` DATETIME DEFAULT NULL COMMENT "when order was confirmed with supplier", | `WhenConfirmed` DATETIME DEFAULT NULL COMMENT "when order was confirmed with supplier", | ||
| − | `WhenKilled` DATETIME DEFAULT NULL COMMENT "when order was | + | `WhenKilled` DATETIME DEFAULT NULL COMMENT "when order was explicitly cancelled or abandoned", |
| + | `WhenClosed` DATETIME DEFAULT NULL COMMENT "when order was no longer considered active", | ||
| + | `WhenOrphaned` DATETIME DEFAULT NULL COMMENT "when we decided the restock record could not be completed", | ||
`WhenExpectedOrig` DATETIME DEFAULT NULL COMMENT "when we originally expected to receive order", | `WhenExpectedOrig` DATETIME DEFAULT NULL COMMENT "when we originally expected to receive order", | ||
`WhenExpectedFinal` DATETIME DEFAULT NULL COMMENT "most recent ETA", | `WhenExpectedFinal` DATETIME DEFAULT NULL COMMENT "most recent ETA", | ||
| Line 26: | Line 42: | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
| − | |||
Latest revision as of 00:12, 14 January 2016
About
Purpose: restock requests (i.e. wholesale orders) sent to Suppliers
History
- 2008-11-07 several fields moved to
rstk_recdrstk_rcd - 2008-11-19 +WhenOrphaned
- 2008-11-20 +WhenClosed
- 2010-01-02 +SuppPONum so we can reliably sort by our assigned PO #s
Related
- rstk_req_item: detail table
- rstk_rcd: every restock received points to a restock request
Fields
- WhenKilled indicates that the order was either cancelled or never sent to the supplier, so no shipments are expected.
- WhenOrphaned indicates that there probably was at least one shipment received in response, but we don't have any data. This is for old restock records where paperwork is missing, so we don't know what date to fill in but we also don't want to keep the restock request active.
- WhenClosed indicates that we are no longer expecting any responses to the restock request. This happens when the order has generated at least one received package and there are no items remaining on backorder with the supplier.
- CarrierDescr will eventually be replaced or supplemented by a more rigorous method of tracking delivery carriers. For now, it lets us keep a record without having to do that.
SQL
<mysql>CREATE TABLE `rstk_req` (
`ID` INT NOT NULL AUTO_INCREMENT, `ID_Supplier` INT NOT NULL COMMENT "cat_supp.ID of supplier", `ID_Warehouse` INT COMMENT "stk_whse.ID of ship-to warehouse", `PurchOrdNum` VARCHAR(63) COMMENT "our purchase order number", `SuppOrdNum` VARCHAR(63) COMMENT "supplier's order number", `SuppPONum` VARCHAR(63) COMMENT "purchase order number assigned by supplier", `CarrierDescr` VARCHAR(63) COMMENT "shipping carrier requested (UPS, USPS...)",
/* eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web */
`TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated by us", `TotalEstFinal` DECIMAL(9,2) COMMENT "estimate of final charge",
/* Final = Merch + Ship + Adj */
`PayMethod` VARCHAR(63) COMMENT "how payment was made (typically: which credit card?)", `WhenCreated` DATETIME DEFAULT NULL COMMENT "when restock order was started (created)", `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when order was placed with supplier", `WhenConfirmed` DATETIME DEFAULT NULL COMMENT "when order was confirmed with supplier", `WhenKilled` DATETIME DEFAULT NULL COMMENT "when order was explicitly cancelled or abandoned", `WhenClosed` DATETIME DEFAULT NULL COMMENT "when order was no longer considered active", `WhenOrphaned` DATETIME DEFAULT NULL COMMENT "when we decided the restock record could not be completed", `WhenExpectedOrig` DATETIME DEFAULT NULL COMMENT "when we originally expected to receive order", `WhenExpectedFinal` DATETIME DEFAULT NULL COMMENT "most recent ETA", `isLocked` BOOL DEFAULT FALSE COMMENT "TRUE = items may not be edited", `Notes` TEXT DEFAULT NULL COMMENT "human-entered notes", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;</mysql>