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).

staticvoid 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

 

   whileselect AccountingCurrencyAmount, TransactionCurrencyAmount, TransactionCurrencyCode

           from generalJournalAccountEntry

           join dimAttrValueComb

               wheredimAttrValueComb.RecId == generalJournalAccountEntry.LedgerDimension

           join AccountingDate, JournalNumberfrom generalJournalEntry

               where generalJournalAccountEntry.GeneralJournalEntry ==generalJournalEntry.RecId

                  && generalJournalEntry.AccountingDate == 017\2010

                  && generalJournalEntry.PostingLayer == OperationsTax::Current

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

               join MainAccountId, Namefrom mainAccount

                   wheremainAccount.RecId == dimAttrValueComb.MainAccount

                       && mainAccount.MainAccountId == ‘130100’

           join subLedgerJournalAccountEntry

               where subLedgerJournalAccountEntry.GeneralJournalAccountEntry ==generalJournalAccountEntry.RecId

                  && subLedgerJournalAccountEntry.LedgerDimension == generalJournalAccountEntry.LedgerDimension

               join Voucherfrom 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: