web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP 2010 Checkbook bala...
Microsoft Dynamics GP (Archived)

GP 2010 Checkbook balance way off from current account balance

(0) ShareShare
ReportReport
Posted on by Richard Wheeler 75,852 Moderator
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

I have the same question (0)
  • L Vail Profile Picture
    L Vail 65,271 on at

    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

  • L Vail Profile Picture
    L Vail 65,271 on at

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

    Kind regards,

    Leslie

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

    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,852 Moderator on at

    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

  • Dinter Profile Picture
    Dinter 10 on at

    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,852 Moderator on at

    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

    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

  • L Vail Profile Picture
    L Vail 65,271 on at

    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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

    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.

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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans