Latest revision as of 13:00, 14 July 2023

This process will convert the mix designs exported from Keystone Batch into Keystone Dispatch/Accounting systems. Several assumptions are made during this process so expect to review the data and expect some changes will be needed to the resulting data.

Assumptions:

Units of Measure default to CY for mixes, OZ for admixes, GL for water and LB for Aggregates and Cements.
Any Inserted mixes set to Class CO. (Mixes are inserted when they exist in the excel spreadsheet but not in Keystone).
All Ingredients exist in Keystone. (We do not automatically add ingredients).

Export Keystone Batch

New Style March 2019. This uses a big query and small Update Model query. Mix Insert and Ingredient Insert are separate steps AND an optional query to find missing ingredients can be run.

Prepare the File

Open .txt file from batch

Highlight Header Record:

Search: “, “ Replace: “,” (Extra space in header makes it hard to use as Excel data connection)
Search: “c-name1,a-tar1” Replace: “c-name1,c-tar1” (Glitch where first a-tar1 written where c-tar1 belongs)
Search: "#mix" Replace "mix"
Search "-" Replace "" (blank)

Or just replace header with :

mix,desc,metric,slump,yield,air,time,max,price,wname1,wtar1,wname2,wtar2,wname3,wtar3,wname4,wtar4,aname1,atar1,aname2,atar2,aname3,atar3,aname4,atar4,aname5,atar5,aname6,atar6,aname7,atar7,aname8,atar8,cname1,ctar1,cname2,ctar2,cname3,ctar3,cname4,ctar4,cname5,ctar5,cname6,ctar6,xname1,xtar1,xname2,xtar2,xname3,xtar3,xname4,xtar4,xname5,xtar5,xname6,xtar6,xname7,xtar7,xname8,xtar8,xname9,xtar9,xname10,xtar10,xname11,xtar11,xname12,xtar12

Either:

Save as .csv and open in Excel
Copy Paste into Excel and use Data/Text to Columns

Change name of current tab to "Mixes" Save file in .xlsx format

Set Search Replace Results

This is on Options Tab in more recent versions of QueryGen. Adjust to the target plant number and set the delimiter ("-" or ".").

Example for plant 01, with a - delimeter and with mixes in class "CO":

~plant~,"01"
~delimiter~,"-"
~productclass~,"CO"

QueryGen

You will need a modern (2018) or later querygen. 32 or 64 bit to match you copy of Excel.

Source For All Operations

LQ1: DB Kind: Excel

C:\{path}\{filename}.xlsx

SELECT * FROM [mixes$]

Update/Insert Mix Products into ARTPROD

For each mix:

Updates the the basic fields of slump, air, mixer time.
Inserts the Mix If it does not exists. NOTE- This item will need to be updated.
Deletes the old Structure.
Then for each ingredient column, determines if the qty is >0 and if so inserts a structure record.

Primary Tab DB Kind: Local

Select Query:

SELECT UPPER(mix) "mix",UPPER(desc) "desc",metric,slump,yield,air,time,max,price,UPPER(wname1) "wname1",wtar1,UPPER(wname2) "wname2",wtar2,UPPER(wname3) "wname3",wtar3,UPPER(wname4) "wname4",wtar4,UPPER(aname1) "aname1",atar1,UPPER(aname2) "aname2",atar2,UPPER(aname3) "aname3",atar3,UPPER(aname4) "aname4",atar4,UPPER(aname5) "aname5",atar5,UPPER(aname6) "aname6",atar6,UPPER(aname7) "aname7",atar7,UPPER(aname8) "aname8",atar8,UPPER(cname1) "cname1",ctar1,UPPER(cname2) "cname2",ctar2,UPPER(cname3) "cname3",ctar3,UPPER(cname4) "cname4",ctar4,UPPER(cname5) "cname5",ctar5,UPPER(cname6) "cname6",ctar6,UPPER(xname1) "xname1",xtar1,UPPER(xname2) "xname2",xtar2,UPPER(xname3) "xname3",xtar3,UPPER(xname4) "xname4",xtar4,UPPER(xname5) "xname5",xtar5,UPPER(xname6) "xname6",xtar6,UPPER(xname7) "xname7",xtar7,UPPER(xname8) "xname8",xtar8,UPPER(xname9) "xname9",xtar9,UPPER(xname10) "xname10",xtar10,UPPER(xname11) "xname11",xtar11,UPPER(xname12) "xname12",xtar12 FROM LQ1

