Command Import

There is not ONE way to import Command Data - each situation is unique.

Know This First

What you need to know before importing Command's Data

• There is NO data integrity.

• Expect sub tables, prices etc. to have records that do have matching main tables. (e.g. prices for customers that don't exist).

• Alpha fields must be trimmed - especially key fields. Expect both left and right padding on Customer and product codes.

MS SQL hasn't had the "TRIM" statement for long, so most often you must select as:

LTRIM(RTRIM((PRJP.PROJ_CODE)) JOB_PO_LOC,

in some cases, such as Oracle you only have trim and must select as:

TRIM(PRJP.PROJ_CODE) JOB_PO_LOC,

• Numeric Fields may be null. Use coalesce... COALESCE(Finc_Chrg_amt,0) Finc_chrg_amt
• Field IDs are longer - especially Tax Authorities, sales people and classes.
• Keep a list of mappings when setting up the class type fields to use in case statements...

case TERMS
WHEN '15N' THEN '15' 
WHEN 'N20' THEN '20' 
   ...
WHEN 'CAS' THEN 'CA' ELSE '' END CUST_TERMS

• Some records, especially prices, have what appear to be duplicates. You need special queries that pull just the latest version of the record.
• Start with Small Tables and Build up (Like any Import)

MS SQL to know

Microsoft SQL Server Query Features

• ltrim,rtrim
• replace
• left, right
• case
• iif
• rank
• with (derived queries)

Import Examples

Here are some samples used in the past...

Examples by Table

Command Import: Hedger Work Notes

Command Import: Rain MO Work Notes

Command Tables

• ACCT: GL Accounts
• ARTB: Aging
• CCON: Contact Connection
• COMP: Company
• CPRD: Customer Prices
• CTCT: Contact
• CUST: Customer Prices
• EMPL: Employee - Sales and Drivers
• ICAT: Item (Product)
• ICST: Component Structure
• ILOC: Product by Location (Similar to Plant)
• IMST: Products
• IPRC: Prices - by Level (Group)
• LOCN: Locations (Similar to Plant)
• PLNT: Plant
• PRCC: Price Group
• PRJP: Project Prices
• PROJ: Project
• SANL: Customer Class
• TAXC: Tax (Similar to Keystone Tax Auth)
• TAXJ: Tax Structure
• TAXL: Tax Locality (not 1 to 1 with Keystone)
• TRMS: Terms
• TRUC: Trucks
• UOMS: Unit of Meausure
• USGE: Useage