Command Import: Hedger Work Notes - Revision history

Chanson: /* =Price Groups */

2024-02-27T15:48:24Z

=Price Groups

← Older revision		Revision as of 15:48, 27 February 2024
Line 14:		Line 14:
	==		==
	</pre>		</pre>
	===Price Groups==		===Price Groups===
	<pre>		<pre>

Line 28:		Line 28:
	</pre>		</pre>
	===GL===		===GL===

	~~===Plants===~~
	<pre>		<pre>
	LQ1: (LIST OF NUMERIC PLANTS IN CMD)		LQ1: (LIST OF NUMERIC PLANTS IN CMD)
Line 66:		Line 64:
	TODO - MAP SEGMENT 3 BY PLANT FROM 9999###		TODO - MAP SEGMENT 3 BY PLANT FROM 9999###
	==========		==========
	=== ~~ARTPLANT~~		</pre>
			===Plant===
			<pre>
	SELECT		SELECT
	p.*		p.*
Line 80:		Line 80:

	TODO: GL MASK - DEFAULT G/L ACCOUNTS		TODO: GL MASK - DEFAULT G/L ACCOUNTS
			</pre>
	===~~TAX~~ (when Flat in Keystone and MultiLevel in CMD)		===Tax===
			(when Flat in Keystone and MultiLevel in CMD)
	Tax Research		Tax Research
			<pre>
	select j.tax_code,a.descr,sum(prim_rate_curr_pct), sum(1) ct		select j.tax_code,a.descr,sum(prim_rate_curr_pct), sum(1) ct
	from taxj j		from taxj j
Line 101:		Line 101:
	select * from taxl;		select * from taxl;

			</pre>
	Confirmed FLAT OK		Confirmed FLAT OK

	==ARTTXLOC/AUT/STR		ARTTXLOC/AUT/STR:
			<pre>
	select j.tax_code,a.descr,sum(prim_rate_curr_pct) TAX_RATE, sum(1) ct		select j.tax_code,a.descr,sum(prim_rate_curr_pct) TAX_RATE, sum(1) ct
	from taxj j		from taxj j
