Difference between revisions of "VbzCart/tables/ord pkgs"

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
(extracted from VbzCart/tables)
 
(ChgItemSale; minor cleanup)
 
(3 intermediate revisions by the same user not shown)
Line 2: Line 2:
 
* '''Purpose''': Packages shipped in response to a customer order ({{vbzcart/table|core_orders}})
 
* '''Purpose''': Packages shipped in response to a customer order ({{vbzcart/table|core_orders}})
 
* '''Refers to''': {{vbzcart/table|core_orders}} (parent), {{vbzcart/table|ord_shipmt}}
 
* '''Refers to''': {{vbzcart/table|core_orders}} (parent), {{vbzcart/table|ord_shipmt}}
−
* '''Notes''':
+
* '''Used by''': {{vbzcart|table|ord_pkg_lines}} (child)
 +
* '''Fields''':
 +
** '''WhenFinished''': I'm going to go out on a limb and define this as the time when the package charges are added up and created as {{vbzcart|table|ord_trxact|order transaction}} records.
 +
** '''WhenChecked''': When the package was marked as ready (checked off) for inclusion in the shipment
 +
*** This field may be less useful in the web-based UI than it was in the Access/desktop UI.
 +
** '''WhenVoided''': We're not deleting packages anymore. Instead, we mark an emptied, unsent package as "VOID", and log it in the container record. Voided package records can be re-used if they aren't assigned to a closed shipment; log the un-voiding prior to re-use. (Should there be a "WhenLocked" field to represent the shipment being closed? <s>Maybe WhenFinished does this...</s>) A package should never be voided without first zeroing stock in all line items.
 +
** '''ChgShipItm''': total charged for per-item shipping
 +
** '''ChgShipPkg''': amount charged for per-package shipping
 +
** '''ChgItmSale''': total charged price of all items in this package
 +
* '''Future''':
 
** The field "ArrivalNotes" should be added to the message log; leaving it out of the migration for this table.
 
** The field "ArrivalNotes" should be added to the message log; leaving it out of the migration for this table.
−
** '''WhenVoided''': We're not deleting packages anymore. Instead, we mark an emptied, unsent package as "VOID", and log it in the container record. Voided package records can be re-used if they aren't assigned to a closed shipment; log the un-voiding prior to re-use. (Should there be a "WhenLocked" field to represent the shipment being closed? Maybe WhenFinished does this...) A package should never be voided without first zeroing stock in all line items.
+
* '''History''':
 +
