SQl view for asset account numbers in Dynamics GP

Want to see all the account numbers set up for all your Fixed Assets? You can do this in SmartList, but SmartList is sometimes limiting when you want to do complex searches. Here is a view that will return all the account numbers assigned for each asset in Dynamics GP.

~~~~~

CREATE VIEW view_Asset_Accounts
AS

/*******************************************************************
view_Asset_Accounts
Created on Aug 10, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
*******************************************************************/

select	F.ASSETID Asset_ID,
		F.ASSETIDSUF Suffix,
		F.ASSETDESC Desciption,
		F.ASSETCLASSID Asset_Class,
		F.Physical_Location_ID,
		G1.ACTNUMST Depreciation_Expense,
		G2.ACTNUMST Depreciation_Reserve,
		G3.ACTNUMST Prior_Yr_Depreciation,
		G4.ACTNUMST Asset_Cost,
		G5.ACTNUMST Proceeds,
		G6.ACTNUMST Recognized_Gain_Loss,
		G7.ACTNUMST Non_Recognized_Gain_Loss,
		G8.ACTNUMST Clearing_Account

from FA00100 F
left outer join FA00400 A
	on F.ASSETINDEX = A.ASSETINDEX
left outer join GL00105 G1
	on A.DEPREXPACCTINDX = G1.ACTINDX
left outer join GL00105 G2
	on A.DEPRRESVACCTINDX = G2.ACTINDX
left outer join GL00105 G3
	on A.PRIORYRDEPRACCTINDX = G3.ACTINDX
left outer join GL00105 G4
	on A.ASSETCOSTACCTINDX = G4.ACTINDX
left outer join GL00105 G5
	on A.PROCEEDSACCTINDX = G5.ACTINDX
left outer join GL00105 G6
	on A.RECGAINLOSSACCTINDX = G6.ACTINDX
left outer join GL00105 G7
	on A.NONRECGAINLOSSACCTINDX = G7.ACTINDX
left outer join GL00105 G8
	on A.CLEARINGACCTINDX = G8.ACTINDX		

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Asset_Accounts

~~~~~

For more Dynamics GP SQL code take a look at the GP Reports page on this blog.

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.