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