Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Cash Manager Out of Ba...
Microsoft Dynamics SL (Archived)

Cash Manager Out of Balance

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

HI,

We have a problem when reconciling our back. Our client was not able to reconcile any period in this financial year so they decided to do a bank recon stretching from period 1 to period 5. But this has no positive effect at all. The GL and CA are out of balance and I have tried every thing in my arsenal to resolve this but had very little luck. Is doing one big recon over 5 period a good idea?

 

CutyAcct and AcctHist do balance so I know the problem is not there. I have suspicion that transactions where done from GL but they did not update CA. Is there any way that I can check if this is the case? I have tried to do a manual recon but with over 5000 transactions it is a very time consuming exorcize and its not the smartest way to do this. 

 

Any help or suggestion will be greatly appreciated. Please feel free to ask if you need more Info.

 

Thank you.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cash Manager Out of Balance

    Nam Dang:

    If you run Cash Manager Integrity Check after changing the dates, that problem will be taken care off.  Just make sure you run the integrity check for the dates covered by both the incorrect date and the corrected date.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cash Manager Out of Balance

    I have had a painful experience when changing transaction date in catran table. Because the daily cash balance report take data from catran table and cashsum table, if you just change transaction date in catran table, the report won't be correct, the total and detail aren't the same. My solution is revert all wrong transaction and redo it. Example: if you have a RC entryID batch with transaction date 12/31/2014 and perpost 01-2015, you should revert with a CK entryID batch with the same transaction date and perpost. After that you redo the batch with correct transaction date and perpost.

    HTH

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cash Manager Out of Balance

    I have changed transaction dates via SQL many times to correct problems in cash manager.  However, like any manipulation of the database in SQL you need to be careful.

    Note that the transaction dates used by Cash Manager come from different tables depending on the type of transaction.  Cash manager Transactions are from CATrans, Journal Entries are the GLTran table, AP checks from APDOC and payroll Checks are from PRDoc.  Payment applications through AR uses the batch record.    

    You will want to run the CA integrity check after changing the dates for the time periods covered by both the incorrect and the corrected date.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cash Manager Out of Balance

    Thank you for the advice. I was a HUGE help. When I followed you recommendation, straight off the bat I noticed that there ending balances for the previous financial year was incorrect. I have correct this now so that is at least that's the first step in the right direction.

    The second thing causing the imbalances was transaction that where posted with the incorrect date and incorrect period. Once they are fixed, all should be O.K.  Witch leads me to my next question:

    How do I go about fixing the date issue? For Example, if transactions appear on the statement on 31 July 2014 ( this is also the last statement date ) but they where captured with a date of 01 August 2014.  I know you should use the "O" transactions to rectify this but is there an easier way?

    One of my co-workers suggested updating the "trandate" in CATRAN and GLTRAN. I have tested this on our testing system and it does seem to work very well, it puts all the transaction into the correct recon dates. My only concern is that I am not sure what consciences this might have on things like customer, Vendor aging and GL. This method would save me loads of time.

    Any thoughts?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cash Manager Out of Balance

    In addition to John's direction, verify that the gl balance that displays in Cash Manager>Bank Rec is the same balance that shows in GL>Acct History.  

    GLtran.s4future11 has a value of O, C, N or blank for the cash acct.

    Select * from gltran where s4future in('','N') and acct = 'xxxx' and sub = 'xxxx' and module = 'gl'

    O (outstanding) or C (cleared) status are the only gltrans that would show up in cash manager.

    You could possibly delete bank recs going back 5 periods  or so.  Then run the CA Integrity for the date of the last bank rec thru today being sure that the year matches the date range.  Otherwise, you would need to run the CA Integrity for 2 date ranges if the dates crossed years.

    Then add back the Bank recs one at a time.  All data is stored in other tables except the statement balance which you must manually enter.

    You could run this script to see if a batch of CA transactions might have an incorrect catran.rcnclstatus.

    -- find catrans where ZZ records don't have rcnclstatus AND at least one non-ZZ has a blank rcnclstatus

    select * from catran c where entryid<>'ZZ' and rcnclstatus=''

    and batnbr in (select batnbr from catran where entryid='ZZ' and rcnclstatus='')

    -- find catrans where ZZ records do have rcnclstatus AND at least one non-ZZ also has a rcnclstatus

    select * from catran c where entryid<>'ZZ' and rcnclstatus<>''

    and batnbr in (select batnbr from catran where entryid='ZZ' and rcnclstatus<>'')

  • John_Joyce Profile Picture
    John_Joyce 1,040 on at
    RE: Cash Manager Out of Balance

    You noted that you went thru everything in your arsenal, but didn't specify what you did.

    Is the CA Setup screen set-up to Accept GL Transactions?

    Is the Cash Account Maintenance screen set-up to Accept GL Transactions?

    When was the account last reconciled successfully in SL?

    Did you review the Daily Cash Balances screen, both By Period and By Date, to see if there are any discrepancies between Transaction Dates and Period-to-Post?

    Are there any Unposted batches affecting the Cash Account?

    There's nothing too wrong about doing a 5-month reconciliation, assuming there was a proper starting-point (in your case, a successful reconciliation in Period 12 of the prior year).

    You may want to go to the Account/Sub Detail Inquiry screen and dump all the transactions out to Excel, and then sort & group by source module. Then do the same in the Bank Rec screen by dumping all Deposits and then all Checks (cleared & un-cleared for both) to Excel & grouping by source module.

    One problem with 5 months of un-reconciled activity is that you really don't know for sure that the GL balance is correct, or if there may be some missing activity that hit the bank but never hit SL in any way.

    John

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!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans