web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Received not invoice r...
Microsoft Dynamics GP (Archived)

Received not invoice report does not tie out

(0) ShareShare
ReportReport
Posted on by Community Member

A few months back I was asked to contruct a RNI report in ANYVIEW ( like SLB) so I found some code , created a view and voila minus a few filters.  I am now being asked why the Canned Dynamics GP RNI report does not agree.

As I have begun to look up the individual PO's and receipts I have noticed that shipment lines seem to be the cause. once I run the SQL stmt :

update POP10500 set QTYMATCH = QTYSHPPD where POPRCTNM = 'RCT00000000465313'

Then I re-run the SQL RNI (see code below) that specific PO is no longer on the RNI report. Is this a worthwhile procedure to resolve the RNI discrepancies? How can this issue with PO be resolved going forward?

Below is the code that I have been running to recreate the RNI report that I found on GPUG from Ryan Hise :

SELECT
     CASE POL.POLNESTA
          WHEN 1 THEN 'New'
          WHEN 2 THEN 'Released'
          WHEN 3 THEN 'Change Order'
          WHEN 4 THEN 'Received'
          WHEN 5 THEN 'Closed'
          WHEN 6 THEN 'Canceled'
          END PO_Line_Status,
     CASE RWH.POPTYPE
          WHEN 1 THEN 'Shipment'
          WHEN 2 THEN 'Invoice'
          WHEN 3 THEN 'Shipment/Invoice'
          END PO_Type,
     RWH.VENDORID AS 'Vendor ID',
     RWH.VENDNAME AS 'Vendor Name',
     RWH.VNDDOCNM AS 'Vendor Doc #',
     RLQ.POPRCTNM AS 'RCT #',
     POL.PONUMBER AS 'PO #',
     POL.LineNumber AS 'PO Line #',
     RLQ.QTYSHPPD AS 'QTY Shipped',
     RLQ.QTYMATCH AS 'QTY Matched',
     RLH.EXTDCOST AS 'Line Extended Cost',
     RWH.Total_Landed_Cost_Amount AS 'Total Landed Cost',
     RWH.BACHNUMB AS 'Batch ID',
     RLH.LOCNCODE AS 'Site ID',
     RWH.POSTEDDT AS 'Posted Date',
     RWH.receiptdate AS 'Receipt Date',
     RWH.GLPOSTDT AS 'GL Posting Date',
     RLH.ITEMNMBR AS 'Item #',
     RLH.VNDITNUM AS 'Vendor Item #',
     RLH.ITEMDESC AS 'Item Description',
     RLH.VNDITDSC AS 'Vendor Item Description',
     RLQ.QTYINVCD AS 'QTY Invoiced',
     RLH.UOFM AS 'U of M',
     RWH.SUBTOTAL AS 'Receipt Subtotal'

FROM
dbo.POP10110 POL,
dbo.POP10500 RLQ,
dbo.POP30300 RWH,
dbo.POP30310 RLH

WHERE
RLQ.PONUMBER = POL.PONUMBER AND RLQ.POLNENUM = POL.ORD AND RWH.POPRCTNM = RLQ.POPRCTNM AND RLH.POPRCTNM = RWH.POPRCTNM AND RLH.POPRCTNM = RLQ.POPRCTNM AND RLH.RCPTLNNM = RLQ.RCPTLNNM AND RLH.PONUMBER = POL.PONUMBER AND RLH.PONUMBER = RLQ.PONUMBER AND (POL.POLNESTA Not In ('5', '6')) AND ((RLQ.QTYSHPPD - RLQ.QTYMATCH) <> '0')

ORDER BY RWH.VENDORID, RWH.VNDDOCNM, POL.PONUMBER,
POL.LineNumber

 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Is there an associated financial impact by running the update statement on POP10500?

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    That SQL query seems like it would be causing more problems.  You are essentially telling the system that you are not going to be receiving an invoice for a shipment that you received, essentially receiving goods for free.  Also, when a shipment is done, the accrued purchases account is credited.  When the invoice is matched to the shipment, the accrued purchases account is debited, clearing it out.  Running that sql script, you wouldn't be able to process the invoice which would then never clear out the accrued account.

  • Community Member Profile Picture
    Community Member on at

    Matt I understand what you are saying thank you.  The difference is removed from the RNI yet there is still a receipt for the shipment when I look it up. I followed the following MSFT KB support.microsoft.com/.../897270

    Last month thre were 37 PO's that created a sizeable variance between GP RNI and the Anyview report that I generated from the code above.

    Does the KB seem sound to you?

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    The article does make sense so if you are using PO returns and seeing those results, then the scripts should work.  I would just follow your trail of accrued purchases through the receipt/invoice/return to make sure everything is being washed out.

    If it's not a return that's causing the problem, I would still be wary of using that script.

  • Community Member Profile Picture
    Community Member on at

    Thank you Matt, any other suggestions for trying to narrow down the causes of these specific PO's showing up on the RNI report?

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    If it were me I'd start by looking at some of them individually and see why they would be occurring.  There could be various reasons.  One being quantity discrepancies between the receipt and invoice.  If you received 10 of an item but the invoice was only for 9 then the system would leave that PO open as received but not invoiced.  Another reason I've seen is that the invoice is being entered directly in to the AP module instead of the Enter/Match window.  Then there's always the potential where there is data corruption (hopefully not the case).

  • Community Member Profile Picture
    Community Member on at

    I have already been noticing receipt anomalies, not sure why they have been partially received. Anyway I can trace if they were directly entered into AP vs Autopost/ Match Invoice window?

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    Through the front end if you drill in to the invoice window and it opens up the Payables Transaction Entry Zoom then that would show it was entered directly in the Payables window versus the PO Invoice window.

    If you are using Smartlist or a SQL query, you could look at TRX Source (TRXSORCE in sql).  If PMTRX, it originated in the paybles module, if POIVC then it originated in the PO Invoice.

  • Community Member Profile Picture
    Community Member on at

    Matt, I am drilling down on a sample group, so far there is no specific issue that is common between them all.

    One examples shows TRXSORCE as RECVT00000002, any ideas on this one?

  • MattPaulen Profile Picture
    MattPaulen 6,912 on at

    RECVT is a receiving void transaction so a receipt was being entered and then voided before it was posted.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans