Finance | Project Operations, Human Resources, ...

Detect posting layers in SQL table

(0) Share

Report

Posted on by SCoPeS

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

I have the same question (0)

All responses (6)

Answers (0)

Sort by

Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
SCoPeS 10 on at

Like (0)

Report
Copy link

Link copied!

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?

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

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
SCoPeS 10 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No