View source for CMS Version 9.0 Database Changes

&nbsp;CMS Version 9.0 introduces internal changes to database structures that need to be considered when working on issues and reports.<br> 

'''Table Changes:'''<br> 

=== AD<br>  ===

&nbsp; AD_ID int new field &gt; converted over from deprecated AD_NUMBER<br>&nbsp; {DROP} AD_NUMBER<br>&nbsp; {DROP} AD_CATALOG<br> <br> 

=== AD_PROD  ===

AD_ID int new field &gt;&nbsp;converted over from deprecated AD_NUMBER<br>&nbsp; {DROP} AD_NUMBER 

=== ADS  ===

&nbsp; CATALOG_ID int new field<br> 

=== CATALOGS<br>  ===

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

=== CUSTHIST<br>  ===

&nbsp; Add NOTES_UNFORMATTED long varchar<br>&nbsp; DIVISION_ID new field int<br>&nbsp; HISTTYPE modified to int 

=== DIVISIONS<br>  ===

&nbsp; New Table:&nbsp;Holds base company division information (see OrderHdr, Catalog)&nbsp; <br>&nbsp; DIVISION_ID int<br>&nbsp; DIVISION_NAME varchar(50)<br>&nbsp; DIVISION_SHORT_NAME varchar(4)<br>&nbsp; DIVISION_DESC varchar(50)<br>&nbsp; DIVISION_EMAILID int<br> 

=== ECMS_LOG<br>  ===

&nbsp; ORDERSRC_ID int new field<br> 

=== ECMS_STORES - {DROP TABLE}<br>  ===

=== ITEMSORD<br>  ===

-LINE_ID converted to Integer<br>KITPARENT_ITEMID int<br>{DROP} KIT_PARENT<br>ONEITEM_ID modified to int<br>-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.<br> 

=== ORDER_XML  ===

&nbsp; BAD_DIVISION bit new field<br>&nbsp; SKIPDISCOUNT bit new field<br>&nbsp; SKIPNOTES bit new field 

=== ORDERHDR<br>  ===

&nbsp; DIVISION_ID int new field see DIVISIONS

ARRIVAL_DT_DFLT DateTime

=== ORDERSRC Order Sources  ===

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

=== PACKAGE  ===

&nbsp; STATUS_UPDATE bit 

=== PLUGIN_OPTIONS  ===

new table<br>&nbsp; PLUGIN_OPTIONS_ID int<br>&nbsp; ORDERSRC_ID int<br>&nbsp; NAME varchar(500)<br>&nbsp; VALUE varchar(500) <br>&nbsp; ENCRYPT bit<br>&nbsp; PLUGIN_OPTIONS_TYPE_ID int<br> 

=== '''PFORMDET '''Print '''Forms'''&nbsp; <br>  ===

&nbsp; - TASK_TYPE changed to varchar(2)<br> 

=== '''PRODUCT_GROUPS'''  ===

&nbsp; PRODUCT_GROUP_ID renamed to GROUP_NAME<br>&nbsp; PRODUCT_GROUP_ID int autoinc&nbsp; new field 

=== PRODUCT_GROUPS_DET  ===

&nbsp; PRODUCT_GROUP_ID renamed to GROUP_NAME<br>&nbsp; PRODUCT_GROUP_ID int new field&nbsp; <br> 

=== PRODUCTS<br>  ===

&nbsp; ECMS {DROP}<br> 

=== [[RECIPIENT]] <br>  ===

&nbsp; New table&nbsp;:&nbsp;Allows permanent storage of Recipients independent of orders.<br>&nbsp; RECIPIENT_ID int<br>&nbsp; ADDRESS_ID&nbsp; int<br>&nbsp; CUST_ID&nbsp; char(10)<br>&nbsp; USER_LABEL varchar(50)<br>&nbsp; <br>

=== RECIPIENT_ORDER<br>  ===

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

SHIPPING_CHARGE money

<br>

=== 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.

<br> 

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

=== SKU<br>  ===

&nbsp; ECMS {DROP}<br> 

=== SKU_ORDERSRC<br>  ===

&nbsp; new table&nbsp; relates products and ecms order sources<br>&nbsp; SKU_ORDERSRC_ID int<br>&nbsp; ORDERSRC_ID int<br>&nbsp; SKU_ID varchar(6)<br>&nbsp; <br> 

=== SMALLSET  ===

- SMALL_ID expanded to varchar(15) 

=== <br> TODO<br>  ===

&nbsp; CATALOG_ID int new field see Catalog changes 

'''Functions and procedures:'''<br> 

&nbsp; new function:&nbsp;fGetCOGSForOrder<br>&nbsp; new function: fGetPackageCostsForOrder<br><br>dropped: <br><br> 

=== ZONEDAYS  ===

Contains user editable data relating SHIPPERS.SHIP_CODE to transit times per zone. &nbsp;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) 

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