I am building an Inventory Cube and i need to know how the inventory calculation is done in AX. So far, i got this information(query) from the report titled Inventory value by inventory dimension:
select InventSum.ItemId,
InventTable.ItemName,
InventSum.PostedQty,
InventSum.PostedValue
from InventDim
inner join InventSum on (InventDim.inventDimId =
InventSum.InventDimId)
left outer join InventTable on (InventSum.ItemId = InventTable.ItemId)
where exists (select *
from InventTable
where InventTable.ItemId = InventSum.ItemId
and not (InventTable.ItemType
= 2)
)
-- Criteria for search
and InventSum.LastUPDDatePhysical
<= @Date
and InventDim.DataAreaId
= @Company
and InventDim.configId
= @Configuration
and InventDim.InventColorId
= @Source
and InventDim.InventSiteId
= @Site
and InventDim.InventLocationId
= @Warehouse
and InventDim.wMSLocationId
= @Location
and InventSum.Closed = @Closed
and not (InventSum.PostedQty = 0
and InventSum.PostedValue = 0)
group by InventSum.ItemId,
InventTable.ItemName,
InventSum.PostedQty,
InventSum.PostedValue
order
by InventSum.ItemId
But the problem is when i run this query i get more results than when i run the report "Inventory value by inventory dimension"
Please help me with the query.
