Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

(0) Share

Report

Posted on by Anjep

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

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

DavidGunawan 1,381 on at

Like (1)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

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,

Was this reply helpful? Yes No
Anjep on at

Like (0)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

Thanks I will test this out tonight and let you know if I manage

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (1)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

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

Was this reply helpful? Yes No
Anjep on at

Like (1)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

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

Was this reply helpful? Yes No
Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

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

Was this reply helpful? Yes No
Anjep on at

Like (0)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

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

Was this reply helpful? Yes No
Chaitanya Golla 17,225 on at

Like (0)

Report

RE: Finding link between GENERALJOURNALENTRY and LEDGERJOURNALTRANSACCRUAL

Hi,

Sure, can you provide me the form name you are referring to.

Thanks,

Chaitanya Golla

Was this reply helpful? Yes No