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

Notifications

Announcements

No record found.

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 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,671 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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans