Skip to main content
Solved

Multiple payments applied to an AP invoice

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!

View original
    Laura02
    1 person likes this
    • Laura02
      Laura02
    Did this topic help you find an answer to your question?

    Laura02
    Captain II
    Forum|alt.badge.img+19
    • Laura02
    • 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 ~ Klear Systems, Inc. Irvine, CA. ~ Click my logo to Follow me ~
    Chris Hackett
    1 person likes this
    • Chris Hackett
      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
    1 person likes this
    • Chris Hackett
      Chris Hackett
    Naveen Boga
    Captain II
    Forum|alt.badge.img+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!

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

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

    iqraharrison
    Laura02
    Chris Hackett
    3 people like this
    • iqraharrison
      iqraharrison
    • Laura02
      Laura02
    • Chris Hackett
      Chris Hackett

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    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