web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
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 19

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

I have the same question (0)
  • Blue Wang Profile Picture
    Blue Wang Microsoft Employee on at

    Hi Mangosteenlu,

    Please check CustInvoiceJour.InvoiceAmount.

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

  • mangosteenlu Profile Picture
    mangosteenlu 19 on at

    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

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,673 Moderator on at

    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 19 on at

    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'

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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 21

#2
dekion Profile Picture

dekion 4

#2
Virginia99 Profile Picture

Virginia99 4

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans