Saturday, 25 July 2020

Get average unit cost of an item using x++ in D365 FO

Use this below code to get the average unit cost of an item using x++ by passing the relevant inventory dimensions and item
public class InventSumQuery
{    
    static void main(Args _args)
    {
        Qty                     totalQty;
        Amount                  cost;
        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;
                cost = abs(inventSum.PostedValue + inventSum.PhysicalValue) / totalQty;
                info(strFmt("Average unit cost - %1", abs(cost)));
            }
        }
    }
}

No comments:

Post a Comment

Best Practices for Troubleshooting Application Issues in D365 and Power Platform

When facing application issues, it’s important to systematically troubleshoot before reaching out for support.  Follow these steps to ensure...