Open Forum

Expand all | Collapse all

AR Aging vs GL balance

  • 1.  AR Aging vs GL balance

    Posted 11 days ago

    I am trying to balance the Aging balance with the GL in a company with a lot of different currencies.
    The Reconcile to GL function doesn't really help.
    I am trying to find it through SQL.
    I would like to find for all the journal entries (I think in GL30000) the subledger Distribution Type (from RM30301?) and then find the incorrect entries.
    Do you have a better idea?

    Thanks a lot

    Nancy Pageau
    Stingray Group Inc.
    Montreal QC

  • 2.  RE: AR Aging vs GL balance

    Posted 11 days ago
    @Nancy Pageau

    It's a fairly simple formula for finding differences between the subledger and the GL:

    • If the field "Series" is NOT Sales then it was a manual entry to the AR Account(s) and that will definitely cause an out of balance
    • If the Distribution in the Sales Module is NOT '3' (Receivables) and the AR Account(s) was used then that will cause an out of balance
    • If the Distribution in the Sales Module IS '3' but the AR Account(s) were not used in that line then that will cause an out of balance.

    Essentially, the Control Accounts (AR, AP, Inventory, Cash, WIP etc) should not allow for manual entry and should be used in default account setups (whichever ones you use be it at the company or card level).

    Any transaction OTHER than the types they are used for will cause an out of balance.

    NOT allowing "Post through to the GL" can cause timing differences if users are not posting the batch at the financial level on the same date they are posted in the sub-ledger.

    So using the accounts incorrectly and timing differences are usually what we see in reconciling subledger accounts to the GL.




  • 3.  RE: AR Aging vs GL balance

    Posted 10 days ago

    Hi Nancy,


    The GL table you need is the GL20000.  That is for the open year transactions.  GL30000 is for historic years.  Likewise RM20000 tables are the open records.  There is a great on line resource.  Looked up Victoria Yuden's RM (google) tables listing to see table names and content. 




    Teri Carpenter, CMA CBM

    Carpenter Associates, Inc.






  • 4.  RE: AR Aging vs GL balance

    Posted 10 days ago
    I previously worked with a company that used multiple currencies (USD, CAD, GBP & EUR) and the revaluation was done on a monthly basis.  So, when I did the reconciliation of the AR Aging to the GL, I had to reflect the revaluation of the prior month and current month so I could balance.

    Also, you can do smatlist showing all transactions (usd & other currency amount) for the GL & period in question.  This will provide you with the financial transactions (Journal entries/revaluation amount).

    Note, please make sure series posting is done prior to reconciling the AR aging to the GL subledger.

    Please feel free to reach out to me and I will gladly help you with this.

    Maria Rito
    Allen Flavors Inc
    Edison NJ



  • 5.  RE: AR Aging vs GL balance

    Posted 10 days ago
    First thing that you probably would like to do, is find a baseline.  When did your AR TB and GL matched.  Start from there and see where it did not match and see if you could do a daily TB and see so you can take a look at those transactions for that day.  Also make sure that AR account in the GL is not revalued.

    Gerald Buenafe
    BTP Technologies LLC


If you've found this thread useful, dive deeper into User Group community content by role