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:
Sumit Loya 
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));
}
}
LikeLike
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.
LikeLike
Okay, thanks 🙂
Another question is, when a transaction existing in generalJournalAccountEntry and subLedgerJournalAccountEntry?
nice greetings
LikeLike
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??
LikeLike
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.
LikeLike
Hi,
In my case, i need to generate cummulative total for reporting currency column. how to do that?
LikeLike
Hi Sumit,
In my case i need to take cummulative total for the reporting currency for the general journal transactions.
How i do that? Can you suggest me on this?
Regards,
Zahir
LikeLike
Hey Sumit, I need to export data of PO->pending Vendor Invoice-> financials-> sub ledger journal to external 3rd party app via service. How can i generate that data using x++?
LikeLike