Database structure changes

• (Documentation changes only: country, country_account, country_TaxCode)
• (Documentation changes only: prentemplate)
• (PaymentcommCountrySetting table added)
• (ArticleAttribute1_Category and article_category group INT added)

• (outorder index s3_outorder replaced with new definition to improve API/GET)
• (viskanramos.showattr2_no_flg SMALLINT added)
• (propertyvalue.sort INT added)
• (country_bankaccount.eInvoice BIT added)

• (prentype.art_idDisplay INT NULL added, also added new log columns in prentype_log)
  
• (viskanramos.reportdir, constraint changed)
• (cust_pren - s1_cust_pren index changed to improve performance in subscription listings)
• (article.productUUID UNIQUEIDENTIFIER added)

• (customer_property - stamp columns added)
• (cust_article / prentemplate: constraint for every_nth)
• (CustomerClubProviderMembership.lastUpdatedAt DATETIME added)
• (New table outdelattempt_outorder)

• (customer_property - new index)
• (propertykey_value - change tracking removed)

• (store, added useLegacyCMSLogin BIT)

• article_attribute_sh: new "aggregated" columns added (to avoid heavy balances_log selections) - firstDayInStock, lastDayInStock and lastDayOutOfStock (all nullable)

• attribute tables (attributeX and attributeX_name): attrX_code column changed to NVARCHAR(100)
• (mailtosend, mailsendlog, mailfaillog: new column mailSendId added)

• related_article_attribute - mandatory BIT and matchQty BIT added (applies to addons)

• (prentype_article - removed)
• PrentypeQuantity - added (only applicable for subscription templates with main product set - prentype.art_id)
• prentype & prentype_name - linkFriendlyName added

• CustomerAddress.alternativeEmailAddress added
• (New year preparations - quite a few table scripts were updated - should not affect the system tables)

• ShippingReference: index added

• (mailattachment - documentation and constraint changes only)

• (stockhouse - documentation changes only)

• (prentype_log - log changes in new art_id column)

• (offer_log - minor fix)
• New column filterTag added to several tables (ArticleBrand, ArticleBrand_name, attribute1, attribute1_name, attribute2, attribute2_name, attribute3, attribute3_name, propertyvalue, propertyvalue_name + log tables)

• (propertykey.allowUpdateInUI + propertyvalue.mediaURL)
• (ContentCategoryDraft - index improvements)
• (deliverypoint - index improvements)

• (ContentCategoryDraft - index improvements)
• (offer_log - added missing filterByPricelist  columns)

• (article_attribute_master, added single column identifier stockKeepingUnitRelationId)
• (related_article_attribute, added single column identifier stockKeepingUnitRelationId)

• ticket: new column helpdeskAccountId INT 

• (viskanuser - new unique index on email_1)

• (outdelivery - documentation improvements only)

• (New unique column customerUUID added to customer - UNIQUEIDENTIFIER)
• (New tables for new permissions handling: UserGroupReport, UserGroupReturnReason, UserGroupRole, UserGroupWarehouse, UserGroupWebStore)
• (New columns for new permissions handling: viskanuser.userId, viskanusergroup.groupName)
• (wayofdelivery.split_parcels_limit data type changed to NUMERIC(19,3) to allow specifying "parts" of a kilogram)
• More support for custom data (new table for shipments: OutdeliveryCustomFieldValue)
• More support for custom data (new table for returns: ReturnCustomFieldValue)
• (New filter setting on offers: offer.filterByPriceList BIT)
• (Documentation improvements only: campaign.club_id_price)

• (Table documentation of table cust_article improved)
• (New unique column storeId (UNIQUEIDENTIFIER) added to store table)
• (New column lastModified (DATETIME) added to campaign table - will be changed if any offer related table is updated)

• (Removed table ETMSync)
• (Added club.clubCode - to be used as an API identifier for price lists)
• (Adding contraint to prentype, to make sure that club_id does not contain -1 values)

• (New columns added to outdel_report - filenamePattern, filepathPattern, reportParameterString)

