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 TO DYNGRP ~~~~~ For more Dynamics GP SQL code take a look at the GP Reports page on this blog
Continue reading →