You’re offline. This is a read only version of the page.
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
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?
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.
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.
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.
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!
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?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (