On Hand Inventory by All Dimensions

Submitted by Nathan Clouse on 01/16/16.

A view showing on Hand Inventory by All Dimensions. This can be made to work with 2009 and 2012 by removing some of new(er) inventDim fields such InventStatusId or LicensePlateId.

AX Version:

AX 2012 R3

Module:

Inventory Management

CREATE view [dbo].[vw_InventOnHandByAllDimensions] as

select  'INVENTSUM_ITEMID' = iSum.ITEMID, 
		'INVENTSUM_DATAAREAID' = iSum.DATAAREAID,
		'INVENTSUM_PARTITION' = iSum.PARTITION,
		'INVENTSUM_ARRIVED' = sum(iSum.ARRIVED), 
		'INVENTSUM_AVAILORDERED' = sum(iSum.AVAILORDERED), 
		'INVENTSUM_AVAILPHYSICAL' = sum(iSum.AVAILPHYSICAL), 
		'INVENTSUM_DEDUCTED' = sum(iSum.DEDUCTED), 
		'INVENTSUM_ONORDER' = sum(iSum.ONORDER), 
		'INVENTSUM_ORDERED' = sum(iSum.ORDERED), 
		'INVENTSUM_PHYSICALINVENT' = sum(iSum.PHYSICALINVENT), 
		'INVENTSUM_PHYSICALVALUE' = sum(iSum.PHYSICALVALUE), 
		'INVENTSUM_PICKED' = sum(iSum.PICKED), 
		'INVENTSUM_POSTEDQTY' = sum(iSum.POSTEDQTY), 
		'INVENTSUM_POSTEDVALUE' = sum(iSum.POSTEDVALUE), 
		'INVENTSUM_QUOTATIONISSUE' = sum(iSum.QUOTATIONISSUE), 
		'INVENTSUM_QUOTATIONRECEIPT' = sum(iSum.QUOTATIONRECEIPT), 
		'INVENTSUM_RECEIVED' = sum(iSum.RECEIVED), 
		'INVENTSUM_REGISTERED' = sum(iSum.REGISTERED), 
		'INVENTSUM_RESERVORDERED' = sum(iSum.RESERVORDERED), 
		'INVENTSUM_RESERVPHYSICAL' = sum(iSum.RESERVPHYSICAL), 
		'INVENTDIM_CONFIGID' = id.CONFIGID, 
		'INVENTDIM_INVENTBATCHID' = id.INVENTBATCHID, 
		'INVENTDIM_INVENTCOLORID' = id.INVENTCOLORID, 
		'INVENTDIM_INVENTGTDID_RU' = id.INVENTGTDID_RU,
		'INVENTDIM_INVENTOWNERID_RU' = id.INVENTOWNERID_RU,
		'INVENTDIM_INVENTPROFILEID_RU' = id.INVENTPROFILEID_RU,
		'INVENTDIM_INVENTLOCATIONID' = id.INVENTLOCATIONID, 
		'INVENTDIM_INVENTSERIALID' = id.INVENTSERIALID,
		'INVENTDIM_INVENTSITEID' = id.INVENTSITEID, 
		'INVENTDIM_INVENTSIZEID' = id.INVENTSIZEID, 
		'INVENTDIM_INVENTSTATUSID' = id.INVENTSTATUSID, 
		'INVENTDIM_INVENTSTYLEID' = id.INVENTSTYLEID, 
		'INVENTDIM_LICENSEPLATEID' = id.LICENSEPLATEID,
		'INVENTDIM_WMSLOCATIONID' = id.WMSLOCATIONID,
		'INVENTDIM_WMSPALLETID' = id.WMSPALLETID
from InventSum iSum
inner join inventDim id on id.inventDimId = iSum.inventDimId
					   and id.dataareaid = iSum.dataareaid
					   and id.partition = iSum.partition
where iSum.Closed = 0 --NoYes::No							   
group by	iSum.PARTITION,
			iSum.DATAAREAID,
			iSum.ITEMID,
			id.CONFIGID, 
			id.INVENTBATCHID, 
			id.INVENTCOLORID, 
			id.INVENTGTDID_RU,
			id.INVENTOWNERID_RU,
			id.INVENTPROFILEID_RU,
			id.INVENTLOCATIONID, 
			id.INVENTSERIALID,
			id.INVENTSITEID, 
			id.INVENTSIZEID, 
			id.INVENTSTATUSID, 
			id.INVENTSTYLEID, 
			id.LICENSEPLATEID,
			id.WMSLOCATIONID,
			id.WMSPALLETID

GO