Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
We have an issue that some products those are WHS enabled has some invalid "Total available" on on-hand inventory form (Product information management/Common/Released products/ on product-tab Manage inventory->On-Hand inventory)
After some search and look at "WHSInventReserve" table we have found, there is wrong "INVENTDIMID" according to "HIERARCHYLEVEL"
Suppose that for the ItemId "8806088640150" and its variant with following query
select ITEMID,cast ( AVAILPHYSICAL as numeric(18,0)) AVAILPHYSICAL,
cast( AVAILORDERED as numeric(18,0)) AVAILORDERED,WHSInventReserve.INVENTDIMID,HIERARCHYLEVEL ,INVENTSERIALID,CONFIGID
from WHSInventReserve inner join INVENTDIM on WHSInventReserve.INVENTDIMID = INVENTDIM.INVENTDIMID and WHSInventReserve.DATAAREAID = INVENTDIM.DATAAREAID
and WHSInventReserve.PARTITION = INVENTDIM.PARTITION
where [HIERARCHYLEVEL] = 0 and itemid = '8806088640150' and INVENTDIM.CONFIGID =N'همراه سروی' and INVENTDIM.INVENTCOLORID = '' and INVENTDIM.INVENTSIZEID ='' and INVENTDIM.INVENTSTYLEID = ''
The result is as follows :
ITEMID AVAILPHYSICAL AVAILORDERED INVENTDIMID HIERARCHYLEVEL INVENTSERIALID CONFIGID
8806088640150 21 44 K1-002311 0 همراه سروی
8806088640150 1 1 K1-007538 0 354021087839666 همراه سروی
I think "K1-007538" is wrong according to HIERARCHYLEVEL.
And aggregation of Inventsum for this ItemId and its variant is as follows :
select ITEMID ,sum(ONORDER),sum(AVAILORDERED), CONFIGID ,INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID
from dbo.INVENTSUM INNER JOIN
dbo.INVENTDIM ON dbo.INVENTSUM.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID AND dbo.INVENTDIM.DATAAREAID = dbo.INVENTSUM.DATAAREAID AND dbo.INVENTDIM.PARTITION = dbo.INVENTSUM.PARTITION
where ITEMID ='8806088640150' and INVENTDIM.CONFIGID =N'همراه سروی' and INVENTDIM.INVENTCOLORID = '' and INVENTDIM.INVENTSIZEID ='' and INVENTDIM.INVENTSTYLEID = ''
GROUP BY dbo.INVENTSUM.ITEMID, dbo.INVENTDIM.CONFIGID, dbo.INVENTDIM.INVENTSIZEID, dbo.INVENTDIM.INVENTCOLORID, dbo.INVENTDIM.INVENTSTYLEID, dbo.INVENTSUM.DATAAREAID, dbo.INVENTSUM.PARTITION
ITEMID ONORDER AVAILORDERED CONFIGID INVENTSIZEID INVENTCOLORID INVENTSTYLEID
8806088640150 44 1 همراه سروی
And according to xpp code "Total available" is (AggregatedWHSInventReserve.AVAILORDERED - AggregatedInventsum.OnOrder), so that according to current code of AX it is "44-44 =0 " but it wrong, when i subtract number of Qty in purchase order from Sales order plus none posted retail transaction it must be 1 and we can get the desire result when i sum of all hierarchy level 0 from WHSInventReserve(44+1) and then subtract from "Aggregated Inventsum On-order" that is (45-44 = 1).
We have found such wrong dimension for almost 17 products.
How to solve this issue?
Business Applications communities