Examples by Table

From KeystoneIntranet
Jump to navigation Jump to search

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 (Locn/Plnt)

Locations and plants are similar but different. Multiple plants may have on location.

  • LOCN: Locations
  • PLNT: Plant

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.

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. Import the actual group prices after products are setup. PRCC

Sample query: 

with aaa (clsno,descr) as
	(
	select -- core query that can be used with qg
	ltrim(rtrim(price_cat)),
	ltrim(rtrim(descry)) description
	from prcc
	)
select -- outer query 
concat('INSERT INTO ARTPRGRP ( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)  VALUES 
  ( ''',clsno,''',''',left(descr,35),''', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y''  );')
,clsno "group", descry description
from aaa

Price Cat codes tend to be short and do not need to be mapped coming into Keystone.

Dispatch Useage Class

  • USGE: Useage


Customer Class (sanl)

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


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,

Product Class (icat)

  • ICAT: Item (Product)

Products

Products are stored in 2 tables, imst and iloc.

  • IMST: Product "Base". One record for base product that stores the category (class) description etc.
  • ILOC: Product by Location

These need to be combined to create keystone products.


Cost

  • ICST: Component Structure

Credit Terms (TRMS)

You can import term codes and descriptions, but the meaning does not map from system to system.


Trucks

Trucks

Unit of Meausure (uoms)

The key field for Unit of Measure is typically a long integer.



Customer Prices (CPRD)

  • A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)


List and Group Prices (IPRC)

The table IPRC stores Category (cmd)/Group Prices and the equivelent of List prices. Category "#" is represents "List" in Keystone.

  • Many times, what appears to be a Group(cat) is in fact used as a list price. You can preview the iprc data to the customer to determine this before the install. You can also import into Keystone and then convert a group to list prices.
  • Often, a large amount of junk pricing is in this table. Preview to customer to determine if large amounts of pricing should be omitted.
  • Where in Keystone, we often base pricing on X$ off list for a group or customer, in Command each price ends up as a record. If possible, find groups (price cats) that can be skipped and replaced with a price off list. (this may only apply to the mix items in a group/price cat)
  • Expect redundant records that tneed to be processed by effect date. (Use of RANK or similar SQL tricks can correct for this)
  • Expect Prices for items that may or may not exist.
  • A plant of # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)

Jobs

Job (proj)

  • 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.
  • Base selection on Expir_date, Setup_date and the inactive_code of projects, customers, iloc and imst.

Job Detail (prjp)

  • Tie this selection to the core query used for PROJ
  • Plant # means all plants. On import try to match this to a Keystone Base Price Plant. (If all plants point to 01 - import price to 01 if possible)
  • 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 (ppre)

  • 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
Retrieved from "http://in.compucrete.com/index.php?title=Examples_by_Table&oldid=942"