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

Challenges with Job Import

• Project Code and Name vs JOB PO Loc. Code is very cryptic. Name is very long. Best solution: - BatchJobCode as ProjCode. Import Proj Name into Invoice Desc..Import proj code into JobPoLoc but update with a cleaned up verion of the code.
• Plant number not cleanly in detail table. You can attempt to use the plant info in the project table. But then the next problem...
• The products from job detail will have mismatches with imported products. You will see prices for product (base) and plant combos that don't exist and thus are not imported into Keystone. Either these get skipped or you need clever rerouting selection queries to find valid product.
• Multiple price records per Customer/Project/Product. You need to use special query features such as rank to sort this out.
• Price Escalation - the price in prjp may not apply if the customer uses escalation.

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