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