Hi everyone!

Many of you use the Received not invoiced report to reconcile your Accrued Purchases account to the General Ledger. We often get questions around why it might not reconcile and what things to look for.  I’d like to share some information I’ve provided over the years to help you identify the discrepancy.  

First, make sure you are using the Received Not Invoiced report under Reports > Purchasing > Analysis > Received not invoiced.  This is the main report we recommend using to reconcile the Accrued purchases account in General Ledger to what is open in Purchase order processing. 

There is a SmartList option in Microsoft Dynamics GP, but there are some specific scenarios such as PO lines still appearing on the list because the PO was closed without invoicing it that might throw your reconcile off.  I've recently wrote a quality issue on this, so if you are running into this issue, please open a support case referencing this blog and we can get you a workaround for the time being as well as add you to the list of customers running into this issue. 

If the actual Received Not Invoiced Report does not balance to your Accrued Purchases account, I have a list of things that I typically check to help try to narrow the issue.   In a support case we won’t be able to assist with reconciling the accounts as that's more consulting, but we can provide some guidance and things to investigate.  If you can identify which transaction is causing the issue, then we are more than happy to look into the data condition of the transaction to determine why it is creating a discrepancy.  

Some reasons why it could be off:

1) Verify if there are any batches in GL that came from POP. (Financial >> Transactions >> Financial >> Batches) Post them and see if the balance reduces for the Accrued Purchases and comes more in align with the report.

2) If you use Landed Cost, do you Match the invoices? (To check go to Cards > Inventory > Landed Cost). If you don't have the box checked to match, it could be that users forgot to enter Invoices in Payables for the Landed cost (reducing the Accrued purchases)

3)  Someone is voiding enter/match invoices directly in Payables not realizing the impact. Typically, we don’t want to void in Payables if the transaction originated in Purchase Order Processing.  Voiding will reverse the account like it didn’t happen, but it will not affect Purchase Order Processing subledger and not pull the document back onto the Received Not Invoiced report. 

4) Most common issue is when users close POs using the Edit PO window (Transactions >> Purchasing >> Edit Purchase Orders) not realizing the impact. If you close a PO that was shipped, but not invoiced, the Accrued purchases never gets reversed.  I wrote a KB 2021453 on this this topic to help you manually back out the Accrued purchases with a General Ledger Journal entry. 

 

In order to determine if you have any Purchase Orders that were closed without Invoicing you can us the following information:

  • When a shipment receipt gets recorded a line gets put into the POP10500 table.  QTYSHPPD is updated. 
  • Then when an invoiced is matched a line gets put into the POP10600.  It also updates the POP10500 by adding a new line with a QTY invoiced.  Then lastly it updates/increments the QTY matched field on the original shipment receipt.    That is the piece that is going to be key.  The QTY matched field says an invoice has been matched against it.  We could look at the POP10500 where the QTYMATCH is NOT updated and the line is closed in the POP10110 table.
  • That would tell me a shipment receipt was posted, but nothing matched.  In that case the POP line should be “Received” and not “Closed”.  If it’s “Closed” then someone closed the line manually in the Edit PO window.

I created some SQL scripts that pull POs that have a QTY matched of 0 (not matched), but have a status of closed (5).  There is one script for Purchase Orders in the Open tables and one for the Historical tables (if you ever MOVE POs to history using the routine).  Any POs that you see on the list should be investigated and will probably need some type of adjustment to the line item.  POLNENUM is the line item number that is represented on the PO number.  The first line on the POP document starts with 16384 and increments each time by that number.

Example:

16384 = line 1 on the PO

32768 = line 2 on the PO number

 

SELECT POP10110.PONUMBER, pop10110.POLNESTA, POP10500.POLNENUM
from (select ponumber, polnenum, SUM(qtymatch)as qtymatch from POP10500
group by PONUMBER, POLNENUM
)POP10500 left outer join pop10110
on pop10500.PONUMBER = pop10110.PONUMBER
and pop10500.POLNENUM = pop10110.ORD
where POP10500.QTYMATCH = 0 and POP10110.POLNESTA = 5
 
SELECT POP30110.PONUMBER, pop30110.POLNESTA, POP10500.POLNENUM from (select ponumber, polnenum, SUM(qtymatch)as qtymatch from POP10500
group by PONUMBER, POLNENUM
)POP10500 left outer join pop30110
on pop10500.PONUMBER = pop30110.PONUMBER
and pop10500.POLNENUM = pop30110.ORD
where POP10500.QTYMATCH = 0 and POP30110.POLNESTA = 5
 

These are some of the main things to look at when discrepancies arise when tying Accrued Purchases.  Hopefully, it’s helpful in determining the reason and get you closer to matching the values.

Warmest Regards,

Angela Ebensteiner | SR Technical Advisor | Microsoft Dynamics GP