April 7, 2010 
Below is a view I have put together to get the monthly Fixed Assets depreciation amounts in Dynamics GP. For more detail on the Fixed Assets module, please visit my Fixed Assets Tables page. If you’re looking for more SQL code, you can find it on my GP Reports page.
~~~~~
CREATE VIEW view_FA_Depreciation AS /******************************************************************* view_FA_Depreciation Created on Apr 7, 2010 by Victoria Yudin - Flexible Solutions, Inc. For updates visit https://victoriayudin.com/gp-reports/ Tables used: FA00100 – fm - Asset General Information Master FA00902 – d - Financial Detail Master FA40200 - b - Book Setup Updated on Apr 7, 2010 to add Book ID *******************************************************************/ SELECT fm.ASSETID Asset_ID, fm.ASSETIDSUF Asset_Suffix, fm.ASSETDESC Asset_Description, b.BOOKID Book_ID, d.FAPERIOD Depr_Month, d.FAYEAR Depr_Year, d.AMOUNT Depreciation, d.GLINTTRXDATE GL_Trx_Date, fm.Master_Asset_ID, fm.ASSETCLASSID Asset_Class, fm.STRUCTUREID Stucture_ID, fm.LOCATNID Location_ID, fm.ACQDATE Acquisition_Date, fm.Acquisition_Cost, fm.Physical_Location_ID, fm.Asset_Label FROM FA00902 d -- financial detail LEFT OUTER JOIN FA00100 fm -- fa master ON fm.assetindex = d.assetindex INNER JOIN FA40200 b -- book setup ON b.BOOKINDX = d.BOOKINDX WHERE d.TRANSACCTTYPE = 2 -- depreciation only /** 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_FA_Depreciation to DYNGRP
~~~~~
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 
Thanks!
LikeLike
Hi Victoria, thank you, this view is very helpful! I am hoping you can help me customize it further to include the column headers below. Eventually I will use this as a Smart List view for all users. Note: we use MEM (Multi-Entity Management), one of the column headers is labeled as Facility ID. I am not sure which table contains this data in order to pull it into this view. Thank you!
Headers:
Facility ID
Asset ID
Asset Suffix
Asset Class ID
Asset Description
Book ID
Original Life Years/Days
Place in Service
Cost Basis
LTD Depreciation Amount
YTD Depreciation Amount
Net Book Value
Depreciated to Date
Depreciated Method
Current Run Depreciation Amount
LikeLike
Hi Ana,
If you are interested, this can be done as a consulting project for you. Let me know if you would like to talk about that in more detail.
-Victoria
LikeLike
Victoria, thank you for your prompt response. Yes, let’s talk. We made some additional progress in updating the script but still have some tables that are not quite linking correctly therefore, we are not getting the data exactly as needed.
Thank you.
LikeLike
Victoria,
Is there a way to easily include projected depreciation from GP in this view?
Great site by the way – I’ve just started using SQL views from GP and have found your info invaluable.
LikeLike
Hi Rob,
Projections are stored in table 41900 and can certainly be added…however, I was wondering how you would want to add them? Show the projection in a separate column? Something else?
-Victoria
LikeLike
Thanks Victoria, I’d actually like to include projections in the same depreciation column – looking at FA41900 it only holds future periods so there should be no overlap with actuals.
My goal was to then pivot the data and show a single row for an Asset with columns for all the static data (No., Description, Class, Acquisition cost) and financial data columns for all periods (historic & future).
LikeLike
Rob,
If you don’t have any overlapping data in your actuals and projections, you could use something like the following code:
SELECT fm.ASSETID Asset_ID, fm.ASSETIDSUF Asset_Suffix, fm.ASSETDESC Asset_Description, b.BOOKID Book_ID, fm.Master_Asset_ID, fm.ASSETCLASSID Asset_Class, fm.STRUCTUREID Stucture_ID, fm.LOCATNID Location_ID, fm.ACQDATE Acquisition_Date, fm.Acquisition_Cost, fm.Physical_Location_ID, fm.Asset_Label, d.FAYEAR [Year], d.FAPERIOD Period, d.AMOUNT Depreciation, d.DepType Depreciation_Type FROM FA00100 fm -- fa master left outer join (select ASSETINDEX, BOOKINDX, YTDDEPRAMT AMOUNT, FAYEAR, FAPERIOD, 'Projection' DepType from FA41900 union all select ASSETINDEX, BOOKINDX, AMOUNT, FAYEAR, FAPERIOD, 'Actual' DepType from FA00902 where TRANSACCTTYPE = 2) d -- depreciation on fm.ASSETINDEX = d.ASSETINDEX INNER JOIN FA40200 b -- book setup ON d.BOOKINDX = b.BOOKINDX-Victoria
LikeLike
That’s great, thanks very much Victoria. I owe you a pint (or a glass of wine!). 🙂
LikeLike
Victoria–> I need Help I am trying to create some historical reports for a Client. I have several assets that do not tie out no matter what I do. Using your query above I created a Life to date total for a specific asset the Query returns 2995.13 depreciation. The Life to day depreciation in GP shows 3881.93. I can’t figure it out. This is an asset that the client retired and I think it has something to do with it’s retirement. Is it possible that there is something that I am missing?
LikeLike
Mark,
I’ve not seen retirement cause any issues like this. Maybe when they entered/imported the asset it was with a non-zero life to date depreciation? Or someone manipulated the data in the tables for some reason? I find a lot of people do this in the FA module for some reason, like it’s not a real subledger…
-Victoria
LikeLike
I had an issue with one asset, that was using the half a year convention dep. when it retired, the life to date dep was just the actual depreciated amount, but transactions happened at the retirement that i guess dont sit in the same table?? Its a mistery!
Look in the financial detail for that asset, you might find your difference there…
LikeLike
Victoria,
Awesome.. as always. I tweaked the view a little to add the GL account number, which our client needs for reconciliation purposes. Here is the updated select statement:
SELECT
fm.ASSETID Asset_ID,
fm.ASSETIDSUF Asset_Suffix,
fm.ASSETDESC Asset_Description,
b.BOOKID Book_ID,
d.FAPERIOD Depr_Month,
d.FAYEAR Depr_Year,
d.AMOUNT Depreciation,
d.GLINTTRXDATE GL_Trx_Date,
d.GLINTACCTINDX GL_Acct_Indx,
gl.actnumst GL_Acct_Nbr,
fm.Master_Asset_ID,
fm.ASSETCLASSID Asset_Class,
fm.STRUCTUREID Stucture_ID,
fm.LOCATNID Location_ID,
fm.ACQDATE Acquisition_Date,
fm.Acquisition_Cost,
fm.Physical_Location_ID,
fm.Asset_Label
FROM FA00902 d — financial detail
LEFT OUTER JOIN
FA00100 fm — fa master
ON fm.assetindex = d.assetindex
INNER JOIN
FA40200 b — book setup
ON b.BOOKINDX = d.BOOKINDX
inner join
GL00105 gl
on d.glintacctindx = gl.ACTINDX
WHERE d.TRANSACCTTYPE = 2 — depreciation only
/** 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_FA_Depreciation to DYNGRP
LikeLike
Mike,
Great, thank you for sharing this.
-Victoria
LikeLike
I’m looking to see if anyone has written a view etc to provide a YTD movements,or MTD for that matter,reconciliation of Fixed Assets.
ie Opening Balance Cost
plus Additions
Transfers\Changes
Less Disposals
Closing Balance Cost
then for Accumulated Depn
Opening Balance
Less Disposals
plus Depn Expense
Transfers\Changes
=Closing Balance AD
This needs to be captured in such a way that I get a split at Asset Class ID and also Property Type (which we use as a split between Rental, Own Use machines,Sponsorhip, Normal type Fixed Assets)
LikeLike
Ross,
This would be a pretty complicated report to create. I have not seen anything like this available anywhere.
-Victoria
LikeLike
I used this SQL View in a short video I just made to show how easy it is to create a SmartList from this view and use it in GP: http://www.youtube.com/watch?v=sAnz1gzZHnk
LikeLike
Steve,
That is fabulous! Awesome video and thank you very much for all the shout outs!
-Victoria
LikeLike