Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

GP 2010 Checkbook balance way off from current account balance

Posted on by 75,730
Does anyone have a sql script that would list the transactions in the GL that do not have a matching transaction in the checkbook transaction table? I have over 6,000 transactions to go through looking for differences. Right now there is a difference of about $200,000. They reconcile their checkbook every day and the checkbook balance is correct it is the current account balance in the ledger that is too high.

*This post is locked for comments

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Leslie, this database is a little over 100GB in size so transported the files will be a bit of a bother. I do have a copy on one my images so perhaps you could remote over to check out your script. I could also backup the image and then upgrade to GP 2013.

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Richard,

    Will you share your adjustments with me? Something is dreadfully wrong with the script and I'd love it if you would help me correct it.

    Regarding cash receipts, I have the same kind of script that compares the cash receipts to the GL, the transfers and payables checks should exist in the CM transaction file - what's not matching up?

    Kind regards,

    Leslie

    PS, I would be tempted to take their data off line, update it to 2013 and run through the new routine for cash reconciliation.

    LV

    Kind regards,

    Leslie

  • Dinter Profile Picture
    Dinter 10 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Since GL initiated transactions 'should' only be used for exceptions/corrections - - perhaps isolating them will help bring your volumes down to a more manageable level.

    .

    Best of luck

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Thanks Dinter. I will see if they have a clerk to check out what I will be giving them. These folks do thousands of transactions per day and this is a monumental task.

  • Dinter Profile Picture
    Dinter 10 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    What I did when I ran into the same problem was do a Smart List of all transactions in the Ledger account that had a transaction code starting GL.  All our discrepancies were the result of General Journals being posted to the GL account, and they don't flow to the checkbook.  A simplistic approach (I am not a techy person) but it worked for me.

    Best of luck!

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    I was able to adjust the script to prove that all the checkbook transactions for this checkbook exist in the ledger. However, that leaves the checkbook transfers, cash receipts and payables checks to track down. Something is hitting the checkbook GL account but not the checkbook. They reconcile everyday. They claim the gap is widening everyday

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Leslie, I am going to have a look at this script. Right now when I run it, it comes back with almost 1,000,000 records.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Thanks Leslie, I will be grabbing a copy of their data tonight and will give this a try.

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Oh, the above is using Fabrikam's account indexes.

    Kind regards,

    Leslie

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: GP 2010 Checkbook balance way off from current account balance

    Hi Richard,

    I feel certain there is a more elegant answer to this, but give the following a try and see if it helps.  Please let me know what changes you think should be made to it - if it's even close :)

    SELECT CM_Union.audittrail

          , CM_Union.chekbkid

          , gl20000.jrnentry

          , gl20000.ortrxsrc

          , gl20000.debitamt

          , gl20000.crdtamnt

          , CM_Union.rcptamt

          , gl20000.dex_row_id

    FROM   gl20000

          FULL OUTER JOIN (SELECT audittrail, chekbkid, rcptamt

                           FROM   cm20300

                           UNION

                           SELECT audittrail, chekbkid, trxamnt

                           FROM   cm20200) AS CM_Union

                       ON CM_Union.audittrail = gl20000.ortrxsrc

    WHERE  ( gl20000.ortrxsrc IS NULL )

       OR ( CM_Union.audittrail IS NULL )

          AND ( gl20000.actindx IN ( 1, 2, 365, 362,366, 363, 368, 367, 364, 452 ) )

          AND ( gl20000.sourcdoc <> 'BBF' )

    ORDER  BY CM_Union.audittrail, gl20000.dex_row_id DESC, CM_Union.rcptamt  

    Kind regards,

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans