DynamicAccounting.netYou, Me, and Dynamics GP

2015-06-29 · 2015

Weekly Dynamic: Reporting Central’s The Closer bailed me out

General Ledger & FinancialsPayables & ReceivablesSQL & Data

Archived post from 2015. Dynamics GP and the links it points to have changed since; check current documentation before relying on version-specific steps.

A couple of weeks ago I had a client call up and say that their AP sub ledger didn’t match the GL. These guys are always in balance. We had done some AP cleanup together earlier in the month and to make matters worse, they do a ton of credit card transactions that run through this account in GP.

Here is an example:

  • You enter an AP voucher to Vendor A. GP creates a sub ledger liability and the GL shows a credit to AP with the Orig Master Name as Vendor A.
  • You pay that voucher with a Visa in GP. GP moves the sub ledger liability from Vendor A to vendor Visa but no GL transaction is created.
  • Next you pay Visa (and a bunch of other vouchers tied to Visa). The sub ledger is cleared and the GL shows a debit to AP but now the Orig Master Name is Visa.

This works and there is nothing incorrect about it, but there is no connection in the GL between the voucher and the ultimate payment. This makes reconciling these kinds of transactions a nightmare. Especially when there are thousands of them.

Back to my story. This company was always in balance and suddenly they were not. I looked and looked and looked and found…nothing. The cleanup we had done seemed to have worked fine. I worked on this on and off for 2 weeks and I was lost, so I applied The Closer from Reporting Central.

Angels sang a large spotlight shown down from heaven pointing to the problem…or not. The Closer didn’t popup a big arrow saying fix this, but it did clarify some things I was seeing. It’s much easier to read than the GP tool or building SQL queries and it convinced me that we didn’t have a normal reconciliation problem. So I dug deeper into the tables, but now with some direction.

Ultimately I found a posted transaction that didn’t properly clear. It existed in both the AP history tables and in some, but not all, AP open tables. Once I confirmed that everything was in history, I removed the transactions remnants from the open tables and we were back in balance…to the penny.

Part of the lesson here is that there are reconciling problems…date issues, wrong accounts, direct GL entries, etc. and there are reconciling problems. For me, The Closer eliminated that first set of reconciliation issues, the typical ones, and led me to focus on something more out of the ordinary.