Question Status

Unanswered
Frank FELIX RODRIGUEZ asked a question on 6 Mar 2015 5:17 PM

Continuing with my blogs on Chart of accounts, in this blog, I will help you find the ledger transactions between a particular period.

In Ax 2009, it was pretty simple as you had to just loop through LedgerTrans table, Ax 2012 it has changed a bit.

Lets take an example and see how we can achieve this. Say you want to display following information in a report (for example purpose we will use infolog, I will cover reports in other blog entries).

Main account number – Main account name, Transaction date, voucher number, amount in base currency, amount in transaction currency and currency code.

To fetch transactions, now there are two tables that we need to use:

GeneralJournalEntry, GeneralJournalAccountEntry (There are more tables with GeneralJournalPrefix, I have not found there use yet but will update this post once I find more use of them).

static void findLedgerTransactions(Args _args)

{

    MainAccount                         mainAccount; //Holds the main accounts

    GeneralJournalEntry                 generalJournalEntry; //Used to hold Ledger transactions

    GeneralJournalAccountEntry          generalJournalAccountEntry; //Used to hold Ledger transactions

    SubledgerJournalEntry               subLedgerJournalEntry; //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)

    SubledgerJournalAccountEntry        subLedgerJournalAccountEntry;  //Used to hold sub Ledger transactions (Like sales/purch invoice etc.)

    DimensionAttributeValueCombination  dimAttrValueComb; //Used to store the combination of main accounts and dimensions

 

    while select AccountingCurrencyAmount, TransactionCurrencyAmount,  TransactionCurrencyCode

            from generalJournalAccountEntry

            join dimAttrValueComb

                where dimAttrValueComb.RecId == generalJournalAccountEntry.LedgerDimension

            join AccountingDate, JournalNumber from generalJournalEntry

                where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

                   && generalJournalEntry.AccountingDate == 017\2010

                   && generalJournalEntry.PostingLayer == OperationsTax::Current

                   && generalJournalEntry.Ledger == Ledger::current()

                join MainAccountId, Name from mainAccount

                    where mainAccount.RecId == dimAttrValueComb.MainAccount

                        && mainAccount.MainAccountId == ‘130100’

            join subLedgerJournalAccountEntry

                where subLedgerJournalAccountEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId

                   && subLedgerJournalAccountEntry.LedgerDimension == generalJournalAccountEntry.LedgerDimension

                join Voucher from subLedgerJournalEntry

                    where subLedgerJournalAccountEntry.SubledgerJournalEntry == subLedgerJournalEntry.RecId

                       //&& subLedgerJournalEntry.Ledger == Ledger::current()

 

    {

        info(strFmt("%1-%2, %3, %4, %5, %6, %7, %8", mainAccount.MainAccountId, mainAccount.Name,

                    generalJournalEntry.AccountingDate, subLedgerJournalEntry.Voucher,

                    generalJournalAccountEntry.TransactionCurrencyCode, generalJournalAccountEntry.TransactionCurrencyAmount,

                    generalJournalAccountEntry.AccountingCurrencyAmount,

                    generalJournalEntry.JournalNumber));

    }

}

The output is as follows:

Reply
Nitesh Ranjan responded on 6 Mar 2015 6:20 PM

Thanks for the information

but it is suggested that you should provide these information using blog

this community is having Blog site also to share these kind of information


Please update with your feedback.

Regards, NITESH RANAJN (PMP) | Dynamics AX Consultant

If this post helps you, Please verify this answer and earn Thank you Badge for yourself (Follow this link for more details https://community.dynamics.com/braggerbadges/badge/48.aspx)

Reply
Frank FELIX RODRIGUEZ responded on 7 Mar 2015 12:33 PM

Nitesh, Tanks for the notation.

Reply
André Arnaud de Calavon responded on 9 Mar 2015 2:37 AM

Hi Frank,

You can recommend your blog using the next link: community.dynamics.com/.../136.request-a-new-blog-or-blog-to-syndicate.aspx

kind regards,

André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

Reply