Skip to main content
Answer

Multiple payments applied to an AP invoice

  • May 22, 2024
  • 4 replies
  • 119 views
P
Freshman I

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.

Thanks

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

 

Best answer by Naveen Boga

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

    Laura03
    LauraWyckoff

    4 replies

    Laura03
    Captain II
    Forum|alt.badge.img+19
    • Laura03
    • Captain II
    • May 22, 2024

    Hello,

    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.

    Laura

    ~ Laura Barber, WM Synergy
    Chris Hackett
    iqraharrison
    Captain II
    Forum|alt.badge.img+8

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

    Iqra ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*. Proud Acumatica MVP (2024, 2025) - See my journey here: https://community.acumatica.com/community-site-discussions-150/iqra-harrison-acumatica-mvp-2025-28977
    Chris Hackett
    Naveen Boga
    Captain II
    Forum|alt.badge.img+19
    • Naveen Boga
    • Captain II
    • Answer
    • May 22, 2024

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

    Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
    Manikanta Dhulipudi
    Chris Hackett
    P
    Freshman I
    • pcraske31
    • Author
    • Freshman I
    • May 22, 2024

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

    iqraharrison
    Laura03
    Chris Hackett
    Terms & ConditionsCookie settingsAccessibility statement
    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