Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Get average unit cost ...
Finance forum
Answered

Get average unit cost of an Item using X++ in D365

Posted on by 742

Hi Everyone,

Need urgent help on below issue

I am trying to fetch average unit cost of Item through X code which is available on below path,

Released products > Manage costs > Cost objects

Below code I found on one of the blogs, but somehow it doesn't give perfect result for all transactions.

 public static void main(Args _args)
    {     
        ItemId                          _itemId = "2108050";
        InventSum                       inventSum;
        Amount                          avgUnitCost;
        InventDim                       inventDim;
        InventDim                       inventDimFind;
        InventCostPriceCache            inventCostPriceCache;
        UnitOfMeasureConverter_Product  productUnitConverter;
        UnitOfMeasureRecId              fromUnitMeasureRecId; 
        UnitOfMeasureRecId              toUnitMeasureRecId;
        InventTable                     inventTable;

        Amount                          costPrice, convertValue;

        select firstonly * from inventSum where inventSum.ItemId == _itemId
            join inventDim;

        if(!inventSum.InventDimId)
        {
            inventDimFind = InventDim::findDim(inventDim);
            if(inventDimFind.inventDimId)
            {
                inventSum.InventDimId = inventDimFind.inventDimId;
            }
        }

        inventCostPriceCache = InventCostPriceCache::construct();

        inventTable = inventSum.inventTable();

        fromUnitMeasureRecId    =   UnitOfMeasure::findBySymbol(inventTable.salesUnitId()).RecId;
        toUnitMeasureRecId      =   UnitOfMeasure::findBySymbol(inventTable.inventUnitId()).RecId;

        productUnitConverter  =   UnitOfMeasureConverter_Product::construct();
        
        productUnitConverter.parmProduct(inventTable.Product);
        productUnitConverter.parmFromUnitOfMeasure(fromUnitMeasureRecId);
        productUnitConverter.parmToUnitOfMeasure(toUnitMeasureRecId);
        productUnitConverter.parmRoundAbsoluteValue(NoYes::Yes);
        productUnitConverter.parmApplyRounding(NoYes::Yes);

        costPrice       = inventCostPriceCache.costPricePcs(inventSum, inventDim);
        convertValue    = productUnitConverter.convertValue(1);

        info(strFmt("costPrice %1, convertValue%2", costPrice, convertValue));


        avgUnitCost = costPrice * convertValue;

        info(strFmt("avgUnitCost %1", avgUnitCost));

        
    }

Please suggest solution on this.

I want to fetch the Average unit cost Site wise. Please refer below highlighted values.

MC_5F00_02.png

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    That works perfectly. Thank you for the solution. 

  • Verified answer
    udaY-ch Profile Picture
    udaY-ch 4,616 on at
    RE: Get average unit cost of an Item using X++ in D365

    HI

    Try this code, this should work as you expect

    public class InventSumQuery
    {    
        static void main(Args _args)
        {
            Qty                     totalQty;
            Query                   query;
            QueryRun                queryRun;
            QueryBuildDataSource    qbdsInventDim, qbdsInventSum;
            InventSum               inventSum;
       
            query = new query();
    
            qbdsInventSum = query.addDataSource(tableNum(InventSum)); 
            qbdsInventDim = qbdsInventSum.addDataSource(tableNum(InventDim));
            qbdsInventDim.relations(true);
            qbdsInventDim.joinMode(JoinMode::InnerJoin);
            qbdsInventDim.addRange(fieldNum(InventDim, InventSiteId)).value("");
            qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("");
            qbdsInventDim.addRange(fieldNum(InventDim, wmsLocationId)).value("");
            qbdsInventSum.addGroupByField(fieldNum(InventSum, ItemId));
    
            qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedQty), SelectionField::Sum);
            qbdsInventSum.addSelectionField(fieldNum(InventSum, Deducted), SelectionField::Sum);
            qbdsInventSum.addSelectionField(fieldNum(InventSum, Received), SelectionField::Sum);
            qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedValue), SelectionField::Sum);
            qbdsInventSum.addSelectionField(fieldNum(InventSum, PhysicalValue), SelectionField::Sum);
        
            qbdsInventSum.addRange(fieldNum(InventSum, ItemId)).value("");
    
            queryrun = new QueryRun(query);
    
            while (queryRun.next())
            {
                queryRun.changed(tableNum(InventSum));
                {
                    inventSum = queryRun.get(tableNum(InventSum));
                    totalQty = inventSum.PostedQty - abs(inventSum.Deducted)   inventSum.Received;
                    totalQty = abs(inventSum.PostedValue   inventSum.PhysicalValue) / totalQty;
                    info(strFmt("Average unit cost - %1", totalQty));
                }
            }
        }
    
    }

    Uday

  • udaY-ch Profile Picture
    udaY-ch 4,616 on at
    RE: Get average unit cost of an Item using X++ in D365

    Then you need to create a x++ query and group by item and sum of these fields to get the group of inventsum for the combination of inventory dimension with aggregation in it.

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    the same code which you have written has been used on InventSum data source method of form InventCostOnHandItem.

    But in that method, we get the group of records in the buffer of _inventSum and not a single record.

    Your code will give the output for only single record of inventsum however, average Unit Cost price displayed on the form InventCostOnHandItem is sum of the multiple records.

  • udaY-ch Profile Picture
    udaY-ch 4,616 on at
    RE: Get average unit cost of an Item using X++ in D365

    Try this code

    var         totalQty;
    InventSum   inventsum;
     
    totalQty = inventSum.PostedQty - abs(inventSum.Deducted)   inventSum.Received; 
    totalQty = abs(inventSum.PostedValue   inventSum.PhysicalValue) / totalQty);

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    Even if I modify the select query with relation, it still doesn't give me the desired result. I modified the query like below,

    select firstonly * from inventSum where inventSum.ItemId == _itemId
                join inventDim
                where inventDim.inventDimId == inventSum.inventDimId
                && inventDim.inventDimId == "#12215326";
                

    But with above code as well I did not have any luck.

  • udaY-ch Profile Picture
    udaY-ch 4,616 on at
    RE: Get average unit cost of an Item using X++ in D365

    hi

    There is no relation between inventdim and inventsum? Did you notice?

    Uday

  • Rhushikesh R Profile Picture
    Rhushikesh R 742 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Ludwig,

    The setup you mentioned is already there. I can see the data Site wise but I wanted to fetch that data through my X++ code. Hence I required help on coding part.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi,

    If you enable the financial inventory parameter in your storage dimension groups then you can get a cost price by site.

    Have you checked this setup?

    Don't you try doing something similar?

    Best regards,

    Ludwig

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans