From NewHaven Software Wiki

Contents 1 Overview 2 Custom Programming Specification 2.1 Covered Services 2.2 Exclusions 3 Data Conversion Specification 3.1 Covered Services (List of Converted Data) 3.1.1 Setup 3.1.2 Contact Manager/Customer Info 3.1.2.1 Fields Converted 3.1.3 Inventory 3.1.3.1 Products 3.1.3.2 Lots 3.2 Exclusions 4 Configuration (client's Responsibility) 4.1 CMS Setup 4.2 Divisions Setup 5 Challenges/Questions 5.1 Products 5.2 Warehouse/Inventory/Purchase Orders 5.3 Customers 5.4 Orders 5.5 Mail List Management 6 Report Modifications 6.1 Divisional 6.2 Non-Divisional 6.3 Custom Reports

Overview

HY, in addition to running a company and eCMS store under their own name, also runs MH's and WH's. There is a strong need to share inventory between the companies as many of the same products are sold by two or more of these companies. To manage this it currently requires duplication of products, receiving, etc. HY has requested modifications to CMS to facilitate the sharing of inventory between all companies to streamline their inventory management.

We find that all three companies are run in the same building, serviced by the same staff and their orders processed in the same warehouse. While all report to different GL codes, it is even desirable to have payment processing under the same account. In fact, as we look at the requirements, there is more similar between the companies than there is different.

Instead of maintaining separate databases for each company, as is done now, the proposed solution is to combine the three databases into one (meaning one customer list, product list, etc.) To properly categorize sales as being for the proper company, we propose to add a new field to CMS called “Division”. Detailed below is the functional specification for this request including a listing of what may optionally be done, external dependencies and remaining questions.

Custom Programming Specification

Covered Services

• Define list of Divisions in CMS Setup.
• Select Division in Order Entry (combo added to Tracking tab).
• Set Division automatically for imported orders (In CMS Setup, association between order source (store)

and (Division).

• Catalog requests – Catalog setup expanded to indicate which Division a catalog is for. When a catalog

request is created, user can choose from active catalogs and see which division each is for. Catalog request processing screen expanded to include catalog filter (allowing user to choose which catalog to print/export).

• Customer record shows which Division(s) they have ordered from – Under the History tab, the grid will

be expanded to include a Division column which will indicate which Division an order or catalog request is from.

• Shipping integration’s modified to indicate the division as the Ship-From name/address so shipping labels

can indicate the proper division.

• Print task/merge field for Division so Division name can be included in CMS forms and emails.
• Email confirmations sent by Division email account – Setup options to associate which email account for

confirmations is to be used for which Division(s).

• Enhancement to make selecting a Division required for save.

Exclusions

• Divisions are not associated to ad codes (one does not affect the other).
• Product selection in Order Entry is not limited by the order’s Division.
• A single shipper account will be used for each carrier.
• A single merchant account will be used for all Divisions.
• Reports – any reports required for modification will be contracted separately.
• Merging CMS Databases (Data Conversion) – any data conversion services will be contracted separately.

Data Conversion Specification

This is a specification of the data being converted into one CMS database.

Covered Services (List of Converted Data)

The challenges unique to converting multiple databases into one, vs converting from one system into another, should be illustrated and considered so informed decisions can be made on if/how to merge data.

For every list/table we are converting (e.g. vendors, products, ads, etc.) there are important questions that must be answered, most by the client, on how to deal with duplicates and merging. For clarity, the premise we're working on is that one database (HY?) is the primary/master and other databases are merged. For each table/list one of the following options must be selected and, for those tables being merged, this may need to be assessed for each field:

• Append only - add to the main list if not there
• Skip - If a matching record is found in the main db, don't merge
• Update - If a matching record is found, and this is where it is potentially really complex, update (e.g. three db's all with the same product code but they have different prices...which do you use? Same question for product reporting groups, web text, check-box settings, etc.)
• Replace - Replace the current record with one from database A if present, otherwise B
• Rename - If, for example, the same adcode was found in both db's, during the merge the duplicate ad code name could be renamed to something like adcode-A for database A and adcode-B for database B.
• Don't convert - In many cases we think it will be more practical for the client to start updating their primary database now to include all of the information needed from all databases (Employees is a good example, perhaps products and others)

Also, for any data being brought in from another database, we must also consider if any coding should be done to indicate the record came from X database.

The Customer list requires special mention. If the same customer exists in all three db's, how do we determine they are a match, which address do we keep, which source code, etc? This can be extremely complex. The best solution might be to simply merge all lists, just giving new customer numbers to the non-HY customers and let HY agents determine if and how dupes are merged on an individual basis.

Listed below are the various sections of CMS outlining what we propose will and won't be converted. The approach taken here was to retain as much critical information while letting go of some less critical data in the interest of reduced issues, complexity, and expense. Should the client wish to expand the list of what is converted, anything is possible, it will just require more analysis, planning, and conversion time.

Setup

Sections labeled as 'Skip' or those omitted are the responsibility of the client to manually maintain/merge. A comprehensive list is below under the heading of Configuration.

• Accounting
  • Vendors - Append

• Advertising
  • Ad Codes - Append

• Company - Skip

• Customers
  • Call Reasons - Append
  • Customer Description Codes - Append
  • Letters - Append
  • Paragraph - Append

• Fulfillment - Skip all

• General - Skip all

• Inventory
  • Price Catagories - Append
  • Products (see below)
  • Product Groups - Append
  • Adjustment Reasons - Append

• Order Entry - Skip

• Payments - Skip

• Shipping - Skip

Contact Manager/Customer Info

Append/Renumber

• Customer numbers from non-primary database(s) will be be saved as 'Alt ID' and a new sequential customer number will be assigned (we could alternately retain the original customer number and assign a prefix like MH0000123)

Fields Converted

• Primary Notes, Mailing Address Info (Honorific, First Name, Last Name, M.I., Degree, Title, Company, Flag for Address is Commercial, Mailing Address Line 3, 2, and 1, City, State, Zip/Postal, County, Country, Salutation, Email, Website, and Phone Numbers/Ext/Type), Billing Address Info (same as Mailing, except for phone number - only Billing Phone), Shipping Address Info (same as Billing, except replace with Shipping phone), Customer Description Codes, ToDo history (excluding orders and not identified by division)
• Flags/Financials
  • Flags (0-9) will be converted
  • Financials including Price Category, Terms, Discount %, Credit Limit, Owes, and Credit Available
• Exclusions - No Order History, No User-Defined Fields (Including Lead Status, Salesperson, Birthdate, Age, Gender, Expiration, and Alternate ID)

Inventory

Products

APPEND Specifics of fields/data converted except where noted:

• Product Code, Invoice Description, Tax Category, General Product Options (checkboxes)
• Price/Inventory
  • SKU (Sizes/Colors), Price (Per Price Category), Per Product Shipping/Handling (Per Price Category), Discount Percentages, Discount Percentage Date Ranges (or No Expiration Date), Discount Quantities, Discontinued Flag, Active (SKU only), UPC Number, Weight
  • Warehouse Location/Name, Stock Quantity, On B/O, On Order, Bin location, Resupply At, Restock To, In Multiples of, Minimum Order, Vendor Sku, Expected Cost, Next Delivery
    • • Vendor SKU
        • Vendor Name, Vendor SKU, Qty Per Unit, Total Unit Cost, Lead Time, Description
• Vendor
• Report/Print
  • Related Customer Descriptions - Skip
  • Product Reporting Groups - Skip
  • Custom Fields - Skip
  • Revenue and Returns account
  • Printable Product - skip
• Picture - All
• Upsells - All
• Tech Info - All
• Shipping - All
• Catalog Web

Lots

All lots from all systems including inactive. For each lot:

• Qty Received, Vendor's Lot #, Received Date, Vendor Name, Warehouse Location, Cost per Unit, Last SKU Cost

Exclusions

• Orders - This data conversion does not contain any order history (invoices, line items on orders, payments, and so on.).

Configuration (client's Responsibility)

This includes a list of items client MUST configure in the CMS database and is NOT included in the data conversion.

CMS Setup

The following sections of CMS will be managed by the client. Having all of these sections completed is a prerequisite to performing the data merge. Many of the data merges will rely on these settings being in place (e.g. Chart of Accounts must be in place before products are merged in that reference those accounts)

• PCI Administration

• Accounting
  • Chart of Accounts
  • Bank List

• Advertising
  • Catalogs
  • Magazines
  • Promotion/Ads
  • Response Curves

• Company - All sections

• Customers
  • Customer Fields
  • Information Requests

• Fulfillment - All sections

• General - All sections

• Inventory
  • Product Defaults
  • Product Tax Categories
  • Warehouses

• Order Entry - All sections

• Payments - All sections

• Shipping - All sections

Divisions Setup

These are the steps to correctly configure Divisions in your CMS Database.

• Primary setup - http://screencast.com/t/9tsUHYvtm
• Assigning to each import order source - http://screencast.com/t/nrROrlSeUcX
• Assigning to catalogs for catalog requests - http://screencast.com/t/lsohm4Na6GF

Challenges/Questions

Products

• Do you use Product Groups?
• Do you use Kits?
• Is one product in one database a simple product code (SAMPLE123) and in another database a Kit or Product Group for the same product?
• Do you use Product Images in CMS? If so, does this need to be converted?
• If the same product exists in all three databases but has different prices, or invoice descriptions, or etc...what product do we use?
• In line with the question above, is there a master database we should use as the baseline?
• Do you use Commission Status, Cost of Goods Account, or Inventory Account?
• How do we handle bin locations in the conversion if the same item (by sku/size/color) exists both in HY and MH but are currently in separate bin locations?

Warehouse/Inventory/Purchase Orders

• Do we need to convert purchase orders? If the answer is No, then inventory per product should equal Zero for On Order.
• Do we need to convert inactive lots?

Customers

• Do we merge customers? What is the criteria? Should we customer records separate and not merge? If the same customer exists in all three db's, how do we determine they are a match, which address do we keep, which source code, etc. Our inclination is to leave them as separate records and let the client merge the customers manually as you run across them. If we keep separate, how should we handle customer numbers?
• Do you need to know where the customer originated from beyond the source code? Meaning, do we need to flag each customer with what database they originated in? How do we flag these customer records (CDC, User-Defined Field, etc.)?
• Do you use Linked Billing?
• Do you need Credit/Refund History converted from Flags and Financials? Do you use Carrier Account Numbers or Purchase Card Information on the customer records (Flags and Financials)?
• Do we need to convert Spending, Frequency, First Contact, First Order, Last Order, and Last Modified from the Customers History section? This is not reliant on doing a data conversion.

Orders

If we do not convert order or purchase order history, here is a list of concerns or workarounds that the client needs to consider:

• Fulfillment - Backorders, Future Ships, On Order Purchase orders, etc. - by the time the customer goes live there can be no on-going fulfillment of orders or purchase orders in previous databases.
• Returns/Exchanges - CMS has the ability to do Unlinked Returns. Unlinked Returns are those processed in CMS without being processed against the original order. You start a return for a customer, add items to it, and save...so the -1 invoice for the order is a return. Compromises in using Unlinked Returns is you don't know the price originally paid (would have to look it up in the other database) and when you save the return it will create a lot without a cost. If this is a concern, you would need to alter the cost in Stock Manager after saving the return.
• Batch Printing - Do you batch printing? If so, do any of the filters needs to be converted?

Mail List Management

• Do you use mail list filters in CMS? Do they need to be convert over?

Report Modifications

Here are a list of reports Steve uses and categorized by Divisional (Required Division parameter) and Non-Divisional (less likely to have a Divisions Parameter):

Divisional

• Fulfillment
  • Batch Status
  • Invoices Captured and Unshipped
  • Invoices Not Printed/Shipped
• Order Processing
  • Gross Demand by Volume
  • Order Processing Summary (all reports) - This potentially could be a big change because this report is made of 26 reports.
• Sales
  • Product Sales By Volume

Non-Divisional

• Ad Codes
  • Ad Codes
• Customers
  • No reports regularly used
• Inventory
  • Inventory Analysis
  • Physical Inventory
• Money
  • Open Invoices
  • Credit Card Transaction List
  • Credit Card Capture
• Purchasing
  • Receiving
  • PO Summary

Custom Reports

I believe none of these reports need a Divisional parameter, but we should double check.

• PayPal Fulfilled Status
• Product Demand by Choice
• Amazon Fulfilled Status
• Product Gross Demand No Prg's
• Product Sales Return Analysis by Vendor