QueryGen Case: Add missing item to all Jobs

Sometimes you want to add an detail item to all jobs.

In this case, we will add the item ENVIRO.01 (adjusted to plant) to all jobs wher it is missing. In the query below we select all jobs and identify which do NOT have a product like ENVIRO. Artjob as d1 represents the detail line that should exist if ENVIRO.XX is in the job detail. Artjob as d0 is used to find a valid plant number on a product. (you can't count on the job header).

select h.session_no,h.trans_no,h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,coalesce(min(p0.plant_no),'1A') Plant,h.active_flag,h.job_tax_code from artjobhd h
inner join artcust c on c.cust_no=h.cust_no
left outer join artjobdt d0 ON d0.session_no=h.session_no and d0.trans_no=h.trans_no 
left outer  join artprod p0 on p0.product_code=d0.product_code
left outer join artjobdt d1 ON d1.session_no=h.session_no and d1.trans_no=h.trans_no AND D1.PRODUCT_CODE LIKE 'ENVIRO.%'
where (d1.product_code is null)
and h.active_flag='Y' AND C.ACTIVE_FLAG='Y'
group by h.session_no,h.trans_no, h.cust_no,c.name, h.job_po_loc, h.start_date, h.stop_date,h.active_flag,h.job_tax_code

Adjust this query to your case:

• Change 'ENVIRO.%' to the product you wish to work with.
• Change the job selection to your situation. (START_DATE, STOP_DATE etc.)

Then use the following QG model Update Query:

INSERT INTO 
artjobdt
( SESSION_NO, TRANS_NO, CUST_NO, JOB_PO_LOC, LINE_NO, PRICE_CODE, PRODUCT_CODE, UNIT_PRICE, QTY_ORDERED, TAX_CODE, QTY_PER_LOAD, UNIT_OF_MEASURE, DESCRIPTION, IMPORTED_FLAG, LAST_CHANGE_DATETIME, LAST_CHANGE_USER )  VALUES 
( {SESSION_NO}, {TRANS_NO}, '{CUST_NO}', '{JOB_PO_LOC}', 99, 'P', 'ENVIRO.{PLANT}', 55, 0, '{JOB_TAX_CODE}', 0, '/L', 'ENVIRONMENTAL FEE PER LOAD', 'Y', CAST('TODAY' AS TIMESTAMP), 'HIT'    );
<pre>

Adjust the model update query:
*Line No: 99 must not exist.  If multiptle products being processed change for each product.
*Price: Here it's 55.  NOTE: you can come back and fix the price later.
*Product Code: Change ENVIRO to yoru product
*Unit of Measure: Match Product
*Desription: Match Product