CMS Version 9.0 Database Changes

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

Table Changes:

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

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