Examples by Table

From KeystoneIntranet
Revision as of 14:44, 26 February 2024 by Chanson (talk | contribs) (→‎Customer)
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.

Drivers (empl)

with driv as (
	SELECT  --the core query 
	rtrim(ltrim(empl_code)) "DRIVER_CODE",
	replace(rtrim(ltrim(NAME)),'''','''''') "DRIVER_NAME",
	rtrim(ltrim(empl.phone_num)) Phone_no,
	rtrim(ltrim(empl.assgn_plant_code)) Home_plant_no,
	--EMPL.*,
	''',''' QCQ,''',' q1,'=''' q2 -- these are helpers for the concat query
	from empl
	where driv_flag = 1
	and inactive_code='00'
)
select -- this creates insert from the core query
concat('INSERT INTO  artdriv ( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo )  VALUES 
( ''',driver_code,qcq,DRIVER_NAME,qcq,Phone_no,qcq,Home_plant_no,qcq ,'Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y',qcq,'Imported from Cmd'');'   ),
driv.* from driv

Salespeople (empl)

with cs as 
	( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
slsemp as -- all sls employees
	(SELECT 
	rtrim(ltrim(empl.empl_code)) empno,
	rtrim(ltrim(empl.empl_code)) USER_1,
	rtrim(LTRIM(empl.NAME)) DESCRIPTION,
	rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
	rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
	 FROM empl
	where slsmn_flag='True') 
,slpn as
	(
	select cs.*,slsemp.*,
	case custslsmn
	WHEN '182' THEN 'NJ'
	WHEN '322' THEN 'RM'
	WHEN 'S15' THEN '15'
	---...
	WHEN 'SALES' THEN 'SA'
	else custslsmn
	end class_no,''',''' qcq 
	from cs
	INNER join slsemp on slsemp.empno=cs.custslsmn --only include slspn that are in active customers
	) select 
concat ( 'INSERT INTO artslspn ( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 )  VALUES (''',class_no,qcq,empno,qcq,DESCRIPTION,qcq,PHONE_NO,qcq,USER_2,''');'),
slpn.* from slpn
order by custslsmn

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)

Tax Locality

with ttl (tax_loc,tax_auth,descr,rate) as
  (select -- Core query (can be used with QG)
  cast(tl.tax_loc as integer),cast(tl.tax_auth as integer),
  replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','('),
  max(prim_rate_curr_pct) from taxl tl
  inner join taxj tj on tl.tax_loc=tj.tax_loc and tl.tax_auth=tj.tax_auth
  group by tl.tax_loc,tl.tax_auth,descr
  )
select 
concat('INSERT INTO ARTTXLOC
( TAX_LOCALITY, LINE_NO, DESCRIPTION, TAX_COL, LOC_LEVEL, TAX_RATE, CALC_METHOD, EFFECT_DATE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  VALUES 
( ''',100*tax_auth + tax_loc,''', ',tax_auth,',''',left(descr,35),''',1 ,',tax_auth,',',rate,', 1, ''01/01/1900'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y''    );'),
ttl.* from ttl
order by tax_auth,tax_loc;

Tax Authority

For the tax authority, we just need the code and the description... 

with tc as
	(select ltrim(rtrim(tax_code)) "tax_code",
	replace(replace(ltrim(rtrim(descr)),'(City of','('),'( ','(') "descr"
	from taxc)
select 
concat('INSERT INTO ARTTXAUT ( TAX_AUTHORITY, DESCRIPTION, INCREASE_DISC_FLAG, DISC_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  VALUES 
( ''',tax_code,''' , ''',left(descr,35),''', ''N'', ''Y'', ''Y'', CAST(''TODAY'' AS TIMESTAMP), ''HIT'', ''Y'' );',''),
tc.* from tc;



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... 

select top (10000) 
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
RTRIM(replace(left(ltrim(rtrim(CUST.NAME)),30),'''','''''')) name,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_1)),30),'''','''''') address_1,
replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_2)),30),'''','''''') address_2,
left(replace(ltrim(rtrim(addr_city)),',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
addr_state,
ltrim(rtrim(PHONE_NUM_1)) phone_no,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,ltrim(rtrim(cust.tax_code)) "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
case ltrim(rtrim(cc_terms_code)) 
  when '10N' THEN '1N'
  WHEN '15T' THEN '5T'
  WHEN '2.5' THEN '25'
  ELSE LTRIM(RTRIM(cc_TERMS_CODE)) END terms_class, --folsom mapping
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS, -- unmapped
case ltrim(rtrim(cc_sales_anl_code)) --use cust class import mapping
	when 'NSF' THEN 'NS'
	-- ... 
	when 'C53' THEN '53'
	else ltrim(rtrim(cc_sales_anl_code))  end "cust_class",  
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
case rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) 
	WHEN '182' THEN 'NJ' -- same mapping as slspn import
	--... 
	WHEN 'SALES' THEN 'SA'
	else rtrim(LTRIM(CC_SLSMN_EMPL_CODE))  end salesperson,
rtrim(LTRIM(CC_PRICE_CAT)) PRICE_GROUP,
rtrim(LTRIM(CC_PRICE_PLANT_CODE) ) PRICE_PLANT,
CASE TAXBLE_CODE WHEN '1' then '' else CONCAT('TAX EXMPT RSN:', LTRIM(NON_TAX_RSN_CODE) ) END TAX_RSN,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
iif(rtrim(LTRIM(INACTIVE_CODE))='00','Y','N') active_flag,
CASE LTRIM(RTRIM(TAXBLE_CODE)) WHEN 1 THEN 'Y' ELSE 'N' END TAX_CODE,
CONCAT('Acct Cat Code: ',RTRIM(LTRIM(ACCT_CAT_CODE))) USER_2,
replace(LEFT(rtrim(ltrim(CONTCT_NAME)),30),'''','''''')CONTACT,
LEFT(replace(LTRIM(RTRIM(SORT_NAME)),'''',''),30) SEQUENCE_CODE,
acct_cat_code
--,cast(coalesce(SETUP_DATE,'1/1/1900') as date) start_date
,convert(varchar(10), setup_date, 101) start_date,
ltrim(rtrim(cust.tax_id_code)) Tax_id
from cust

Product Class (icat)

A good product class table is essential for a Command conversion.

You will mostly likely need to map long item cat codes to product class codes. Instead of making them like the CMD item cat codes, so focus on making a well organized Keystone list and then have the customer help you mapping CMD item categories to Keystone classes.

Separate the sellable ingredient items for admixes, colors etc. from the non-sellable ingredient codes. When creating product classes, set the defaults for sellable, product type and ingredient type. Then use the following script to update: 

update artprod p set  sellable_flag =(select std_sellable_flag from artprcls c where c.class_no=p.product_class);
update artprod p set  product_type =(select std_product_type from artprcls c where c.class_no=p.product_class);
update artprod p set  ingredient_type =(select std_ingredient_type from artprcls c where c.class_no=p.product_class);

Setup default sales G/L and make sure the plants are properly masked. You can use a variant of this script to update yoru sales g/l accounts... 

UPDATE ARTPROD P
SET p.SALES_GL=
(select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class)||
(select substr(sales_gl_plant_mask,9,19) from artplant pl where pl.plant_no=p.plant_no)
where (select substr(std_sales_gl,1,8) from artprcls pc where pc.class_no=p.product_class) is not null
and p.product_class IN ('AA','BB','CC')  -- Optionally only do this for select classes

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.


Component Structure (icst)

CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

Credit Terms (TRMS)

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

WITH T AS (
	SELECT 
	case ltrim(rtrim(terms_code)) -- setup mapping for terms longer than 2 char.  Save for customer import etc.
		when '10N' THEN '1N'
		WHEN '15T' THEN '5T'
		WHEN '2.5' THEN '25'
		ELSE LTRIM(RTRIM(TERMS_CODE)) END CLASS_NO
	,LEFT(LTRIM(RTRIM(descr)),30) DESCRIPTION
	FROM dbo.trms
  )
  SELECT 
  CONCAT('iNSERT INTO ARTCTCLS (CLASS_NO,DESCRIPTION) VALUES (''',CLASS_NO,''',''',DESCRIPTION,''');'),
  * FROM T

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

Zone

SELECT 
  CONCAT('iNSERT INTO ARTzone (zone,DESCRIPTION,def_tax_authority) VALUES (''',rtrim(ltrim(zone_code)),
  ''',''',left(rtrim(ltrim(DESCR)),35),
  ''',''',rtrim(ltrim(tax_code)),''');'),
  zone.* 
 FROM ZONE


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=952"