Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Get VendInvoiceTrans rows with an specific dimension

Posted on by Microsoft Employee

Hi everyone

To resume the post, the user give me a PROJ ID and I have to get all the invoices of this project, with this cases in mind,

A) An invoice could be done directly from the project, without a purchase order.

B ) A purchase order could be invoiced to a project without purch request.

C) And a purchse order with puch request could be invoiced to a project.

And for that I'm trying to get the vendor invoice lines (VendInvoiceTrans) which are registered in a specific LedgerAccount with a specific Project Dimesion.

I tried different ways, but all have some issues.

First one I tried to get the invoices from the dimensionAttributeValueCombination, this works, but I can't get only the invoice lines I wanted. The code I tried:

    select InvoiceAmount, InvoiceAccount, CurrencyCode, InvoiceId, InvoiceDate
    from vendInvoiceJour
        where   !vendInvoiceJour.PurchId
        &&      vendInvoiceJour.InvoiceDate                     >= fromDate
        &&      vendInvoiceJour.InvoiceDate                     <= toDate
    join generalJournalEntry
        where   generalJournalEntry.SubledgerVoucher            == vendInvoiceJour.LedgerVoucher
        &&      generalJournalEntry.SubledgerVoucherDataAreaId  == vendInvoiceJour.dataAreaId
    join generalJournalAccountEntry
        where   generalJournalAccountEntry.GeneralJournalEntry  == GeneralJournalEntry.RecId
    join dimensionAttributeValueCombination
        where   dimensionAttributeValueCombination.RecId        == generalJournalAccountEntry.LedgerDimension
        &&      dimensionAttributeValueCombination.DisplayValue like strFmt("*%1*", projId)
    join vendTable
        where vendTable.AccountNum                          == vendInvoiceJour.InvoiceAccount
    join Name
    from dirPartyTable
        where dirPartyTable.RecId                           == vendTable.Party;


The secondone was try to relate VendInvoiceTrans with DimensionAttributeValueCombination directly, but the issue I found was that my company didn't fill in the "ledgerDimension" in vendInvoiceTrans so the following code should worked properly if the field were filled...

    select LineAmount, CurrencyCode, InvoiceId, InvoiceDate
    from vendInvoiceTrans
        where   !vendInvoiceTrans.PurchId
        &&      vendInvoiceTrans.InvoiceDate                >= fromDate
        &&      vendInvoiceTrans.InvoiceDate                <= toDate
    join dimensionAttributeValueCombination
        where   dimensionAttributeValueCombination.RecId    == vendInvoiceTrans.LedgerDimension
        &&      dimensionAttributeValueCombination.DisplayValue like strFmt("*%1*", projId)
    join InvoiceAccount
    from vendInvoiceJour
        where   vendInvoiceJour.PurchId                     == vendInvoiceTrans.PurchID
        &&      vendInvoiceJour.InvoiceId                   == vendInvoiceTrans.InvoiceId
        &&      vendInvoiceJour.InvoiceDate                 == vendInvoiceTrans.InvoiceDate
        &&      vendInvoiceJour.numberSequenceGroup         == vendInvoiceTrans.numberSequenceGroup
        &&      vendInvoiceJour.InternalInvoiceId           == vendInvoiceTrans.InternalInvoiceId
    join vendTable
        where   vendTable.AccountNum                        == vendInvoiceJour.InvoiceAccount
    join Name
    from dirPartyTable
        where   dirPartyTable.RecId                         == vendTable.Party;

I know that the use of "like" isn't a best practice and it isn't the best for perfomance, but relating DimensoinAttributeValueSetItem with DimesionAttributeValueCombination across all the intermediate tables I got so much rows than I didn't want.

Thanks so much,

