Dynamics Community Forum Thread Details

Hello,

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?

Thanks