In our old blog on balancing GP we promised that we would cover balancing the RNI in more depth, and since then our not so old dog has learned some new tricks. Please review our old blog for generic advice that affects balancing all subledger in all modules to the GL, and correcting the posted GL subledger AJEs (Adjusting Journal Entries):
This is the MS Dynamics GP support discussion of balancing the RNI report to the GL, please review it in case Microsoft adds any suggestions we do not cover here.
CAVEATS: If you use Landed Cost or Multi-currency, you may need to take these adjustments into account.
By definition, drop-shipment transactions never appear on the RNI report because it is your customer who receives the inventory. For direct shipments your customer has the RNI liability, not you.
PRINTING THE POP RNI REPORT
The RNI, or Received Not Invoiced report, is a PURCHASING ANALYSIS report. You should probably use the date you began using the POP module as your report beginning date.
We balance our Received Not Invoiced report monthly, but in hind-sight we recommend that you try to balance this at least on a weekly basis, at least until you successfully tame your RNI beast, and keep him tamed. You should be able to improve your business processes so this report balances to the Accrued Expenses account in the general ledger to the penny. This is how the general ledger entries flow for the RNI, or Accrued Expenses GL Account:
When balancing, an accrued expense will drop off this report when it is INVOICED, not when it has been POSTED. Which means that posting as many invoice batches as you can will make this account easier to balance, and that the POP Invoice unposted batch reports will be a reconciling item. You can build a SmartList for the unposted RNI balances you need to add back, or you can run this SQL query:
USE [TWO18] — use your own company database
SELECT [POPRCTNM],[POPTYPE],[VNDDOCNM],[receiptdate],[BACHNUMB],[VENDORID],[VENDNAME], [SUBTOTAL],sum([SUBTOTAL]) OVER() as GrandTotal
TIPS AND TRAPS WHEN CLOSING PURCHASE ORDERS
If you close a purchase order where the inventory shipments received have been partially invoiced, a journal entry will be generated for the un-invoiced items. You should review the journal entry; the item cost may not be what you expect.
If you close a purchase order where none of the inventory shipments have been received, NO JOURNAL ENTRY will be created by Great Plains. Great Plains says this is an intentional design decision.
The preferable method to write off an RNI amount that will never be invoiced is to enter a Returns Transaction Entry. This method always generates a GL adjusting journal entry and provides a better audit trail. When you enter a RETURN, access the help screen for the window and review the FIELD documentation. The type affects the allowable return quantity, the unit cost of the return items, and for non-US companies, the value-added Tax Schedule selected.
For RNI writeoffs, my preference is to select the RETURN type. The RETURN type uses the POP inventory receiving cost, aka the original shipment receipt cost.
OTHER REASONS WHY VALID POP INVOICES DO NOT APPEAR ON THE RNI REPORT
Why would valid vendor POP Invoices not appear on the RNI Report? The most common reason is the PO has been cancelled in error. Unfortunately, Great Plains does not permit you to un-cancel a purchase order that has been cancelled in error. You can only uncancel a purchase order with a SQL query. Note that Status 4 means is the RECEIVING status, and that you will need to bring the cancelled subtotal or CANCSUB to zero, and reinstate the outstanding SUBTOTAL amount.
USE TWO18 — use your own company database
declare @PONumber varchar(17)
select @PONumber = ‘PO123456’
set postatus = ‘4’,statgrp = ‘1’,subtotal = cancsub where PONUMBER = @PONumber
set cancsub = 0 where PONUMBER = @PONumber
set polnesta = ‘4’ where PONUMBER = @PONumber
Over the past decade we have had several instances where the POP Invoice amount does not appear on the RNI report, but Great Plains will not let us AUTO-INVOICE the PO because it has already been invoiced! How do you discover which POP Invoice is hiding? You simply run a SQL query looking for any unposted POP Invoice lines that are hiding:
USE [TWO18] — use your own company database
FROM [dbo].[POP10310] WHERE [POPRCTNM] NOT IN
( SELECT [POPRCTNM] FROM [dbo].[POP10300] )
This SQL query simply looks for unposted POP Invoice or POP Receiving lines that have inventory detail but no header record saved. This can happen because the underlying Dexterity engine saves each scrolling window line immediately, but does not immediately save the header information.
If this query returns some transactions, how do you fix them? By far the simplest solution is to enter the hiding transaction in either the POP Invoice Entry or the POP Receiving Entry form, enter the hiding POP Receipt Number. You will see the lines with inventory items but no vendor information. My strong advice is to simply delete the invoicing or receiving transaction and re-process the transaction. If you delete and start over, you know your link file data will be valid.
You will often see POP10300 orphaned header records with a zero POPTYPE flag and no details. You can delete them if you wish, they have never caused us problems.
PARTIALLY POSTED BATCHES AND OTHER POP DATA ISSUES
If the posting for your POP Invoice or Receiving batch is interrupted, usually some or all of the POP records for one transaction will be in both the work and history files. Print the batch report to screen, enter another batch number for the problem transaction with duplicates, and post the remaining valid unposted transactions.
We always puzzled out the SQL queries needed to fix the data in the PO tables (POP10100/110), POP Receipt tables (POP10300/310/POP30300/310) and the link tables (POP10500/600), if yours is a more complex situation there may be other tables. Our situation is we had multiple custom tables with data that was hard to duplicate. Alternately, you can delete the unposted transaction, and delete with a SQL query any leftover records in the history and link tables.
The following rogue event happened to us: twice in the past two years, but never in the decade previous, in the middle of the day, during data entry, not during posting, all the line inventory details were deleted for SOME but not ALL unposted POP Invoice and POP Receivings. Our sister custom detail tables were never touched, so it was unlikely that our custom Dexterity code was at fault. Dr Google told us that several other reputable consultants have had clients that experienced this problem. However, Great Plains support says that this is a rare event, that they do have a record of anyone opening a support reporting this issue in recent years (I know I did not). Great Plains support told me to encourage everyone who experiences this problem to contact them.
The very best way to recover from this event is to delete the problem POP Invoices and POP Receivings that have no lines and re-enter them. BUT, if the user tries to POST these batches with headers and no lines, they will post without warning and without lines, which will mean that the RNI Report for these transactions will no longer be accurate, until someone fixes the link files with SQL queries as best they can.
USEFULNESS OF POP CHECK LINKS
IMHO, running Check Links for the POP module is not only a waste of time, it is also taking an unnecessary risk of data corruption. Not to mention that the POP check link procedures are glacially slow. We ran check links on a test backup of our production data (IMHO, you should never run Check Links first on production data, though these functions are much safer now than in the earlier years of Dynamics GP). Check Links for the POP modules verifies mostly the PO files, but not so much the POP Receipt and POP Link files, which are the real accounting transactions in GP. We came to this conclusion after exhaustively analyzing the text file output of all our POP check link procedures for several years of work and historical. If other consultants have differing opinions or experiences, we would be happy to hear from them.