Line 125:		Line 127:
	( 0, {TAX_RATE\|INC=1}, '{TAX_CODE}', '{TAX_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );		( 0, {TAX_RATE\|INC=1}, '{TAX_CODE}', '{TAX_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

	==ARTUMS (This finds CMD U/M that are actually used but not in Keystone)		</pre>
			===Unit of Measure===
			ARTUMS (This finds CMD U/M that are actually used but not in Keystone)
			<pre>
	LQ1:		LQ1:
	SELECT * FROM aRTUMS		SELECT * FROM aRTUMS
Line 154:		Line 158:
	( UNIT_OF_MEASURE, EXPANDED_UMS, DESCRIPTION, UMS_TYPE, UMS_SYSTEM, SUBTOTAL, SYSTEM_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES		( UNIT_OF_MEASURE, EXPANDED_UMS, DESCRIPTION, UMS_TYPE, UMS_SYSTEM, SUBTOTAL, SYSTEM_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
	( '{UM}', '{UOM}', '{DESCR}', 'U', 'E', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );		( '{UM}', '{UOM}', '{DESCR}', 'U', 'E', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );



	-- Set UofM Expanded		-- Set UofM Expanded

Chanson at 15:45, 27 February 2024

2024-02-27T15:45:18Z

← Older revision		Revision as of 15:45, 27 February 2024
Line 1:		Line 1:

			2022DEC19
			===Customer Class===
	<pre>		<pre>
	~~2022DEC19~~
	~~==Customer Class~~
	select		select
	ltrim(rtrim(sales_anl_code)) class_no,		ltrim(rtrim(sales_anl_code)) class_no,
	ltrim(rtrim(descry)) description, *		ltrim(rtrim(descry)) description, *
	from sanl		from sanl

	==		==
	INSERT INTO artcucls		INSERT INTO artcucls
Line 11:		Line 13:
	( '{CLASS_NO}', '{DESCRIPTION\|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );		( '{CLASS_NO}', '{DESCRIPTION\|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
	==		==
			</pre>
			===Price Groups==
			<pre>

	~~==Price Groups~~
	SELECT		SELECT
	RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO,		RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO,
Line 22:		Line 26:
	( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' , 'CMD PRICE CAT:{PRICE_CAT\|TRIM=0}' );		( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' , 'CMD PRICE CAT:{PRICE_CAT\|TRIM=0}' );

	== ~~GLTACCT (Frmrs only - using existing company)~~		</pre>
			===GL===

			===Plants===
			<pre>
	LQ1: (LIST OF NUMERIC PLANTS IN CMD)		LQ1: (LIST OF NUMERIC PLANTS IN CMD)
	SELECT		SELECT
Line 42:		Line 50:
	( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES		( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
	( '{segment_1}-00-9999{PLANT_CODE}-{PLANT_CODE}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );		( '{segment_1}-00-9999{PLANT_CODE}-{PLANT_CODE}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );


	= gl 000 =		= gl 000 =

Chanson: Created page with "

 2022DEC19 ==Customer Class select   ltrim(rtrim(sales_anl_code)) class_no, ltrim(rtrim(descry)) description,  *  from sanl == INSERT INTO artcucls ( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG )  VALUES  ( '{CLASS_NO}', '{DESCRIPTION|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    ); ==  ==Price Groups SELECT  RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO, RTRIM(LTRIM(DESCR)) DESCRIPTION, PRCC.* FROM  prcc =..."

2023-11-21T16:48:17Z

Created page with "<pre> 2022DEC19 ==Customer Class select ltrim(rtrim(sales_anl_code)) class_no, ltrim(rtrim(descry)) description, * from sanl == INSERT INTO artcucls ( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES ( '{CLASS_NO}', '{DESCRIPTION|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ); == ==Price Groups SELECT RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO, RTRIM(LTRIM(DESCR)) DESCRIPTION, PRCC.* FROM prcc =..."

New page

<pre>
2022DEC19
==Customer Class
select
ltrim(rtrim(sales_anl_code)) class_no,
ltrim(rtrim(descry)) description, *
from sanl
==
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
==

==Price Groups
SELECT
RTRIM(LTRIM(replace(PRICE_CAT,'.',''))) GROUP_NO,
RTRIM(LTRIM(DESCR)) DESCRIPTION,
PRCC.* FROM prcc
==
INSERT INTO ARTPRGRP
( GROUP_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' , 'CMD PRICE CAT:{PRICE_CAT|TRIM=0}' );

== GLTACCT (Frmrs only - using existing company)
LQ1: (LIST OF NUMERIC PLANTS IN CMD)
SELECT
ISNUMERIC(plant_code),p.*
FROM plnt p
where isnumeric (plant_code)=1

LQ2: (MODEL G/L)
select * from gltacct
where segment_4 like '150'

PRIMARY:
select lq1.plant_code,lq2.* from lq2
inner join lq1 on 1=1
ORDER BY 1,2

INSERT INTO
gltacct
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{segment_1}-00-9999{PLANT_CODE}-{PLANT_CODE}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

= gl 000 =

select * from gltacct
where segment_4=000

INSERT INTO GLTACCT
( GL_ACCOUNT, DESCRIPTION, GL_GROUP, SEQUENCE, ACCOUNT_CLASS, COMPANY_NO, DIVISION_NO, ALLOC_RULE_ID, NRMCA_ACCOUNT, ACCOUNT_TYPE, DR_CR_FLAG, INTRA_COMPANY, JC_REQUIRED, SECURITY_CLASS, IMPORTED_FLAG, VALID_FROM_DATE, VALID_TO_DATE, NOTES_1, NOTES_2, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{DESCRIPTION}', '{GL_GROUP}', {SEQUENCE}, '{ACCOUNT_CLASS}', '{COMPANY_NO}', '{DIVISION_NO}', '{ALLOC_RULE_ID}', '{NRMCA_ACCOUNT}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', '{INTRA_COMPANY}', '{JC_REQUIRED}', '{SECURITY_CLASS}', '{IMPORTED_FLAG}', '{VALID_FROM_DATE}', '{VALID_TO_DATE}', '{NOTES_1}', '{NOTES_2}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

TODO - MAP SEGMENT 3 BY PLANT FROM 9999###
==========
=== ARTPLANT
SELECT
p.*
FROM plnt p
where isnumeric (plant_code)=1

INSERT INTO ARTPLANT ( PLANT_NO, SYS_LOC, PLANT_NAME, ADDRESS_1, ADDRESS_2, PHONE_NO, PLANT_TYPE, PLANT_SCREEN_ID, DIVISION_NO,PLANT_WASH_MINLD, JOB_WASH_MINLD,
BATCH_CAPACITY,
MEMO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{plant_code|trim=0}', 0, '{name}', '{addr_line_1}', '{addr_line_2}', '{PHONE_Num}', 'D', '{short_name}', null, {PLANT_WASHDOWN_TIME}, {JOB_WASHDOWN_TIME},
{MAX_BATCH_SIZE},
'{LOC_CODE|trim=0}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

TODO: GL MASK - DEFAULT G/L ACCOUNTS

===TAX (when Flat in Keystone and MultiLevel in CMD)

Tax Research

select j.tax_code,a.descr,sum(prim_rate_curr_pct), sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

select *
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
order by j.tax_code,a.descr;

select * from taxj order by tax_code,tax_auth,tax_loc;
select * from taxc;
select * from taxl;

Confirmed FLAT OK

==ARTTXLOC/AUT/STR
select j.tax_code,a.descr,sum(prim_rate_curr_pct) TAX_RATE, sum(1) ct
from taxj j
inner join taxc a on a.tax_code=j.tax_code
inner join taxl l on j.tax_loc=l.tax_loc and l.tax_auth=j.tax_auth
group by j.tax_code,a.descr;

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
( '{TAX_CODE}', 1, '{DESCR}', 1, 1, {TAX_RATE}, 1, '01/01/1900', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

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}', '{DESCR}', 'N', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

INSERT INTO
ARTTXSTR
( SESSION_NO, TRANS_NO, TAX_AUTHORITY, TAX_LOCALITY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 0, {TAX_RATE|INC=1}, '{TAX_CODE}', '{TAX_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

==ARTUMS (This finds CMD U/M that are actually used but not in Keystone)

LQ1:
SELECT * FROM aRTUMS

LQ2:
SELECT
LTRIM(UPPER(ABBR)) UM,
UOMS,* FROM UOMS

LQ3: -- This finds the uoms used for importable items (no need to import baggage)
SELECT DISTINCT (order_uom) UMX FROM IMST UNION
SELECT DISTINCT (price_uom) FROM IMST UNION
SELECT DISTINCT (invy_uom) FROM IMST UNION
SELECT DISTINCT (purch_uom) FROM IMST UNION
SELECT DISTINCT (batch_uom) FROM IMST UNION
SELECT DISTINCT (rpt_uom) FROM IMST UNION
SELECT DISTINCT (order_dosage_qty_uom) FROM IMST

SELECT:
SELECT lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON LQ2.UM=LQ1.UNIT_OF_MEASURE
WHERE UNIT_OF_MEASURE IS NULL

INSERT INTO ARTUMS
( UNIT_OF_MEASURE, EXPANDED_UMS, DESCRIPTION, UMS_TYPE, UMS_SYSTEM, SUBTOTAL, SYSTEM_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{UM}', '{UOM}', '{DESCR}', 'U', 'E', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- Set UofM Expanded
SELECT lq1.*,lq2.* FROM LQ2
INNER JOIN LQ3 ON LQ2.UOM=LQ3.UMX
LEFT OUTER JOIN LQ1
ON cast(LQ2.UM as text)=cast(LQ1.UNIT_OF_MEASURE as text)
WHERE (UNIT_OF_MEASURE IS not NULL)

update ARTUMS set EXPANDED_UMS={uom} where unit_of_measure='{UM}';

Note - the expanded UofM stored for potential linking on imports.

TODO: Clear expanded at roll out - it will print on reports.

=== artprcls
Straight rip from Alt Company

===artslspn

SELECT
ltrim(empl_code) CLASS_NO,
ltrim(empl_code) USER_1,
rtrim(LTRIM(NAME)) DESCRIPTION,
LTRIM(PHONE_NUM) PHONE_NO,
LTRIM(ASSGN_PLANT_CODE) USER_2
FROM empl
where slsmn_flag='True'

INSERT INTO artslspn
( CLASS_NO, EMPLOYEE_NO, DESCRIPTION, PHONE_NO, USER_2 ) VALUES
( SUBSTR(100+{CLASS_NO|INC=1},2,3), '{USER_1}', '{DESCRIPTION}', '{PHONE_NO}', '{USER_2}' );
========
=== arttruck
select
rtrim(ltrim(truck_code)) "TRUCK_NO",
rtrim(ltrim(DESCR)) "DESCR",
truc.* from truc
ORDER BY UPDATE_DATE DESC

INSERT INTO ARTTRUCK
( TRUCK_NO, TARE_WEIGHT, LAST_TARE_DATE, HOME_PLANT_NO, PLATE_NUMBER, EXPIRATION_DATE, ACTIVE_FLAG, memo ) VALUES
( '{TRUCK_CODE|TRIM=0}', {TARE_WGT|null=0}, '{TARE_DATE}', '{FROM_PLANT_CODE|TRIM=0}', '{LIC_NUM}', '{expir_date_1}', 'Y' ,
'Cmd Import mm/yy/dddd
Description: {DESCR}
Owner: {OWNER_NAME}
Trk Type: {truck_TYPE}
Inactive: {INACTIVE_FLAG}
Max Ld Sz: {MAX_LOAD_SIZE}
Driver Emp: {assgn_driv_empl_code|TRIM=0}'
); /* ------------------------------------ */
========
=== ARTDRIV
SELECT
rtrim(ltrim(empl_code)) "DRIVER_CODE", -- 12/11
rtrim(ltrim(NAME)) "DRIVER_NAME",
EMPL.* from empl
where driv_flag = 1
order by update_date desc

INSERT INTO artdriv
( DRIVER_CODE, DRIVER_NAME, PHONE_NO, HOME_PLANT_NO, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,memo ) VALUES
( '{empl_CODE|trim=0}', '{NAME}', '{PHONE_Num}', '{assgn_plant_code}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y',
'Imported mm/dd/yyyy
Inactive Date: {Inactive_date}
Inactive code: {inactive_code}
' );
========
=== ARTPROD

--t22
(01/05/22 simplified and cleaned up)
SELECT
CONCAT(RTRIM(LTRIM(IMST.ITEM_CODE)),'.',
PLNT.PLANT_CODE) PRODUCT_CODE_ORIG,
UPPER(CONCAT( RTRIM(LTRIM(IMST.ITEM_CODE)) ,'.',PLNT.PLANT_CODE)) "PRODUCT_CODE",
UPPER(RTRIM(LTRIM(IMST.ITEM_CODE))) "BASE_PRODUCT", -- 12/15 ADJ . TO -
RTRIM(lTRIM(ILOC.LOC_CODE)) LOC_CODE,
rtrim(LTRIM(PLNT.PLANT_CODE)) PLANT_NO,
coalesce(max_load_size,0) mxldsz,
RTRIM(LTRIM(IMST.ITEM_CODE)) ITEM_CODE,UPPER(IU.ABBR) INVUM,UPPER(PU.ABBR) PRICEUM,UPPER(PU.ABBR) BATCH_UMS,
CASE INVY_FLAG WHEN 'True' then 'Y' else 'N' END STOCKED,
CASE imst.TAXBLE_CODE WHEN '1' then 'Y' else 'N' END TAX_CODE_1,
LTRIM(imst.ITEM_CAT) ITEM_CAT,
case RTRIM(Ltrim(imst.item_cat)) -- icat to product class translation
WHEN '1' THEN 'C5'
WHEN '3' THEN 'X2'
WHEN '4' THEN 'X3'
WHEN '5' THEN 'X5'
WHEN '6' THEN 'Y1'
WHEN '7' THEN 'Y5'
WHEN '8' THEN 'Y4'
WHEN '9' THEN 'A4'
WHEN '10' THEN '23'
WHEN '11' THEN 'Y3'
WHEN '12' THEN 'Y6'
WHEN '15' THEN 'P1'
WHEN '18' THEN 'P2'
WHEN '21' THEN 'X1'
WHEN '23' THEN '23'
WHEN '24' THEN 'X4'
WHEN '1.1' THEN 'C5'
WHEN '1.2' THEN 'C5'
WHEN '1.3' THEN 'C5'
WHEN '1.4' THEN 'C5'
WHEN '1.5' THEN 'C5'
WHEN '1.6' THEN 'C5'
WHEN '1.7' THEN 'C5'
WHEN '1.8' THEN 'C5'
WHEN '1.9' THEN 'C5'
WHEN '13.1' THEN 'R1'
WHEN '13.2' THEN 'R2'
WHEN '13.6' THEN 'T1'
WHEN '13.8' THEN 'D1'
WHEN '20.1' THEN 'P1'
WHEN '1.10' THEN 'C5'
elSe 'Z1' END PRODUCT_CLASS,
COALESCE (IMST.PCT_AIR,0) PCT_AIR,
COALESCE (IMST.WATER_CEM_RATIO,0) WATER_CEM_RATIO,
--IIF( LEN(REPLACE(imst.DESCR, '''', '~' ))<=35, REPLACE(imst.DESCR, '''', '~' ),REPLACE(imst.SHORT_DESCR, '''', '~' )) DESCRIPTION,
replace(replace(replace(REPLACE(imst.DESCR, '''', '~' ),' ',' '),' ',' '),' ',' ') DESCR2,
iif(resale_flag=1,'Y','N') "SELLABLE_FLAG",
COALESCE(iloc.curr_std_cost,0) CURR_STD_COST,
prev_std_cost,
cost_ext_code,
prev_cost_ext_code,
std_cost_effect_date,
curr_opt_cost,
prev_opt_cost,
opt_cost_effect_date
,imst.short_descr
,iloc.inactive_code
,imst.tax_rate_code
,imst.NON_TAX_RSN_CODE
,imst.descr
--,IMST.*
FROM IMST
INNER JOIN ILOC ON ILOC.ITEM_CODE=IMST.ITEM_CODE
INNER JOIN PLNT ON PLNT.LOC_CODE=ILOC.LOC_CODE
LEFT OUTER JOIN DBO.UOMS IU ON IU.UOM=IMST.INVY_UOM
LEFT OUTER JOIN DBO.UOMS PU ON PU.UOM=IMST.PRICE_UOM
LEFT OUTER JOIN DBO.UOMS BU ON BU.UOM=IMST.BATCH_UOM
where isnumeric (plnt.plant_code)=1

INSERT INTO
ARTPROD
( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, DESCRIPTION_2, LOCATION, STOCKED, TAX_CODE, TAX_CODE_2, TAX_CODE_3, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, INGREDIENT_TYPE, EXPORT_FLAG, ROLLUP_FLAG, SELLABLE_FLAG, SEND_TO_BATCH, UPC_CODE, VENDOR_NO, VENDOR_PART_NO, LIST_PRICE, SPECIFIC_GRAVITY, PERCENT_AIR, MIX_WC_RATIO, MIX_YIELD, MIXER_TIME_SEC, MAX_LOAD_SIZE, STD_PCT_DISC, AUTO_QTY, DEFAULT_SLUMP, PURCHASE_UMS, ENTRY_UMS, MIX_UMS, BATCH_UMS, NOTES_1, NOTES_2, NOTES_3, NOTES_4, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( '{PRODUCT_CODE}', '{PRICEUM}', '{DESCR2|STRLEN=35}', '{SHORT_DESCR}', NULL, '{STOCKED}', '{TAX_CODE_1}', 'Y', 'Y', '{ITEM_CODE}', '{PRODUCT_CLASS}', '{PLANT_NO}', 'X', NULL, 'N', 'N', 'Y', 'N', NULL, NULL, NULL, 0, 0, {PCT_AIR}, {WATER_CEM_RATIO}, 0 , 0, {MXLDSZ}, NULL, 'N', 0, NULL, NULL, NULL, '{BATCH_UMS}', '{ITEM_CAT}', 'LOC: {LOC_CODE} / INACTIVE: {INACTIVE_CODE}', '{TAX_RATE_CODE}/{NON_TAX_RSN_CODE}', '{DESCR}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'
, '***Import Notes 01/03/2023 ***' );

todo:
Prices
Apply Product Class Updates
Update Product, Ingredient Types / G/L from product classes.

To update missing products do main query in LQ1 and use:

select LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.PRODUCT_CODE IS NULL
LIMIT 5000;
(5000 at a time for processing efficiency)

-- FIX (01/04/2023 - unneeded since reprocessed products)

ALTER TRIGGER argprod_bupdate INACTIVE;
ALTER TRIGGER argprod_Aupdate INACTIVE;

select PRODUCT_CODE,upper(BASE_PRODUCT) BASE_PRODUCT,PLANT_NO from artPROD
WHERE PRODUCT_CODE <> upper(BASE_PRODUCT)||'.'||PLANT_NO
ORDER BY PLANT_NO,PRODUCT_CODE

UPDATE ARTPROD SET PRODUCT_CODE='{BASE_PRODUCT}.{PLANT_NO}' WHERE upper(BASE_PRODUCT) = '{BASE_PRODUCT}' AND PLANT_NO='{PLANT_NO}';

ALTER TRIGGER argprod_bupdate ACTIVE;
ALTER TRIGGER argprod_Aupdate ACTIVE;

=======
=== ARTCTCLS

SELECT ltrim(rtrim(terms_code)) terms_code
,descry
,disc_amt_1
,disc_amt_2
,disc_day_1
,disc_day_2
,due_day_1
,due_day_2
,short_descr
,terms_type
,per_uom
,update_date
FROM CMDPROD_ARK.dbo.trms

INSERT INTO ARTCTCLS
( CLASS_NO, DESCRIPTION, DUE_BY, DUE_BY_PIVOT, DUE_IN, DISC_PCT, DISC_BY, DISC_BY_PIVOT, DISC_IN, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{TERMS_CODE}', '{DESCR}', NULL, NULL, NULL, {DISC_AMT_1}, NULL, NULL, {DISC_DAY_1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

NOTE: Not 100% clear on how to match days -manually edit.
========
=== ARTALCLS (FROM alt company)
select aa.* ,
a.segment_1,a.segment_2,a.segment_3,a.segment_4
from artalcls aa
left outer join gltacct a on a.gl_account = aa.allow_gl

INSERT INTO ARTALCLS
( CLASS_NO, DESCRIPTION, ALLOW_GL, ALLOW_INCLUDES_TAX, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CLASS_NO}', '{DESCRIPTION}', '{SEGMENT_1}-00-9999{SEGMENT_4}-{SEGMENT_4}', '{ALLOW_INCLUDES_TAX}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' );

========
=== ARTCUST

select
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
left(ltrim(rtrim(CUST.NAME)),30) name,
left(CUST.ADDR_LINE_1,30) addr_line_1,
left(CUST.ADDR_LINE_2,30) addr_line_2,
left(replace(addr_city,',',''),16) CITY,
replace(LEFT(rtrim(ltrim(ADDR_STATE)),2),'M0','MO') STATE,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,
LTRIM(RTRIM(ADDR_POSTCD)) ZIP,cust.tax_code "TAX_AUTHORITY",
--cast(cust.tax_code as integer) txcd_int,
--left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
LTRIM(RTRIM(CC_TERMS_CODE)) TERMS,
LTRIM(RTRIM(UPPER(CC_SALES_ANL_CODE))) CUCLS,
rtrim(LTRIM(CC_SLSMN_EMPL_CODE)) SLS_EMP,
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,
case isnumeric(CC_SALES_ANL_CODE)
WHEN 0 THEN LTRIM(CC_SALES_ANL_CODE) ELSE RIGHT(CAST(100+CAST(CC_SALES_ANL_CODE AS INTEGER) AS CHAR(3)),2) END CUST_CLASS,
rtrim(LTRIM(INACTIVE_CODE)) INACTIVE_CODE,
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,
LEFT(CONTCT_NAME,30) CONTACT,
LEFT(SORT_NAME,30) SEQUENCE_CODE,
acct_cat_code,SETUP_DATE
from cust
--left outer join taxj on cast(taxj.tax_code as integer)=cast(cust.tax_code as integer)
order by cust_no

INSERT INTO artcust
( CUST_NO, SALESPERSON, CUST_CLASS, TERMS_CLASS, PRICE_GROUP, DIVISION_NO, PHONE_NO, FAX_NO, PAGER_NO, MOBILE_NO, EMAIL, URL, START_DATE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, COD_FLAG, NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, USER_1, USER_2, NOTES_1, TAX_AUTHORITY, CONTACT, SEQUENCE_CODE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{CUST_NO}', (SELECT CLASS_NO FROM ARTSLSPN WHERE EMPLOYEE_NO='{SLS_EMP}') , '{CUCLS}', '{TERMS}', '{PRICE_GROUP}' , NULL, '{PHONE_NUM_1}', NULL, NULL, '{PHONE_NUM_3}', NULL, NULL, '{SETUP_DATE}', '{TAX_CODE}', 'Y', 'Y', 'N', '{NAME|STRLEN=30}', '{ADDR_LINE_1|STRLEN=30}', '{ADDR_LINE_2|STRLEN=30}', '{CITY}', '{STATE}', '{ZIP}', 'Sls Emp: {SLS_EMP|trim=0}', '{USER_2}', '{TAX_RSN}', '{TAX_AUTHORITY}', '{CONTACT}', '{SEQUENCE_CODE}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

=====
== LIST PRICE ==
Price cat 1 as list price (54986 prices are cat 1)

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) = '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

UpdATE ARTPROD SET list_PRICE={PRICE} WHERE product_code='{BASE_PRODUCT}.{PLANT_NO}';

-- Local to make manageable chunks and update only mismatches...
LQ2:
select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE

select lq2.*,LQ1.* from lq1
LEFT OUTER JOIN LQ2 ON LQ2.PRODUCT_CODE = LQ1.PRODUCT_CODE
WHERE LQ2.LIST_PRICE <> LQ1.PRICE
limt 10000 offset 20000 -- 20000- 29999

========
=== artprice - group prices plant specific

SELECT
concat(upper(rtrim(LTRIM(IPRC.ITEM_CODE))) , '.' , rtrim(ltrim(plnt.plant_code))) "PRODUCT_CODE",
upper(rtrim(LTRIM(IPRC.ITEM_CODE))) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
rtrim(ltrim(iprc.price_cat)) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,
PRICE UNIT_PRICE,1 CT,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE AND ISNUMERIC(PLNT.plant_code)>0
INNER JOIN ILOC ON ILOC.item_code=IPRC.item_code AND ILOC.loc_code=iprc.loc_code
INNER JOIN IMST ON IMST.item_code=ILOC.item_code
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
WHERE RTRIM(LTRIM(iprc.PRICE_CAT)) > '1' and price <> 0
ORDER BY BASE_PRODUCT,PLANT_NO,PRICE_CAT,effect_date

INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, GROUP_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 1, {GROUP_NO|INC=1}, 'G', NULL,'{GROUP_NO}', 'P', '{BASE_PRODUCT}.{PLANT_NO}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

-- 13 records
========

No Customer Price (CPRD)

========
ARTSTR WIP

select -- TOP (100)
1 SESSION_NO,1 TRANS_NO,SORT_LINE_NUM SEQUENCE_CODE,
PLNT.LOC_CODE "LOC_CODE",
(PLNT.PLANT_CODE) PLANT_NO,
LTRIM(RTRIM(ICST.ITEM_CODE)) ASSY_PRODUCT_CODE,
LTRIM(RTRIM(CONST_ITEM_CODE)) COMP_PRODUCT_CODE,
UPPER(LTRIM(CU.ABBR)) COMP_UMS,
QTY QTY_ASSEMBLY, ILC.ITEM_CODE INGITEMLOC ,IMC.ITEM_CODE INGITEM
from icst
LEFT OUTER JOIN UOMS CU ON CU.UOM=ICST.QTY_UOM
INNER JOIN PLNT ON ICST.LOC_CODE=PLNT.LOC_CODE AND ISNUMERIC(PLNT.PLANT_CODE)>0
INNER join iloc on iloc.item_code=icst.item_code AND ILOC.LOC_CODE=ICST.LOC_CODE
inner join imst on imst.item_code=iloc.item_code
LEFT OUTER JOIN ILOC ILC ON ILC.ITEM_CODE=ICST.CONST_ITEM_CODE AND ILC.LOC_CODE=ICST.LOC_CODE
LEFT OUTER JOIN IMST IMC ON IMC.ITEM_CODE=ICST.CONST_ITEM_CODE

INSERT INTO ARTPRSTR
( SESSION_NO, TRANS_NO, SEQUENCE_CODE, ASSY_PRODUCT_CODE, COMP_PRODUCT_CODE, COMP_UMS, QTY_ASSEMBLY, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( {SESSION_NO}, {TRANS_NO|INC=1}, {SEQUENCE_CODE}, '{ASSY_PRODUCT_CODE}.{PLANT_NO}', '{COMP_PRODUCT_CODE}.{PLANT_NO}', '{COMP_UMS}', {QTY_ASSEMBLY}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

note: left outer allows missing items to go through - 5 components for plant 314 added.
(CMD does not have integrity - so structure for invalid products permitted)

======================================================
jobs jobs jobs

Mismatch Items - no ILOC:

select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName",plnt.plant_code plant,(prjp.prod_code) ProjectProduct
,prjp.price
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
and iloc.item_code is null and price_plant_code <>'#'
order by proj.cust_code,prjp.proj_code,cust.name,proj.expir_date,plnt.plant_code,prjp.prod_code

0== job header query ==

with pj as(
select
proj.cust_code
,prjp.proj_code,CUST.NAME "CustName"
from prjp
INNER JOIN CUST ON CUST.CUST_CODE=PRjp.CUST_CODE
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
--inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
--INNER JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join plnt on plnt.plant_code = PROJ.Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
--left outer join imst on imst.item_cat=prjp.prod_code and price_plant_code='#'
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
group by prjp.proj_code,proj.cust_code,cust.name,proj.expir_date
)
SELECT
rtrim(LTRIM(PROJ.CUST_CODE)) CUST_NO,PROJ.EXPIR_DATE,
Left(rtrim(LTRIM(PROJ.PROJ_code)),18) JOB_PO_LOC,
PROJ_NAME,PO,
Left(rtrim(LTRIM(PROJ_name)),18) batch_job_code,
rtrim(LTRIM(PROJ_NAME)) DELV_INST_1,
Left(LTRIM(proj.tax_code),3) TAX_code,
--'IMP' TAX_AUTHORITY,
CA_TERMS_CODE,PROJ.CA_PRICE_PLANT_CODE,
LTRIM(CA_TERMS_CODE) TERMS_CLASS,
--LTRIM(CA_PRICE_PLANT_CODE) PLANT_NO,
Left(rtrim(LTRIM(PO)) ,20) CUSTOMER_PO,
LTRIM(UPPER(PROJ.CA_SALES_ANL_CODE)) CUCLS,
'' SHIP_TO_1,
--Left(RTRIM(LTRIM(ship_name)),30) SHIP_TO_1 ,
Left(RTRIM(LTRIM(PROJ.ship_addr_line_1)),30) SHIP_TO_2 ,
Left(RTRIM(LTRIM(ship_addr_line_2)),30) SHIP_TO_3 ,
CASE LTRIM(TAXBLE_CODE) WHEN 1 THEN 'Y' ELSE 'N' END JOB_TAX_CODE,
Left(concat(SHIP_CITY,' ',SHIP_STATE,' ',SHIP_POSTCD),30) SHIP_TO_4,
Taxj.tax_loc "NOTES_1",
PROJ.EXPIR_DATE "NOTES_2",
--USE_FOR_PROD_LINE_CODE "NOTES_2",
LTRIM(NON_TAX_RSN_CODE) "CUSTOM_1",
LTRIM(PROJ.CA_SLSMN_EMPL_CODE) "CUSTOM_2",
Left(RTRIM(LTRIM(CONTCT_NAME)),30) "CONTACT",
Left(RTRIM(LTRIM(PHONE_NUM_1)),30) PHONE_NO,
Left(RTRIM(LTRIM(PROJ_NAME)),30) "INVOICE_DESC"
,ship_addr_line_1,ship_addr_line_2,
PHONE_NUM_1,PHONE_NUM_2,PHONE_NUM_3,CONTct_NAME,custname
FROM proj
inner join pj on pj.proj_code=proj.proj_code and pj.cust_code=proj.cust_code
INNER JOIN TAXJ ON TAXJ.TAX_CODE=proj.TAX_CODE and taxj.tax_auth=1
where inactive_code = 0
and coalesce (PROJ.expir_date,'12/31/2099')>'1/1/2023'
ORDER BY CUST_NO,PROJ_NAME

------

INSERT INTO ARTJOBHD
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, BATCH_JOB_CODE , TERMS_CLASS, TAX_AUTHORITY, INVOICE_DESC, NOTES_1, NOTES_2, JOB_TAX_CODE, JOB_TAX_CODE_2, JOB_TAX_CODE_3, PLANT_NO, SHIP_TO_1, SHIP_TO_2, SHIP_TO_3, SHIP_TO_4, PHONE_NO, CONTACT, CUSTOMER_PO, CUSTOM_1, CUSTOM_2, COD_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG, MEMO ) VALUES
( 1, {CUST_NO|INC=1}, '{CUST_NO}', '{JOB_PO_LOC}', '{batch_job_code}', NULL, '{TAX_AUTHORITY}', '{INVOICE_DESC}', '{NOTES_1}', '{NOTES_2}', '{JOB_TAX_CODE}', 'Y', 'Y', NULL, '{SHIP_TO_1}', '{SHIP_TO_2}', '{SHIP_TO_3}', '{SHIP_TO_4}', '{PHONE_NO}', '{CONTACT}', '{CUSTOMER_PO}', '{CUSTOM_1}', '{CUSTOM_2}', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'***Import Notes Jan 2023***
PROJ NAME: {PROJ_NAME}
PO: {PO}
CONTACT: {CONTct_NAME}
PHONE1: {phone_num_1}
PHONE2: {phone_num_2}
PHONE3: {phone_num_3}
ship_addr_line_1 {ship_addr_line_1}
ship_addr_line_2 {ship_addr_line_2}
expir_date: {expir_date}
*****************************************
' ); -- {custname}

NOTE: plant ok?
Uses numeric job code as JOB ID -- this should be updated on ib side.

== job detail query ==

WITH JDT AS
(
select --top 100
proj.cust_code,
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB_PO_LOC,
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROD_CODE)) PRODUCT_CODE,
RTRIM(LTRIM(Cc_PRICE_PLANT_CODE)) PLANT_code,
PRJP.INTRNL_LINE_NUM LINE_NO,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = PRJP.PRICE_UOM) UNIT_OF_MEASURE,
left(RTRIM(LTRIM(PROD_DESCR)),35) DESCRIPTION,
PRICE UNIT_PRICE,prjp.effect_date
,PRJP.PROD_CODE
,LAG(PROD_CODE,1,'__') OVER (partition by PRJP.CUST_CODE,PRJP.PROJ_CODE,Cc_PRICE_PLANT_CODE
order by PRJP.CUST_CODE,PRJP.PROJ_CODE,PRJP.PROD_CODE,Cc_PRICE_PLANT_CODE,PRJP.EFFECT_DATE DESC) PREVPROD
,iif(iloc.loc_code is null,(select min(loc_code) from iloc where iloc.item_code=imst.item_code and iloc.inactive_code=0),iloc.loc_code) altclocode
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE AND PROJ.CUST_CODE=PRJP.CUST_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
left outer JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
inner join imst on imst.item_code=prjp.prod_code
WHERE proj.INACTIVE_CODE=0
AND COALESCE(proj.EXPIR_DATE,'12/31/2099' ) >'1/1/23'
)
SELECT JDT.*,plnt.plant_code "PLANT_NO"
FROM JDT
inner join cust on cust.cust_code=jdt.cust_code
inner join plnt on plnt.loc_code=jdt.altclocode AND ISNUMERIC(PLNT.PLANT_CODE)=1
WHERE PREVPROD<>PROD_CODE

INSERT INTO ARTJOBDT
( SESSION_NO, TRANS_NO, JOB_PO_LOC, CUST_NO, PRODUCT_CODE, LINE_NO, UNIT_OF_MEASURE, DESCRIPTION, UNIT_PRICE, TAX_CODE, TAX_CODE_2, TAX_CODE_3, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( 1, (SELECT TRANS_NO FROM ARTJOBHD WHERE JOB_PO_LOC='{JOB_PO_LOC}' AND CUST_NO='{CUST_NO}' )
, '{JOB_PO_LOC}', '{CUST_NO}', '{PRODUCT_CODE}.{PLANT_NO}', {LINE_NO}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', {UNIT_PRICE}, 'Y', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'Y' );

NOTES: Not loving the plant mismatches on detail
If primary plant not specified - takes the lowest value.

------
ZONE
------

SELECT * FROM ZONE

INSERT INTO artzone
( ZONE, DESCRIPTION, PLANT_NO, ZONE_REGION, DEF_TAX_AUTHORITY, IMPORTED_FLAG, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{ZONE_CODE|TRIM=0}', '{DESCR|STRLEN=35}', NULL, NULL, '{TAX_CODE}', 'Y', 'Imported 01/17/2023', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------------
CONTACT
------
SELECT
LTRIM(RTRIM(CCON.CUST_CODE)) ENTITY_ID,
LTRIM(RTRIM(NAME)) NAME,
LTRIM(RTRIM(PHONE_NUM_1)) PHONE_NO,
LTRIM(RTRIM(PHONE_NUM_2)) CELL_NO,
LTRIM(RTRIM(PHONE_NUM_2)) FAX_NO,
LTRIM(RTRIM(JOB_TITLE)) TITLE,
ltrim(rtrim(email_addr)) EMAIL,
* FROM CCON
INNER JOIN CTCT ON CTCT.CONTCT_CODE=CCON.CONTCT_CODE
---
INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{ENTITY_ID}', '{NAME|STRLEN=30}', '{PHONE_NO|STRLEN=30}', '{CELL_NO|STRLEN=30}', '{FAX_NO|STRLEN=30}', '{TITLE}', '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
---------

contacts from ONBASE...

Lq3:
SELECT
--RTRIM(rd.distprocessnum) dist_proc_num,
--RTRIM(dp.distprocessname) dist_proc_name,
--rd.recipientnum OBRecipientNO,
--RTRIM(rd.distributionflags) dist_flags,
RTRIM(replace(rd.idkeywords,'!','')) 'CustomerNO',
RTRIM(sr.recipientname) 'CustomerName',
RTRIM(sr.email) 'Email',
RTRIM(sr.faxnum) 'FaxNO'
FROM hsi.recipientxdist rd
INNER JOIN hsi.stmtrecipient sr ON rd.recipientnum = sr.recipientnum LEFT OUTER JOIN hsi.distprocess dp ON rd.distprocessnum = dp.distprocessnum
ORDER BY rd.distprocessnum, sr.recipientname, rd.distributionflags
lq2:
select cust_no,name from artcust c

Primary:
select * from lq3
left outer join lq2 on lq2.cust_no=lq3.CustomerNO
where lq2.cust_no is not null
order by cust_no

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_NO}', 'Accounts Payable', null, null, '{FAXNO|STRLEN=30}', null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

--------
Contacts Merge (If email from cmd and ob - use the cmd)

SELECT ENTITY_ID,UPPER( EMAIL) EMAIL ,COUNT(*),MIN(NAME),MAX(NAME),MIN(RECKEY) ORIGKEY,MAX(RECKEY) NEWKEY FROM CCTCONT
WHERE EMAIL IS NOT NULL
GROUP BY 1,2
HAVING COUNT(*)>1

(export a csv)

update cctcont set email_opt_1='Y' ,email_opt_2='Y' WHERE RECKEY = {ORIGKEY}; /* {EMAIL} */
DELETE FROM CCTCONT WHERE RECKEY={NEWKEY};

----

contacts deleted all where email null and cell_no null

------------ Even MOre Onbase contacts

select lq1.*,lq2.*,1 ct,lq3.contemail from lq2
left outer join lq1 on lq1.cname=lq2.rname
left outer join lq3 on lq3.cust_no=lq1.cust_code and lq2.email=lq3.contemail
where not (cust_code is null);

select ltrim(rtrim(cust_code)) cust_code,upper(name) cname from cust;

select upper(recipientname) rname,upper(ltrim(rtrim(email))) email,1 rcpct from hsi.stmtrecipient
where coalesce(email,'')<>'';

SELECT CUST_NO,c.NAME "CustomerName",
c.CONTACT "CustomerContact",cc.cell_no,cc.phone_no
,CC.NAME "ContName",upper(cc.email) "ContEmail",
email_opt_1 "EmailInv",email_opt_2 "EmailStmt"
FROM ARTCUST C
INNER JOIN CCTCONT CC ON CC.ENTITY_ID=C.CUST_NO
where cc.email is not null
order by c.cust_no,cc.reckey

INSERT INTO cctcont
( RECKEY, ENTITY_TYPE, ENTITY_ID, NAME, PHONE_NO, CELL_NO, FAX_NO, TITLE,EMAIL, EMAIL_OPT_1, EMAIL_OPT_2, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{CUST_code}', 'Accounts Payable', null, null, null, null, '{EMAIL|STRLEN=80}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

----

Update Prods From Class:

SELECT product_code,PRODUCT_CLASS,SELLABLE_FLAG,PRODUCT_TYPE,INGREDIENT_TYPE, (SELECT PC.STD_SELLABLE_FLAG FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_SELLABLE_FLAG,
(SELECT PC.STD_PRODUCT_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_PRODUCT_TYPE,
(SELECT PC.STD_ingredient_TYPE FROM ARTPRCLS PC WHERE PC.CLASS_NO=P.PRODUCT_CLASS) STD_ingredient_TYPE
FROM ARTPROD P

update artprod p
SET SELLABLE_FLAG='{STD_SELLABLE_FLAG}',
INGREDIENT_TYPE='{STD_INGREDIENT_TYPE}',
PRODUCT_TYPE='{STD_PRODUCT_TYPE}'
where product_code='{PRODUCT_CODE}';

------
in mssql: {set cust inactive}

select
CONCAT( 'UPDATE ARTCUST C SET ACTIVE_FLAG=''N'' WHERE CUST_NO = ''',RTRIM(LTRIM(CUST_CODE)),''';')
from cust
where inactive_code <> 00

===
Update sales g/l accounts by plant
(Setup Plants with default g/l!!!!)

with ga (act,s1,s2,s3,s4,actnm) as
(select gl_account,segment_1,segment_2,segment_3,segment_4,max(description)
from gltacct a
where segment_4 between '300' and '998'
group by gl_account,segment_1,segment_2,segment_3,segment_4
)
,pr (pact,ps1,ps2,ps3,ps4,plt) as
(select GL_ACCOUNT ,segment_1,segment_2,segment_3,segment_4,p.plant_no from artprod p
inner join gltacct a on a.gl_account=p.sales_gl
group by gl_account,segment_1,segment_2,segment_3,segment_4,plant_no)
select * from ga
inner join pr on pr.ps1=ga.s1 and pr.ps2=ga.s2 and pr.plt=ga.s4
order by s1,s4

update artprod p set sales_gl = '{act}' WHERE sales_gl='{pact}' and plant_no = '{plt}';

NOTE: This could be done more generically by taking part of acct from artprcls and some from the g/l pattern

</pre>