Select GeneralJournalEntry records by subledger record

(0) Share

Report

Posted on by NickYurov

Dear experts,

Can you help me to verify the following statements:

1) LedgerTrans table holding general ledger transactions in some previous versions of Dynamics 365FO (Dynamics AX 2009 and so on) is now replaced by GeneralJournalEntry and GeneralJournalAccountEntry tables.

2) By "subledger" we consider transaction tables actual for various modules. Am I right? For example, CustTrans, VendTrans, RPayTrans, TaxTrans and so on.

3) Let's suppose we have a TaxTrans record as an incoming parameter. And we have to fetch GeneralJournalAccountEntry records associated with this TaxTrans record. Does the following select statement make sense:

while select generalJournalAccountEntry

join generalJournalEntry

where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

exists join subledgerVoucherGeneralJournalEntry

where generalJournalEntry.RecId ==subledgerVoucherGeneralJournalEntry.GeneralJournalEntry

&& subledgerVoucherGeneralJournalEntry.Voucher == taxTrans.Voucher

&& subledgerVoucherGeneralJournalEntry.AccountingDate == taxTrans.TransDate

{

//do smth

}

I have the same question (0)

All responses (9)

Answers (2)

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello Nick,

Why do you need to code something here?

Do the standard tax and ledger reports not include all data that you are looking for?

Would be great if you could elaborate.

Many thanks,

Ludwig

Was this reply helpful? Yes No
NickYurov 20 on at

Like (0)

Report
Copy link

Link copied!

Hello Ludwig,

We are working on new localization module for D365 FO. That's why I ask the questions mentioned above.

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,423 on at

Like (1)

Report
Copy link

Link copied!
Hi Nick,

1. Yes. These tables (GeneralJournalEntry and GeneralJournalAccountEntry) hold the records for general ledger transactions.

2. You can use tables like CustTrans and VendTrans for sub-ledger transactions.

3. For the TaxTrans table in particular, there is a table TaxTransGeneralJournalAccountEntry which can be used in the query -

while select generalJournalAccountEntry join generalJournalEntry where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId join taxTransGeneralJournalAccountEntry where taxTransGeneralJournalAccountEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId join taxTrans where taxTrans.RecId == taxTransGeneralJournalAccountEntry.TaxTrans { //do smth }

Was this reply helpful? Yes No
NickYurov 20 on at

Like (0)

Report
Copy link

Link copied!

Hello Gunjan,

1) Thank you, that's clear. I have read the appropriate information here: https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/mapping-the-ledgertrans-table-to-general-journal-tables-white-paper.

2) I am a little bit messed up by your answer. Can you explain what you mean by "sub-ledger" concept in D365FO?

I have some understanding of the sub-ledger concept in accounting. As far as I know, General Ledger is a register holding information about monetary movements between main accounts, and these movements occur according to business operations held by the company. While sub-ledger in accounting is a sub-set of detailed transactions, specific for a certain accounting purpose. According to this concept, totals of sub-ledger transactions roll up into General Ledger.

In D365FO my vision is as follows. The system consists of modules. Almost every module stands for a detailed accounting of come accounting operations. Almost every module contains its main transaction (or maybe transaction header / transaction line) table which is considered to be the sub-ledger. Records of this main transaction table for the module are transferred into GeneralJournalEntry / GeneralJournalAccountEntry according to transfer rules, determined inside the module.

Would you be so kind as to point out if I misunderstand something?

3) Thank you, I see. But if it hadn't been for TaxTransGeneralJournalAccountEntry table (specific for TaxTrans), could my approach be valid?

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,423 on at

Like (1)

Report
Copy link

Link copied!

Hi Nick,

2. I think your understanding of sub-ledger concept is correct. I was trying to confirm that CustTrans and VendTrans are examples of sub-ledger tables as you mentioned.

3. Yes, your approach could be valid had it not been for TaxTransGeneralJournalAccountEntry table in case of TaxTrans.

Was this reply helpful? Yes No
NickYurov 20 on at

Like (0)

Report
Copy link

Link copied!

Gunjan, thank you!

I appreciate your help.

Was this reply helpful? Yes No
NickYurov 20 on at

Like (0)

Report
Copy link

Link copied!

Hi, Gunjan,

Just one more thing concerning this topic.

Can you help me to understand the concept of "journalizing sub-ledger transactions"?

I have met this concept mentioned in various sources and whitepapers.

For example these: https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/what-s-new-ledger-and-subledger-lines-and-distributions#new-functionality, https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/about-accounting-distributions-and-subledger-journal-entries-for-free-text-invoices, https://www.erpsoftwareblog.com/2018/02/understanding-ledger-and-sub-ledger-processing-of-entries-in-ax/.

But I can hardly understand the purpose of this operation and technical background of it (for example, which tables / classes are involved).

Thank you!

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report
Copy link

Link copied!

Hi Nick,

For these sub-ledger transactions, the main accounts are setup using posting groups, posting profiles etc. For an example, you can go through this link to check how accounting distribution is done for vendor invoices. On posting these sub-ledger entries get transferred to GL as per settings done. You can check this post for some details regarding the same.

As for the technical aspect of it, there are multiple objects involved. You can take a look at objects present under SourceDocumentation model.

Was this reply helpful? Yes No
Nick_Yurov 60 on at

Like (0)

Report
Copy link

Link copied!

Thanks, Gunjan,

That's it!

Was this reply helpful? Yes No