Update Query:

/* Update / Add if missing: {mix}~delimiter~~plant~ */
UPDATE ARTPROD SET DEFAULT_SLUMP={slump}, percent_air={air}, mixer_time_sec={time}, last_change_datetime=CAST('TODAY' AS DATE),last_change_user='HIT' WHERE PRODUCT_CODE='{mix|TRIM=0}~delimiter~~plant~';
INSERT INTO ARTPROD ( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, TAX_CODE, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, SELLABLE_FLAG, LIST_PRICE, MIXER_TIME_SEC, MAX_LOAD_SIZE, DEFAULT_SLUMP, PERCENT_AIR, IMPORTED_FLAG, NOTES_3, NOTES_4, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)  SELECT '{mix|TRIM=0}~delimiter~~plant~', 'CY', '{desc|STRLEN=35}', 'Y', '{mix|TRIM=0}',  '~productclass~', NULL, 'M', 'Y', 0, {time}, {max}, {slump}, {air}, 'Y', '~plant~',  '***ADDED FROM BATCH*** '||CAST('today' as DATE), CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'  from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTPROD WHERE PRODUCT_CODE= '{mix|TRIM=0}~delimiter~~plant~'  );
delete from artprstr where assy_product_code='{mix|TRIM=0}~delimiter~~plant~';

NOTE: Set Plant numbers for these mixes (stored in NOTES_3).

Execute Query in IBConsole:

Review the results before committing. Try select * from artprod where last_change_datetime = cast('today' as date) for all impacted mixes or select * from artprod where last_change_datetime = cast('today' as date) AND Note_4 like '***ADDED%' to ID just new items. Look for glaring errors such as added mixes where you expected to have just updated a mix. Common errors can be the result of an improper delimiter or plant setup in the Search Replace Results step.

If good Commit else Rollback, fix and repeat.

Insert Mix Design

In this step we assume that their is not already a mix design configured for the mixes.

From Primary tab

Source Query:

SELECT upper(mix) mix,desC,21 SEQUENCE, UPPER(wname1) PRODUCT_CODE,wtar1 QTY, "GL"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,22 SEQUENCE, UPPER(wname2) PRODUCT_CODE,wtar2 QTY, "GL"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,23 SEQUENCE, UPPER(wname3) PRODUCT_CODE,wtar3 QTY, "GL"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,24 SEQUENCE, UPPER(wname4) PRODUCT_CODE,wtar4 QTY, "GL"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,11 SEQUENCE, UPPER(aname1) PRODUCT_CODE,atar1 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,12 SEQUENCE, UPPER(aname2) PRODUCT_CODE,atar2 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,13 SEQUENCE, UPPER(aname3) PRODUCT_CODE,atar3 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,14 SEQUENCE, UPPER(aname4) PRODUCT_CODE,atar4 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,15 SEQUENCE, UPPER(aname5) PRODUCT_CODE,atar5 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,16 SEQUENCE, UPPER(aname6) PRODUCT_CODE,atar6 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,17 SEQUENCE, UPPER(aname7) PRODUCT_CODE,atar7 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,18 SEQUENCE, UPPER(aname8) PRODUCT_CODE,atar8 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,1 SEQUENCE, UPPER(cname1) PRODUCT_CODE,ctar1 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,2 SEQUENCE, UPPER(cname2) PRODUCT_CODE,ctar2 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,3 SEQUENCE, UPPER(cname3) PRODUCT_CODE,ctar3 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,4 SEQUENCE, UPPER(cname4) PRODUCT_CODE,ctar4 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,5 SEQUENCE, UPPER(cname5) PRODUCT_CODE,ctar5 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,6 SEQUENCE, UPPER(cname6) PRODUCT_CODE,ctar6 QTY, "LB"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,31 SEQUENCE, UPPER(xname1) PRODUCT_CODE,xtar1 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,32 SEQUENCE, UPPER(xname2) PRODUCT_CODE,xtar2 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,33 SEQUENCE, UPPER(xname3) PRODUCT_CODE,xtar3 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,34 SEQUENCE, UPPER(xname4) PRODUCT_CODE,xtar4 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,35 SEQUENCE, UPPER(xname5) PRODUCT_CODE,xtar5 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,36 SEQUENCE, UPPER(xname6) PRODUCT_CODE,xtar6 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,37 SEQUENCE, UPPER(xname7) PRODUCT_CODE,xtar7 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,38 SEQUENCE, UPPER(xname8) PRODUCT_CODE,xtar8 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,39 SEQUENCE, UPPER(xname9) PRODUCT_CODE,xtar9 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,40 SEQUENCE, UPPER(xname10) PRODUCT_CODE,xtar10 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,41 SEQUENCE, UPPER(xname11) PRODUCT_CODE,xtar11 QTY, "OZ"UM FROM lq1 where QTY<> 0 union all
SELECT upper(mix) mix,desc,42 SEQUENCE, UPPER(xname12) PRODUCT_CODE,xtar12 QTY, "OZ"UM FROM lq1 where QTY<> 0

