Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Detect posting layers in SQL table

(0) ShareShare
ReportReport
Posted on by 10

Hi,

For reporting purposes (outside AX) I need to find the right sql tables and fields on posting layers. The report must have a filter based on the reporting layer so the user can select what layer to report on.

Any suggestions?

Kind regards,

Johan

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Detect posting layers in SQL table

    Hello SCoPeS,

    Where and how do you use the different posting layers; in the fixed asset module? If you use it there be careful because the fixed asset module is usually not setup in a way to record delta postings, which can result in a double counting of the numbers you add.

    Best regards,

    Ludwig

  • SCoPeS Profile Picture
    SCoPeS 10 on at
    RE: Detect posting layers in SQL table

    Thanks. LedgerTrans does not hold that field. I found it in the related journalname table

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Detect posting layers in SQL table

    Hi SCoPeS,

    If you have an AX4 database, please check table LedgerTrans. If you are looking for Operations layer, you should query by value 2 only.

    Could you please elaborate on what your exact requirementis?

  • SCoPeS Profile Picture
    SCoPeS 10 on at
    RE: Detect posting layers in SQL table

    Thanks for your reply.

    I think it's an AX4 database, don't have access to the application itself.

    I did find CURRENTOPERATIONSTAX in the LEDGERJOURNALTABLE table and it seems to be the integer defining which posting layer it is.

    Would you happen to know if you need to accumulate the layers? That is, that if you want layer 2 (operations) you actually need layer 1+2?

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Detect posting layers in SQL table

    Hello Johan,

    Can you provide more information on your requirement because not all transactions can make use of posting layers.

    What kind of report do you need to create based on which data and where?

    Best regards,

    Ludwig

  • Suggested answer
    Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Detect posting layers in SQL table

    HI SCoPeS,

    You can use the tables - GeneralJournalEntry and GeneralJournalAccountEntry. The field "PostingLayer" is present in GeneralJournalEntry  table.

    SELECT * FROM GENERALJOURNALACCOUNTENTRY AS GJE
        JOIN GENERALJOURNALENTRY AS GJ ON GJ.RECID = GJE.GeneralJournalEntry

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans