Hi,
I looking for at way using SQL to make a statement where I get some dimension informations on my finacial transactions together with the Accrual identification and start date.
I have the script below I'm using to find the transactions with the dimensions but I can't find the path to the LEDGERJOURNALTRANSACCRUAL table.
Would anyone know if there is a path and how it looks?
Br,
Anders
*This post is locked for comments
Hi,
Sure, can you provide me the form name you are referring to.
Thanks,
Chaitanya Golla
Hi Chaitanya,
Sorry for the confusion it might be me mis-understanding how tings is linked i AX as it seem quite complex :-)
The statement I already have is giving me the finanvials postings and the related dimensions I'm using in a export and then I was hoping I "just" could extend that query as I want to include the "Accrual identification" / "Start date" / "Vendor name" for the related invoice.
Does it makes sense or am I off track?
Br,
Anders
Hi,
I am bit confused with your sql query, as per your (earlier)post you want to fetch data from GeneralJournalEntry and LedgerJournalTransAccrual but instead your query is trying to pull main account financial dimension information for the ledger transactions. So, could you let us know what exactly are you looking for.
Thanks,
Chaitanya
Hi Both,
I might be offtrack on this one but I can't figure out how to convert the statements and merge them into my existing statement.
Is there any easy way of converting them?
I have included a sample below so you understand where my starting point are:
SELECT
a.RECID,
b.ACCOUNTINGDATE,
d.MAINACCOUNTID,
a.TEXT,
a.TRANSACTIONCURRENCYAMOUNT,
c.DISPLAYVALUE AS DIMENSION,
b.CREATEDDATETIME,
d.NAME AS MAINACCOUNTIDNAME
FROM [dbo].GENERALJOURNALACCOUNTENTRY as a WITH(NOLOCK) ,
[dbo].GENERALJOURNALENTRY as b WITH(NOLOCK) ,
[dbo].DIMENSIONATTRIBUTEVALUECOMBINATION as c WITH(NOLOCK) ,
.[dbo].MAINACCOUNT as d WITH(NOLOCK)
WHERE
a.GENERALJOURNALENTRY = b.RECID
AND a.LEDGERDIMENSION = c.RECID
AND c.MAINACCOUNT = d.RECID
AND d.MAINACCOUNTID BETWEEN 2300 AND 2399
ORDER BY a.RECID ASC
Hi,
Please find the code below that provides the link between GeneralJournalAccountEntry and LedgerJournalTransAccrual. Its a job and written in Ax(need to be translated into SQL)
static void Link(Args _args)
{
GeneralJournalEntry generalJournalEntry;
GeneralJournalAccountEntry journalAccountEntry;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalTransAccrual ledgerJournalTransAccrual;
SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry;
DataAreaId company;
company = curext();
select journalAccountEntry // Can include postingtype to further filter the data
join generalJournalEntry
where ((generalJournalEntry.RecId == journalAccountEntry.GeneralJournalEntry)
&& (generalJournalEntry.SubledgerVoucherDataAreaId == company)) // current company
join Voucher, AccountingDate from subledgerVoucherGeneralJournalEntry
where (subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId)
join ledgerJournalTrans
where ledgerJournalTrans.Voucher == subledgerVoucherGeneralJournalEntry.Voucher
join ledgerJournalTable
where ledgerJournalTable.JournalNum == ledgerJournalTrans.JournalNum
join ledgerJournalTransAccrual
where ledgerJournalTransAccrual.RefTableid == ledgerJournalTrans.TableId
&& ledgerJournalTransAccrual.RefRecid == ledgerJournalTrans.RecId;
}
If its finds helpful, please mark it verified such that it is helpful to others and if anything is wrong/missing let me know.
Thanks,
Chaitanya Golla
Hi Anjep,
The relations are:
- LedgerJournalTransAccrual.RefTableId = LedgerJournalTrans.TableId && LedgerJournalTransAccrual.RefRecId = LedgerJournalTrans.RecId
- LedgerJournalTrans.JournalNum = LedgerJournalTable.JournalNum
- LedgerEntryJournal.LedgerJournalTableDataAreaId = LedgerJournalTrans.DataAreaId &&LedgerEntryJournal.JournalNumber = LedgerJournalTrans.JournalNum
- GeneralJournalEntry.LedgerEntryJournal = LedgerEntryJournal.RecId
You can find the table relations, by go to AOT>DataDictionary>Table>(Find the table name)> Relations.
Please look into the link below for the ERD diagram.
community.dynamics.com/.../entity-relationship-diagrams-of-dynamics-ax-2012-tables
Best regards,
#1
André Arnaud de Cal...
292,961
Super User 2025 Season 1
#2
Martin Dráb
231,801
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
