Command Import: Rain MO Work Notes
Jump to navigation
Jump to search
PLANTS===========================
SELECT * FROM PLNT
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' );
ARCUCLS============================
SELECT RTRIM(LTRIM(SALES_ANL_CODE)) SALES_ANL_CODE,DESCR FROM SANL
INSERT INTO artcucls
( CLASS_NO, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{sales_anl_code|TRIM=0}', '{DESCR|LEN=35}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
PRODCLS==================
primary: SELECT RTRIM(LTRIM(ITEM_CAT)) NEWID, ICAT.* FROM ICAT
---
INSERT INTO artprcls
( CLASS_NO, DESCRIPTION, SUBTOTAL, DESC_FROM_PROD_FLAG, WEIGHT_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{NEWID}', '{DESCR}', 'Y', 'N', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
price grp=======================
SELECT
RTRIM(LTRIM(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 ) VALUES
( '{GROUP_NO}', '{DESCRIPTION}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
TAX=================================
--TAX (This works when 1 level tax)
select LTRIM(LEFT(TAX_LOC,3)) TXLOC,
LTRIM(TAX_AUTH) "LVL",
COALESCE(PRIM_RATE_EFFECT_DATE,'1/1/1900') RATEDATE,
TAXL.* from TAXL
ORDER BY LTRIM(TAX_LOC)
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
( '{TXLOC}', 1, '{DESCR}', 1, {LVL}, {PRIM_RATE_CURR_PCT}, 1, '{RATEDATE}', '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
( '{TXLOC}', '{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, {TXLOC|INC=1}, '{TXLOC}', '{TXLOC}', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
==SALESPERSON=====================
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}' );
Note- Needs Trimmming...
Adjust: update artslspn
set employee_no=rtrim(ltrim(employee_no));
UofM======================
ARTUMS... (ALL FOR 3 UM :( )
LQ1:
SELECT * FROM aRTUMS
LQ2:
SELECT
LTRIM(UPPER(ABBR)) UM,
UOMS,* FROM UOMS
LQ3:
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
Primary:
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 not (UNIT_OF_MEASURE IS not NULL)
Update:
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.
GL Acct======================
Select acct_code,descr,short_descr,sum_flag
,CASE acct_SIGN
WHEN '+' THEN 'D' ELSE 'C' END DR_CR_FLAG,
case ACCT_TYPE
when 'A' THEN 'B'
when 'L' THEN 'B'
ELSE 'I' END ACCOUNT_TYPE,
ACCT_TYPE,acct_sign,limit_cost_center_flag FROM acct
ORDER BY ACCT_CODE
INSERT INTO gltacct
( GL_ACCOUNT, DESCRIPTION, ACCOUNT_TYPE, DR_CR_FLAG, IMPORTED_FLAG, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( '{ACCT_CODE|TRIM=0}', '{DESCR|TRIM=0}', '{ACCOUNT_TYPE}', '{DR_CR_FLAG}', 'Y', 'AcctType: {acct_type}', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
--
Usage - do manually cmd: SELECT * from usge
vs ditusecl
==truck==========================
select * from truc
/* Truck */
INSERT INTO ARTTRUCK
( TRUCK_NO, TARE_WEIGHT, LAST_TARE_DATE, HOME_PLANT_NO, PLATE_NUMBER, NOTES_1, NOTES_2, NOTES_3, NOTES_4, NOTES_5, EXPIRATION_DATE, MEMO, ACTIVE_FLAG ) VALUES
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/dd/yyyy
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}'
); /* ------------------------------------ */
==Drivers=================================
SELECT
* from empl
where driv_flag = 1
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}
' );
--Prod---
lq2:
SELECT lTRIM(ILOC.LOC_CODE) LOC_CODE,lTRIM(PLNT.PLANT_CODE) PLANT_CODE,
iif( len(ltrim(plnt.plant_code))=1, '0','') PLT0 ,
coalesce(max_load_size,0) mxldsz,
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,LTRIM(imst.ITEM_CAT) CATID,
COALESCE (IMST.PCT_AIR,0) PCT_AIR,
COALESCE (IMST.WATER_CEM_RATIO,0) WATER_CEM_RATIO,
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
select lq2.*,
REPLACE(DESCR, '''', '~' ) DESCR2
from lq2
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 ) VALUES
( '{ITEM_CODE}.{PLT0}{PLANT_CODE}', '{PRICEUM}', '{DESCR2|STRLEN=35}', '{SHORT_DESCR}', NULL, '{STOCKED}', '{TAX_CODE_1}', 'Y', 'Y', '{ITEM_CODE}', '{CATID}', '{PLANT_CODE}', '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' );
-- NOTE SEE QUERY FOR IMPROVEMENTS. MOVE REPLACE INTO MAIN QUERY. LEFT INSTEAD OF |STRLEN ETC.
==Customers=========================================
select
LTRIM(RTRIM(cust.CUST_CODE)) CUST_NO,
CUST.NAME,
CUST.ADDR_LINE_1,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,
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' );
== Product Prices ==================
SELECT
rtrim(LTRIM(ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,PRICE UNIT_PRICE,
IPRC.*
FROM IPRC
INNER JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
WHERE iprc.PRICE_CAT LIKE '%#%'
UpdATE ARTPROD P SET P.list_PRICE={UNIT_PRICE} WHERE base_product = '{BASE_PRODUCT}' and plant_no = '{PLANT_NO}';
Note: Plant no's in short form '1' not '01' at this point in conversion so can't do as product_code = '{BASE_PRODUCT}.{PLANT_NO}';
==Cust PRICES ====================
-- All PLANTS
SELECT
RTRIM(LTRIM(CPRD.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(CPRD.PROD_CODE)) BASE_PRODUCT,coalesce(PRICE,0) UNIT_PRICE,UPPER(PU.ABBR) UNIT_OF_MEASURE,
ILOC.LOC_CODE,right(PLNT.PLANT_CODE+100,2) PLANT_NO,
CPRD.*
FROM CPRD
INNER JOIN ILOC ON ILOC.ITEM_CODE=CPRD.PROD_CODE
INNER JOIN PLNT ON PLNT.LOC_CODE=ILOC.LOC_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=CPRD.PRICE_UOM
where price_plant_code like '%#'
INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 2, {CUST_NO|INC=1}, 'C', '{CUST_NO}', 'P', '{BASE_PRODUCT}.{PLANT_NO}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
-- By Plant
SELECT
RTRIM(LTRIM(CPRD.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(CPRD.PROD_CODE)) BASE_PRODUCT,coalesce(PRICE,0) UNIT_PRICE,UPPER(PU.ABBR) UNIT_OF_MEASURE,
right(PLNT.PLANT_CODE+100,2) PLANT_NO,
CPRD.*
FROM CPRD
INNER JOIN PLNT ON PLNT.plant_CODE=cprd.price_plant_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=CPRD.PRICE_UOM
where price_plant_code not like '%#'
/* CustPrice All Plt - */
INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 3, 1, 'C', 'P0000100', 'P', 'DELIVERYTEL.12', 'EA', 125, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
INSERT INTO ARTPRICE
( SESSION_NO, TRANS_NO, PRICE_TYPE, CUST_NO, PRICE_CODE, PRODUCT_CODE, UNIT_OF_MEASURE, UNIT_PRICE, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG ) VALUES
( 3, 2, 'C', 'S0000235', 'P', 'D0101119.12', 'EA', 2.06, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
Notes: See if enought plant specific prices to matter.
Group Prices===============================----
---- And Not Group Prices
This is example where 42K "group" prices found ... so preprocessing....
-- Make List prices from "Group Prices" base level - when all the same
-- Make list prices from "Group Prices" - when cat price identical and no list price specified
SELECT 1 ct,
rtrim(LTRIM(IPRC.ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
min (COALESCE(PRICE,0)) MIN_UNIT_PRICE,
max (COALESCE(PRICE,0)) Max_UNIT_PRICE,
min (iif( isnumeric(price_cat)<>0, cast(price_cat as integer),0)) Min_GROUP_NO, -- Converts '#' (List) to grp 0
max (iif( isnumeric(price_cat)<>0, cast(price_cat as integer),0)) Max_GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,max(imst.descr) descr
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where (iif( isnumeric(price_cat)<>0, cast(price_cat as integer),0))>0 -- Do not process if list price (cat #) is setup
group by
LTRIM(IPRC.ITEM_CODE),
rtrim(ltrim(plnt.plant_code)),
UPPER(PU.ABBR)
having min (COALESCE(PRICE,0)) = max (COALESCE(PRICE,0)) -- Only include if all prices the same
UpdATE ARTPROD P SET P.list_PRICE={MIN_UNIT_PRICE}, memo =
case memo
when '' then 'MM/DD/YYYY - Set List Price from identical Group Prices'
else '
Set List Price from identical Group Prices' end
WHERE base_product = '{BASE_PRODUCT}' and plant_no = '{PLANT_NO}' AND LIST_PRICE=0;
-- Mixes that do not match the pattern Detail Report
with aaa as -- main group price + 0 List price -- MM/DD/YYYY Detail of mix grp prices that do not match the pattern
(
--Inner query from group prices (after adding price group header)
7SELECT 1 ct,
LTRIM(IPRC.ITEM_CODE) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,COALESCE(PRICE,0) UNIT_PRICE,
iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,imst.descr,
imst.item_cat,imst.inactive_code,iprc.price
,lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat) PrevCatPrice
,iif( lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)=0,0,
price-lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)) PriceChg,
case iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) when 0 then 0.00 when 1 then 0.00 when 2 then 7.00
when 3 then 8.00 when 4 then 8.00 when 5 then 6.00 else -.99 end ExpPriceChg -- Pattern Price Chg
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat=1
--order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat
--WHERE iprc.PRICE_CAT not LIKE '%#%'
)
select aaa.* from aaa where pricechg<>expPriceChg
=== -- ====== Mixes that do not match report Insert into group work
--MM/DD/YYYY All Group Price Detail of mix grp prices that do not match the pattern (e.g. if one price does not match - include all for base price)
with aaa as
(
SELECT 1 ct,
LTRIM(IPRC.ITEM_CODE) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
imst.inactive_code,
iif( lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)=0,0,
price-
lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)) PriceChg,
case iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) when 0 then 0.00 when 1 then 0.00 when 2 then 7.00
when 3 then 8.00 when 4 then 8.00 when 5 then 6.00 else -.99 end ExpPriceChg
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat=1
) ,
bbb as -- FOr the actual detail
(
SELECT
LTRIM(IPRC.ITEM_CODE) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
right(rtrim(ltrim(100+plnt.plant_code)),2) Plant_no0,
COALESCE(PRICE,0) UNIT_PRICE,
iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,imst.descr,
imst.item_cat,imst.inactive_code,iprc.price
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat=1
)
,
ccc as
(select base_product,plant_no,sum(ct) ct from aaa where pricechg<>expPriceChg group by base_product,plant_no)
select * from ccc
inner join bbb on bbb.base_product=ccc.base_product and ccc.plant_no=bbb.plant_no
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_NO0}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
-- end grp price mixes non pattern
== begin grp price mixes that match pattern==========
(Nearly same query on pattern match)
--mm/dd/yyyy All Group Price Detail of mix grp prices that DO match the pattern (e.g. if one price does not match - include all for base price)
with aaa as
(
SELECT 1 ct,
rtrim(LTRIM(IPRC.ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
imst.inactive_code,
iif( lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)=0,0,
price-
lag(price,1,0) OVER (partition by imst.item_cat,imst.item_code,plnt.plant_code order by imst.item_cat,imst.item_code,plnt.plant_code,iprc.price_cat)) PriceChg,
case iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) when 0 then 0.00 when 1 then 0.00 when 2 then 7.00
when 3 then 8.00 when 4 then 8.00 when 5 then 6.00 else -.99 end ExpPriceChg
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat=1
) ,
bbb as -- FOr the actual detail
(
SELECT
rtrim(LTRIM(IPRC.ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,
right(rtrim(ltrim(100+plnt.plant_code)),2) Plant_no0,
COALESCE(PRICE,0) UNIT_PRICE,
iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,imst.descr,
imst.item_cat,imst.inactive_code,iprc.price
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat=1
) ,
ccc as -- ccc all base products with a mismatch of pattern
(select base_product,plant_no,sum(ct) ct from aaa where pricechg<>expPriceChg group by base_product,plant_no)
select * from bbb
left outer join ccc on bbb.base_product=ccc.base_product and ccc.plant_no=bbb.plant_no
where ccc.base_product is null and bbb.group_no=5 -- only matches and grp 5
--
UpdATE ARTPROD P SET P.list_PRICE={PRICE}, memo =
(case memo when '' then '' else '
' end)||'mm/dd/yyyy - Set List Price from mix price grp 5 when pattern matches'
WHERE base_product = '{BASE_PRODUCT}' and plant_no = '{PLANT_NO}' AND LIST_PRICE=0;
-- End Mixes match pattern update list price
--- Non Mixes Non Identical ------
with aaa as
(
SELECT
rtrim(LTRIM(IPRC.ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,UPPER(PU.ABBR) "UM"
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where imst.item_cat>1
group by imst.item_cat,
IPRC.ITEM_CODE,plnt.plant_code,UPPER(PU.ABBR)
having min (COALESCE(PRICE,0)) <> max (COALESCE(PRICE,0)) -- Only include if all prices the same
),
bbb as -- FOr the actual detail
(
SELECT
rtrim(LTRIM(IPRC.ITEM_CODE)) BASE_PRODUCT,
rtrim(ltrim(plnt.plant_code)) PLANT_NO,UPPER(PU.ABBR) "UM",
right(rtrim(ltrim(100+plnt.plant_code)),2) Plant_no0,
COALESCE(PRICE,0) UNIT_PRICE,
iif( isnumeric(iprc.price_cat)<>0, cast(iprc.price_cat as integer),0) GROUP_NO,
UPPER(PU.ABBR) UNIT_OF_MEASURE,imst.descr,
imst.item_cat,imst.inactive_code,iprc.price
FROM IPRC
left outer JOIN PLNT ON plnt.LOC_CODE=iprc.LOC_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=IPRC.ITEM_CODE
LEFT OUTER JOIN UOMS PU ON PU.UOM=IMST.PRICE_UOM
where item_cat<>1
)
select * from BBB
INNER join AAA on bbb.base_product=AAA.base_product and AAA.plant_no=bbb.plant_no
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
( 4, {GROUP_NO|INC=1}, 'G', NULL,'{GROUP_NO}', 'P', '{BASE_PRODUCT}.{PLANT_NO0}', '{UNIT_OF_MEASURE}', {UNIT_PRICE}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );
==ARTPRSTR STRUCTURE
select
1 SESSION_NO,1 TRANS_NO,SORT_LINE_NUM SEQUENCE_CODE,
PLNT.LOC_CODE "LOC_CODE",
right(PLNT.PLANT_CODE+100,2) 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
from icst
LEFT OUTER JOIN UOMS CU ON CU.UOM=ICST.QTY_UOM
INNER JOIN PLNT ON ICST.LOC_CODE=PLNT.LOC_CODE
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
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' );
-- Product Types... from product class
Manually set artprcls - STD_PRODUCT_TYPE and STD_INGREDIENT_TYPE
UPDATE ARTPROD P
SET PRODUCT_TYPE = (SELECT STD_PRODUCT_TYPE
FROM ARTPRCLS PC
WHERE PC.CLASS_NO=P.PRODUCT_CLASS),
INGREDIENT_TYPE = (SELECT STD_INGREDIENT_TYPE
FROM ARTPRCLS PC
WHERE PC.CLASS_NO=P.PRODUCT_CLASS);
-- Contacts
SELECT
LTRIM(RTRIM(CCON.CUST_CODE)) ENTITY_ID,
left(LTRIM(RTRIM(NAME)),30) NAME,
left(LTRIM(RTRIM(PHONE_NUM_1)),30) PHONE_NO,
left(LTRIM(RTRIM(PHONE_NUM_2)),30) CELL_NO,
left(LTRIM(RTRIM(PHONE_NUM_4)),30) FAX_NO,
left(LTRIM(RTRIM(JOB_TITLE)),30) TITLE,
left(ltrim(rtrim(email_addr)),80) 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,memo ) VALUES
( GEN_ID(GEN_CCCONTKEY,1), 'C', '{ENTITY_ID}', '{NAME}', '{PHONE_NO}', '{CELL_NO}', '{FAX_NO}', '{TITLE}', '{EMAIL}', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'***Import Notes***
Email: {email_addr}
Name: {name}
Phone 1: {phone_num_1} Type:{phone_num_1_type_code}
Phone 2 :{phone_num_2} Type: {phone_num_2_type_code}
Phone 3: {phone_num_3} Type: {phone_num_3_type_code}
Phone 4: {phone_num_4} Type: {phone_num_4_type_code}'
);
==invoices mm/dd/yyyy
-- Success
with inv as (
SELECT
left(taxj.tax_loc,3) tax_auth,
CASE ISNUMERIC(ITEM_REF_CODE) WHEN 0 THEN 0 ELSE ITEM_REF_CODE END INVOICE_NO,
CASE ISNUMERIC(ITEM_REF_CODE) WHEN 0 THEN ITEM_REF_CODE ELSE LTRIM(RTRIM(PROJ.PROJ_NAME)) END JOB_PO_LOC,proj.proj_name,
1 SESSION_NO,1 TRANS_NO,TRANS_DATE "INVOICE_DATE",
LTRIM(RTRIM(ARTB.CUST_CODE)) CUST_NO,
DISC_DATE DISC_VALID_DATE,DUE_DATE,0 DISC_TAKEN,
LTRIM(ARTB.PO) CUSTOMER_PO,
LTRIM(ARTB.PLANT_CODE) PLANT_NO,
LTRIM(PLNT.TAX_CODE) TAX_AUTHORITY,
LTRIM(ARTB.PROJ_CODE) PROJ_CODE,
ARTB.DELV_ADDR "DELIVERY_ADDR_1",
COALESCE(curr_bal_amt,0) CURR_BAL_AMT,
COALESCE(cred_memo_tax_amt,0) CRED_MEMO_TAX_AMT,
COALESCE(tax_amt,0) TAX_AMT,
COALESCE(tax_adj_amt,0) TAX_ADJ_AMT,
COALESCE(debit_memo_tax_amt,0) DEBIT_MEMO_TAX_AMT,
COALESCE(sales_amt,0) SALES_AMT,
COALESCE(cred_memo_amt,0) CRED_MEMO_AMT,
COALESCE(debit_memo_amt,0) DEBIT_MEMO_AMT,
COALESCE(adj_amt,0) ADJ_AMT,
COALESCE(PMT_AMT,0) PMT_AMT,
COALESCE(assgn_amt,0) ASSGN_AMT,
COALESCE(DISC_ALLOWED_AMT,0) DISC_OFFERED
,COALESCE(cred_memo_tax_amt,0)+COALESCE(debit_memo_tax_amt,0)+COALESCE(tax_amt,0)+COALESCE(tax_adj_amt,0) TAX
,COALESCE(sales_amt,0)+COALESCE(cred_memo_amt,0)+COALESCE(debit_memo_amt,0)+COALESCE(adj_amt,0)+COALESCE(assgn_amt,0) DEBIT_PRE_TAX
FROM artb
INNER JOIN PLNT ON PLNT.PLANT_CODE=ARTB.PLANT_CODE
INNER JOIN TAXJ ON TAXJ.TAX_CODE=PLNT.TAX_CODE
LEFT OUTER JOIN PROJ ON PROJ.PROJ_CODE=ARTB.PROJ_CODE
WHERE ARTB.CUST_CODE NOT LIKE '%@#@#%'
and (COALESCE(curr_bal_amt,0)<>0 or trans_date > '1/1/20' )
--ORDER BY ARTB.cust_code desc,ITEM_REF_CODE
)
select
tax+debit_pre_tax DEBIT_AMOUNT,TAX
SALES_TAX_DUE,
tax+debit_pre_tax-CURR_BAL_AMT CREDIT_AMOUNT,
inv.* from inv
--note: the CTE inv allows convenient summarization of complex fields
INSERT INTO ARTINV
( SESSION_NO, TRANS_NO, CUST_NO, INVOICE_DATE, INVOICE_NO, DEBIT_AMOUNT, CREDIT_AMOUNT, INVOICE_TYPE, SALES_TAX_DUE, DISC_OFFERED, DISC_TAKEN, DISC_VALID_DATE, DUE_DATE, TAX_AUTHORITY, JOB_PO_LOC, PLANT_NO, CUSTOMER_PO, VOID_FLAG, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER ) VALUES
( 1, {TRANS_NO|INC=1}, '{CUST_NO}', '{INVOICE_DATE}', {INVOICE_NO}, {DEBIT_AMOUNT}, {CREDIT_AMOUNT}, 'I', {SALES_TAX_DUE}, {DISC_OFFERED}, {DISC_TAKEN}, '{DISC_VALID_DATE}', '{DUE_DATE}', '{TAX_AUTH}' , '{JOB_PO_LOC|STRLEN=18}', '{PLANT_NO}', '{CUSTOMER_PO|STRLEN=20}', 'N', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT' );
===== job header -====
select
rtrim(LTRIM(CUST_CODE)) CUST_NO,
left(rtrim(LTRIM(PROJ_code)),18) JOB_PO_LOC,
left(rtrim(LTRIM(PROJ_name)),18) batch_job_code,
rtrim(LTRIM(PROJ_NAME)) DELV_INST_1,
left(LTRIM(taxj.tax_loc),3) TAX_AUTHORITY,
LTRIM(CA_TERMS_CODE) TERMS_CLASS,
LTRIM(CA_PRICE_PLANT_CODE) PLANT_NO,
left(rtrim(LTRIM(PO)) ,20) CUSTOMER_PO,
LTRIM(UPPER(CA_SALES_ANL_CODE)) CUCLS,
LEFT(RTRIM(LTRIM(ship_name)),30) SHIP_TO_1 ,
LEFT(RTRIM(LTRIM(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,
RTRIM(CONCAT (
IIF (LEN(SHIP_CITY)>1,CONCAT(RTRIM(LTRIM(SHIP_CITY)),', '),'') ,
IIF (LEN(SHIP_STATE)>1,CONCAT(RTRIM(LTRIM(SHIP_STATE)),' '),'') ,
IIF (LEN(SHIP_POSTCD)>1,RTRIM(LTRIM(SHIP_POSTCD)),'')
)) SHIP_TO_4,
RTRIM(inactive_code) "NOTES_1",
USE_FOR_PROD_LINE_CODE "NOTES_2",
LTRIM(NON_TAX_RSN_CODE) "CUSTOM_1",
LTRIM(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",
PROJ.* FROM proj
INNER JOIN TAXJ ON TAXJ.TAX_CODE=proj.TAX_CODE
ORDER BY CUST_NO,PROJ_NAME
model:
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}', '{TERMS_CLASS}', '{TAX_AUTHORITY}', '{INVOICE_DESC}', '{NOTES_1}', '{NOTES_2}', '{JOB_TAX_CODE}', 'Y', 'Y', '{PLANT_NO}', '{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 Oct 2020***
PROJ NAME: {PROJ_NAME}
PO: {PO}
CONTACT: {CONTct_NAME}
PHONE1: {phone_num_1}
PHONE2: {phone_num_2}
PHONE3: {phone_num_3}
PHONE4: {phone_num_4}
ship_addr_line_1 {ship_addr_line_1}
ship_addr_line_2 {ship_addr_line_2}
expir_date: {expir_date}
guid: {guid}
*****************************************
' );
= Job Detail
WITH JDT AS -- CTE Used to filter multi-product items based on latest effect_date
(
select --top 100 /* - Added join to iloc to filter valid */
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB_PO_LOC,
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROD_CODE)) PRODUCT_CODE,PRJP.PROD_CODE,
RTRIM(LTRIM(Cc_PRICE_PLANT_CODE)) PLANT_NO,
right(Cc_PRICE_PLANT_CODE+100,2) PLANT_NO0,
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
,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
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
INNER JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
)
SELECT * FROM JDT
WHERE PREVPROD<>PROD_CODE
-- Note: LAG on PREVPROD set in descending effect date order so first is latest. Where clause then filters any case where older records with same prod code follow latest.
Note - when dup prods in a job take last based on effect date
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_NO0}', {LINE_NO}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', {UNIT_PRICE}, 'Y', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'Y' );
NOTES:
Inner join on ILOC to deal with missing items hell
Product codes have 0# format for plants - so using PLANT_NO0 field for that.
===
--Job Details not imported query:
---
WITH JDT AS -- CTE Used to filter multi-product items based on latest effect_date
(
select --top 100 /* MM/DD/YYYY - Added join to iloc to filter valid */
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB,
PRJP.PROD_CODE,
right(Cc_PRICE_PLANT_CODE+100,2) PLANT_NO,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = PRJP.PRICE_UOM) "UofM",
LEFT(RTRIM(LTRIM(PROD_DESCR)),35) DESCRIPTION,
PRICE UNIT_PRICE,prjp.effect_date
,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,
LAG(price,1,0.00) OVER (partition by PRJP.CUST_CODE,PRJP.PROJ_CODE,Cc_PRICE_PLANT_CODE,prjp.prod_code
order by PRJP.CUST_CODE,PRJP.PROJ_CODE,PRJP.PROD_CODE,Cc_PRICE_PLANT_CODE,PRJP.EFFECT_DATE DESC) PriceUsed,
iloc.item_code "LocItem",iloc.loc_code
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
left JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
)
SELECT jdt.*,1 ct,
iif( (LocItem is null), 'CMD Item/Plant Missing', 'Older Version of Item' ) "Reason"
FROM JDT
WHERE (PREVPROD=PROD_CODE) or (loc_code is null)
-- Note: LAG on PREVPROD set in descending effect date order so first is latest. Where clause then filters any case where older records with same prod code follow latest.
----
Job Detail (WIP)... -- ?????
Proj Detail for legit products??
lq1:
select
LTRIM(PRJP.PROJ_CODE) JOB_PO_LOC,
LTRIM(PRJP.CUST_CODE) CUST_NO,
LTRIM(PRJP.PROD_CODE) PRODUCT_CODE,
LTRIM(proj.CC_PRICE_PLANT_CODE) PLANT_NO,
LTRIM(PROJ.PROJ_NAME) PROJNAME,
PRJP.INTRNL_LINE_NUM LINE_NO,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = PRJP.PRICE_UOM) UNIT_OF_MEASURE,
LTRIM(PROD_DESCR) DESCRIPTION,
PRICE UNIT_PRICE,
PRJP.*
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE
order by cust_code,proj_code,intrnl_line_num
lq3:
SELECT * FROM artprod
Main:
SELECT
trim(lq1.product_code)||'.'||trim(lq1.plant_no) "PRC",lq1.* FROM lq1
left outer join lq3 on
lq3.product_code = trim(lq1.product_code)||'.'||trim(lq1.plant_no)
where lq3.product_code is null
order by cust_no,job_po_loc,line_no
"{CUST_NO}","{JOB_PO_LOC}","{PLANT_NO}","{PROJNAME}","{PRODUCT_CODE}","{PRC}"
=== Job Detail
=====================
field analysis trick with QG
select top 10 * from xxx
create the megaquery with the "Grab Fields"
select '~' Fld,count(*) NonNullCT,count(distinct(~)) "Distinct",min(cast(~ as char(40))) "First",max(cast(~ as char(40))) "Last" from cprd where ~ is not null union all
== add addition products from job detail ==
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
( '{ITEM_CODE}.{PLT0}{PLANT_CODE}', '{PRICEUM}', '{DESCR2}', '{SHORT_DESCR}', NULL, '{STOCKED}', '{TAX_CODE_1}', 'Y', 'Y', '{ITEM_CODE}', '{CATID}', '{PLANT_CODE}', '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}', NULL, NULL, NULL, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'MM/DD/YYYY ** Add CMD products from Jobs with no ILOC activity **
DESCR: {DESCR}
TAX RATE CODE:{TAX_RATE_CODE}
NON TAX RSN CODE: {NON_TAX_RSN_CODE}
LOC: {LOC_CODE}
INACTIVE: {INACTIVE_CODE}
' );
== Job Det no ILOC
WITH JDT AS -- CTE Used to filter multi-product items based on latest effect_date -- Job Det for no ILOC based on PREV Job Det
(
select --top 100 /* Added join to iloc to filter valid */
RTRIM(LTRIM(PRJP.PROJ_CODE)) JOB_PO_LOC,
RTRIM(LTRIM(PRJP.CUST_CODE)) CUST_NO,
RTRIM(LTRIM(PRJP.PROD_CODE)) PRODUCT_CODE,PRJP.PROD_CODE,
RTRIM(LTRIM(Cc_PRICE_PLANT_CODE)) PLANT_NO,
right(Cc_PRICE_PLANT_CODE+100,2) PLANT_NO0,
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
,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
from prjp
INNER JOIN PROJ ON PROJ.PROJ_CODE=PRJP.PROJ_CODE
inner join plnt on plnt.plant_code = Cc_PRICE_PLANT_CODE
INNER JOIN IMST ON IMST.ITEM_CODE=PRJP.PROD_CODE
LEFT OUTER JOIN ILOC ON ILOC.ITEM_CODE=PRJP.PROD_CODE and PLNT.LOC_CODE=ILOC.LOC_CODE
WHERE ILOC.LOC_CODE IS NULL
)
SELECT * FROM JDT
WHERE PREVPROD<>PROD_CODE
-- Note: LAG on PREVPROD set in descending effect date order so first is latest. Where clause then filters any case where older records with same prod code follow latest.
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_NO0}', {LINE_NO}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', {UNIT_PRICE}, 'Y', 'Y', 'Y', 'Y', CAST('TODAY' AS TIMESTAMP), 'Y' );
====
=====
DELETED GROUP 5
AFTER TURNED TO LIST
select pp.unit_price,p.list_price "ListPrice",pp.* from artprice pp
inner join artprod p on p.product_code=pp.product_code
where pp.group_no='5'
and p.list_price=0
ETC
============
--ARTORDHD DEC 02 2020
SELECT -- top (100)
1 SESSION_NO,
1 TRANS_NO,
rtrim(LTRIM(CUST_CODE)) CUST_NO,
1 ORDER_CODE,
ltrim(order_code) ORDER_DESC,
cast(order_date as date) DELIVERY_DATe,
datepart(hh,start_time)*100+datepart(mi,start_time) DELIVERY_TIME,
RTRIM(LTRIM(price_plant_code)) PLANT_NO,
left(LTRIM(RTRIM(taxj.tax_loc)),3) TAX_AUTHORITY,
ltrim(rtrim(proj_code)) JOB_PO_LOC,
lot_block JOB_LOT,
LEFT(RTRIM(LTRIM(delv_addr)),30) DELV_INST_1,
LEFT(RTRIM(LTRIM(instr)),30) DELV_INST_2,
Iif(cod_order_amt<>0.0,'Y','N') COD_FLAG,
LEFT(RTRIM(LTRIM(ship_addr_line)),30) SHIP_TO_1 ,
RTRIM(CONCAT (
IIF (LEN(SHIP_CITY)>1,CONCAT(RTRIM(LTRIM(SHIP_CITY)),', '),'') ,
IIF (LEN(SHIP_STATE)>1,CONCAT(RTRIM(LTRIM(SHIP_STATE)),' '),'') ,
IIF (LEN(SHIP_POSTCD)>1,RTRIM(LTRIM(SHIP_POSTCD)),'')
)) SHIP_TO_2,
left(ltrim(rtrim(PO)),20) customer_po,
slsmn_empl_code,
convert( int, convert (varbinary, concat('0x',map_lat), 1 ) )/1000000.0 GPS_Lat,
convert( int, convert (varbinary, concat('0x',map_long), 1 ) )/1000000.0 GPS_Lon,
0.2 gps_radius,
1 LAST_CHANGE_DATETIME,
1 LAST_CHANGE_USER,
1 ACTIVE_FLAG,
contact_code,
guid,
taxble_code, po
FROM ordr
INNER JOIN TAXJ ON TAXJ.TAX_CODE=ordr.TAX_CODE
where order_date BETWEEN '5/1/2020' AND '5/31/20'
// first batch
INSERT INTO ARTORDHD
( SESSION_NO, TRANS_NO, CUST_NO, ORDER_CODE, ORDER_DESC, DELIVERY_DATE, DELIVERY_TIME, PLANT_NO, TAX_AUTHORITY, JOB_PO_LOC, JOB_LOT, COD_FLAG, SHIP_TO_1, SHIP_TO_2, customer_po, GPS_Lat, GPS_Long, gps_radius, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,
custom_1, MEMO ) VALUES
( 1, {TRANS_NO|INCA=1}, '{CUST_NO}', {ORDER_CODE|INCA=0}, 'Import Order: {ORDER_DESC}', '{DELIVERY_DATE}', {DELIVERY_TIME}, '{PLANT_NO}', '{TAX_AUTHORITY}', '{JOB_PO_LOC}', '{JOB_LOT}', '{COD_FLAG}', '{SHIP_TO_1}', '{SHIP_TO_2}', '{customer_po}', {GPS_Lat}, {GPS_Lon}, {gps_radius}, CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'{ORDER_DESC}',
'***Import Notes Dec 2020***
taxble_code {taxble_code}
contact_code: {contact_code}
guid: {guid}
slsmn_empl_code: {slsmn_empl_code}
po: {PO}' );
// model all later batches(needs a coalesce 1 when no order) (REVISE)
INSERT INTO ARTORDHD
( SESSION_NO, TRANS_NO, CUST_NO, ORDER_CODE, ORDER_DESC, DELIVERY_DATE, DELIVERY_TIME, PLANT_NO, TAX_AUTHORITY, JOB_PO_LOC, JOB_LOT, COD_FLAG, SHIP_TO_1, SHIP_TO_2, customer_po, GPS_Lat, GPS_Long, gps_radius, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG,
custom_1, MEMO ) VALUES
( 1, 1+(select max(trans_no) from artordhd where session_no=1), '{CUST_NO}', 1+(select max(trans_no) from artordhd where session_no=1), 'Import Order: {ORDER_DESC}', '{DELIVERY_DATE}', {DELIVERY_TIME}, '{PLANT_NO}', '{TAX_AUTHORITY}', '{JOB_PO_LOC}', '{JOB_LOT}', '{COD_FLAG}', '{SHIP_TO_1}', '{SHIP_TO_2}', '{customer_po}', {GPS_Lat}, {GPS_Lon}, {gps_radius}, CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' ,
'{ORDER_DESC}',
'***Import Notes Dec 2020***
taxble_code {taxble_code}
contact_code: {contact_code}
guid: {guid}
slsmn_empl_code: {slsmn_empl_code}
po: {PO}' );
=====
-- ORDER DETAIL Close
-- === ARTORDDT
--
SELECT --top 100 -- ord dt -- revise (qtyperld)
1 session_no,
1 trans_no,
ORDL.ORDER_DATE, LTRIM(RTRIM(ORDL.ORDER_CODE)) ORDER_CODE,
rtrim(LTRIM(ORDR.CUST_CODE)) CUST_NO,
COALESCE(SORT_LINE_NUM,0) line_no,
LTRIM(RTRIM(PROD_CODE)) PROD_CODE,
RTRIM(LTRIM(price_plant_code)) PRODUCT_PLANT,
IIF(PRICE>0,PRICE,0) unit_price,
0 unit_pct_disc,
ORDER_QTY qty_ordered,
ORDL.taxble_code,
ORDL.non_tax_rsn_code,
IIF(ORDL.taxble_code=1,'Y','2') tax_code,
'M' PRICE_SOURCE,
DELV_QTY QTY_DELIVERED,
dflt_load_qty QTY_PER_LOAD,
(SELECT UPPER(ABBR) FROM UOMS WHERE UOMS.UOM = ORDER_QTY_UOM) UNIT_OF_MEASURE,
--short_prod_descr,
--LEN(PROD_DESCR) LENPR,
IIF( LEN(PROD_DESCR)<=35, PROD_DESCR,SHORT_PROD_DESCR) DESCRIPTION,
'N' AUTO_ADDED_FLAG,
'Y' IMPORTED_FLAG
--,ORDL.*
from ordl
INNER JOIN ORDR ON ORDR.ORDER_DATE=ORDL.ORDER_DATE AND ORDR.ORDER_CODE=ORDL.ORDER_CODE
where ORDr.order_date between :"Beg Ord Date" AND :"End Ord Date"
INSERT INTO ARTORDDT
( session_no, trans_no, ORDER_CODE, CUST_NO, line_no, PRODUCT_CODE, unit_price, unit_pct_disc, qty_ordered, tax_code, PRICE_SOURCE, QTY_DELIVERED, QTY_PER_LOAD, UNIT_OF_MEASURE, DESCRIPTION, AUTO_ADDED_FLAG, IMPORTED_FLAG ) VALUES
( 1,
(SELECT TRANS_NO FROM ARTORDHD WHERE DELIVERY_DATE='{ORDER_DATE}' AND custom_1='{ORDER_CODE}'),
(SELECT CAST(TRANS_NO AS VARCHAR(18))FROM ARTORDHD WHERE DELIVERY_DATE='{ORDER_DATE}' AND custom_1='{ORDER_CODE}'),
'{CUST_NO}', {line_no}, '{PROD_CODE}.{PRODUCT_PLANT}', {unit_price}, {unit_pct_disc}, {qty_ordered}, '{tax_code}', '{PRICE_SOURCE}', {QTY_DELIVERED}, {QTY_PER_LOAD}, '{UNIT_OF_MEASURE}', '{DESCRIPTION}', '{AUTO_ADDED_FLAG}', '{IMPORTED_FLAG}' );
=======================================
Reference
PRODUCT_TYPE_CONCRETE_MIX = 'M'; // 09/23/2005
PRODUCT_TYPE_INGREDIENT = 'I'; // 09/23/2005
PRODUCT_TYPE_AGGREGATE = 'A'; // 09/23/2005
PRODUCT_TYPE_DELIVERY = 'D'; // 09/23/2005
PRODUCT_TYPE_OTHER = 'Z'; // 09/23/2005
PRODUCT_TYPE_ADDON_ADMIX = 'X'; // 3.5.9.1
PRODUCT_TYPE_ADDON_DELIVERY = 'Y'; // 3.5.9.1
PRODUCT_TYPE_MIX_MODIFIER = 'N'; // 3.5.99.39
PRODUCT_TYPE_RECS: array[0..7] of TCodeDescRec = (
(Code: PRODUCT_TYPE_CONCRETE_MIX; Description: 'Concrete Mix'),
(Code: PRODUCT_TYPE_INGREDIENT; Description: 'Ingredient'),
(Code: PRODUCT_TYPE_AGGREGATE; Description: 'Aggregate'),
(Code: PRODUCT_TYPE_DELIVERY; Description: 'Delivery'),
(Code: PRODUCT_TYPE_ADDON_ADMIX; Description: 'Add On-Admixture'),
(Code: PRODUCT_TYPE_ADDON_DELIVERY; Description: 'Add On-Delivery'),
(Code: PRODUCT_TYPE_MIX_MODIFIER; Description: 'Mix Modifier'),
(Code: PRODUCT_TYPE_OTHER; Description: 'Other')); // 3.5.99.39
// product ingredient type constants // 3.2.8.9
// PRODINGRED_TYPE_CEMENT = INGREDIENT_TYPE_CEMENT;
// PRODINGRED_TYPE_COARSE_AGG = INGREDIENT_TYPE_AGGREGATE_COARSE;
// PRODINGRED_TYPE_FINE_AGG = INGREDIENT_TYPE_AGGREGATE_FINE;
// PRODINGRED_TYPE_WATER = INGREDIENT_TYPE_WATER;
// PRODINGRED_TYPE_ADMIX = INGREDIENT_TYPE_ADMIX;
PRODINGRED_TYPE_RECS: array [0..4] of TCodeDescRec = (
(Code: PRODINGRED_TYPE_CEMENT; Description: 'Cement'),
(Code: PRODINGRED_TYPE_COARSE_AGG; Description: 'Coarse Aggregate'),
(Code: PRODINGRED_TYPE_FINE_AGG; Description: 'Fine Aggregate'),
(Code: PRODINGRED_TYPE_WATER; Description: 'Water'),
(Code: PRODINGRED_TYPE_ADMIX; Description: 'Admixture'));