Rebuild QTYONORDER of IV00102 Dynamics GP

I had to rebuild the quantity on purchase order of the inventory site qtys table today (On Order in Item Enquiry).

Wrote this SQL script that works for our install with our settings, but you should verify your install does not have other modules or modifications that may make this break.

 

Comment out the UPDATEs and uncomment the selects to see what is going to happen first, running SQL against your production environment is dangerous, test, test, test first. This is on the HIGH side of risky as a script to run. 

 

-- Rebuilds the qty on order in inventory from purchase order table
-- Don't know how manufacturing module would play with this
BEGIN TRANSACTION
-- Part 1 Correct the site/location qty on order from receipts and purchase orders
;
WITH CTE_ShippedQtys
AS (
SELECT PONUMBER
,POLNENUM
,SUM(QTYSHPPD*UMQTYINB )TotQTYSHIPPED
,SUM(QTYREPLACED*UMQTYINB ) TotQTYREPLACED
FROM POP10500
GROUP BY PONUMBER
,POLNENUM
)
,CTE_OnOrderQtys
AS (
SELECT SUM(CASE WHEN POTYPE IN (1,3) THEN ((QTYORDER - QTYCANCE)*POP10110.UMQTYINB) - ISNULL(TotQTYSHIPPED, 0)+ISNULL(TotQTYREPLACED,0) ELSE 0 END) AS QTYORDER
,ITEMNMBR
,LOCNCODE
FROM POP10110
LEFT JOIN CTE_ShippedQtys ON POP10110.PONUMBER = CTE_ShippedQtys.PONUMBER
AND pop10110.ORD = CTE_ShippedQtys.POLNENUM
WHERE POLNESTA IN (
2
,3
)
GROUP BY ITEMNMBR
,LOCNCODE
)
UPDATE IV00102
SET QTYONORD = ISNULL(QTYORDER,0)
--SELECT QTYONORD,QTYORDER, *
FROM IV00102
LEFT JOIN CTE_OnOrderQtys ON IV00102.ITEMNMBR = CTE_OnOrderQtys.ITEMNMBR
AND IV00102.LOCNCODE = CTE_OnOrderQtys.LOCNCODE
AND IV00102.LOCNCODE !=''
WHERE
IV00102.LOCNCODE!=''AND
(QTYONORD != QTYORDER
OR (QTYONORD!=0 AND QTYORDER IS NULL))
ROLLBACK TRANSACTION

--Summ up the location on order Qtys for the summary record
BEGIN TRANSACTION;
--Part 2 Correct the summary location
WITH CTE_SumLocations
AS (
SELECT SUM(QTYONORD) totqty
,ITEMNMBR
FROM IV00102
WHERE LOCNCODE != ''
GROUP BY ITEMNMBR
)
UPDATE IV00102
SET QTYONORD = totqty
--SELECT *
FROM IV00102
JOIN CTE_SumLocations ON CTE_SumLocations.ITEMNMBR = IV00102.ITEMNMBR
AND IV00102.LOCNCODE = ''
AND IV00102.RCRDTYPE = 1
AND QTYONORD != totqty

ROLLBACK TRANSACTION