Examples by Table

GL Accounts

ACCT is the source in CMD but it's not high quality data.

Company (Divisions)

COMP will provide company list. This might be separate databases or separate divisions within a database.

Plants

Locations and plants are similar but different. Multiple plants may have on location. Prices and items are normally setup by location. For example you may have location 01 with plants 1A and 1B. The product tables (IMST/ILOC) would only have a reference to location 01 where Keystone will have 1A and 1B.

• LOCN: Locations (Similar to Plant)
• PLNT: Plant

Customer Class

• SANL:

Salespeople and Drivers

• EMPL: Employee - Sales and Drivers

Taxes

This is somewhat similar to Keystone setup, but not quite the same. Terminlogy is quite different: Command - Keystone Tax Code - Tax Authority Tax Authority - Tax Level (e.g. 1=state, 2=County, 3=City)

CMD is always setup multi-level whereas Keystone customer often want to flatten the taxes to single level. This is a key up-front question explaining the pros/cons of each direction.

• TAXC: Tax (Similar to Keystone Tax Auth)
• TAXJ: Tax Structure
• TAXL: Tax Locality (not 1 to 1 with Keystone)

Price Group Headers

You need to import the Price Group (Category in CMD) before customers. The actual prices after products are setup. PRCC

Dispatch Useage Class

• USGE: Useage

Customer

• CUST: Customer

State needs to be mapped... <pre) CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ',)

  WHEN 'NEBRASK' THEN 'NE'
  WHEN 'NEWYORK' THEN 'NY'
  WHEN 'NEWJERSEY' THEN 'NJ'
  ...
  WHEN 'PENNSYLVAN' THEN 'PA'
  WHEN 'SOUTHCARO' THEN 'SC'
  WHEN 'TENNESSEE' THEN 'TN'
ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,

• ICAT: Item (Product)
• IMST: Products
• ILOC: Product by Location (Similar to Plant)
• ICST: Component Structure

with sac (clsno,descr) as
	(
	select 
	case ltrim(rtrim(sales_anl_code)) 
	when 'NSF' THEN 'NS'
	when 'CNA' THEN 'CN'
	when 'C27' THEN '27'
	when 'C29' THEN '29'
	when 'C34' THEN '34'
	when 'C53' THEN '53'
	else ltrim(rtrim(sales_anl_code))  end
	"class_no",
	ltrim(rtrim(descry)) description
	from sanl
	)
select  
concat('
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  VALUES 
( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y''    );
'),
ltrim(rtrim(clsno)) "class_no",
ltrim(rtrim(descry)) description,  * 
from sac

• TRMS: Terms
• TRUC: Trucks
• UOMS: Unit of Meausure

Prices

• CPRD: Customer Prices

• IPRC: Prices - by Category (Group)

Jobs

The number one challenge is getting useful codes from CMD into Keystone. CMD Proj codes tend to be short and cryptic to end customer. Proj Names are very long. the typical process is to import the code as job_po_loc (can also into batch_job_code) and invoice description comes from proj_name. Then after import, we udpate job_po_loc from invoice desc the help of the customer.

• PROJ: Project
• PRJP: Project Prices

Conctacts

• CCON: Contact Connection
• CTCT: Contact

A/R Invoice

AR may or may not come from Command system. This needs to be researched.

• Invoice Numbers may be numeric or Alpha. When Alpha - Invoice Number=0, JobPoLoc = Alpha Inovice Number.
• Create Preview outputs - DataScopeX is good for this.
• Establish a Hard Cutoff Date

ARTB

• Curr_Bal_amt key field