Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / LedgerJournalTrans mai...
Microsoft Dynamics AX (Archived)

LedgerJournalTrans main accounts when account type is not Ledger

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I have a requirement where I am given a main account and date range and I need to provide the general ledger entries, plus their associated ledger journal lines. 

It is easy enough to grab the correct general journal rows because GeneralJournalAccountEntry has main account (through LedgerDimension) and GeneralJournaEntry has the accounting date.  But I am having trouble getting the related rows in LedgerJournalTrans. 

If the LedgerJournalTrans row has Account Type or Offset Account Type value of 'Ledger' then the LedgerDimension or OffsetLedgerDimension can be used to get the main account.  That works fine. 

But what about this actual example?  The LedgerJournalTrans row has Account Type = 'Vendor', Account = '16000', Offset Account Type = 'Ledger', Offset Account = Null.  How could I know that that row has the required main account?  I checked vendor '16000': neither the Vendor detail form, the Invoice>Default Account, or the Invoice>Posting form shows any tie to the main account of interest.

Currently, I am provided only LedgerJournalTrans rows with AccountType = 'Ledger' and the main account, or OffsetAccountType = 'Ledger' and the main account.  But then I am excluding rows such as the one above, and the LedgerJournalTrans sum can be many thousands of dollars less than the GeneralJournalAccountEntry total for the main account. 

On the other hand, if I ignore AccountType, Account, OffsetAccountType, and OffsetAccount, and provide all LedgerJournalTrans rows, then I am including rows that are not for the main account of interest, and the LedgerJournalTrans sum can be many thousands of dollars more than the GeneralJournalAccountEntry total for the main account.

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: LedgerJournalTrans main accounts when account type is not Ledger

    I found the solution, at least for our company, and it was straightforward.

    I had checked the posting profile for Vendor 16000 (Vendor details form, Invoice > Posting) and also for that vendor's Vendor Group (Vendor group list page, Item Posting), and there was no tie to the main account of interest. 

    But there is in the Accounts Payable > Vendor Posting Profiles.  

    Our setup there is simple, so this might not work for other companies.  We have only 1 Posting Profile, and the Setup fast tab has only half a dozen or so Groups in Account Code, and those rows have only Summary Accounts.   So I can easily enough follow the Vendor on the LedgerJournalTrans line to the VendTable, from there to VendGroup, from there to VendLedgerAccounts, and there look to see if the vendor's vendor group has the main account of interest in the summary account.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: LedgerJournalTrans main accounts when account type is not Ledger

    Thanks, I saw the query at that link before posting: it unfortunately doesn't include the LedgerJournalTrans table or how to find the main account for a row when the AccountType is not Ledger.

    The query at that link goes from the general journal cluster of tables to the SubledgerJournalAccounEntry and SubledgerJournalEntry tables.  I already have that part of the overall requirement working so I didn't mention it in my post.

  • Bilal Issa Profile Picture
    Bilal Issa 4,370 on at
    RE: LedgerJournalTrans main accounts when account type is not Ledger

    Hi ,

    please check the link below, it should give you a start.

    sumitsaxfactor.wordpress.com/.../getting-ledger-transactions-in-ax-2012

    Regards,

    Bilal

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

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans