On Hand Inventory by Site

Submitted by Nathan Clouse on 01/10/16.

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

AX Version:

Module:

Inventory Management

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

GO

quicktab