Skip to main content
Unanswered

General Ledger all booked entries data

editSubscribe (0) ShareShare
ReportReport
Posted on by 19
I need all the entries booked in the General Ledger i am trying to retrive it through sql 
  1. JOURNALNUMBER
  2. SUBLEDGERVOUCHER
  3. LEDGERACCOUNT
  4. MAINACCOUNTID
  5. NAME
  6. ACCOUNTINGDATE
  7. REPORTINGCURRENCYAMOUNT
  8. SUBLEDGERVOUCHERDATAAREAID
  9. PostingType
  10. Description
  11. TRANSACTIONCURRENCYCODE
  12. TRANSACTIONCURRENCYAMOUNT
  13. VendorAccount
  14. VendorName
Query I am made  :
SELECT
GJE.JOURNALNUMBER as  JOURNALNUMBER,
GJE.SUBLEDGERVOUCHER as SUBLEDGERVOUCHER,
GJAE.LEDGERACCOUNT as LEDGERACCOUNT,
MA.MAINACCOUNTID as MAINACCOUNTID,
MA.NAME as Name,
GJE.ACCOUNTINGDATE as ACCOUNTINGDATE,
GJAE.REPORTINGCURRENCYAMOUNT as REPORTINGCURRENCYAMOUNT,
GJE.SUBLEDGERVOUCHERDATAAREAID as  SUBLEDGERVOUCHERDATAAREAID,
 GJAE.POSTINGTYPE as POSTINGTYPE, 
GJAE.TEXT as DESCRIPTION,
GJAE.TRANSACTIONCURRENCYCODE as TRANSACTIONCURRENCYCODE,
GJAE.TRANSACTIONCURRENCYAMOUNT as TRANSACTIONCURRENCYAMOUNT,
VTA.ACCOUNTNUM as VENDORACCOUNTNUMBER
--VDPT.NAME as VENDORNAME
FROM GENERALJOURNALENTRY GJE
                           join GeneralJournalACcountENtry GJAE on GJAE.GENERALJOURNALENTRY = GJE.RECID
                           join LEDGERENTRYJOURNAL LEJ on GJE.LEDGERENTRYJOURNAL=LEJ.RECID
                           join VENDTRANS VTR on GJE.SUBLEDGERVOUCHER = VTR.VOUCHER 
                           join VENDTABLE VTA on VTR.ACCOUNTNUM = VTA.ACCOUNTNUM
                           join DIRPARTYTABLE VDPT on VTA.Party = VDPT.RECID
                           join MAINACCOUNT MA on  GJAE.MAINACCOUNT=MA.RECID 
                        
 Where 
 
 GJE.ACCOUNTINGDATE >= '2023-01-01 00:00:00.000'  and GJE.ACCOUNTINGDATE <= '2023-01-31 00:00:00.000' and GJE.SUBLEDGERVOUCHERDATAAREAID='PLA' 
 group by GJAE.TRANSACTIONCURRENCYAMOUNT,
GJAE.ACCOUNTINGCURRENCYAMOUNT,
GJAE.REPORTINGCURRENCYAMOUNT,
GJAE.TRANSACTIONCURRENCYCODE,
GJAE.POSTINGTYPE, 
GJAE.TEXT,
GJE.ACCOUNTINGDATE,
GJE.JOURNALNUMBER,
GJAE.LEDGERACCOUNT,
GJE.SUBLEDGERVOUCHER,
MA.MAINACCOUNTID,
VTA.ACCOUNTNUM,
--VDPT.NAME ,
MA.NAME,
SUBLEDGERVOUCHERDATAAREAID 
order by GJE.ACCOUNTINGDATE

 
I want to get these record for auditor.
Please help if you know  to retrive all these data for certain period
ERP: Dynamics ax2012 R3