Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Faster way to reconcil...
Finance | Project Operations, Human Resources, ...
Unanswered

Faster way to reconcile fiscal year information for customers

(0) ShareShare
ReportReport
Posted on by StefanieC 434

I am doing a fiscal year change and part of that is to reconcile GL, AP, and AR.  In one of my companies, GL takes about an hour for the 2 affected years, AP takes about 5 minutes, and AR takes about 5 days for just ONE of the years.  I am doing this in a sandbox environment so resources could be affecting it a little bit.  Is there a faster way to do this like the PSTL Inventory reconcile where it only reconciles what needs reconciled?  I have tried to search but using the words "reconcile" and "receivables" just brings me tons of stuff about reconciling to GL.

Thank you

Categories:
Microsoft Dynamics GP
  • Cheryl Waswick Profile Picture
    Cheryl Waswick on at
    RE: Faster way to reconcile fiscal year information for customers

    No, not at all, actually I was impressed!  LOL. 

    Here is our product suggestion web site, in case you want to log it.   It gives users a chance to vote on it and the more votes it gets, it could be considered for a future enhancement.  I didn't provide this the first time because I have never actually heard this request before.  So I do hope you get more suggestions from the community if this something more users would like to have. 

     https://experience.dynamics.com/ideas/

    Thanks!  Have a good night!

  • StefanieC Profile Picture
    StefanieC 434 on at
    RE: Faster way to reconcile fiscal year information for customers

    LOL I'm definitely NOT an "expert SQL Scripter" or I wouldn't have come here for help.  My last reply was to sincerely thank you for trying to help me, offer a suggestion for improvement, and hopefully prompt anyone who has written similar scripts to give input.  I apologize if it came off otherwise. :)

  • Cheryl Waswick Profile Picture
    Cheryl Waswick on at
    RE: Faster way to reconcile fiscal year information for customers

    It sounds like the size of your tables is playing a role here.  I don't know of any other way to reconcile in GP for this.  You could  remove some history, depending on how far it goes back to make your tables smaller.  But it sounds like you are an expert SQL Scripter, and can find your way to verify.   I hope others in the community will contribute to this post on what others are doing.  

  • StefanieC Profile Picture
    StefanieC 434 on at
    RE: Faster way to reconcile fiscal year information for customers

    Hi Cheryl,


    Thank you for your reply.  I am doing the receivables fiscal year reconcile and I believe it is going through open and history tables so I'm not sure moving to history will help, unfortunately.  It looks like there is about 5.6 million in open and about 4.8 million in history.  I randomly selected a few different size ranges of customer IDs and it does appear to be taking a lot of time on customers that do not even need to be reconciled.  I choose like 5 customers to reconcile, none of them had balances and it took a few seconds.  I then choose about 20 customers and it took about 1.5 minutes.  None of them had discrepancies (not sure about balances). 

    The PSTL tool has an Inventory Reconcile tool that basically just reconciles the ones that need to be reconciled.  From prior experience, I know it is MUCH faster.  I was looking for something like this and wondered why this didn't exist already.  I am thinking I may have to just write the scripts to do the process in SQL.

    Again, thank you for assisting.

  • Cheryl Waswick Profile Picture
    Cheryl Waswick on at
    RE: Faster way to reconcile fiscal year information for customers

    I assume you are talking about the 'Reconcile' utility that is taking so long for GL and RM?    I don't know of any other way to reconcile.

    If your RM table is huge, then it has to sift through the whole table and that may be a part of the problem.   Do you ever run the 'Paid transaction removal' sales routine to move fully paid documents to history?    I think that if you have older years still in the open tables and can move some of those older years to history, that would help a lot.  

    --You can use these scripts to get a record count of each table.

    select count(*) from RM20101

    select count(*) from RM30101

    --Under tools >> Setup >> Sales >> Receivables >> options, you can view the system date that 'Paid transaction removal' was last run for this company.

    --these scripts will also help you gauge the oldest doc date in the RM open table that is fully applied, and the most recent date in your RM history table.

    select min(DOCDATE) from RM20101 where CURTRXAM = '0.00'

    select max(DOCDATE) from  RM30101

    If your tables are huge, it's going to take a long time.  You can turn on a SQL profiler trace and see it working it's way through the table.  So if you can move any data to history or even purge data, it's going to help all processes (especially voiding)

    Also, make sure that you have all logs, anti-virus, dexsql.logs, etc turned off during the process as well.

    I hope that helps,

    Cheryl Waswick

    Microsoft Dynamics GP support

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans