Command Import: Hedger Work Notes

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}'   );

GL

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###
==========

Plant

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'    );

Unit of Measure

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