Cheers.

  • jasman Profile Picture
    jasman 1,411 on at
    RE: Get VendInvoiceTrans rows with an specific dimension

    I was *much* simpler in previous AX versions, but also not as flexible for the users. :)

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Get VendInvoiceTrans rows with an specific dimension

    After some research I understood the behavior of the tables involved in AX 2012 dimension combination when you have a LedgerDimension, the behavior when you have a DefaultDimension is a bit diferent.

    So basing in Jacob Hjørnet Sørensen code and some own research, I think I could explain how I solved the issue of getting all the combinations of a dimension, account, department, project...

    -----------------------------------------------------------------------------------------------

    First, the tables involved:

    DimensionAttributeValueSetItem

    DimensionAttributeValue

    DimensionAttributeLevelValue

    DimensionAttributeValueGroup

    DimensionAttributeValueGroupCombination

    DimensionAttributeValueCombination

    -----------------------------------------------------------------------------------------------

    The relations between these tables are the following:

    DimensionAttributeValueSetItem.DimesionAttributeValue = DimensionAttributeValue.RecId

    DimensionAttributeValue.RecId = DimensionAttributeLevelValue.DimensionAttributeValue

    DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId

    DimensionAttributeValueGroup.RecId = DimensionAttributeValueGroupCombination.DImensionAttributeValueGroup

    DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId

    -----------------------------------------------------------------------------------------------

    Because of the above you can exclude some tables and remake the relations getting this:

    DimensionAttributeValueSetItem.DimesionAttributeValue = DimensionAttributeLevelValue.DimensionAttributeValue

    DimensionAttributeLevelValue.DimensionAttributeValueGroup  = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup

    DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId

    -----------------------------------------------------------------------------------------------

    Also the LedgerDimension field in a table is a RecId so you can change "DimensionAttributeValueCombination.RecId" with "yourTableWithLedgerDimension.LedgerDimension"

    For example if you use generalJournalAccountEntry, the last relation would be like

    DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = generalJournalAccountEntry.LedgerDimension

    -----------------------------------------------------------------------------------------------

    After understood this, we have only 2 steps left:

    -----------------------------------------------------------------------------------------------
    - Know where we have to filter: this is probably the "hardest" of the 2 steps, because you have 2 ways to do this. You have to filter using the DisplayValue field wich is in DimensionAttributeValueSetItem and DimensionAttributeLevelValue after few tests I discover that if you use DimensionAttributeValueSetItem you get one record less than using DimensionAttributeValueCombination, and the combination that you won't get is the combination that has only the dimension you are looking for.

     (bellow the post is the SQL code that prove that, if someone can test in another environment to confirm that is a general curiosity and it doesn't happen only in my company because of the dimesion setup)

    - Now, after discard the DimensionAttributeValueSetItem table, you have only to write your X++ code, below code is a way to get all the invoices from projects that are directly invoiced without a purchase order or purch requisition.

    -----------------------------------------------------------------------------------------------

        insert_recordset XXX_MyTMPTable( VendAccount, CurrencyCode, InvoiceId, InvoiceDate, TotalPrice, VendName)

        select InvoiceAccount, CurrencyCode, InvoiceId, InvoiceDate
        from vendInvoiceJour
            where   !vendInvoiceJour.PurchId
            &&      vendInvoiceJour.InvoiceDate                                                 >= fromDate
            &&      vendInvoiceJour.InvoiceDate                                                 <= toDate
        join generalJournalEntry
            where   generalJournalEntry.SubledgerVoucher                                 == vendInvoiceJour.LedgerVoucher
            &&      generalJournalEntry.SubledgerVoucherDataAreaId                == vendInvoiceJour.dataAreaId
        join AccountingCurrencyAmount
        from generalJournalAccountEntry
            where   generalJournalAccountEntry.GeneralJournalEntry                  == GeneralJournalEntry.RecId
        join DimensionAttributeValueGroupCombination    
            where DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination    == generalJournalAccountEntry.LedgerDimension
        join dimensionAttributeLevelValue
            where dimensionAttributeLevelValue.DisplayValue                                     == _DimensionDisplayValue
            &&    DimensionAttributeLevelValue.DimensionAttributeValueGroup        == DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
        join vendTable
            where vendTable.AccountNum                                                   == vendInvoiceJour.InvoiceAccount
        join Name
        from dirPartyTable
            where dirPartyTable.RecId                                                           == vendTable.Party;

    -----------------------------------------------------------------------------------------------

    And in the end the SQL code for prove how DimensionAttributeValueSetItem and DimensionAttributeLevelValue show you different records. You can copy and paste it directly in your sql server to test it.
    -----------------------------------------------------------------------------------------------

    --Use DimensionAttributeValueSetItem:

    -----------------------------------------------------------------------------------------------

    select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from
     DIMENSIONATTRIBUTEVALUESETITEM
     inner join DIMENSIONATTRIBUTELEVELVALUE on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE= DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUE
     inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION on  DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
     inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID =  DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION

    where DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE = 'YourDimensionDisplayValue'
    group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE

    -----------------------------------------------------------------------------------------------

    --Use DimensionAttributeLevelValue

    -----------------------------------------------------------------------------------------------

    select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUE
    inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION    
    on  DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
    inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID =  DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION

    where DIMENSIONATTRIBUTELEVELVALUE.DISPLAYVALUE = 'YourDimensionDisplayValue'
    group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE

    -----------------------------------------------------------------------------------------------

    --Show the differences between 2 above queries

    -----------------------------------------------------------------------------------------------

    select DIMENSIONATTRIBUTEVALUECOMBINATION.recid, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUE
    inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION    
    on  DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
    inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID =  DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
    where DIMENSIONATTRIBUTELEVELVALUE.DISPLAYVALUE = 'YourDimensionDisplayValue'
    and DIMENSIONATTRIBUTEVALUECOMBINATION.recid not in(select DIMENSIONATTRIBUTEVALUECOMBINATION.recid from
     DIMENSIONATTRIBUTEVALUESETITEM
     inner join DIMENSIONATTRIBUTELEVELVALUE on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE= DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUE
     inner join DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION on  DIMENSIONATTRIBUTELEVELVALUE.DIMENSIONATTRIBUTEVALUEGROUP = DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUEGroup
     inner join DIMENSIONATTRIBUTEVALUECOMBINATION on DIMENSIONATTRIBUTEVALUECOMBINATION.RECID =  DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION.DIMENSIONATTRIBUTEVALUECOMBINATION
    where DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE = 'YourDimensionDisplayValue'
    group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE)
    group by DIMENSIONATTRIBUTEVALUECOMBINATION.RECID, DIMENSIONATTRIBUTEVALUECOMBINATION.DISPLAYVALUE

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Get VendInvoiceTrans rows with an specific dimension

    Today and tomorrow, I have 2 EP training days so I will try this probably on Thursday, I will post the results, thanks :)

  • Verified answer
    jasman Profile Picture
    jasman 1,411 on at
    RE: Get VendInvoiceTrans rows with an specific dimension

    I guess using the "like" construct has the disadvantage that theoretically projects and GL accounts (and indeed any other dimensions used) can have the same numbers, and thus you won't get the desired result.

    So I think you will have to join the DimensionAttribute table to get the right dimension, and DimensionAttributeValue to get the right dimension value.

    I have tried something similar once, where we had made a new table (PurchaseShipment - as in receipt of multiple containers of a shipment on a purchase order) in AX and used this as a backing entity for a financial dimension.

    The task was to do backtracking from a single container no (financial dimension) to get any posted GL transactions on that container.

    I came up with the following little job, for which you might be able to get a little inspiration:

    static void testLookupGLTransFromContainer(Args _args)

    //LedgerJournalTrans _sourceTrans,LedgerAllocationRuleDestination _ledAllocationRuleDest)

    {

        ShipmentTable       st;

        DimensionAttributeValue dav;

        DimensionAttribute da;

        DimensionAttributeLevelValue dalv;

        DimensionAttributeValueGroup davg;

        DimensionAttributeValueGroupCombination davgc;

        DimensionAttributeValueCombination davc;

        GeneralJournalAccountEntry gjac;

        while select st

            where st.shipmentid == "100271"

        join dav

            where dav.EntityInstance == st.RecId

        join da

           where da.RecId == dav.DimensionAttribute

       join dalv

           where dalv.DimensionAttributeValue == dav.recid

       join davg

           where davg.recid == dalv.DimensionAttributeValueGroup

       join davgc

           where davgc.DimensionAttributeValueGroup == davg.recid

       join davc

           where davgc.DimensionAttributeValueCombination == davc.recid

       {

           info(strFmt("***** %1 %2 %3 DAVC.recid = %4 ****",st.ShipmentId,da.Name,davc.DisplayValue,davc.recid));

           while select gjac

               where gjac.LedgerDimension == davc.RecId

           {

               info(strFmt("%1 %2 %3 %4",gjac.LedgerAccount,gjac.Text,gjac.TransactionCurrencyAmount,gjac.TransactionCurrencyCode));

           }

       }

    }

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,339 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,177 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans