Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Which table holds sale...
Microsoft Dynamics AX (Archived)

Which table holds sales invoice payment information? how does it link to sales invoice?

(0) ShareShare
ReportReport
Posted on by mangosteenlu 15

Hi All,

Thank you in advance for reading my post here. Our company is using an old system - AX 2009. I wonder if someone can enlighten me with which table(s) holds the information I require.

I am trying to create a report to see how quick we are collecting cash from each customer from 2018 to date. I can find all sales invoices from CUSTINVOICETRANS table but I am not sure how to find when the invoices being paid and how much.  

I wonder if someone can enlighten me with the question I have above.

Thank you very much for your help.

*This post is locked for comments

  • mangosteenlu Profile Picture
    mangosteenlu 15 on at
    RE: Which table holds sales invoice payment information? how does it link to sales invoice?

    Hi Satish,

    Thank you very much for your reply. sorry to be a pain, I don't understand the SQL script from you as I am not a developer (too complicated for me :). I only have very basic knowledge of SQL and I am learning.

    I have done the below query to get the payment information relating to the customer invoice, but it is far more tedious, any suggestion?

    SELECT CUSTTRANS.ACCOUNTNUM, CUSTINVOICEJOUR.INVOICINGNAME, CUSTINVOICEJOUR.INVOICEID, CUSTINVOICEJOUR.LEDGERVOUCHER,CUSTTRANS.LASTSETTLEVOUCHER,

    CUSTINVOICEJOUR.INVOICEDATE, CUSTINVOICEJOUR.DUEDATE, CUSTTRANS.LASTSETTLEDATE, CUSTINVOICEJOUR.INVOICEAMOUNTMST, CUSTTRANS.SETTLEAMOUNTMST,

    CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTTRANS.SETTLEAMOUNTCUR, CUSTINVOICEJOUR.CURRENCYCODE, CUSTINVOICEJOUR.PAYMENT

    from CUSTINVOICEJOUR, CUSTTRANS

    WHERE CUSTTRANS.ACCOUNTNUM='M1700' and CUSTTRANS.INVOICE=CUSTINVOICEJOUR.INVOICEID AND CUSTINVOICEJOUR.INVOICEDATE >= '2019-07-01'

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,645 on at
    RE: Which table holds sales invoice payment information? how does it link to sales invoice?

    Hi mangosteenlu,

    Check custSettlement Table. Use query below, you can join the tables and streamline the query. CustTrans.RecId is used in custSettlement table with fields OffsetRecId and TransRecId

    select top 10 recid , * from custtrans where dataareaid = 'usmf' and INVOICE = 'ABC'

    select top 10 * from CUSTSETTLEMENT where dataareaid = 'usmf' and OFFSETRECID in (recid from above) or TRANSRECID in (recid from above)

    Thanks,

    Satish Panwar

    Please take time to click 'Yes' against the answers that help you guide in right direction to help other community members.

  • mangosteenlu Profile Picture
    mangosteenlu 15 on at
    RE: Which table holds sales invoice payment information? how does it link to sales invoice?

    Hi Blue Wang,

    Thanks for your reply. I have tried to join two tables together however

     1) I am still not knowing where the payments are for all invoices.  

    2) There are multiple lines when SalesId being joined

    can you show me SQL script for it?

    Thanks

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Which table holds sales invoice payment information? how does it link to sales invoice?

    Hi Mangosteenlu,

    Please check CustInvoiceJour.InvoiceAmount.

    It's the invoice total，you can join from SalesId.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans