web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Query for Journal ...
Microsoft Dynamics AX (Archived)

SQL Query for Journal Entries

(0) ShareShare
ReportReport
Posted on by Community Member

I am looking to find the person who created journals, and this is found on the LedgerJournalTable.

From GeneralJournalAccountEntry, I can get myself to the LedgerEntryJournal table. Supposedly I should be able to go from LedgerEntryJournal to LedgerJournalTable easily via the following relation:

LedgerEntryJournal-Relations.png

However, this relationship fails for me because LedgerJournalTableDataAreaId is largely blank. Any advice on what to do, or advice on if something appears to be broken and how it may be fixed?

Missing-Ledger-Data-Area-ID.png

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: SQL Query for Journal Entries

    Hi Mark S,

    Look at LedgerTransPerJournal AOT query, it has all tables and releations you need.

  • Community Member Profile Picture
    Community Member on at
    RE: SQL Query for Journal Entries

    Hi Sukrut Parab,

    community.dynamics.com/.../241085 --- the verified answer by David Gunawan outlines the exact relationship that I show does not work for me due to missing LEDGERJOURNALTABLEDATAAREAID in my second screenshot. All of the other answers do not perform a join between LedgerJournalTable and LedgerEntryJournal, unless I am missing something?

    community.dynamics.com/.../145749 --- same thing, there is no join performed for LedgerJournalTable and LedgerEntryJournal.

  • Community Member Profile Picture
    Community Member on at
    RE: SQL Query for Journal Entries

    Hi ievgen Miroshnikov,

    The query has the joins I expect and have performed, however, that simply doesn't work because of my blank LEDGERJOURNALTABLEDATAAREAID.

    My query is simple:

    SELECT TOP 10 *

    FROM [dbo].[GENERALJOURNALACCOUNTENTRY] gjae

    LEFT JOIN [dbo].[GENERALJOURNALENTRY] gje ON gjae.GENERALJOURNALENTRY = gje.RECID

    LEFT JOIN [dbo].[LEDGERENTRYJOURNAL] lej ON lej.recid = gje.LEDGERENTRYJOURNAL

    LEFT JOIN [dbo].[LEDGERJOURNALTABLE] ljt ON ljt.JOURNALNUM = lej.JOURNALNUMBER

    AND ljt.DATAAREAID = lej.LEDGERJOURNALTABLEDATAAREAID

    The problem is that final join to LedgerJournalTable. Matching on LEDGERJOURNALTABLEDATAAREAID does not work because it is blank. See my screenshots in my original post. Joining on just JOURNALNUMBER explodes the results inaccurately because of the same values in different legal entities.

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: SQL Query for Journal Entries

    LedgerTransPerJournal does not have dataareaid relation. You SQL query is missing "where ledgerjournaltable.dataareaid = 'yourCompany'" range, or do you want to do crosscopmany select ?

  • Community Member Profile Picture
    Community Member on at
    RE: SQL Query for Journal Entries

    I need the join to work for all company's. Adding the where clause as you suggest doesn't work either. Using my screenshots, I have 6 LedgerJournalTable records for JOURNALNUM = 20 --- one for each dataarea. Similarly, I have 6 LedgerEntryJournal records for JOURNALNUMBER = 20. Joining just on LedgerJouralTable.JOURNALNUM = LedgerEntryJournal.JOURNALNUMBER explodes the results to 6 x 6 = 36 records, with each dataarea being replicated 6 times. If I were to apply LedgerJournalTable.DATAAREAID = 'myCompany', I will still get 6 x 1 = 6 duplicated rows returned.

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: SQL Query for Journal Entries

    Another range you are missing is a range over GeneralJournalEntry.Ledger field, it should be equal to RecId from ledger where ledger.PrimaryForLegalEntity == companyInfo.RecId

  • Community Member Profile Picture
    Community Member on at
    RE: SQL Query for Journal Entries

    Hi ievgen,

    Thank you for pointing this out! I am still curious as to why my LedgerEntryJournal.LEDGERJOURNALTABLEDATAAREAID is blank and unusable for joining, in spite of the relationship it is supposed to have to LedgerJournalTable.

    Your recommendation for this join allows me to circumvent that dataarea lookup with a different one based on Ledger -> DimAttributeCompanyInfo. Updated query below, and my data replication issue looks to be solved!

    SELECT TOP 100 *

    FROM [dbo].[GENERALJOURNALACCOUNTENTRY] gjae

    LEFT JOIN [dbo].[GENERALJOURNALENTRY] gje ON gjae.GENERALJOURNALENTRY = gje.RECID

    LEFT JOIN [dbo].[LEDGER] l ON gje.LEDGER = l.RECID

    LEFT JOIN [dbo].[DIMATTRIBUTECOMPANYINFO] company ON l.PRIMARYFORLEGALENTITY = company.RECID

    LEFT JOIN [dbo].[LEDGERENTRYJOURNAL] lej ON lej.recid = gje.LEDGERENTRYJOURNAL

    LEFT JOIN [dbo].[LEDGERJOURNALTABLE] ljt ON ljt.JOURNALNUM = lej.JOURNALNUMBER

    AND ljt.DATAAREAID = l.NAME

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Community Member Profile Picture

Community Member 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans