On Hand Inventory by Site, Warehouse, Location, Configuration, Style, Color, Size

Submitted by Nathan Clouse on 01/17/16.

A view for on Hand Inventory by Site, Warehouse, Location, Configuration, Style, Color, Size. This should be safe for all versions of 2009 and 2012.

AX Version:

Module:

Inventory Management

CREATE view [dbo].[vw_InventOnHandBySiteWarehouseLocationConfigStyleColorSize] 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_INVENTSITEID' = id.INVENTSITEID, 
		'INVENTDIM_INVENTLOCATIONID' = id.INVENTLOCATIONID, 		 
		'INVENTDIM_WMSLOCATIONID' = id.WMSLOCATIONID,		
		'INVENTDIM_CONFIGID' = id.CONFIGID, 
		'INVENTDIM_INVENTCOLORID' = id.INVENTCOLORID, 
		'INVENTDIM_INVENTLOCATIONID' = id.INVENTLOCATIONID, 
		'INVENTDIM_INVENTSITEID' = id.INVENTSITEID, 
		'INVENTDIM_INVENTSIZEID' = id.INVENTSIZEID, 
		'INVENTDIM_INVENTSTYLEID' = id.INVENTSTYLEID
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.INVENTSITEID, 
			id.INVENTLOCATIONID, 						
			id.WMSLOCATIONID,
			id.CONFIGID, 
			id.INVENTCOLORID, 
			id.INVENTSIZEID, 
			id.INVENTSTYLEID

GO

quicktab