On Hand Inventory by Site, Warehouse

On Hand Inventory by Site, Warehouse

Submitted by Nathan Clouse on 01/11/16.

A view showing On Hand Inventory by Site, Warehouse. This should be safe for all versions of 2009 and 2012.

AX Version: 

Module: 

CREATE view [dbo].[vw_InventOnHandBySiteWarehouse] 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
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

GO