• (Added ServiceProvider.productProvider BIT)

• New settings added to stockhouse table (purchasingAllowed, locationsAllowed)
• (Removed table viskanusergroup_installation)
• (Removed column visinstall_id from viskanusergroup_object / viskanusergroup_objectLog)

• (New table CustomerArticleInterestGroup)
• (CustomerArticleInterest.customerArticleInterestGroupId UNIQUEIDENTIFIER added)
• (outdel_parcel.s2_outdel_parcel index improved)
• (Removed table CashregisterZReport)

• (new table CustomerForgetQueue)

• (Clean up of not used permissions handling): supplier.visobj_id removed
• (Clean up of not used permissions handling): propertykey.visobj_id removed

• (create_flg/change_flg/delete_flg removed from viskanuser_object)
• (category.categoryId UNIQUEIDENTIFIER)

• (article_comment table removed)

(B2B support):

• New table CustomerAddress

(B2B support):

• customer.companyUser (BIT NOT NULL DEFAULT 0)
• purchase.cust_idPlacedOrderFor + cust_idPlacedOrderBy (INT NULL)
• New table CompanyUser

• (PriceHistory.changeDirection BIT NULL)

• (personalization_text (several tables) NVARCHAR(512) -> NVARCHAR(MAX))
• (article_attribute_sh.estimatedShippingDate DATE NULL)

• store.entry_id INTEGER NOT NULL DEFAULT -1

Changes for new tax handling (US Sales Tax project):

• article (+ article_log).taxCodeId INT NULL
  

• country.serviceProviderConfigIdForTaxRates INT NULL, shippingFeeWeighed BIT DEFAULT 1, paymentFeeWeighed BIT DEFAULT 1, serviceFeeWeighed BIT DEFAULT 1, pricesIncludingTax BIT DEFAULT 1

• New table country_TaxCode
  

• cust_article (+ cust_article_log).discountExcludingTax NUMERIC(28,8)
  

• cust_pren (+ cust_pren_log).shippingTaxRate NUMERIC(9,8) NULL, paymentFeeTaxRate NUMERIC(9,8), shippingFeeExcludingTax NUMERIC(19,2), paymentFeeExcludingTax (19,2)

• outdelivery.shippingFeeExcludingTax NUMERIC(9,8), paymentFeeExcludingTax NUMERIC(9,8), serviceFeeExcludingTax NUMERIC(9,8) NULL
  

• (outdelivery_process + outdelivery_process_log.process_status_extra VARCHAR(100) -> NVARCHAR(1024))

• outdeliveryrow.discountExcludingTax NUMERIC(19,2)
  

• outorder.shippingTaxRate NUMERIC(9,8) NULL, paymentFeeTaxRate NUMERIC(9,8) NULL, serviceFeeTaxRate NUMERIC(9,8) NULL, shippingFeeExcludingTax NUMERIC(9,8), paymentFeeExcludingTax NUMERIC(9,8), serviceFeeExcludingTax NUMERIC(9,8) NULL
  

• outorderrow.discountExcludingTax NUMERIC(19,2)
  

• return_del.amountClaimExcludingTax NUMERIC(19,2) NULL
  

• return_delhead.shippingFeeExcludingTax NUMERIC(19,2), paymentFeeExcludingTax NUMERIC(19,2), serviceFeeExcludingTax NUMERIC(19,2), amountReturnedExcludingTax NUMERIC(19,2)
  

• (return_delhead_process.process_status_extra NVARCHAR(1024) NULL)

• (ServiceProvider.taxRateProvider BIT DEFAULT 0)

• New table TaxCode

• New table TaxCodeExternal
  

• (viskanramos: taxCodeIdForShippingFee INT NULL, taxCodeIdForPaymentFee INT NULL, taxCodeIdForServiceFee INT NULL)
  

• (propertykey.sendToShippingProvider BIT DEFAULT 0)
  

• purchase.webStore_id INT NULL - holds the id of the web store (store_id could contain a reference to a physical store)
• (media.dateTaken DATETIME NULL)

