Get VendInvoiceTrans rows with an specific dimension

(0) Share

Report

Posted on by Community Member 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.

*This post is locked for comments

All responses (4)

Answers (2)

jasman 1,411 on at

Like (1)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 1,411 on at

Like (1)

Report

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));

}

}

}