Where can I find the pending amount from invoices in DynamicsAX Database?

Question

This is what I already know, Tables for open invoices:

• "CustTransOpen"
• "CustInvoiceJour"
• "CustTrans"

I've been working on this for a while without a clue, there must be a way to get the pending amount or at least to know if these invoices have been paid.

I already looked on all the Microsoft documentation with no success, hope you can help me.

Answer 1

This is the AX Query to get the open balance in MST for a customer. To do it in SQL, you can rewrite it and/or at least use this to maybe get towards what you want.

This code originates from \Data Dictionary\Tables\CustTable\Methods\openInvoiceBalanceMST in AX.

this below refers to CustTable

while select sum(AmountMST) from custTransOpen
    where custTransOpen.AccountNum  == this.AccountNum
       && custTransOpen.TransDate   >= _fromDate
       && custTransOpen.TransDate   <= _toDate
    exists join custTrans
        where custTrans.RecId       == custTransOpen.RefRecId
           && (custTrans.Invoice    != ''
           || (custTrans.Invoice    == '' && custTrans.AmountMST   > 0))
{
    openBalanceMST += custTransOpen.AmountMST;
}