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

(0) Share

Report

Posted on by Rhushikesh R

758

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.

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

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

Was this reply helpful? Yes No
Rhushikesh R 758 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
udaY-ch 5,133 on at

Like (1)

Report

hi

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

Uday

Was this reply helpful? Yes No
Rhushikesh R 758 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
udaY-ch 5,133 on at

Like (1)

Report
Try this code

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

Was this reply helpful? Yes No
Rhushikesh R 758 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
udaY-ch 5,133 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

udaY-ch 5,133 on at

Like (1)

Report
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

Was this reply helpful? Yes No
Rhushikesh R 758 on at

Like (1)

Report

Hi Uday,

That works perfectly. Thank you for the solution.

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates