SQL query for Journal Entries.

(0) Share

Report

Posted on by Community Member

I would like to know if somebody can help me with the tables that I have to use in order to do a query showing who did the JE and who approve it and when?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

You can use the following query to get the data like createdBy, approvedby, createddatetime on the JE. Please let me know if anything is missing.

select ledgerJournalTable.APPROVER, generalJournalEntry.SUBLEDGERVOUCHER, journalAccountEntry.POSTINGTYPE, ledgerJournalTable.CREATEDBY, ledgerJournalTable.CREATEDDATETIME from GeneralJournalAccountEntry as journalAccountEntry inner join generalJournalEntry on ((generalJournalEntry.RecId = journalAccountEntry.GeneralJournalEntry) and (generalJournalEntry.SubledgerVoucherDataAreaId = 'XYZ')) -- specify companyId inner join subledgerVoucherGeneralJournalEntry on (subledgerVoucherGeneralJournalEntry.GeneralJournalEntry = generalJournalEntry.RecId) inner join ledgerJournalTrans on ledgerJournalTrans.Voucher = subledgerVoucherGeneralJournalEntry.Voucher inner join ledgerJournalTable on ledgerJournalTable.JournalNum = ledgerJournalTrans.JournalNum

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Chaitanya

But when I run that query I got a 0 on the Approver field.

I need a query to run a report similar to the Ledger transaction Report-Audit.

But looks like the approver is not in the ledgerJournalTable .

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Sukrut

But when I run that query I got a 0 on the Approver field.

I need a query to run a report similar to the Ledger transaction Report-Audit.

But looks like the approver is not in the ledgerJournalTable .

Thanks

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

Please use the below query.

select dirPartyTable.NAME as [Approver], generalJournalEntry.SUBLEDGERVOUCHER, journalAccountEntry.POSTINGTYPE, ledgerJournalTable.CREATEDBY, ledgerJournalTable.CREATEDDATETIME from GeneralJournalAccountEntry as journalAccountEntry inner join generalJournalEntry on ((generalJournalEntry.RecId = journalAccountEntry.GeneralJournalEntry) and (generalJournalEntry.SubledgerVoucherDataAreaId = '3B10')) -- specify companyId inner join subledgerVoucherGeneralJournalEntry on (subledgerVoucherGeneralJournalEntry.GeneralJournalEntry = generalJournalEntry.RecId) inner join ledgerJournalTrans on ledgerJournalTrans.Voucher = subledgerVoucherGeneralJournalEntry.Voucher inner join ledgerJournalTable on ledgerJournalTable.JournalNum = ledgerJournalTrans.JournalNum inner join hcmworker on ledgerJournalTable.Approver = hcmWorker.RecId inner join dirPartyTable on hcmWorker.Person = dirPartyTable.RecId

Was this reply helpful? Yes No
Suggested answer

Amir Nazim 5,994 on at

Like (0)

Report

details example and good explanation is here

instructorbrandon.com/sql-for-general-ledger-data-by-dimension-for-building-non-ax-reports-with-ax-data-part-3-final

Was this reply helpful? Yes No