VbzCart/tables/core restocks

From HTYP, the free directory anyone can edit

[edit] About

To be replaced by VbzCart/tables/core_restock_new

[edit] SQL

CREATE TABLE `core_restocks` (
   `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 receiving warehouse",
   `PurchOrdNum`    VARCHAR(63) COMMENT "our purchase order number",
   `SuppOrdNum`     VARCHAR(63) COMMENT "supplier's order number",
   `SuppInvcNum`    VARCHAR(63) COMMENT "supplier's invoice number",
   `CarrierDescr`   VARCHAR(63) COMMENT "shipping carrier (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 */
   `TrackingCode`   VARCHAR(63) COMMENT "carrier's tracking number",
   `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated by us",
   `TotalEstFinal`  DECIMAL(9,2) COMMENT "estimate of final charge",
   /* amounts 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",
/* Final = Merch + Ship + Adj */
   `TotalInvFinal`  DECIMAL(9,2) COMMENT "final total on invoice (must match amt paid)",
   `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 negated, cancelled, or otherwise terminated unsuccessfully",
   `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",
   `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",
   `isInvcComplete` BOOL DEFAULT FALSE COMMENT "NO = part of invoice is missing (lost, not filed, etc.); YES = we have complete invoice",
   `isInvcPartial`  BOOL DEFAULT FALSE COMMENT "NO = invoice is all together; YES = part of invoice is missing",
   `isInvcAbsent`   BOOL DEFAULT FALSE COMMENT "YES = no invoice paperwork from supplier (invoice is known to exist from bank records or earlier data entry)",
   `Notes`          TEXT DEFAULT NULL COMMENT "human-entered notes",
   PRIMARY KEY(`ID`)
 )
 ENGINE = MYISAM;

[edit] Notes

• TotalEstFinal has been largely unnecessary, but it may become useful again for budget planning (especially during heavy buying seasons)
• isInvc* fields are meaningless if restock package has not yet been received; one or both of WhenReceived and WhenDebited should be non-NULL in this case, but there may be some situations where we don't know either of those -- so there should probably be an isRecd flag (add this later).