CMS Version 9.0 Database Changes

From NewHaven Software Wiki

 CMS Version 9.0 introduces internal changes to database structures that need to be considered when working on issues and reports.

Table Changes:

Contents 1 AD 2 AD_PROD 3 ADS 4 CATALOGS 5 CUSTHIST 6 DIVISIONS 7 ECMS_LOG 8 ECMS_STORES - {DROP TABLE} 9 ITEMSORD 10 ORDER_XML 11 ORDERHDR 12 ORDERSRC Order Sources 13 PACKAGE 14 PLUGIN_OPTIONS 15 PFORMDET Print Forms  16 PRODUCT_GROUPS 17 PRODUCT_GROUPS_DET 18 PRODUCTS 19 RECIPIENT 20 RECIPIENT_ORDER 21 SHIPPERS 22 SKU 23 SKU_ORDERSRC 24 SMALLSET 25 TODO 26 ZONEDAYS

AD

  AD_ID int new field > converted over from deprecated AD_NUMBER
  {DROP} AD_NUMBER
  {DROP} AD_CATALOG

AD_PROD

AD_ID int new field > converted over from deprecated AD_NUMBER
  {DROP} AD_NUMBER

ADS

  CATALOG_ID int new field

CATALOGS

  Catalogs, Ad, Ads, Todo, Ordersrc have undergone changes to reorganize their links
  CATALOG_ID int new field replaces CATALOG_NUMBER as new primary key
  {DROP >> CATALOG_NUMBER}
  DIVISION_ID int new field
  DIV_PRIMARY_CAT int new field - primary catalog for a Division
  ACTIVE bit new field

CUSTHIST

  Add NOTES_UNFORMATTED long varchar
  DIVISION_ID new field int
  HISTTYPE modified to int

DIVISIONS

  New Table: Holds base company division information (see OrderHdr, Catalog) 
  DIVISION_ID int
  DIVISION_NAME varchar(50)
  DIVISION_SHORT_NAME varchar(4)
  DIVISION_DESC varchar(50)
  DIVISION_EMAILID int

ECMS_LOG

  ORDERSRC_ID int new field

ECMS_STORES - {DROP TABLE}

ITEMSORD

-LINE_ID converted to Integer
KITPARENT_ITEMID int
{DROP} KIT_PARENT
ONEITEM_ID modified to int
-The Kit Parent/Child relationship has changed. KITPARENT_ITEMID is a new integer field that holds the ItemID of the Parent item of the kit. This relationship was previously expressed using the KITPARENT string field, which held a concatenation of the Invoice_ID + Line_ID of the Kit Parent.

ORDER_XML

  BAD_DIVISION bit new field
  SKIPDISCOUNT bit new field
  SKIPNOTES bit new field

ORDERHDR

  DIVISION_ID int new field see DIVISIONS

ARRIVAL_DT_DFLT DateTime

ORDERSRC Order Sources

  Ordersrc table change:  ID field name changed to ORDERSRC_ID
  CATALOG_ID int new field
  DIVISION_ID int new field
  PLUGIN_NAME varchar(500) new field
  {DROP} WEBSERVICE_ID {DROP}
  AUTO_PRODUCT_EXPORT bit
  SKIPDISCOUNT bit new field
  SKIPNOTES bit new field

PACKAGE

  STATUS_UPDATE bit

PLUGIN_OPTIONS

new table
  PLUGIN_OPTIONS_ID int
  ORDERSRC_ID int
  NAME varchar(500)
  VALUE varchar(500)
  ENCRYPT bit
  PLUGIN_OPTIONS_TYPE_ID int

PFORMDET Print Forms 

  - TASK_TYPE changed to varchar(2)

PRODUCT_GROUPS

  PRODUCT_GROUP_ID renamed to GROUP_NAME
  PRODUCT_GROUP_ID int autoinc  new field

PRODUCT_GROUPS_DET

  PRODUCT_GROUP_ID renamed to GROUP_NAME
  PRODUCT_GROUP_ID int new field 

PRODUCTS

  ECMS {DROP}

RECIPIENT

  New table : Allows permanent storage of Recipients independent of orders.
  RECIPIENT_ID int
  ADDRESS_ID  int
  CUST_ID  char(10)
  USER_LABEL varchar(50)
 

RECIPIENT_ORDER

  New table : Holds recipient detail for a specific order 'Correlation table linking Orders and Recipients'
  RECIPIENT_ID int     See ItemsOrd table
  ORDER_ID char(9)
  SEQUENCE smallint
  FULFILL_DT_DFLT date
  ARRIVAL_DT_DFLT date
  SHIPMETHID int
  RECIP_NOTE long varchar

SHIPPING_CHARGE money

SHIPPERS

DAYS Integer

• Used by CMS to determine transit times for shipments.
• If greater than zero, represents transit days for delivery (all air/next day methods)
• If equal to zero, represents a method that requires zone/transit days information from the ZONEDAYS table in order to determine transit time
• If null, represents a method not considered by CMS in transit time calculations.

Shippers table deployment is now done via data table.  The table used is called shippers.dat.

SKU

  ECMS {DROP}

SKU_ORDERSRC

  new table  relates products and ecms order sources
  SKU_ORDERSRC_ID int
  ORDERSRC_ID int
  SKU_ID varchar(6)
 

SMALLSET

- SMALL_ID expanded to varchar(15)

TODO

  CATALOG_ID int new field see Catalog changes

Functions and procedures:

  new function: fGetCOGSForOrder
  new function: fGetPackageCostsForOrder

dropped:

ZONEDAYS

Contains user editable data relating SHIPPERS.SHIP_CODE to transit times per zone.  Used in conjunction with SHIPPERS.DAYS to determine transit times in CMS.

SHIP_CODE VARCHAR(3)

ZONEID VARCHAR(5)

DAYS INTEGER

PRIMARY KEY(SHIP_CODE,ZONEID,DAYS)

Alternate Search Terms: CMS 9.0 Database Changes, v9 changes, v9.0 table changes