Getting Ledger transactions in Ax 2012

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:

image

image

9 thoughts on “Getting Ledger transactions in Ax 2012

  1. hey,
    why do you join the SubledgerJournalAccountEntry to get the voucher?

    Its think it’s better to use the subledgerVoucherGeneralJournalEntry and additional use some outerjoins ๐Ÿ™‚

    What do you mean?

    static void SB_findLedgerTransactions(Args _args)
    {
    MainAccount mainAccount;
    GeneralJournalEntry generalJournalEntry;
    GeneralJournalAccountEntry generalJournalAccountEntry;
    DimensionAttributeValueCombination dimAttrValueComb;
    SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry;
    ledgerEntry ledgerEntry;
    ledgerEntryJournal ledgerEntryJournal;
    ledgerEntryJournalizing ledgerEntryJournalizing;

    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 == str2Date(‘12012012’, 123)
    && generalJournalEntry.PostingLayer == OperationsTax::Current
    && generalJournalEntry.Ledger == Ledger::current()
    join MainAccountId, Name from mainAccount
    where mainAccount.RecId == dimAttrValueComb.MainAccount
    && mainAccount.MainAccountId == ‘1105’
    join subledgerVoucherGeneralJournalEntry
    where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == GeneralJournalEntry.RecId
    outer join RecId from ledgerEntry
    where ledgerEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId
    outer join RecId from ledgerEntryJournal
    where ledgerEntryJournal.RecId == generalJournalEntry.LedgerEntryJournal
    outer join RecId from ledgerEntryJournalizing
    where ledgerEntryJournalizing.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId
    {
    info(strFmt(“%1-%2, %3, %4, %5, %6, %7, %8”, mainAccount.MainAccountId, mainAccount.Name,
    generalJournalEntry.AccountingDate, subledgerVoucherGeneralJournalEntry.Voucher,
    generalJournalAccountEntry.TransactionCurrencyCode, generalJournalAccountEntry.TransactionCurrencyAmount,
    generalJournalAccountEntry.AccountingCurrencyAmount,
    generalJournalEntry.JournalNumber));
    }
    }

    1. Hey Severin,

      Yes if you need just the voucher then you can use subledgerVoucherGeneralJournalEntry only. I took those tables as examples not specificially for voucher alone.

  2. Okay, thanks ๐Ÿ™‚

    Another question is, when a transaction existing in generalJournalAccountEntry and subLedgerJournalAccountEntry?

    nice greetings

  3. Hi Sumit,

    I just want to know how ledger account has taken while posting Sales invoice?? Where is the code and on what basis , Ledger account has been taken??

  4. Hey Sumit,
    I want to see the Database Log of General Journals AX 2012, I have setup the log but the problem is system is allowing me generate log against Update, Delete, but my requirement is to Track New Transactions. Please suggest.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s