I have developed a SQL query which looks into this issue. Actually, it gives you all the POs not closed properly or closed through Edit Purchase Order window. Please refer to this -
SELECT POLINE.PONUMBER,
'HISTORY' AS POSTATUS,
POLINE.ORD AS LINENUMBER,
POHISH.DOCDATE,
POHISH.CREATDDT,
POLINE.ITEMNMBR,
POLINE.FSTRCPTDT,
POLINE.LSTRCPTDT,
SUM(PORCV.QTYSHPPD) AS SHIPPED,
SUM(PORCV.QTYINVCD) AS INVOICED
FROM POP10500 PORCV
JOIN POP30110 POLINE ON
POLINE.PONUMBER=PORCV.PONUMBER
AND POLINE.ORD=PORCV.POLNENUM
JOIN POP30100 POHISH ON
POHISH.PONUMBER=POLINE.PONUMBER
WHERE POLINE.POLNESTA=5 -- PO LINE IS FLAGGED AS CLOSED
AND PORCV.STATUS=1 -- INCLUDE ONLY POSTED INVOICE MATCHING DOCUMENTS
GROUP BY POLINE.PONUMBER,
POLINE.ORD,
POLINE.ITEMNMBR,
POHISH.DOCDATE,
POHISH.CREATDDT,
POLINE.FSTRCPTDT,
POLINE.LSTRCPTDT
HAVING SUM(PORCV.QTYSHPPD) <> SUM(PORCV.QTYINVCD)
UNION ALL
SELECT POLINE2.PONUMBER,
'WORK' AS POSTATUS,
POLINE2.ORD AS LINENUMBER,
POOPENH.DOCDATE,
POOPENH.CREATDDT,
POLINE2.ITEMNMBR,
POLINE2.FSTRCPTDT,
POLINE2.LSTRCPTDT,
SUM(PORCV2.QTYSHPPD) AS SHIPPED,
SUM(PORCV2.QTYINVCD) AS INVOICED
FROM POP10500 PORCV2
JOIN POP10110 POLINE2 ON
POLINE2.PONUMBER=PORCV2.PONUMBER
AND POLINE2.ORD=PORCV2.POLNENUM
JOIN POP10100 POOPENH ON
POOPENH.PONUMBER=POLINE2.PONUMBER
WHERE POLINE2.POLNESTA=5 -- PO LINE IS FLAGGED AS CLOSED
AND PORCV2.STATUS=1 -- INCLUDE ONLY POSTED INVOICE MATCHING DOCUMENTS
GROUP BY POLINE2.PONUMBER,
POLINE2.ORD,
POLINE2.ITEMNMBR,
POOPENH.DOCDATE,
POOPENH.CREATDDT,
POLINE2.FSTRCPTDT,
POLINE2.LSTRCPTDT
HAVING SUM(PORCV2.QTYSHPPD) <> SUM(PORCV2.QTYINVCD)
GO
GRANT
SELECT ON VIEW_CLOSED_PO_ANALYSIS_CA TO DYNGRP
