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 / Microsoft Dynamics SL (Archived) / where to find overpayment
Microsoft Dynamics SL (Archived)

where to find overpayment

(0) ShareShare
ReportReport
Posted on by Alex Pan 350

Hi

This is a technical question since i am not using SL but pulling data from SL.

My client has quite a few overpayment. It looks like the overpayment can only apply to the invoices amount. So they do not know how to apply the rest of the money.

To me, I need to generate a chart with invoices date and the amount was paid.

For example a client paid for an invoice 11111 for $30,000, but the invoice is only $20,000 where do i get the $10,000 from?

I used the table ARAdjust to get the Paid amount,but ARadjust give me the correct info only until the overpayment showed.

 

Can someone please help??

 

*This post is locked for comments

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    Re: where to find overpayment

    Hi Alex,

    So I understand that you received a check from a customer for $20,000.  You applied $12,000 to an invoice and that is recorded in the ARadjust table.  That leaves $8000 left on the payment.

    What do you see in ARdoc for that payment number?  Does it have a DocBal of $8000?  

    Or are you saying that the Docbal is zero and you can't find any other applications?

  • Alex Pan Profile Picture
    Alex Pan 350 on at
    Re: where to find overpayment

    Hi Carolyn

    Here goes the problem.

    For one client that over paid $8000, in adjamt,it only has the $12000  that was applied to the first invoice.

    but they paid $20000. Where do i find the $8000 in which table?

    I am trying to build a client ledger chart and that amount was missing. It is not in ARAdjust table.

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    Re: where to find overpayment

    Hi Alex,

    The Ardoc table doesn't record the amount of the application.  You would need to use ARadjust, and Adjamt is the payment amount.  The ADJGrefnbr is the payment number and the Adjdrefnbr is the invoice number to which the payment was applied.

    Let me know if you need more help.

  • Alex Pan Profile Picture
    Alex Pan 350 on at
    Re: where to find overpayment

    Carolyn

    We do have 5 open payments which 4 of them were partially applied. Now, should I use OrigDocAmt as payment amount? I have been using AdjAmt from ARAdjust.

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    Re: where to find overpayment

    Hi Alex,

    I'm sorry, my statement was incorrect.  I should have had DocBal in the statement and not OpenDoc and I put Apdoc instead of ARdoc.  My mistake.

    Select * from ardoc where doctype = 'PA' and opendoc = '1'

    This will return all open payments.

    Select * from ardoc where doctype = 'PA' and opendoc = '1' and docbal < origdocamt

    This will return all open payments that have been partially applied.

    Do you have any open payments?  The above statement would pull any payment documents that have a remaining balance less than the original balance.

    If not, then it would appear that all of the overpayments have been applied to other invoices.  So it would be hard to find payments that originally were submitted that were greater than the current open invoice but then later applied to other invoices.  Then it's no longer an overpayment after the entire payment balance is at zero.  

    If you receive a payment for $5000, and it is applied to an invoice of $1000, the payment would have a DocBal of $4000, an OrigDocAmt of $5000, and an OpenDoc of 1 (which means it's open).  If you keep decreasing the open balance by applying it to invoices that arrive subsequent to the first application of $1000 till the DocBal is at zero, then there is no more overpayment to look for.

    With the above statements, do you have any open payments?

    Let me know what you find.

  • Alex Pan Profile Picture
    Alex Pan 350 on at
    Re: where to find overpayment

    Hi Carolyn

    Sorry for the late response and thank you for replying.

    Please forgive me for my ignorance in the functional side, but I was not sure your point where says:  open payments who's balance is less than the orignal balance.

    should that be greater?

    but anyway I tried your sql and i got nothing.

    and then I tried Select * from apdoc where doctype = 'PA' and opendoc = '1'  I have nothing either.

    Do you think it is a problem of setting up ?

    So here is the scenario:

    when a client over paid, the accouting dept will hold the extra money and they will apply to the next bill.

    now with the money being applied and the over payment is used. I was unable to find out the actual payment on this.

    the payment is applied only for the first time it was applied, but the rest of the money was nowhere to be found. for example, the bill is $1,000 and the client paid $5,000, so the over payment is $4000.

    now it is the 2nd bill, we bill $2000, and left $2000, and so forth, when the money is used in the client ledger we can get all the bills but the client only 'pays' $1000 for the first time. the $4000 was gone.

    I am not sure where to find the $4000, becuse i was looking for the amount in ARAdjust where AdjgDocType='PA'.  How do I identify the overpayment?

    Please help!

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    Re: where to find overpayment

    Hello Alex,

    You could use the Customer Trial Balance.  Use the format - Open Documents Only.  Then on the Select tab, choose field vr_08620o.doctype, use an operator of Equal, and then enter in a value of PA.  This would show you all open payments.  If you need to know what it was applied to, you could use the Application Inquiry/Reversal screen to pull up the payment.  You could see how many payments you need to look at and then try and find the most efficient way to find what invoices they were applied to.

    I supposed if you wanted to do all this in SQL, you could first find the open payments who's balance is less than the orignal balance.

    Select * from apdoc where doctype = 'PA' and opendoc = '1' and docbal > 0.00 and opendoc < origdocamt

    Then having those results, you could run another select from apadjust where adjgrefnbr = the results of that select statement.  Then look at the Apadjust.adjdrefnbr to get the invoice number.  That alone won't tell you that the payment was larger than the invoice, but hopefully there aren't so many that you can't look each one up.

    Is this heading in the right direction?  

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... 293,207 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans