Subledger Work/Open Plumbing

I've been slowly working on a project at work to pull together all of our subledger, GL and unposted GL transactions for various accounts into a daily report email. This way, if a subledger/GL combo gets out of balance we know it right away and can fix it without wading through hundreds of transactions at month end. It's a bit of challenge because not all of our subledgers live in GP. In the Scripts section of the website there is a SQL script with much of the plumbing for this if you want to play with it yourself.

While working on this, I've found a few idiosyncracies in the plumbing of GP and I wanted to share them before I forgot.

1. The received/not invoiced report is used to balance accrued purchased to the GL. But it's not a true historical report so I've found your better off running it as of today and comparing to today's balances. In spite of that, my numbers kept moving around. Ultimately, I figured out that an Invoice Matching transaction that is still in Work (not posted, still being edited) is treated on the report as if it's complete. So if someone starts and saves an Invoice Match, that receiving is removed from the received/not invoiced report (ie, the subledger is affected WITHOUT a subledger posting) but the transaction is not in the GL until its posted. This creates a difference because the subledger is not really posted but the subledger report is showing as if it were posted and you're stuck!
   
   My fix, was to include the work tables in my comparison. Using only GP tools, you need to make sure that there is no unposted invoice matching when comparing the sub to the GL.
   
2. There is a similar issue in AP. In our case, we see it when we create a Payables Voucher and on the same transaction, pay the voucher with a credit card. The payment appears as a transaction in the subledger, even though the voucher is still in the works. For us, this only presents an issue when processing Amex expense reports so for now, we just live with it for a couple days a month and compare the difference to the Amex payments that have been processed.

There may be more of these little Work/Open oddities out there. I'll let you know when I find some more.