Fixed Assets Balance by Asset

Submitted by Nathan Clouse on 10/25/16.
Fixed Assets Balance by Asset to today. This can be modified to get a balance at a point in time by replacing getDate() with a particular date.
AX Version:
AX 2012 R3
Module:
Fixed Assets
CREATE VIEW [dbo].[vw_faAssetBalances]
AS 


	select	at.ASSETGROUP,
			at.ASSETID,
			at.NAME,
			ab.BOOKID,
			'bookType' = 'Value Model',
			ab.STATUS, 
			'aquisition' = (select isnull(SUM(AMOUNTMST),0)
							from ASSETTRANS atrans
							where atrans.partition = at.partition
							and atrans.dataareaid = at.DATAAREAID
							and atrans.ASSETID = at.ASSETID
							  and atrans.TRANSTYPE in (1,2,27) --acquisition
							  and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID),
			'bonus' =	   (select isnull(SUM(AMOUNTMST),0)
							from ASSETTRANS atrans
							where atrans.partition = at.partition
							and atrans.dataareaid = at.DATAAREAID
							and atrans.ASSETID = at.ASSETID
							  and atrans.TRANSTYPE = 32 --bonus
							 and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID),
			'depreciation' =   (select isnull(SUM(AMOUNTMST),0)
									from ASSETTRANS atrans
									where atrans.partition = at.partition
									and atrans.dataareaid = at.DATAAREAID
									and atrans.ASSETID = at.ASSETID
								  and atrans.TRANSTYPE in (3,4) --depreciation
								  and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID),
			'profitLoss' =	   (select isnull(SUM(AMOUNTMST),0)
									from ASSETTRANS atrans
									where atrans.partition = at.partition
									and atrans.dataareaid = at.DATAAREAID
									and atrans.ASSETID = at.ASSETID
								  and atrans.TRANSTYPE = 11 --profitLoss
								  and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID),
			'issue'		=		(select isnull(SUM(AMOUNTMST),0)
									from ASSETTRANS atrans
									where atrans.partition = at.partition
									and atrans.dataareaid = at.DATAAREAID
									and atrans.ASSETID = at.ASSETID
								  and atrans.TRANSTYPE in (8,9) --issue
								  and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID),							  
			'netValue' =	   (select isnull(SUM(AMOUNTMST),0)
								from ASSETTRANS atrans
								where atrans.partition = at.partition
								and atrans.dataareaid = at.DATAAREAID
								and atrans.ASSETID = at.ASSETID
								  and atrans.TRANSDATE <= getdate()
							  and atrans.DATAAREAID = at.DATAAREAID)				
	from ASSETTABLE at
	inner join ASSETBOOK ab 
	on ab.partition = at.partition
	and ab.DATAAREAID = at.DATAAREAID	and ab.ASSETID = at.ASSETID 
	union all
	select	at.ASSETGROUP,
			at.ASSETID,
			at.NAME,
			adb.DEPRECIATIONBOOKID,
			'bookType' = 'Depreciation Book',
			adb.ASSETSTATUS,
			'aquisition' = (select isnull(SUM(AMOUNTMST),0)
							from ASSETDEPBOOKTRANS adbtrans
							where   adbtrans.partition =  at.partition
							 and adbtrans.DATAAREAID = at.DATAAREAID
							and adbtrans.ASSETID = at.ASSETID
							  and adbtrans.TRANSTYPE in (1,2,27) --acquisition
							  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
							  and adbTrans.TRANSDATE <= getdate()
							 ),
			'bonus' =	   (select isnull(SUM(AMOUNTMST),0)
							from ASSETDEPBOOKTRANS adbtrans
							where  adbtrans.partition = at.partition
							and adbtrans.DATAAREAID = at.DATAAREAID
							and adbtrans.ASSETID = at.ASSETID
							  and adbtrans.TRANSTYPE = 32 --bonus
							  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
							  and adbTrans.TRANSDATE <= getdate()),
			'depreciation' =   (select isnull(SUM(AMOUNTMST),0)
								from ASSETDEPBOOKTRANS adbtrans
								where adbtrans.partition = at.partition
								and adbtrans.DATAAREAID = at.DATAAREAID
								and adbtrans.ASSETID = at.ASSETID
								  and adbtrans.TRANSTYPE in (3,4) --depreciation
								  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
								  and adbTrans.TRANSDATE <= getdate()),
			'profitLoss' =	   (select isnull(SUM(AMOUNTMST),0)
								from ASSETDEPBOOKTRANS adbtrans
								where adbtrans.partition = at.partition
								and adbtrans.DATAAREAID = at.DATAAREAID 
								and adbtrans.ASSETID = at.ASSETID
								  and adbtrans.TRANSTYPE = 11 --profitLoss
								  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
								  and adbTrans.TRANSDATE <= getdate()),
			'issue'		=		(select isnull(SUM(AMOUNTMST),0)
								from ASSETDEPBOOKTRANS adbtrans
								where adbtrans.partition = at.partition
								and adbtrans.DATAAREAID = at.DATAAREAID 
								and adbtrans.ASSETID = at.ASSETID
								  and adbtrans.TRANSTYPE in (8,9) --issue
								  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
								  and adbTrans.TRANSDATE <= getdate()),
			'netValue' =	   (select isnull(SUM(AMOUNTMST),0)
								from ASSETDEPBOOKTRANS adbtrans
								where adbtrans.partition = at.partition
								and adbtrans.DATAAREAID = at.DATAAREAID 
								and adbtrans.ASSETID = at.ASSETID
								  and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID
								  and adbTrans.TRANSDATE <= getdate())
	from ASSETTABLE at
	inner join ASSETDEPBOOK adb 
	on	adb.partition = at.partition
	and	adb.dataareaid = at.dataareaid 
	and  adb.ASSETID = at.ASSETID 
	
GO