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 https://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 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.

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view for asset account numbers in Dynamics GPSQL view for asset account numbers in Dynamics GP - DynamicAccounting.net - April 29, 2016

    […] Yudin is back with a SQL view for asset account numbers in Dynamics GP. I love seeing Fixed Asset related posts in blogs and this one is no exception. It will go in the […]

    Like

  2. DynamicAccounting.net - August 11, 2010

    SQL view for asset account numbers in Dynamics GP…

    Victoria Yudin is back with a SQL view for asset account numbers in Dynamics GP . I love seeing Fixed…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: