Unlike an A/R or an A/P aging report, I cannot obtain an RNI report as of a certain date (usually at month end for financial reporting purposes). Is this report available for any given date I so desire and what are the steps to generate this report?
*This post is locked for comments
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
What you are looking for is a historical received not invoiced report, this is a new feature to be introduced in GP 2015 R2, which is expected to be released hopefully throughout June.
Here is the announcement for this upcoming feature, Microsoft Dynamics GP 2015 R2 Feature of the Day: Historical Received Not Invoiced Report
Let us know if you have any further concerns,
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
