CODEWITHSUNDEEP
sql-serveraxaptadynamics-ax-2012
Bernardo Morales
Bernardo Morales

Reputation: 142

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

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

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.

Upvotes: 0

Views: 1544

Answers (1)

Alex Kwitny
Alex Kwitny

Reputation: 11544

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;
}

Upvotes: 2

Related Questions