QueryGen Case: Move Sales to Plant Tickets

This process copies tickets from the sales header/detail tables to the plant ticket header/detail tables. this can be used to export existing tickets from dispatch.

Prep

• Identify the session numbers for the source tickets in artslshd. (don't rely on a date range)
• Get unique unused session no to be used as the target session (open qryexport, select * from cctsessn where last_change_user = 'HIT' ORDER BY SESSION_NO DESC ROWS 5)
• These instructions assume no duplicate customer/ticket numbers in sales (Select cust_no,ticket_no,count(*) from artslshd sh where ... group by cust_no,ticket_no having count(*) >1)
• Verify there are no duplicates between sales tickets and plant tickets. (With LQ1 and LQ2 setup for the headers set Primary query to: select * from lq1 inner join lq2 on lq1.cust_no=lq2.cust_no and lq1.ticket_no=lq2.ticket_no)

Sales Header to Plant Ticket Header

lq1

SELECT * FROM artslshd sh where source _session_no in ({list of source sessions} order by ticket_date,trans_no

lq2

SELECT * FROM artptkhd where ticket_date > ##/##/## (Select timeframe that matches source Tickets)

Primary

select SESSION_NO,TRANS_NO,CUST_NO,TICKET_NO,TICKET_DATE,SLUMP,MILES,TICKET_TIME,SALESPERSON,SYS_LOC,PLANT_NO,TAX_AUTHORITY,JOB_PO_LOC,JOB_LOT,JOB_BLOCK,JOB_CLASS,ORDER_CODE,ORDER_SESSION_NO,ORDER_TRANS_NO,ORDER_LOAD_NO,USAGE_TYPE,TRUCK_NO,TRAILER_1,TRAILER_2,SOURCE_FLAG,VOID_FLAG,VOID_LOC,VOID_SESSION_NO,VOID_REASON,PRICED_FLAG,PRINTED_FLAG,OFFICE_PRICE_FLAG,PRINT_WEIGHTS,ZONE,COD_FLAG,QTY_OB,PRODUCT_CODE_OB,QTY_RET,PRODUCT_CODE_RET,ELAPSED_TIME,BATCH_START_DT,BATCH_END_DT,LEAVE_PLANT_DT,ARRIVE_JOB_DT,BEGIN_POUR_DT,END_POUR_DT,LEAVE_JOB_DT,RETURN_PLANT_DT,TGT_BATCH_START_DT,TGT_BATCH_END_DT,TGT_LEAVE_PLANT_DT,TGT_ARRIVE_JOB_DT,TGT_BEGIN_POUR_DT,TGT_END_POUR_DT,TGT_LEAVE_JOB_DT,TGT_RETURN_PLANT_DT,CUSTOMER_PO,DRIVER,PAYMENT_AMOUNT,CHECK_DATE,CHECK_NO,BANK_NO,SHIP_TO_1,SHIP_TO_2,SHIP_TO_3,SHIP_TO_4,DELV_INST_1,DELV_INST_2,DELV_INST_3,DELV_INST_4,WEATHER,WEIGHMASTER,CUSTOM_1,CUSTOM_2,CUST_NOTES,DRIVER_NOTES,EMAILED_TO,DOC_LINK,IMPORTED_FLAG,MEMO,LAST_CHANGE_DATETIME,LAST_CHANGE_USER from lq1

( or select * from ) (You can also select * from artslshd directly. Lq1 is used in this step just for the test query.)

Update Query (Model)

NOTE:Replace #####' with new target session no.

INSERT INTO ARTPTKHD
( SESSION_NO, TRANS_NO, CUST_NO, TICKET_NO, TICKET_DATE,  SLUMP, MILES, TICKET_TIME, SALESPERSON, SYS_LOC, PLANT_NO, TAX_AUTHORITY, JOB_PO_LOC, JOB_LOT, JOB_BLOCK, JOB_CLASS, ORDER_CODE, ORDER_SESSION_NO, ORDER_TRANS_NO, ORDER_LOAD_NO, USAGE_TYPE, TRUCK_NO, TRAILER_1, TRAILER_2, SOURCE_FLAG, VOID_FLAG, VOID_LOC, VOID_SESSION_NO, VOID_REASON, PRICED_FLAG, PRINTED_FLAG, OFFICE_PRICE_FLAG, PRINT_WEIGHTS, ZONE, COD_FLAG, QTY_OB, PRODUCT_CODE_OB, QTY_RET, PRODUCT_CODE_RET, ELAPSED_TIME, BATCH_START_DT, BATCH_END_DT, LEAVE_PLANT_DT, ARRIVE_JOB_DT, BEGIN_POUR_DT, END_POUR_DT, LEAVE_JOB_DT, RETURN_PLANT_DT, TGT_BATCH_START_DT, TGT_BATCH_END_DT, TGT_LEAVE_PLANT_DT, TGT_ARRIVE_JOB_DT, TGT_BEGIN_POUR_DT, TGT_END_POUR_DT, TGT_LEAVE_JOB_DT, TGT_RETURN_PLANT_DT, CUSTOMER_PO, DRIVER, PAYMENT_AMOUNT, CHECK_DATE, CHECK_NO, BANK_NO, SHIP_TO_1, SHIP_TO_2, SHIP_TO_3, SHIP_TO_4, DELV_INST_1, DELV_INST_2, DELV_INST_3, DELV_INST_4, WEATHER, WEIGHMASTER, CUSTOM_1, CUSTOM_2, DOC_LINK, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  VALUES 
( #####, {TRANS_NO|INC=1}, '{CUST_NO}', {TICKET_NO}, '{TICKET_DATE}',  {SLUMP}, {MILES}, {TICKET_TIME}, '{SALESPERSON}', {SYS_LOC}, '{PLANT_NO}', '{TAX_AUTHORITY}', '{JOB_PO_LOC}', '{JOB_LOT}', '{JOB_BLOCK}', '{JOB_CLASS}', '{ORDER_CODE}', {ORDER_SESSION_NO}, {ORDER_TRANS_NO}, {ORDER_LOAD_NO}, '{USAGE_TYPE}', '{TRUCK_NO}', '{TRAILER_1}', '{TRAILER_2}', '{SOURCE_FLAG}', '{VOID_FLAG}', {VOID_LOC}, {VOID_SESSION_NO}, '{VOID_REASON}', '{PRICED_FLAG}', '{PRINTED_FLAG}', '{OFFICE_PRICE_FLAG}', '{PRINT_WEIGHTS}', '{ZONE}', '{COD_FLAG}', {QTY_OB}, '{PRODUCT_CODE_OB}', {QTY_RET}, '{PRODUCT_CODE_RET}', {ELAPSED_TIME}, '{BATCH_START_DT|DATETIME=0}', '{BATCH_END_DT|DATETIME=0}', '{LEAVE_PLANT_DT|DATETIME=0}', '{ARRIVE_JOB_DT|DATETIME=0}', '{BEGIN_POUR_DT|DATETIME=0}', '{END_POUR_DT|DATETIME=0}', '{LEAVE_JOB_DT|DATETIME=0}', '{RETURN_PLANT_DT|DATETIME=0}', '{TGT_BATCH_START_DT|DATETIME=0}', '{TGT_BATCH_END_DT|DATETIME=0}', '{TGT_LEAVE_PLANT_DT|DATETIME=0}', '{TGT_ARRIVE_JOB_DT|DATETIME=0}', '{TGT_BEGIN_POUR_DT|DATETIME=0}', '{TGT_END_POUR_DT|DATETIME=0}', '{TGT_LEAVE_JOB_DT|DATETIME=0}', '{TGT_RETURN_PLANT_DT|DATETIME=0}', '{CUSTOMER_PO}', '{DRIVER}', {PAYMENT_AMOUNT}, '{CHECK_DATE}', '{CHECK_NO}', '{BANK_NO}', '{SHIP_TO_1}', '{SHIP_TO_2}', '{SHIP_TO_3}', '{SHIP_TO_4}', '{DELV_INST_1}', '{DELV_INST_2}', '{DELV_INST_3}', '{DELV_INST_4}', '{WEATHER}', '{WEIGHMASTER}', '{CUSTOM_1}', '{CUSTOM_2}', {DOC_LINK}, 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT'    );

Sales Detail to Plant Ticket Detail

Update the detail by getting matching session no and trans no from the corresponding ticket header.

lq1

SELECT sd.* FROM artslsDT SD 
inner join artslshd sh on sh.session_no=sd.session_no and sh.trans_no=sd.trans_no
where sh.session_no in ({list of source sessions}) order by ticket_date

lq2

SELECT * FROM artptkhd
where session_no={target session} (Same New Session as Header)

Primary

select LQ2.SESSION_NO TSNO,LQ2.TRANS_NO TTNO,   LQ1.* FROM LQ1
Inner join lq2 on lq1.cust_no=lq2.cust_no and lq1.ticket_no=lq2.ticket_no

Update Model

INSERT INTO artptkDT
( SESSION_NO, TRANS_NO, CUST_NO, TICKET_NO, LINE_NO, COMMENT_FLAG, UNIT_PRICE, QTY_SOLD, PRODUCT_CODE, UNIT_OF_MEASURE, DESCRIPTION, TAX_CODE, TAX_CODE_2, TAX_CODE_3, PRICE_SOURCE, GROSS_WEIGHT, TARE_WEIGHT, WEIGHT_UMS, SCALE_WEIGHT_FLAG, IMPORTED_FLAG )  VALUES 
( {TTNO}, {TTNO}, '{CUST_NO}', {TICKET_NO}, {LINE_NO}, '{COMMENT_FLAG}', {UNIT_PRICE}, {QTY_SOLD}, '{PRODUCT_CODE}', '{UNIT_OF_MEASURE}', '{DESCRIPTION}', '{TAX_CODE}', '{TAX_CODE_2}', '{TAX_CODE_3}', '{PRICE_SOURCE}', {GROSS_WEIGHT}, {TARE_WEIGHT}, '{WEIGHT_UMS}', '{SCALE_WEIGHT_FLAG}', '{IMPORTED_FLAG}'    );