VbzCart/tables/rstk req

About

Purpose: restock requests (i.e. wholesale orders) sent to Suppliers

History

• 2008-11-07 several fields moved to rstk_recd 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

• 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 "cat_supp.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>