August 10, 2010 
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 https://victoriayudin.com/gp-reports/
*******************************************************************/
select F.ASSETID Asset_ID,
F.ASSETIDSUF Suffix,
F.ASSETDESC Asset_Description,
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 TO DYNGRP
~~~~~
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.
Victoria Yudin 
Victoria – have you written a view to compare two or more asset books? There is a canned report available within GP but it is not Excel friendly. I was hoping to find you had written an asset book comparison view that I could borrow to use in SmartList.
LikeLike
Hi Kristie,
No, I don’t have anything like that already written. Unless you are hardcoding your books, I would think this would need to be a stored procedure where you can enter the books to compare as parameters.
-Victoria
LikeLike