By using this website, you agree to our Terms of Use (click here)
How do I connect data in GI or Report to show what AR Invoices were paid with a particular AR Payment?
@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!
THANK YOU!
Online Members