Heading Text:

/*CREATE  TABLE TMP_SNO(SNO INTEGER);*/  delete from TMP_SNO;
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'Insert Mix Structure', '127.0.0.1', '3.5.x'); COMMIT;

NOTE: Remove the comment characters, /* and */ around "Create Table..." if the table TMP_SNO does not exist.

Update Query:

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  (  (SELECT SNO FROM TMP_SNO), {mix|INC=1}, {SEQUENCE}, '{mix|TRIM=0}~delimiter~~plant~', UPPER('{PRODUCT_CODE|TRIM=0}~delimiter~~plant~'), '{UM}', {QTY}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y' );

Find Missing Ingredients

Here we can use querygen to create a query that lists all missing ingredients. Keep the same setup as above with LQ1 set to the batch spread sheet and the Search/Repalace configured for ~delimiter~ and ~plant~

Source Query:

SELECT UPPER(wname1) PRODUCT_CODE FROM lq1 where wtar1<> 0 union 
SELECT UPPER(wname2) PRODUCT_CODE FROM lq1 where wtar2<> 0 union 
SELECT UPPER(wname3) PRODUCT_CODE FROM lq1 where wtar3<> 0 union 
SELECT UPPER(wname4) PRODUCT_CODE FROM lq1 where wtar4<> 0 union 
SELECT UPPER(aname1) PRODUCT_CODE FROM lq1 where atar1<> 0 union 
SELECT UPPER(aname2) PRODUCT_CODE FROM lq1 where atar2<> 0 union 
SELECT UPPER(aname3) PRODUCT_CODE FROM lq1 where atar3<> 0 union 
SELECT UPPER(aname4) PRODUCT_CODE FROM lq1 where atar4<> 0 union 
SELECT UPPER(aname5) PRODUCT_CODE FROM lq1 where atar5<> 0 union 
SELECT UPPER(aname6) PRODUCT_CODE FROM lq1 where atar6<> 0 union 
SELECT UPPER(aname7) PRODUCT_CODE FROM lq1 where atar7<> 0 union 
SELECT UPPER(aname8) PRODUCT_CODE FROM lq1 where atar8<> 0 union 
SELECT UPPER(cname1) PRODUCT_CODE FROM lq1 where ctar1<> 0 union 
SELECT UPPER(cname2) PRODUCT_CODE FROM lq1 where ctar2<> 0 union 
SELECT UPPER(cname3) PRODUCT_CODE FROM lq1 where ctar3<> 0 union 
SELECT UPPER(cname4) PRODUCT_CODE FROM lq1 where ctar4<> 0 union 
SELECT UPPER(cname5) PRODUCT_CODE FROM lq1 where ctar5<> 0 union 
SELECT UPPER(cname6) PRODUCT_CODE FROM lq1 where ctar6<> 0 union 
SELECT UPPER(xname1) PRODUCT_CODE FROM lq1 where xtar1<> 0 union 
SELECT UPPER(xname2) PRODUCT_CODE FROM lq1 where xtar2<> 0 union 
SELECT UPPER(xname3) PRODUCT_CODE FROM lq1 where xtar3<> 0 union 
SELECT UPPER(xname4) PRODUCT_CODE FROM lq1 where xtar4<> 0 union 
SELECT UPPER(xname5) PRODUCT_CODE FROM lq1 where xtar5<> 0 union 
SELECT UPPER(xname6) PRODUCT_CODE FROM lq1 where xtar6<> 0 union 
SELECT UPPER(xname7) PRODUCT_CODE FROM lq1 where xtar7<> 0 union 
SELECT UPPER(xname8) PRODUCT_CODE FROM lq1 where xtar8<> 0 union 
SELECT UPPER(xname9) PRODUCT_CODE FROM lq1 where xtar9<> 0 union 
SELECT UPPER(xname10) PRODUCT_CODE FROM lq1 where xtar10<> 0 union 
SELECT UPPER(xname11) PRODUCT_CODE FROM lq1 where xtar11<> 0 union 
SELECT UPPER(xname12) PRODUCT_CODE FROM lq1 where xtar12<> 0

heading query: blank

UPDATE QUERY:

select cast('{PRODUCT_CODE}~delimiter~~plant~' as char(20)) from artLOC L where LOC_ID=0 AND NOT EXISTS (SELECT * FROM ARTPROD P WHERE P.PRODUCT_CODE='{PRODUCT_CODE}~delimiter~~plant~') UNION

Note: Remove last union from the results.

The Results box is a query you can run in query/export or IBConsole that lists missing ingredients. When run in query/export you can export to excel and provide the customer with a list of required ingredients.

Alternate "Live" Ingredient Verification

LQ2: Save Ingredient List and Load LQ3: SELECT * FROM ARTPROD PRIMARY:

select lq2.product_code||'.01' ing_id,lq3.* from lq2
left outer join lq3 on lq3.product_code=lq2.product_code||'.01'

This will compare ingredients in excel list to artprod in ccw###. Refresh the lq3 query and primary to retest.

Old Style

Cleanup the Export File

There are a couple of quirks with the export file that won't play nice with excel. Do the following Search and replaces to the header record:

Search: “, “ Replace: “,” (Extra space in header makes it hard to use as Excel data connection)
Search: “c-name1,a-tar1” Replace: “c-name1,c-tar1” (Glitch where first a-tar1 written where c-tar1 belongs)

Save as a .csv

Convert to Excel

Open .csv
Change Tab Name to “mixes”
Save in .xlsx format

QueryGen

Connect to Excel Sheet

Sample Connect String:

C:\WORK\ACMEMIXES.XLSX

Set Select Query

SELECT * FROM [mixes$]

Set Header

Uncomment Create Table by removing the "/*" and "*/" if it does not exist for first attempt. After that always leave it commented (or remove it).

/*CREATE  TABLE TMP_SNO(SNO INTEGER);*/  delete from TMP_SNO;
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'Insert Mix Structure', '127.0.0.1', '3.4.x'); COMMIT;

