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

Calculate ledger balance by dimension set in X++ in AX2012/Dynamics 365 FO

There are a variety of ways users can view balances in the general ledger. Some of the most common options are: 1. Trial balance 2. Financia...