Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / General Ledger all boo...
Finance | Project Operations, Human Resources, ...
Unanswered

General Ledger all booked entries data

(1) ShareShare
ReportReport
Posted on by Ayushaman 170
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
 
 
  • Ayushaman Profile Picture
    Ayushaman 170 on at
    General Ledger all booked entries data
     Hi Andre Arnaud!
     
    Thanks for your response. I want to have all the transactions and I verified and  the fields SubledgerVoucher and SubledgerVoucherDataAreaID some of them are blank too. I am being a bit greedy but can you Tell me the query for the correct joining from the SubledgerTrans table and to avoid data gap and for the field ledger link . As I earlier told you i want these data for auditors which company gives them for their verification and all. This is the Query I prepared and most of them matches but i think i m getting extra date (
    select GJE.SUBLEDGERVOUCHER,dc.DISPLAYVALUE as LEDGERACCOUNT,m.MAINACCOUNTID,m.NAME ,gje.ACCOUNTINGDATE as ACCOUNTINGDATE, GJAE.REPORTINGCURRENCYAMOUNT,
    com.NAME as SUBLEDGERVOUCHERDATAAREAID,
     GJAE.POSTINGTYPE, 
    CASE
        WHEN GJAE.POSTINGTYPE = 3 THEN 'Intercompany Accounting'
        WHEN GJAE.POSTINGTYPE = 4 THEN 'Tax'
        WHEN GJAE.POSTINGTYPE = 14 THEN      'Ledger Journal'
        WHEN GJAE.POSTINGTYPE = 20 THEN      'Bank'
        WHEN GJAE.POSTINGTYPE = 31 THEN      'Customer Balance'
        WHEN GJAE.POSTINGTYPE = 41 THEN   'Vendor Balance'
        WHEN GJAE.POSTINGTYPE = 45 THEN   'Vendor Cash Discount'
        WHEN GJAE.POSTINGTYPE = 48 THEN   'Vendor Settlement'
        WHEN GJAE.POSTINGTYPE = 49 THEN   'InterCompanySettlement'
        WHEN GJAE.POSTINGTYPE = 55 THEN   'Order,Freight'
        WHEN GJAE.POSTINGTYPE = 56 THEN   'Order Fee'
        WHEN GJAE.POSTINGTYPE = 71 THEN   'Purchase Expenditure For Product'
        WHEN GJAE.POSTINGTYPE = 72 THEN   'Purchase,Discount'
        WHEN GJAE.POSTINGTYPE = 83 THEN   'Purchase Expenditure,Un-inoviced'
        WHEN GJAE.POSTINGTYPE = 121 THEN  'Project-cost'
        WHEN GJAE.POSTINGTYPE = 123 THEN  'Project-WIPCost'
        WHEN GJAE.POSTINGTYPE = 201 THEN  'Purchase -charge'
        WHEN GJAE.POSTINGTYPE = 203 THEN  'Purchase-accural'   
    END AS POSTINGTYPE,
    GJAE.TEXT as Description,
    gjae.ACCOUNTINGCURRENCYAMOUNT
    ,GJAE.TRANSACTIONCURRENCYCODE, GJAE.TRANSACTIONCURRENCYAMOUNT,
    VT.ACCOUNTNUM as VendorAccount,
    DPT.NAME as VendorName
    from GENERALJOURNALENTRY gje
    inner join GENERALJOURNALACCOUNTENTRY gjae on gje.RECID = gjae.GENERALJOURNALENTRY
    inner join DIMENSIONATTRIBUTEVALUECOMBINATION dc on gjae.LEDGERDIMENSION = dc.RECID
    inner join MAINACCOUNT m on dc.MAINACCOUNT = m.RECID
    inner join ledger com on gje.LEDGER = com.RECID 
    left outer join VENDTRANS VTR on  GJE.DOCUMENTDATE = VTR.DOCUMENTDATE and GJE.SUBLEDGERVOUCHER = VTR.VOUCHER 
    left outer join VENDTABLE VT on VTR.ACCOUNTNUM = VT.ACCOUNTNUM
    Join DIRPARTYTABLE DPT on DPT.RECID = VT.PARTY
    left outer join DIMENSIONATTRIBUTELEVELVALUEVIEW dcv on dc.recid= dcv.VALUECOMBINATIONRECID --and DIMENSIONATTRIBUTE = '5637146076'
    where GJE.SUBLEDGERVOUCHERDATAAREAID='PLA'and (gje.ACCOUNTINGDATE >= '2023-01-01 00:00:00.000'  and gje.ACCOUNTINGDATE <= '2023-10-31 00:00:00.000')

    order by GJE.ACCOUNTINGDATE
    )
     
    Thanks!
     
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,642 Super User 2024 Season 2 on at
    General Ledger all booked entries data
    Are you interested in only transactions linked to a vendor transaction or do you really want to have all transactions? You are now having a join on the VendTrans table. If you look at all the Accounting entries, then the fields SubledgerVoucher and SubledgerVoucherDataAreaID are not always filled. As you also have a join, you might miss records where these fields are not filled. In case these fields are not filled, you can find voucher details in the subledger transaction tables.
    Also, not all accounting entries are posted via journals. The field Ledger has always the correct link to a legal entity.
     
    As I don't know the exact requirement and how transactions are created and posted, I can only suggest you to verify starting from missing accounting entries how particular data is stored. You might need to use outer joins and/or get details from other tables as well.  
  • Ayushaman Profile Picture
    Ayushaman 170 on at
    General Ledger all booked entries data
    Hi Andre Arnaud
    The data i am getting has most of the data but records are missing too. secondly when trying to retrieve the data of some time periods the query return 0 rows even though data is available on GENERALJOURNALENTRY ,GeneralJournalAccountEntry . I just want to verify that the joins are right because I want these for auditors. I also tried to retrieve from (GJE.JOURNALNUMBER,
    LT.voucher as SUBLEDGERVOUCHER,
    DAVC.DisplayValue as LEDGERACCOUNT, 
    DAVC.MainAccount as MAINACCOUNTID, 
    LT.LEDGERDIMENSION as NAME, 
    GJE.ACCOUNTINGDATE as ACCOUNTINGDATE, 
    GJAE.TRANSACTIONCURRENCYAMOUNT as TRANSACTIONCURRENCYAMOUNT,
    GJAE.REPORTINGCURRENCYAMOUNT as REPORTINGCURRENCYAMOUNT,
    GJAE.TRANSACTIONCURRENCYCODE as TRANSACTIONCURRENCYCODE,
    LT.COMPANY as SUBLEDGERVOUCHERDATAAREAID,
    GJAE.POSTINGTYPE as POSTINGTYPE, 
    LJT.NAME as Description
    --GJAE.TEXT as DESCRIPTION
    --VT.ACCOUNTNUM as VendorAccount,
    --DPT.NAME as VendorName
     from LEDGERJOURNALTRANS LT
    join LEDGERJOURNALTABLE LJT on LJT.JOURNALNUM = LT.JOURNALNUM 
    join LEDGERENTRYJOURNAL LEJ on LEJ.JOURNALNUMBER=LJT.JOURNALNUM
    Join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC on DAVC.RECID = LT.LEDGERDIMENSION
    --join VENDTABLE VT on VT.OFFSETLEDGERDIMENSION = DAVC.RECID
    --Join DIRPARTYTABLE DPT on DPT.RECID = VT.PARTY
    Join GENERALJOURNALENTRY GJE on GJE.LEDGERENTRYJOURNAL=LEJ.RECID
    join GeneralJournalACcountENtry GJAE on GJAE.GENERALJOURNALENTRY = GJE.RECID
    join VENDTRANS VTR on GJE.SUBLEDGERVOUCHER = VTR.VOUCHER 
    join VENDTABLE VT on VTR.ACCOUNTNUM = VT.ACCOUNTNUM
                              -- join DIRPARTYTABLE VDPT on VTA.PARTY = VDPT.RECID
    where LT.COMPANY='PLA' and 
     ACCOUNTINGDATE >= '2022-01-01 00:00:00.000'  and ACCOUNTINGDATE <= '2022-06-28 00:00:00.000') this query but it takes lots of time and mostly return time fail error to retreive the data.  
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,642 Super User 2024 Season 2 on at
    General Ledger all booked entries data
    Hi Singh,
     
    What is the current outcome of the query and is there anything wrong or missing? 

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

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans