Difference between revisions of "VbzCart/tables/rstk rcd"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
m (fixed table name in link)
(added Supplier and Warehouse fields)
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
[[category:VbzCart/tables]]
+
* '''Purpose''': Master record for a restock (wholesale) shipment received
−
* '''Created''': 2008-11-07
+
* '''Related''':
−
* Master record for a restock shipment received; contents are in [[../rstk_recd_line]]
+
** {{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==
−
<section begin=sql /><mysql>CREATE TABLE `rstk_recd` (
+
<mysql>CREATE TABLE `rstk_rcd` (
 
   `ID`            INT NOT NULL AUTO_INCREMENT,
 
   `ID`            INT NOT NULL AUTO_INCREMENT,
−
   `ID_Restock`    INT COMMENT "core_restocks.ID of restock this shipment fulfills",
+
   `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...)",
−
/* eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web */
+
  `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 by us",
+
   `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated from contents of received restock",
−
  `TotalEstFinal`  DECIMAL(9,2) COMMENT "estimate of final charge",
+
/* from supplier invoice */
−
  /* amounts 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",
−
/* Final = Merch + Ship + Adj */
 
 
   `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 24: Line 47:
 
  )
 
  )
 
  ENGINE = MYISAM;</mysql>
 
  ENGINE = MYISAM;</mysql>
−
<section end=sql />
 

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.

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>
Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/rstk_rcd&oldid=21452"