Hello,
These issues can be extremely difficult to figure out due to how the Historical Reports work in GP. These reports use the Document Date, Apply Date and Apply Amount to determine when they drop off the report. This is why these get difficult because even though the transaction can exist in both Payables and General Ledger, you can still be off due to how the Historical versions of the reports work.
The very first thing that needs to be done is determine when you were last in balance. This gives you your starting point and can work forward from there. Then you need to determine which one is actually correct. Is the Payables side correct or the GL side correct?
Once you have your starting point, you can start running both the Historical Aged Trial Balance (Detail) and the GL Trial Balance (Detail) weekly from your starting point to determine when the balances start to not match. Once you locate this, you can run the reports without any restriction except for the account, and compare line by line the two reports to determine if there is anything that exists on one side and not the other or in a different period. I say to use detail, because the summary amounts can go wrong at times.
One thing that I personally like to is restore a copy of live to test. In test, you can pay everything that open and vice versa if you have credits open. When everything is paid and moved to history, your Historical Aged Trial Balance will be zero and the Detailed Trial Balance for your AP account will also be zero. If one of them is not zero, then this tells you that module is incorrect.
This is also helpful because if everything is paid and in history, if anything shows on the report or has a balance, you have the transactions on the Payables Side that can be dug into to see why this is showing on the report but is fully applied and in history. This helps with locating possible bad transactions that comparing the reports usually does not help identify.
The other option is to use a Smartlist.,
Using Smartlist would be your next best thing. This is because the Smartlist report already has header information and it details out the transactions in PM. Together with the Reconcile to GL report, you can check the distributions that make up the PM transactions then use them both to tie with the GL Detailed Trial Balance.
Another suggestion is within Smartlist, open up the Payables Transactions Smartlist object. Add the column Posted Date. This column stores the date from your computer of when the document was physically entered in payables. Even if the document date was for the previous year, the posted date stores the date of when you physically entered it. You can compare the Posted Date to the Posting Date (holds the Document Date) or the Document Date as well. You can narrow your search by entering this month's Posted Date since you were in balance last month. This will tell you all of the transactions that were entered in Payables since then and you can compare your GL report to that and see if one module is missing the transaction, has a different document date, or a wrong amount as well (possibly the batch was edited in GL before it was posted).
Next, you will want to open the Account Transactions Smartlist object. Add the column Originating Posted Date. This stores the Posted Date (system date) from the originating module. Even if you change the batch date, this stores the date of when it was entered. You can compare the originating posted date to the trx date (document date) in account transactions as well to see if they vary from what your payables transaction report returns. For example, if you were in balance last month, then you can create a report for the payables transactions based on the system date range and then create a report for GL (using the Account Transactions Smartlist object) for the system date range. This way, you can compare when the information was physically entered in the system (even with a different document date between modules). If one of the modules has the transaction posted and the other is missing, you can determine if the transactions were posted with a different date in GL than in Payables.
These reports will give you a good guideline on trying to narrow down when something was entered, but there could be other possibilities that could be a cause such as a posting interruption (but this may have updated one module and not the other so you can compare what's missing) or payables history was removed (which only affects payables and not GL).
I hope this helps!
Thank you!
Brandon