• (stockhouse.external_packing_flg removed)

• (CrossdockingStockhouse removed)
• (stockhouse.sup_idCrossdocking removed)
• (viskanramos_indexrow removed)
• (indexrow_log removed)

• (stockhouse.managedByExternalSystem/balancesUpdatedByExternalSystem added)
• paymenttransactiongroup.paymentMethodType NVARCHAR(512) - actual payment method (Adyen)

• (New table SubscriptionPaymentAuthorizationHistory to handle recurring payments)
• (purchase.additionalOrderText NVARCHAR(1000) -> NVARCHAR(MAX))

• (wayofdelivery/wayofdelivery_name.wdel_code NVARCHAR(30) -> NVARCHAR(100))

• (return_log.outpayhead_id INT)
• entry.deactivationStdnote_id INT - support in the new back office to have different standard note for a specific method of arrival when cancellation a subscription
• (New table InorderrowCustomFieldValue)

• notes.noteId INT (new single column PK)
  

• New table WeeklySubscriptionDeliveryDayOffset (only applicable for the weekly subscription type)

• New table returnreason_entry (allow filtering on return reasons by order's method of arrival - InStore editor only)

• (obsolete tables contact / customer_contact removed)

• (viskanramos.advancedSubscriptions BIT DEFAULT 0: allow using advanced subscription settings in the new backoffice)

• (obsolete tables matter / matter_customer / measure removed)

• (article_attribute_media.sort INT DEFAULT 0: add the possibility to sort sku media)

• (article_attribute_sh.skuStockhouseId INT IDENTITY: new unique id - not PK for now)
  

• (outdel_config & outdelattempt.onlyVirtualGiftCardsAndVouchers BIT DEFAULT 0: for Gift cards - make sure digital gift cards are "delivered" separately)
  

• (Gift card/Diggecard integration: added ServiceProvider.voucherProvider BIT, added table Voucher, added article.serviceProviderConfigId INT NULL, ServiceProviderParam.secret BIT NOT NULL)

• (CMS: ContentCategoryDraft & ContentCategoryHead & ContentCategoryHeadDraft & ContentCategoryHeadHistory

  & ContentCategoryHeadLog.categoryName changed length from 30 to 120)

• HelpDesk v2: HelpdeskLabel.cat_id (INT NULL) and ticket_HelpdeskLabel added (temporary solution which will be removed when HelpDesk v1 is removed) - note! Released in some production databases.
  

• dupcontrolsetting
• this table contains settings for matching customers and finding customers duplicates
• an obsolete column for remembering processed customer ids is now removed: cust_id_prev2

• (HelpDeskLabel.labelName changed length from 20 to 30)
  

• interval_type
• subscription tables originally had support for having daily, weekly and monthly intervals. Only daily intervals are actually used/supported. The old interval_type will be replaced with something else in the near future. For now, the old ones have been removed
• cust_pren (subscriptions): interval_type
• cust_pren_log (log of subscription changes): interval_type_new, interval_type_old
• prentype (subscription templates): interval_type
• prentype_log (log of subscription template changes): interval_type_new, interval_type_old

• note
• the notes table contains user and system generated notes on customers, conversations, subscriptions, orders, deliveries and returns
• new columns have been added to simplify selections and improve performance
• cust_id INT NULL
• cust_pren_id INT NULL
• outorder INT NULL
• del_no INT NULL
• return_id INT NULL
• conversationId VARCHAR(36) NULL
• the upgrade will populate the new columns with values.
• ticket
• the ticket table contains HelpDesk ticket information and is used by HelpDesk v1. HelpDesk v2 populates the ticket table for backward compatibility reasons (will be removed)
• new column has been added to better handle conversation (only applicable for HelpDesk v2): conversationId VARCHAR(36) NULL
• outorder_external
• contains references to external (non Viskan) systems
• obsolete columns have been removed
• shopcart_ref
• requested_delivery_date
• a new column has been added to store arbitrary references: externalOrderReference1 NVARCHAR(100) NULL