Finance | Project Operations, Human Resources, ...

Posted General Ledger Transaction Information

(2) Share

Report

Posted on by texasbbqribs

Greetings!

I'm assisting with the creation of reports in Power BI for a D365 F&O client.

Basically, I need this information for posted transactions:

GL Account Number

GL Account Description

Posting Date (LedgerJournalTable.PostedDateTime)

Debit Amount (LedgerJournalTrans.amountcurdebit)

Credit Amount (LedgerJournalTrans.amountcurcredit)

Journal Name (LedgerJournalTable.journalname)

Transaction Description (LedgerJournalTable.name)

Journal Entry Number (LedgerJournalTable.journalnum)

I believe I need, at least, these tables:

LedgerJournalName -- Contains the list of journal names, like JE, AP, etc. and their descriptions.

LedgerJournalTable -- Contains the header information, like journalname, journalnum, name, posteddatetime for a transaction.

LedgerJournalTrans -- Contains the line item transactions. Information found is amountcurdebit, amountcurcredit.

What I cannot find is the source of the account number and description. There is a column in LedgerJournalTrans called LedgerDimension. What table would have the account number used for a transaction and the description?

If there are different tables to use, please let me know.

I'm only looking for posted transactions.

Here's an example:

GL Account Number	GL Account Description	Posting Date	Debit Amount	Credit Amount	Journal Name	Transaction Description	Journal Entry Number
2100-20-2	Trade Accounts Payable	12/16/2024		100	JE	Month-end accrual	LBJ48482
5200-40-3	Misc Expenses	12/16/2024	100		JE	Month-end accrual	LBJ48482

Thank you

Categories:

Finance

All responses (2)

Answers (0)

Suggested answer

Alireza Eshaghzadeh 12,354 Super User 2024 Season 2 on at

Like (1)

Report

Posted General Ledger Transaction Information

Hi,

For LedgerDimensions, it represents AccountType values (e.g., Ledger, Cust, Vend, Bank, FixedAssets) and has a relation to DimensionAttributeValueCombination to determine the exact value:
LedgerJournalTrans.LedgerDimension == DimensionAttributeValueCombination.RecId

To get a better overview of voucher transactions, you can refer to GeneralJournalEntry and GeneralJournalAccountEntry:
GeneralJournalAccountEntry.GeneralJournalEntry == GeneralJournalEntry.RecId
texasbbqribs 53 on at

Like (0)

Report
Posted General Ledger Transaction Information

In searches, I've seen conflicting information about the tables and, according to a Microsoft Copilot response about posted activity, the tables should be:

GeneralJournalEntry: This table stores information about each journal entry, including the posting date, transaction number, and main account.

GeneralJournalAccountEntry: This table contains details about the accounts affected by each journal entry, such as the ledger account, description, and amounts in transaction currency.

LedgerAccount: This table stores information about the ledger accounts, including dimensions and account details.

CustTrans and VendTrans: These tables store customer and vendor transactions, respectively, and are linked to the general journal entries.