web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Using T-SQL, link Gene...
Microsoft Dynamics AX (Archived)

Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

Any help will be greatly appreciated.

I am in the need of linking information presented on the Journal entries of a certain GL account (table "GENERALJOURNALACCOUNTENTRY") to the Vendor "attached" to the Purchase Invoice Journal (PurchInvoicejour). Not only that, I also need to breakdown the "LedgerDimension" on the ledger table, so I can surface the worker and the department attached to that ledger transaction.

There is no obvious link between the two indicated tables, so I need expert help, and also I don't know how to breakdown the LedgerDimension value (which table I have to connect to).

Cheers.

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    The following will get the segments (main account and financial dimensions) for all ledger dimensions, storing the results in a temp table that you can then join to.  You would just need to change the financial dimension names from those our company uses (region, location, discipline, project, customer, and marketsector) to those that your company uses.

    IF OBJECT_ID('tempdb..#LedgerDimensionSegments') IS NULL
    BEGIN
    SELECT *
    INTO #LedgerDimensionSegments
    FROM (
    SELECT
    LedgerDimension = DAVC.RecId
    , DAVC_DisplayValue = DAVC.DisplayValue
    , DAVC_LedgerDimensionTypeText = Enums_LDT.EnumItemLabel
    , DALV_DisplayValue = DALV.DisplayValue
    , DA.Name
    FROM DimensionAttributeValueCombination DAVC
    INNER JOIN DimensionAttributeValueGroupCombination DAVGC ON DAVGC.DimensionAttributeValueCombination = DAVC.RecId
    INNER JOIN DimensionAttributeValueGroup DAVG ON DAVG.RecId = DAVGC.DimensionAttributeValueGroup
    LEFT OUTER JOIN DimensionAttributeLevelValue DALV ON DALV.DimensionAttributeValueGroup = DAVG.RecId
    LEFT OUTER JOIN DimensionAttributeValue DAV ON DAV.RecId = DALV.DimensionAttributeValue
    LEFT OUTER JOIN DimensionAttribute DA ON DA.RecId = DAV.DimensionAttribute
    LEFT OUTER JOIN SRSAnalysisEnums Enums_LDT ON Enums_LDT.EnumName = 'LedgerDimensionType' AND Enums_LDT.EnumItemValue = DAVC.LedgerDimensionType AND Enums_LDT.LanguageId = 'en-us'
    WHERE
    DAV.IsDeleted = 0
    -- Using just the above criterion returns more results per LedgerDimension (DAVC.RecId) than desired.
    -- For the few examples I checked, setting DAVGC.Oridinal = 1 gave me only the desired results.
    -- I did find AX using the above joins along with DAVGC.Ordinal == 1 in several places:
    -- \Classes\VendOutPaymPrenote_NACHA\validate
    -- \Classes\LedgerYearAccountDiskBEL\writeData
    -- \Classes\DimensionValidation\validateByJournalName
    -- \Classes\DimensionValidation\syncStructureChangesPrivate (probably)
    -- \Classes\DimensionValidation\isStructureChanged
    -- \Classes\DimensionValidation\getRuleHierarchies
    -- \Data Dictionary\Tables\TrvExchSetup\Methods\accountName
    AND
    DAVGC.Ordinal = 1
    ) AS Unpivoted
    PIVOT
    (
    MAX(DALV_DisplayValue)
    FOR [Name] IN ([MainAccount], [Region], [Location], [Discipline], [Project], [Customer], [MarketSector])
    ) AS Pivoted
    CREATE UNIQUE INDEX [I_LedgerDimensionSegments] ON #LedgerDimensionSegments ([LedgerDimension]) INCLUDE ([MainAccount], [Region], [Location], [Discipline], [Project], [Customer], [MarketSector]) WITH (FILLFACTOR = 100, PAD_INDEX = ON)
    END

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Hi Adfc,

    This is not so easy. So you can look at LedgerTransModule class proj() method. This class accepts generaljournalentry (you an easily get it from GENERALJOURNALACCOUNTENTRY) and finds all transaction associated with it.  Having proj*trans you can find invoice line.

  • Community Member Profile Picture
    Community Member on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Hi ievgen,

    Your reply covers part of my initial question ("...to breakdown the "LedgerDimension" on the ledger table..."), for which I thank you.

    But my main issue still is "linking information presented on the Journal entries of a certain GL account (table "GENERALJOURNALACCOUNTENTRY") to the Vendor "attached" to the Purchase Invoice Journal (PurchInvoicejour)".

    Can you help me with it?

    Cheers.

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Hi Adfc,

    You can do the same on T-SQL level, you just need to translate AX select statement to T-SQL statement, there is no businesses logic involved so it should be straightforward.   Do you have any particular issues?

  • Community Member Profile Picture
    Community Member on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Anyone has any idea on how to do this on a T-SQL level?

  • Community Member Profile Picture
    Community Member on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Thank you Ievgen, I will have a try on it to get the Dimension values.

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Using T-SQL, link General Ledger transaction to Purchase Invoice Journal for Finance team's reporting purposes

    Hi Adfc,

    To get all individual values from LedgerDimension you can use DimensionAttributeLevelValueView view, please refer to this blog post for details sumitsaxfactor.wordpress.com/.../getting-individual-dimension-combination-valuesusing-views-ax-2012

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Guy Terry Profile Picture

Guy Terry 2 Moderator

#2
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans