How to getting ledger transactions in AX 2012
In Dynamics 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).
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 THK_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 <= today() //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));
}
{
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 <= today() //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));
}
}