By using this website, you agree to our Terms of Use (click here)
In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Sincerely,
Tim Rodman
How do I connect data in GI or Report to show what AR Invoices were paid with a particular AR Payment?
Here's a quick example on how to join AR documents to payments and memos by joining ARAdjust to ARPayment.
@ncantral better to join ARRegister (2 sets), ARPayment and ARInvoice.
Why - because invoice can be "paid" not necessarily by a payment.
Your query will return Cash Sales applied to itself. Charges being paid. Small Credit write-offs.
For example when payment is refunded it is applied to itself, PRF applied to PMT.
So your "invoice" will be a payment, and payment will be a customer refund....
Etc.
So, by right, he would need to inner join ARRegister INV to ARInvoice by doctype and refnbr, that will be your invoices. And these are main search for us.
Then we left join ARAdjust to it by (INV.DocType = ARAdjust.AdjdDocType and INV.RefNbr = ARAdjust.AdjdRefNbr)
Then we left join ARRegister PMT to ARAdjust by (PMT.DocType = ARAdjust.AdjgDocType and PMT.RefNbr = ARAdjust.AdjgRefNbr)
Then we left join ARPayment to ARRegister PMT by doctype and refnbr, that will be our Payments.
Then you can also apply restrictions by document type to display.
Thank you, I will try this as well, the other worked but now running into on wrong amounts adding up when customer makes multiple payments at a different time in same date range for same invoice. I appreciate the feedback!
@mike-b Hi Mike, did you ever get a working report for AR Payment detailing which transactions it is applied to?
THANK YOU!
Online Members
