SQL query to get GL transaction in AX2012R2/3 with main account and department

Finance and Operations/AX User Forum
1

Hi,

I would like to share this SQL query script, this use to get the GL transacitons with main account and department,

select com.NAME, gje.ACCOUNTINGDATE,m.MAINACCOUNTID, gjae.ACCOUNTINGCURRENCYAMOUNT, dc.DISPLAYVALUE, isnull(dcv.DISPLAYVALUE,’’) as ‘Departmnet’

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 --ledger table is companyid

left outer join DIMENSIONATTRIBUTELEVELVALUEVIEW dcv on dc.recid= dcv.VALUECOMBINATIONRECID and DIMENSIONATTRIBUTE = ‘5637146076’

–get dimensionattribute value from dimensionattribute able for department

Koong