Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Customer Balance calcu...
Microsoft Dynamics GP (Archived)

Customer Balance calculation issue (RM00103 table to blame?)

(0) ShareShare
ReportReport
Posted on by Nate Owens 400

Hi All, thanks in advance to anyone lurking here and might want to bang collective heads on the wall with me...  ;)

In our world we have a variety of applications, systems and sites that must query for customer balances throughout the day, on a frequent basis (200K "hits" a day is typical).  They call a custom sproc to do this, it has some other logical pieces to it, however the relevant excerpt is this:

SELECT @CustomerBalance = (CUSTBLNC + UNPSTDSA + UNPSTOSA - UNPSTDCA - UNPSTOCA) FROM RM00103 WITH(NOLOCK)

This has performed nearly flawlessly, however it is sometimes (we'll say 3 times a day) returning completely incorrect information at a particular point in time (thanks to high levels of logging and irate customers, we know this even if in reality this happens as rarely as it does).  Usually when support staff is called to investigate it is no longer incorrect.

Some of the obvious culprits we've ruled out, like accounting staff posting batches, closing related activities, etc, also, the sproc call is not failing due to SQL Server being taxed, if it does this we'd get no value or a 0, not a real value that looks correct but is in fact not so.  Scheduled system tasks that run during the day and overnight don't seem to be coinciding with the times of the issues either.

Some direction, anything at all really, would be appreciated.

Thank you.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Customer Balance calculation issue (RM00103 table to blame?)

    I think removing the WITH (NOLOCK) from the query would put locks on the table in order to get accurate data.

  • steveendow Profile Picture
    steveendow 2,281 on at
    Customer Balance calculation issue (RM00103 table to blame?)

    Hi Nate,

    I am the reseller for Post Master and would be happy to have a call to discuss your requirements and see if Post Master might help.  We'd obviously want to confirm that posting batches in a more timely manner would resolve your issue, in which case Post Master might be a good solution. 

    PCI compliance and strict security is a challenge, as we are limited by GP's architecture, but there are some potential workarounds that we've discussed with other customers.

    You can contact me via e-mail at steveendow [at] gmail [dot] com.

    Steve Endow

     

  • Nate Owens Profile Picture
    Nate Owens 400 on at
    Re: Re: Re: Re: Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    True.  Of course the noted behavior (Richard Whaley's post above) of GP deleting then inserting a record to this table instead of updating really has me scratching my head, and may explain the temporary glitches in balances that seem to self correct.  Is this done because this method is actually more efficient than a single UPDATE?  Would still present a challenge even if we were able to just query the CustomerBalance field and no other...

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: Re: Re: Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Nate, I would definitely talk to the Post Master folks about the issues and see what they suggest. 

    I think the whole reason we originally went down the road of talking about posting in a more timely fashion is to see if you could eliminate the need to include anything except the customer balance column in your queries.  Not sure if that will fix the issues you're seeing, but my feeling is that you will get more consistent results if you are not looking to include unposted transactions/balances.

  • Nate Owens Profile Picture
    Nate Owens 400 on at
    Re: Re: Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Thanks Victoria, the logged in user can definitely be a deal-breaker depending on how exactly this works.  PCI compliance requires fairly stringent security measures in which a dedicated logged in user is a significant violation (potentially allows anyone with access to the PC to do what they'd like in GP), but also is a logistical problem in that the PC must stay up, and the GP session can not be terminated (due to people logging in and out, windows updates, etc.).  And of course requires an additional user license for all intents and purposes.

    I will check out the blog though, could be that some of the downsides can be overcome.

    I am still concerned though that this may not ultimately fix the issue - posting may solve the problem with a user having activity, but what about one that has not during a given invoice period?

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Nate, I believe Post Master requires a user to be logged into GP - is that a deal breaker for you?  If someone was to manually post the batches, wouldn't they need to be logged into GP? 

    I am not very familiar with the exact setup or the product itself, as I did not work with the customer directly on this.  My recollection is that they have multiple batches, typically SOP and RM, with thousands of transactions in each, that are being imported nightly and now posted automatically using Post Master.  I do know they have been very happy with it. 

    Here is a blog post that talks about Post Master in some more detail: http://dynamicsgpland.blogspot.com/2009/04/automatically-post-batches-in-dynamics.html.

  • Nate Owens Profile Picture
    Nate Owens 400 on at
    Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Not sure about Post Master, our VAR demoed and ruled out one product initially, and I don't know the name of it, then they chose a second product which is basically a security joke (unencrypted passwords in text files required for automating, or a dedicated and actively logged-in user to GP), so it is turned off at the moment.

    Can you tell me a little about Post Master (the URL you included did not contain any real information), and the kinds of volumes your customer is running, or how complex (ie multi-faceted) their environ is?

    Thank you.

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Nate, you're saying you've tried Post Master and it has not worked for you?  We have a customer using it for nightly imported jobs that are quite large with great success.  And it's definitely not $100K. 

    You cannot post using eConnect.  Something else is needed to do the posting.

  • Nate Owens Profile Picture
    Nate Owens 400 on at
    Re: Customer Balance calculation issue (RM00103 table to blame?)

    OK, thank you, sounds like we'd have to primarily rely on invoicing (which we do twice monthly) to correct the one-offs, not frequent enough obviously.  We've tried twice now to use auto-posting software, but not for real-time posting, just nightly stuff, and they have not been adequate for the job (perhaps because we can't spend $100K on a real product).

    This sound like a dead end for us?  Or can I "post" via the eConnect call when I enter payments or other transactions?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: Re: Re: Re: Customer Balance calculation issue (RM00103 table to blame?)

    Outside of the standard transaction processing the other processes that could update RM00103 would include:

    1. Running a debtors ageing process.
    2. Running a finance charge process.
    3. Applying sales documents - (and writing off amounts during the apply).
    4. Processing NSF Transactions and Voids.
    5. Running and printing customer statements.

    There may be others. Victoria and Richard, any ideas?

    Best regards,

    Ian.

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans