Fixing onhand quantity in F41021 file from Item Ledger F4111 in JD Edwards

Fixing onhand quantity in F41021 file from Item Ledger F4111 in Jd Edwards.

first create onhand quantity to update

drop table zdev.dbo.temp_pqoh
SELECT a.LOCN,a.MCU,a.ITM,PQOH,TRQT into zdev.dbo.temp_pqohFROM(SELECT MCU=LTRIM(RTRIM(LIMCU)),ITM=CAST(LIITM as INT),PQOH=SUM(LIPQOH),LOCN=LILOCN FROM JDE_PRODUCTION.PRODDTA.F41021 GROUP BY LIMCU,LILOCN, LIITM) a INNER JOIN (SELECT LOCN=ILLOCN, MCU=LTRIM(RTRIM(ILMCU)),ITM=CAST(ILITM as INT),TRQT=SUM(ILTRQT) FROM JDE_PRODUCTION.PRODDTA.F4111 GROUP BY ILMCU,ILLOCN,ILITM) b ON a.ITM=b.ITM AND a.MCU=b.MCU and a.LOCN=b.LOCN
WHERE PQOH!=TRQT
ORDER BY 1,2

update f41021 table.

UPDATE proddta.f41021 SET lipqoh =trqtfrom proddta.f41021 INNER JOIN zdev.dbo.temp_pqoh on liITM=itm and rtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and lilocn=locnwhere rtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and liITM=itm and lilocn=locn andrtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and lipqoh<>trqt


www.svdeals.com