QueryGen Case: Replicate Tickets

This example pulls plant tickets from a source database and writes them to office tickets (arttckhd/dt) in another database. It was used for recovering tickets from a corrupt backup.

Local queries are used to compare and exclude transactions that exist in both databases. Treat this example as a model of the process but expect that you may need to adjust for your specific case.

Ticket Headers

lq1 (source):

select * from artptkHD TH
WHERE TH.TICKET_DATE >= '<sincedate>'

lq2: (new ticket headers - look up new trans no)

select * from artptkHD TH
WHERE TH.TICKET_DATE >= '<sincedate>'

Main query (Standard - set to local). The join and inclusion of just records with null values in the target db exclude transactions that exist in both databases.

select lq1.* from lq1
left outer join lq2 on lq1.session_no=lq2.session_no and lq1.trans_no=lq2.trans_no and lq1.order_code=lq2.order_code
where lq2.session_no is null

Header Query:

/* drop table TMP_SNO; */ CREATE TABLE TMP_SNO(SNO INTEGER);
INSERT INTO TMP_SNO (SNO)  SELECT session_no FROM CCP_SESSION_BEGIN('ADMIN', 'QG Save tix', '127.0.0.1', '3.3.x');

INSERT INTO ARTTCKHD
( SESSION_NO, TRANS_NO, CUST_NO, TICKET_NO, TICKET_DATE, SLUMP, MILES, TICKET_TIME, SALESPERSON, SYS_LOC, PLANT_NO, BILLING_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, SUB_TOTAL, TAX_TOTAL, 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, INV_ADJUST_FLAG, INV_SESSION_NO, DOC_LINK, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  VALUES 
( (SELECT SNO FROM TMP_SNO), {TRANS_NO|INC=1}, '{CUST_NO}', {TICKET_NO}, '{TICKET_DATE}', {SLUMP}, {MILES}, {TICKET_TIME}, '{SALESPERSON}', {SYS_LOC}, '{PLANT_NO}', '{BILLING_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}, {SUB_TOTAL}, {TAX_TOTAL}, '{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}', '{INV_ADJUST_FLAG}', {INV_SESSION_NO}, {DOC_LINK}, '{IMPORTED_FLAG}', CAST('TODAY' AS TIMESTAMP), 'HIT'    );

Ticket Detail

lq1 (source): Selection is optional.

select * from artptkDT TD
INNER JOIN ARTPTKHD TH ON TH.SESSION_NO=TD.SESSION_NO AND TH.TRANS_NO=TD.TRANS_NO
WHERE TH.TICKET_DATE = '5/22'

lq3: (target database - look up new trans no from ticket headers just created)

SELECT * FROM arttckHD
WHERE SESSION_NO=(SELECT SNO FROM TMP_SNO)

Standard query (set to local)

SELECT LQ1.*,LQ3.TRANS_NO "NEWTRANS" FROM LQ1
INNER JOIN LQ3 ON LQ1.TICKET_NO=LQ3.TICKET_NO

Clear Heading query.

Model Insert query:

INSERT INTO ARTTCKDT
( SESSION_NO, TRANS_NO, CUST_NO, TICKET_NO, LINE_NO, COMMENT_FLAG, UNIT_PRICE, UNIT_PCT_DISC, 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 
( (SELECT SNO FROM TMP_SNO), {NEWTRANS}, '{CUST_NO}', {TICKET_NO}, {LINE_NO}, '{COMMENT_FLAG}', {UNIT_PRICE}, '{UNIT_PCT_DISC}', {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}'    );