View source for Command Import: Rain MO Work Notes

<pre>


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





</pre>