AUGForums.com

An Acumatica User Group

  • Free
    • Start Here
    • Rolodex
    • Podcast
    • Blog
    • Forums
  • Course
  • Consulting
  • Login
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Report De...
How to create repor...
 
Notifications
Clear all

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

Questions How to create report where AR Invoices were paid with a particular AR Payment?

Acumatica Report Designer
Last Post by abulger 11 months ago
7 Posts
5 Users
5 Likes
1,470 Views
RSS
Mike Baio
Posts: 12
 Mike Baio
Topic starter
April 27, 2020 7:13 pm
(@mike-b)
Member
Joined: 3 years ago

How do I connect data in GI or Report to show what AR Invoices were paid with a particular AR Payment?  

6 Replies
ncantral
Posts: 21
 ncantral
April 27, 2020 8:02 pm
(@ncantral)
Member
Joined: 3 years ago

Here's a quick example on how to join AR documents to payments and memos by joining ARAdjust to ARPayment.

image
Reply
Sergey
 Sergey
(@sergey)
Joined: 3 years ago

Member
Posts: 4
May 4, 2020 4:57 am
Reply toncantralncantral

@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.

 

Reply
ncantral reacted
Mike Baio
 Mike Baio
(@mike-b)
Joined: 3 years ago

Member
Posts: 12
May 21, 2020 8:39 am
Reply toSergeySergey
ncantral

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!

Reply
Tim Rodman reacted
sjohnson
 sjohnson
(@sjohnson)
Joined: 2 years ago

Member
Posts: 1
December 16, 2021 11:27 am
Reply toMike BaioMike Baio
Sergey
ncantral

@mike-b Hi Mike, did you ever get a working report for AR Payment detailing which transactions it is applied to?

Reply
Tim Rodman reacted
abulger
 abulger
(@abulger)
Joined: 3 years ago

Member
Posts: 2
February 25, 2022 10:40 am
Reply toSergeySergey
ncantral

@sergey Would you be able to post your full query here?

Reply
Tim Rodman reacted
Mike Baio
Posts: 12
 Mike Baio
Topic starter
April 28, 2020 11:12 am
(@mike-b)
Member
Joined: 3 years ago

THANK YOU!

Reply
Tim Rodman reacted
Forum Jump:
  Previous Topic
Next Topic  
Forum Information
Recent Posts
Unread Posts
Tags
  • 11 Forums
  • 2,236 Topics
  • 9,868 Posts
  • 4 Online
  • 1,812 Members
Our newest member: Heather Jones
Latest Post: Sales Order Comment Lines / Memo / Note Lines
Forum Icons: Forum contains no unread posts Forum contains unread posts
Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2023 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×