Multiple payments applied to an AP invoice

Userlevel 2

I would like to have a generic enquiry that lists out any AP invoices that are still open and have multiple payments applied.  Can someone advise how to get started.  I don’t know which tables I would need and how to join them.


Here is an example of the data I am looking for:



Best answer by Naveen Boga 22 May 2024, 20:25

View original

4 replies

Badge +18


I recommend starting with APAdjust.  APAdjust holds the relationship between AP Bill documents and the documents that pay them (Debit Adjustments, Payments, Prepayments).

To identify bills that have multiple payments, you’ll want to add a Count field to count the number of times the same Bill-Reference number appears in the results.


Userlevel 7
Badge +7

@pcraske31 if you have the customizer security role you can use the “inspect element” option to find out what tables fields show up:

here’s the table info @Laura02 was spot on!

Userlevel 7
Badge +19

@pcraske31  Below is the SQL query, and you can refer this query and add the JOINS in the GI to fetch the AP Bill and Payment details.


Select * from APInvoice AI Inner join APAdjust AA ON
AI.DocType = AA.AdjdDocType and AI. RefNbr = AA.AdjdRefNbr
Where AI.RefNbr='005295'



Hope this helps!

Userlevel 2

Thank you everyone for your replies.  I’ll get to work and update you on my progress.


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved