Monday, October 3, 2011

Reconciling GL & AP Data

by Marc Hall, Sage Accpac Consultant
Last week, I spent some time reconciling a client’s Sage ERP Accpac AP aging to their GL. Below is the process I used:
  1. Look for GL (or non-AP) transactions in the GL.
  2. Query the AP Posting Journal Details (APPJD) table for instances where the AP Control account was posted to but the Account Type was 2, indicating that the control account was used as the offset account (in the grid) to an AP transaction (where APPJD.ACCTTYPE = 2). I’ve seen this most often on AP Adjustments.
  3. Query the AP Posting Journal Details table where the account is the AP control account and paste in to sheet1 in Excel. Query the GL Posted Transactions table where the account is the AP Control account (GLPOST.ACCTID) and post in to sheet 2.
  4. I then summed both sheet1 and sheet 2 by Source Type so that I could determine the total of IN, PY, AD, CR, and JE entries I had in each and I compared the APPJD summary to the GLPOST summary to identify which Source Types differed.
  5. For those Source Types that differed, I copied out just those transactions to a separate sheet (one for APPJD and one for GLPOST) and sorted by transaction date and amount so that they should line up. I then copied the transaction amount column from APPJD to GLPOST and looked for differences. When I found differences (e.g. posted in AP but not GL), I investigated the batch-entry in AP and GL to see what happened.
Post a comment and let me know if you've done anything similar or different when reconciling GL & AP data in Sage Accpac.