This step creates a new Session Number for the import. This will be used in the for the Insert Structure query.

Set Update Model Query

Yes this is long but it's really not that complicated.

For each mix:

Updates the the basic fields of slump, air, mixer time.
Inserts the Mix If it does not exists. NOTE- This item will need to be updated. It assumes a product class of "CO".
Deletes the old Structure.
Then for each ingredient column, determines if the qty is >0 and if so inserts a structure record.

UPDATE ARTPROD SET DEFAULT_SLUMP={slump}, percent_air={air}, mixer_time_sec={time}, NOTES_4=CAST('TODAY' AS DATE)||'Mix Design Update ' WHERE PRODUCT_CODE='{#Mix|TRIM=0}.~plant~';
INSERT INTO ARTPROD ( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, TAX_CODE, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, SELLABLE_FLAG, LIST_PRICE, MIXER_TIME_SEC, MAX_LOAD_SIZE, DEFAULT_SLUMP, PERCENT_AIR, IMPORTED_FLAG, NOTES_4, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)  SELECT '{#Mix|TRIM=0}.~plant~', 'CY', '{desc|STRLEN=35}', 'Y', '{#Mix|TRIM=0}', '~productclass~', '~plant~', 'M', 'Y', 0, {time}, {max}, {slump}, {air}, 'Y', '***ADDED FROM BATCH*** '||CAST('today' as DATE), CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'  from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTPROD WHERE PRODUCT_CODE= '{#Mix|TRIM=0}.~plant~'  );
delete from artprstr where assy_product_code='{#Mix|TRIM=0}.~plant~'; /* Reset {#Mix|INCA=R} */
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name1|TRIM=0}.~plant~'), 'LB', {c-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name2|TRIM=0}.~plant~'), 'LB', {c-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name3|TRIM=0}.~plant~'), 'LB', {c-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name4|TRIM=0}.~plant~'), 'LB', {c-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name5|TRIM=0}.~plant~'), 'LB', {c-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{c-name6|TRIM=0}.~plant~'), 'LB', {c-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name1|TRIM=0}.~plant~'), 'LB', {a-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name2|TRIM=0}.~plant~'), 'LB', {a-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name3|TRIM=0}.~plant~'), 'LB', {a-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name4|TRIM=0}.~plant~'), 'LB', {a-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name5|TRIM=0}.~plant~'), 'LB', {a-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name6|TRIM=0}.~plant~'), 'LB', {a-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name7|TRIM=0}.~plant~'), 'LB', {a-tar7}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar7} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{a-name8|TRIM=0}.~plant~'), 'LB', {a-tar8}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar8} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{w-name1|TRIM=0}.~plant~'), 'GL', {w-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{w-name2|TRIM=0}.~plant~'), 'GL', {w-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{w-name3|TRIM=0}.~plant~'), 'GL', {w-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{w-name4|TRIM=0}.~plant~'), 'GL', {w-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name1|TRIM=0}.~plant~'), 'OZ', {x-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name2|TRIM=0}.~plant~'), 'OZ', {x-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name3|TRIM=0}.~plant~'), 'OZ', {x-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name4|TRIM=0}.~plant~'), 'OZ', {x-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name5|TRIM=0}.~plant~'), 'OZ', {x-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name6|TRIM=0}.~plant~'), 'OZ', {x-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name7|TRIM=0}.~plant~'), 'OZ', {x-tar7}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar7} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name8|TRIM=0}.~plant~'), 'OZ', {x-tar8}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar8} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name9|TRIM=0}.~plant~'), 'OZ', {x-tar9}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar9} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name10|TRIM=0}.~plant~'), 'OZ', {x-tar10}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar10} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name11|TRIM=0}.~plant~'), 'OZ', {x-tar11}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar11} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}.~plant~', UPPER('{x-name12|TRIM=0}.~plant~'), 'OZ', {x-tar12}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar12} >0;