** '''2010-10-19''' Added '''ChgShipItm''', '''ChgShipPkg''' to complete the record of what was charged for the package. (This should make it a lot easier to estimate shipping rates, though that's not why I added them. I added them from a vague sense that they should be there.)
 +
** '''2014-05-25''' Added '''ChgItmSale''' to complete the record of how each package contributes to the total.
 
==SQL==
 
==SQL==
−
<section begin=sql /><mysql>CREATE TABLE `ord_pkgs` (
+
<mysql>CREATE TABLE `ord_pkgs` (
 
   `ID`            INT NOT NULL AUTO_INCREMENT,
 
   `ID`            INT NOT NULL AUTO_INCREMENT,
 
   `Seq`            INT COMMENT "number used to make user-friendly pkg #",
 
   `Seq`            INT COMMENT "number used to make user-friendly pkg #",
 
   `ID_Order`      INT NOT NULL COMMENT "core_orders.ID",
 
   `ID_Order`      INT NOT NULL COMMENT "core_orders.ID",
 
   `WhenStarted`    DATETIME COMMENT "when package record was created",
 
   `WhenStarted`    DATETIME COMMENT "when package record was created",
−
   `WhenFinished`  DATETIME, /* need to check code for actual purpose of field */
+
   `WhenFinished`  DATETIME,
−
   `WhenChecked`    DATETIME, /* need to check code for actual purpose of field */
+
   `WhenChecked`    DATETIME,
 
   `WhenVoided`    DATETIME COMMENT "when package was emptied; can be reused later",
 
   `WhenVoided`    DATETIME COMMENT "when package was emptied; can be reused later",
 
   `isReturn`      BOOL COMMENT "TRUE = this package is being returned, not shipped out",
 
   `isReturn`      BOOL COMMENT "TRUE = this package is being returned, not shipped out",
 
   `ID_Shipment`    INT COMMENT "ord_shipmt.ID",
 
   `ID_Shipment`    INT COMMENT "ord_shipmt.ID",
−
   `ShipCost`      DECIMAL(9,2) COMMENT "cost of shipping (postage)",
+
  `ChgShipItm`    DECIMAL(9,2) DEFAULT NULL COMMENT "total amount charged for per-item shipping",
−
   `PkgCost`        DECIMAL(9,2) COMMENT "cost of packaging / insurance",
+
  `ChgShipPkg`    DECIMAL(9,2) DEFAULT NULL COMMENT "amount charged for per-package shipping",
 +
  `ChgItmSale`    DECIMAL(9,2) DEFAULT NULL COMMENT "total sale amount of all charged items in package",
 +
   `ShipCost`      DECIMAL(9,2) DEFAULT NULL COMMENT "actual cost of shipping (postage)",
 +
   `PkgCost`        DECIMAL(9,2) DEFAULT NULL COMMENT "actual cost of packaging / insurance",
 
   `ShipPounds`    FLOAT COMMENT "shipping weight in pounds (whole or fractional)",
 
   `ShipPounds`    FLOAT COMMENT "shipping weight in pounds (whole or fractional)",
 
   `ShipOunces`    FLOAT COMMENT "shipping weight in ounces, less ShipPounds",
 
   `ShipOunces`    FLOAT COMMENT "shipping weight in ounces, less ShipPounds",
Line 26: Line 40:
 
)
 
)
 
ENGINE = MYISAM;</mysql>
 
ENGINE = MYISAM;</mysql>
−
<section end=sql />
 

Latest revision as of 16:21, 25 May 2014

About

  • Purpose: Packages shipped in response to a customer order (
  1. REDIRECT Template:l/vc/table)
  • Refers to: core_orders (parent), ord_shipmt
  • Used by: ord_pkg_lines (child)
  • Fields:
    • WhenFinished: I'm going to go out on a limb and define this as the time when the package charges are added up and created as order transaction records.
    • WhenChecked: When the package was marked as ready (checked off) for inclusion in the shipment
      • This field may be less useful in the web-based UI than it was in the Access/desktop UI.
    • WhenVoided: We're not deleting packages anymore. Instead, we mark an emptied, unsent package as "VOID", and log it in the container record. Voided package records can be re-used if they aren't assigned to a closed shipment; log the un-voiding prior to re-use. (Should there be a "WhenLocked" field to represent the shipment being closed? Maybe WhenFinished does this...) A package should never be voided without first zeroing stock in all line items.
    • ChgShipItm: total charged for per-item shipping
    • ChgShipPkg: amount charged for per-package shipping
    • ChgItmSale: total charged price of all items in this package
  • Future:
    • The field "ArrivalNotes" should be added to the message log; leaving it out of the migration for this table.
  • History:
    • 2010-10-19 Added ChgShipItm, ChgShipPkg to complete the record of what was charged for the package. (This should make it a lot easier to estimate shipping rates, though that's not why I added them. I added them from a vague sense that they should be there.)
    • 2014-05-25 Added ChgItmSale to complete the record of how each package contributes to the total.

SQL

<mysql>CREATE TABLE `ord_pkgs` ( 

 `ID`             INT NOT NULL AUTO_INCREMENT,
 `Seq`            INT COMMENT "number used to make user-friendly pkg #",
 `ID_Order`       INT NOT NULL COMMENT "core_orders.ID",
 `WhenStarted`    DATETIME COMMENT "when package record was created",
 `WhenFinished`   DATETIME,
 `WhenChecked`    DATETIME,
 `WhenVoided`     DATETIME COMMENT "when package was emptied; can be reused later",
 `isReturn`       BOOL COMMENT "TRUE = this package is being returned, not shipped out",
 `ID_Shipment`    INT COMMENT "ord_shipmt.ID",
 `ChgShipItm`     DECIMAL(9,2) DEFAULT NULL COMMENT "total amount charged for per-item shipping",
 `ChgShipPkg`     DECIMAL(9,2) DEFAULT NULL COMMENT "amount charged for per-package shipping",
 `ChgItmSale`     DECIMAL(9,2) DEFAULT NULL COMMENT "total sale amount of all charged items in package",
 `ShipCost`       DECIMAL(9,2) DEFAULT NULL COMMENT "actual cost of shipping (postage)",
 `PkgCost`        DECIMAL(9,2) DEFAULT NULL COMMENT "actual cost of packaging / insurance",
 `ShipPounds`     FLOAT COMMENT "shipping weight in pounds (whole or fractional)",
 `ShipOunces`     FLOAT COMMENT "shipping weight in ounces, less ShipPounds",
 `ShipNotes`      VARCHAR(255) COMMENT "human-entered notes about this pkg",
 `ShipTracking`   VARCHAR(127) COMMENT "shipper's tracking number for pkg",
 `WhenArrived`    DATETIME COMMENT "when arrived at customer, if known",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/ord_pkgs&oldid=20035"