Examples by Table - Revision history

Chanson: /* Job (proj) */

2024-02-26T16:54:02Z

Job (proj)

@@ Line 307: / Line 307: @@
 *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)====

Chanson: /* Customer */

2024-02-26T14:44:07Z

Customer

@@ Line 175: / Line 175: @@
 State needs to be mapped...
 <pre>
-CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
+select top (10000)
-   WHEN 'NEBRASK' THEN 'NE'
+LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
-   WHEN 'NEWYORK' THEN 'NY'
+RTRIM(replace(left(ltrim(rtrim(CUST.NAME)),30),'''','''''')) name,
-   WHEN 'NEWJERSEY' THEN 'NJ'
+replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_1)),30),'''','''''') address_1,
-   ...
+replace(left(LTRIM(RTRIM(CUST.ADDR_LINE_2)),30),'''','''''') address_2,
-   WHEN 'PENNSYLVAN' THEN 'PA'
+left(replace(ltrim(rtrim(addr_city)),',',''),16) CITY,
-   WHEN 'SOUTHCARO' THEN 'SC'
+replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
-   WHEN 'TENNESSEE' THEN 'TN'
+addr_state,
-  ELSE SUBSTR(UPPER(TRIM(ADDR_STATE)),1,2) END STATE,
+ltrim(rtrim(PHONE_NUM_1)) phone_no,PHONE_NUM_2,PHONE_NUM_3,
 </pre>

Chanson: /* Customer */

2024-02-26T13:58:24Z

Customer

← Older revision		Revision as of 13:58, 26 February 2024
Line 174:		Line 174:

	State needs to be mapped...		State needs to be mapped...
	<pre)		<pre>
	CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')		CASE REPLACE(UPPER(TRIM(ADDR_STATE)),' ','')
	WHEN 'NEBRASK' THEN 'NE'		WHEN 'NEBRASK' THEN 'NE'

Chanson: /* Salespeople (empl) */

2024-02-26T13:57:54Z

Salespeople (empl)

← Older revision		Revision as of 13:57, 26 February 2024
Line 36:		Line 36:
	<pre>		<pre>
	with cs as		with cs as
	( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ),		( select distinct(ltrim(rtrim(cust.cc_slsmn_empl_code))) custslsmn from cust where inactive_code='00' ), -- LIst of slsmn in active customers
	slsemp as		slsemp as -- all sls employees
	(SELECT		(SELECT
	rtrim(ltrim(empl.empl_code)) empno,		rtrim(ltrim(empl.empl_code)) empno,
	rtrim(ltrim(empl.empl_code)) USER_1,		rtrim(ltrim(empl.empl_code)) USER_1,
	rtrim(LTRIM(empl.NAME)) DESCRIPTION,		rtrim(LTRIM(empl.NAME)) DESCRIPTION,
	rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,		rtrim(LTRIM(empl.PHONE_NUM)) PHONE_NO,
	rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2		rtrim(LTRIM(empl.ASSGN_PLANT_CODE)) USER_2
	FROM empl		FROM empl
	where slsmn_flag='True')		where slsmn_flag='True')
	,slpn as		,slpn as
	(		(
	select cs.,slsemp.,		select cs.,slsemp.,
	case custslsmn		case custslsmn
	WHEN '182' THEN 'NJ'		WHEN '182' THEN 'NJ'
	WHEN '322' THEN 'RM'		WHEN '322' THEN 'RM'
	...		WHEN 'S15' THEN '15'
	WHEN 'SALES' THEN 'SA'		---...
	else custslsmn		WHEN 'SALES' THEN 'SA'
	end class_no,''',''' qcq		else custslsmn
	from cs		end class_no,''',''' qcq
	~~left outer~~ join slsemp on slsemp.empno=cs.custslsmn		from cs
	) select		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,''');'),		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		slpn.* from slpn
	order by custslsmn		order by custslsmn
	</pre>		</pre>



	===Taxes===		===Taxes===

Chanson at 13:25, 26 February 2024

2024-02-26T13:25:25Z

@@ Line 13: / Line 13: @@
 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.
-===Salespeople and Drivers===
+===Drivers (empl)===
-*'''EMPL:''' Employee - Sales and Drivers
+<pre>
 ===Salespeople (empl)===

Chanson at 13:20, 26 February 2024

2024-02-26T13:20:56Z

@@ Line 12: / Line 12: @@
 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.
 ===Salespeople and Drivers===
 *'''EMPL:''' Employee - Sales and Drivers
 ===Taxes===
@@ Line 30: / Line 60: @@
 *'''TAXJ:''' Tax Structure
 *'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)
@@ Line 48: / Line 80: @@
 order by tax_auth,tax_loc;
 </pre>
 ===Price Group Headers===
@@ Line 222: / Line 271: @@
 *'''CCON:''' Contact Connection
 *'''CTCT:''' Contact

Chanson: /* Component Stucture */

2024-02-23T19:34:06Z

Component Stucture

← Older revision		Revision as of 19:34, 23 February 2024
Line 155:		Line 155:


	===~~=Cost=~~===		===Component Structure (icst)===
	*'''ICST:''' Component Structure		CMD dispatch mixes rarely are worth importing. Verify with customer, but expect to import directly from batch controls.

	===Credit Terms (TRMS)===		===Credit Terms (TRMS)===

Chanson: /* Taxes */

2024-02-23T18:25:37Z

Taxes

← Older revision		Revision as of 18:25, 23 February 2024
Line 31:		Line 31:
	*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)		*'''TAXL:''' Tax Locality (not 1 to 1 with Keystone)


			<pre>
			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;
			</pre>

	===Price Group Headers===		===Price Group Headers===

Chanson: /* Product Class (icat) */

2024-02-22T21:11:51Z

Product Class (icat)

@@ Line 105: / Line 105: @@
 ===Product Class (icat)===
-*'''ICAT:''' Item (Product)
 ===Products===

Chanson: /* Credit Terms (TRMS) */

2024-02-22T15:08:44Z

Credit Terms (TRMS)

← Older revision		Revision as of 15:08, 22 February 2024
Line 121:		Line 121:
	You can import term codes and descriptions, but the meaning does not map from system to system.		You can import term codes and descriptions, but the meaning does not map from system to system.

			<pre>
			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
			</pre>

	===Trucks===		===Trucks===