Set Seach Replace Results

This is on Options Tab in more recent versions of QueryGen. Adjust to the target plant number.

Example for plant 01:

~plant~,"01"

Special Case - No Plant Numbers

UPDATE ARTPROD SET DEFAULT_SLUMP={slump}, percent_air={air}, mixer_time_sec={time}, NOTES_4=CAST('TODAY' AS DATE)||'Mix Design Update ' WHERE PRODUCT_CODE='{#Mix|TRIM=0}';
INSERT INTO ARTPROD ( PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION_1, TAX_CODE, BASE_PRODUCT, PRODUCT_CLASS, PLANT_NO, PRODUCT_TYPE, SELLABLE_FLAG, LIST_PRICE, MIXER_TIME_SEC, MAX_LOAD_SIZE, DEFAULT_SLUMP, PERCENT_AIR, IMPORTED_FLAG, NOTES_4, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)  SELECT '{#Mix|TRIM=0}', 'CY', '{desc|STRLEN=35}', 'Y', '{#Mix|TRIM=0}', '~productclass~', NULL, 'M', 'Y', 0, {time}, {max}, {slump}, {air}, 'Y', '***ADDED FROM BATCH*** '||CAST('today' as DATE), CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'  from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM ARTPROD WHERE PRODUCT_CODE= '{#Mix|TRIM=0}'  );
delete from artprstr where assy_product_code='{#Mix|TRIM=0}'; /* Reset {#Mix|INCA=R} */
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name1|TRIM=0}'), 'LB', {c-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name2|TRIM=0}'), 'LB', {c-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name3|TRIM=0}'), 'LB', {c-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name4|TRIM=0}'), 'LB', {c-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name5|TRIM=0}'), 'LB', {c-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{c-name6|TRIM=0}'), 'LB', {c-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {c-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name1|TRIM=0}'), 'LB', {a-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name2|TRIM=0}'), 'LB', {a-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name3|TRIM=0}'), 'LB', {a-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name4|TRIM=0}'), 'LB', {a-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name5|TRIM=0}'), 'LB', {a-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name6|TRIM=0}'), 'LB', {a-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name7|TRIM=0}'), 'LB', {a-tar7}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar7} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{a-name8|TRIM=0}'), 'LB', {a-tar8}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {a-tar8} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{w-name1|TRIM=0}'), 'GL', {w-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{w-name2|TRIM=0}'), 'GL', {w-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{w-name3|TRIM=0}'), 'GL', {w-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{w-name4|TRIM=0}'), 'GL', {w-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {w-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name1|TRIM=0}'), 'OZ', {x-tar1}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar1} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name2|TRIM=0}'), 'OZ', {x-tar2}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar2} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name3|TRIM=0}'), 'OZ', {x-tar3}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar3} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name4|TRIM=0}'), 'OZ', {x-tar4}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar4} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name5|TRIM=0}'), 'OZ', {x-tar5}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar5} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name6|TRIM=0}'), 'OZ', {x-tar6}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar6} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name7|TRIM=0}'), 'OZ', {x-tar7}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar7} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name8|TRIM=0}'), 'OZ', {x-tar8}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar8} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name9|TRIM=0}'), 'OZ', {x-tar9}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar9} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name10|TRIM=0}'), 'OZ', {x-tar10}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar10} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name11|TRIM=0}'), 'OZ', {x-tar11}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar11} >0;
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 )    select  (SELECT SNO FROM TMP_SNO), {#Mix|INC=1}, {#Mix|INCA=1}-1, '{#Mix|TRIM=0}', UPPER('{x-name12|TRIM=0}'), 'OZ', {x-tar12}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT', 'Y'    from artloc where loc_id=0 and  {x-tar12} >0;

Advanced: Creating the Model Query Tool

The above queries were created using a special Excel Spreadsheet that can be found here: Import Batch Mixes Query Creator Worksheet

This can be used for changes needed in the future.

QueryGen Case: Import Keystone Batch Mixes: Difference between revisions

Latest revision as of 13:00, 14 July 2023

Contents

Export Keystone Batch

Prepare the File

Set Search Replace Results

QueryGen

Source For All Operations

Update/Insert Mix Products into ARTPROD

Insert Mix Design

Find Missing Ingredients

Alternate "Live" Ingredient Verification

Old Style

Cleanup the Export File

Convert to Excel

QueryGen

Connect to Excel Sheet

Set Select Query

Set Header

Set Update Model Query

Set Seach Replace Results

Special Case - No Plant Numbers

Advanced: Creating the Model Query Tool

Navigation menu

Revision as of 18:59, 10 August 2020 (view source) Intra>Chanson (→‎Export Keystone Batch)	Latest revision as of 13:00, 14 July 2023 (view source) WikiAdmin (talk \| contribs) m (1 revision imported)
(No difference)