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) / Report on invoice Dued...
Microsoft Dynamics AX (Archived)

Report on invoice Duedate and settlement date

(0) ShareShare
ReportReport
Posted on by Community Member

Hi Guys,

Need some help to extract some info from AX 2012. I would like to get all invoices and the due dates for each and the last settlement date made on the invoice.  It sounds straight forward but I have been struggling with this for the past 2 days. far I have managed to run a query that does not really give me all I want but something close.

here is the query am running:

SELECT DISTINCT INVOICEID

    ,[INVOICEACCOUNT]

    ,[INVOICINGNAME]

    ,[CUSTSETTLEMENT].DUEDATE

    ,[CUSTSETTLEMENT].[CREATEDDATETIME]

       ,[SETTLEAMOUNTREPORTING]

       ,[INVOICEAMOUNT]

    ,[CUSTSETTLEMENT].DUEDATE

       ,[PAYMENT]

       ,[INVOICEAMOUNTMST]

  FROM [CUSTSETTLEMENT],CUSTINVOICEJOUR]

 WHERE ((([CUSTSETTLEMENT].DUEDATE=[CUSTINVOICEJOUR].DUEDATE)AND

 [CUSTSETTLEMENT].CREATEDDATETIME>'2017-01-01') AND [CUSTINVOICEJOUR].INVOICEID LIKE 'XXX%')

Any help would be greatly appreciated

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Please try the following sql.

    SELECT DISTINCT

        CustInvJour.INVOICEID

       ,CustInvJour.[INVOICEACCOUNT]

       ,CustInvJour.[INVOICINGNAME]

       ,CustInvJour.DUEDATE

       ,CustInvJour.[CREATEDDATETIME]

       ,CustSettlement.[SETTLEAMOUNTREPORTING]

    ,CustInvJour.[INVOICEAMOUNT]

       ,CustInvJour.DUEDATE

    ,CustInvJour.[PAYMENT]      

    ,CustInvJour.[INVOICEAMOUNTMST]

     FROM CUSTINVOICEJOUR as CustInvJour

     join CustTrans as Trans

     on Trans.Invoice = CustInvJour.InvoiceId

     and Trans.AccountNum = CustInvJour.InvoiceAccount

     and Trans.TransDate = CustInvJour.InvoiceDate

     and Trans.Voucher = CustInvJour.LedgerVoucher

     join CustSettlement

     on CustSettlement.TransCompany = Trans.dataAreaId

     and CustSettlement.TransRecId = Trans.RecId

     and CustSettlement.AccountNum = Trans.AccountNum

     WHERE [CUSTSETTLEMENT].DUEDATE = CustInvJour.DUEDATE

     AND [CUSTSETTLEMENT].CREATEDDATETIME>'2016-01-01'

     AND CustInvJour.INVOICEID LIKE 'XYZ%'

     and Trans.TRANSTYPE = 8

    Hope this helps you.

    Thanks,

    Chaitanya Golla

  • Lionel07 Profile Picture
    Lionel07 495 on at

    Hello Chaitanya, thanks a mil for your post.

    I have tried what you suggested and it have gotten me a little closer to what i want.

    I have removed this line as I am not sure what hey do" and Trans.TRANSTYPE = 8"

    my only issue not is that it gives me all the transactions done on an invoice but i rather have the last transaction.

    Thanks a mil

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Please use the below query to get latest settled transaction for a given invoice:

    SELECT

       CustInvJour.INVOICEID

      ,CustInvJour.[INVOICEACCOUNT]

      ,CustInvJour.[INVOICINGNAME]

      ,CustInvJour.DUEDATE

      ,CustInvJour.[CREATEDDATETIME]

      ,CustSettlement.[SETTLEAMOUNTREPORTING]

      ,CustSettlement.TRANSDATE

    ,CustInvJour.[INVOICEAMOUNT]

      ,CustInvJour.DUEDATE

    ,CustInvJour.[PAYMENT]      

    ,CustInvJour.[INVOICEAMOUNTMST]

    FROM CUSTINVOICEJOUR as CustInvJour

    join CustTrans as Trans

    on Trans.Invoice = CustInvJour.InvoiceId

    and Trans.AccountNum = CustInvJour.InvoiceAccount

    and Trans.TransDate = CustInvJour.InvoiceDate

    and Trans.Voucher = CustInvJour.LedgerVoucher

    join CustSettlement

    on CustSettlement.TransCompany = Trans.dataAreaId

    and CustSettlement.TransRecId = Trans.RecId

    and CustSettlement.AccountNum = Trans.AccountNum

    WHERE [CUSTSETTLEMENT].DUEDATE = CustInvJour.DUEDATE

    AND [CUSTSETTLEMENT].CREATEDDATETIME>'2016-01-01'

    AND CustInvJour.INVOICEID = 'XYZ%'

    and Trans.TRANSTYPE = 8

    and CustSettlement.TRANSDATE =

    (select max(CustSet2.TRANSDATE) from CustSettlement CustSet2

     where CustSet2.TransCompany = CustSettlement.TransCompany

     and CustSet2.TransRecId = CustSettlement.TransRecId

     and CustSet2.AccountNum = CustSettlement.AccountNum)

    TransType: 8 refers to customer.

    Thanks,

    Chaitanya Golla

  • Suggested answer
    Lionel07 Profile Picture
    Lionel07 495 on at

    Thanks a million Chaitanya, really appreciate your help. With your suggestions and some minor tweaking i was able to get what i wanted.

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    NP...can you mark the answer as verified such that its helpful to others.

    Thanks,

    Chaitanya Golla

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
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans