CMS Version 9.0 Database Changes - Revision history

Russ horton at 18:29, 13 December 2012

2012-12-13T18:29:20Z

← Older revision		Revision as of 18:29, 13 December 2012
Line 18:		Line 18:

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

	~~=== CHARGEINFO ===~~

	~~  PROCESSDATA varchar(50) (new field, required for MPS processing)<br>~~

	~~  ORIG_AUTH Money (new field, holds original auth amount)~~

	~~  PARTIAL_AUTH Bit (boolean, flag for when partial auth has taken place)~~

	=== CUSTHIST<br> ===		=== CUSTHIST<br> ===

Russ horton: /* RECIPIENT */

2012-08-23T22:10:03Z

RECIPIENT

← Older revision		Revision as of 22:10, 23 August 2012
Line 85:		Line 85:
	=== [[RECIPIENT]] <br> ===		=== [[RECIPIENT]] <br> ===

	New table : Allows permanent storage of Recipients independent of orders.<br>  RECIPIENT_ID int<br>  ADDRESS_ID  int<br>  CUST_ID  char(10)<br>  USER_LABEL varchar(50)<br>  ~~<strike>RECIP_NOTE long varchar</strike> {moved RT35802}~~<br>		New table : Allows permanent storage of Recipients independent of orders.<br>  RECIPIENT_ID int<br>  ADDRESS_ID  int<br>  CUST_ID  char(10)<br>  USER_LABEL varchar(50)<br>  <br>

	=== RECIPIENT_ORDER<br> ===		=== RECIPIENT_ORDER<br> ===

Russ horton at 22:09, 23 August 2012

2012-08-23T22:09:28Z

← Older revision		Revision as of 22:09, 23 August 2012
Line 51:		Line 51:
	=== ORDERHDR<br> ===		=== ORDERHDR<br> ===

	DIVISION_ID int new field see DIVISIONS~~<br>~~		DIVISION_ID int new field see DIVISIONS

			ARRIVAL_DT_DFLT DateTime

	=== ORDERSRC Order Sources ===		=== ORDERSRC Order Sources ===
Line 81:		Line 83:
	ECMS {DROP}<br>		ECMS {DROP}<br>

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

	New table : Allows permanent storage of Recipients independent of orders.<br>  RECIPIENT_ID int<br>  ADDRESS_ID  int<br>  CUST_ID  char(10)<br>  USER_LABEL varchar(50)<br>  RECIP_NOTE long varchar<br>		New table : Allows permanent storage of Recipients independent of orders.<br>  RECIPIENT_ID int<br>  ADDRESS_ID  int<br>  CUST_ID  char(10)<br>  USER_LABEL varchar(50)<br>  <strike>RECIP_NOTE long varchar</strike> {moved RT35802}<br>

	=== RECIPIENT_ORDER<br> ===		=== RECIPIENT_ORDER<br> ===

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

			SHIPPING_CHARGE money

	<br>		<br>

	=== SHIPPERS ===		=== SHIPPERS ===

Russ horton: /* RECIPIENT */

2012-07-30T23:17:41Z

RECIPIENT

← Older revision		Revision as of 23:17, 30 July 2012
Line 81:		Line 81:
	ECMS {DROP}<br>		ECMS {DROP}<br>

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

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

	=== RECIPIENT_ORDER<br> ===		=== RECIPIENT_ORDER<br> ===

Russ horton: Created page with ' CMS Version 9.0 introduces internal changes to database structures that need to be considered when working on issues and reports.
'''Table Changes:'''
=== AD
2012-07-30T23:17:05Z

Created page with ' CMS Version 9.0 introduces internal changes to database structures that need to be considered when working on issues and reports. '''Table Changes:''' === AD<br…'

New page
 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 

=== CHARGEINFO ===

  PROCESSDATA varchar(50) (new field, required for MPS processing) 

  ORIG_AUTH Money (new field, holds original auth amount)

  PARTIAL_AUTH Bit (boolean, flag for when partial auth has taken place)

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

=== 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)   RECIP_NOTE long varchar 

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

 

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