Chanson at 15:34, 27 March 2024

2024-03-27T15:34:36Z

← Older revision		Revision as of 15:34, 27 March 2024
Line 26:		Line 26:
	( 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, 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' );		( {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>		</pre>

	Adjust the model update query:		Adjust the model update query:
Line 34:		Line 34:
	*Unit of Measure: Match Product		*Unit of Measure: Match Product
	*Desription: Match Product		*Desription: Match Product
	~~</pre>~~

Chanson: Created page with "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_..."

2024-03-27T15:34:09Z

Created page with "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). <pre> select h.session_no,h.trans_..."

New page

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).

<pre>
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
</pre>

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:
<pre>
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
</pre>

QueryGen Case: Add missing item to all Jobs - Revision history

Chanson at 15:34, 27 March 2024