briefcase invoice

Yearly Fixed Assets depreciation totals in Dynamics GP


Around this time of year we get a lot of questions about closing the year for the Dynamics GP Fixed Assets module, since you cannot run January depreciation until you close the prior year. And right after we start getting requests for reports of prior year depreciation. Here is a view that shows year to date, last year and life to date depreciation by asset. It also has a pretty cool example of how to group your data into columns by year.

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_Yearly
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_FA_Depreciation_Yearly
-- Created Feb 28, 2013 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
--   FA40400 - a - Asset Account Master
--   GL00105 - am - Account Index Master
-- Updated Nov 30, 2011 to add accumulated depreciation account
-- Updated Apr 9, 2013 to add asset status
-- Updated Feb 27, 2015 to add current net book value for
--   non-retired assets
-- Updated Mar 28, 2016 to add prorated retirement date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
case fm.ASSETSTATUS
	when 1 then 'Active'
	when 2 then 'Deleted'
	when 3 then 'Partial Open'
	when 4 then 'Retired'
	end Asset_Status,
fm.ASSETID Asset_ID,
fm.ASSETIDSUF Asset_Suffix,
fm.ASSETDESC Asset_Description,
fm.ACQDATE Acquisition_Date,
fm.Acquisition_Cost,
b.BOOKID Book_ID,
sum(d.AMOUNT) Life_to_Date_Depreciation,
sum(case
	when year(getdate()) = d.FAYEAR
	then d.AMOUNT
	else 0 end) Year_to_Date_Depreciation,
sum(case
	when year(dateadd(yy,-1,getdate()))=d.FAYEAR
	then d.AMOUNT
	else 0 end) Last_Year_Depreciation,
case fm.ASSETSTATUS
	when 4 then 0
	else bd.NETBOOKVALUE
	end Net_Book_Value,
fm.ASSETCLASSID Asset_Class,
am.ACTNUMST Accum_Depr_Account,
bd.PRORATEDRETDATE Retirement_Date

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	

left outer join FA00200 bd  -- book details
	on bd.ASSETINDEX = d.ASSETINDEX
	and bd.BOOKINDX = d.BOOKINDX

inner join FA00400 a  -- account numbers
	on a.ASSETINDEX = fm.ASSETINDEX

inner join GL00105 am  -- GL account for accum depr
	on am.ACTINDX = a.DEPRRESVACCTINDX  

where d.TRANSACCTTYPE = 2  -- depreciation only 

group by fm.ASSETID, fm.ASSETIDSUF, fm.ASSETDESC, b.BOOKID,
	fm.ASSETCLASSID, fm.ACQDATE, fm.Acquisition_Cost,
	am.ACTNUMST, fm.ASSETSTATUS, bd.NETBOOKVALUE,
    bd.PRORATEDRETDATE

-- add permissions
go
grant select on view_FA_Depreciation_Yearly 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

13 Responses to “Yearly Fixed Assets depreciation totals in Dynamics GP”

  1. We are using the view_FA_Depreciation_Yearly in smart list and our auditors are asking to include the retirement date of those assets that are retired. Could this view be changed to include the retirement date?

    I tried to add the retirement table in Smartlist builder but I was struggling with the linkage and thought changing the view might be a better option.

    Thank you.

    Like

  2. Hi Victoria,

    How can you add net book value to your query? Thanks

    Like

  3. This was extremely helpful to us! My Finance people are so relieved. Thank you! We also needed to identify the disposals (aka Retired) assets which I pulled in the AssetStatus.
    Is there a way to pull in or translate the number representation? I want to add this view to SmartList and it would be better if the status was 1 – Active, 2 – Deleted, 3 – Partial Open, 4 – Retired
    Thanks!

    Like

  4. Hello! Your SQL Views are always helpful Victoria. Just curious if it is possible to alter this view so it shows Accumulated Depreciation as of the end of the prior fiscal year similar to how it shows Last Year Depreciation. My client has a need to see Accumulated Depreciation as of 12/31/2011, but they have already closed FA for 2011 and run depreciation through 1/31/2012. So running the standard GP reports (i.e. Depreciation Ledger) is showing the Accumulated Depreciation amount through 1/31/2012 and there is no way for them to go back and see the 12/31/2011 amount. Thoughts?

    Like

    • Lyndy,

      How about adding a column for life to date minus year to date depreciation to this? You can do this by adding the following before the FROM line in my code:

      ,sum(d.AMOUNT) - sum(case
      when year(getdate()) = d.FAYEAR
      then d.AMOUNT else 0 end) 
      Accumulated_Thru_Last_Year
      

      Another option could be using this view instead and just summing up the needed years.

      Hope that helps,
      -Victoria

      Like

  5. Hi Victoria,
    Thank you for all of the great resources you provide! I am trying to come up with a report or smartlist builder that will list Fixed Assets along with their Accumulated Depreciation Grouped by Account Group. The out-of-the box Fixed Asset Inventory report is actually not too bad, and it allows you to group/sort by Class, Location, or Structure.. but nothing is available by Account Group. My client has several different Accumulated Depreciation GL accounts, and they are trying to reconcile the FA Acc Depr Balances to the GL Acc Depr accounts. Any thoughts you have would be much appreciated.

    Like

    • Hi Mike,

      The problem with using Account Group is that it is not saved with the asset – it is simply used as a ‘template’ to populate the account numbers initially. You can see this by opening any existing asset in GP and going to the Asset Account window – the Account Group ID will be empty. Because of this, you’re not going to find anything that can group by Account Group.

      I just updated this view to add the Accumulated Depreciation account currently selected for each asset. Hopefully that will help with what you’re looking to do.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Yearly Fixed Assets depreciation totals in Dynamics GP | Victoria YudinYearly Fixed Assets depreciation totals in Dynamics GP | Victoria Yudin - DynamicAccounting.net - May 2, 2016

    […] Fresh off her latest book interview, Victoria Yudin give us SQL code for Yearly Fixed Assets depreciation totals in Dynamics GP […]

    Like

  2. Interesting Findings & Knowledge Sharing » Yearly Fixed Assets depreciation totals in Dynamics GP - April 30, 2011

    […] Read the rest here: Yearly Fixed Assets depreciation totals in Dynamics GP […]

    Like

  3. Yearly Fixed Assets depreciation totals in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - February 16, 2011

    […] Comments 0 Fresh off her latest book interview, Victoria Yudin give us SQL code for Yearly Fixed Assets depreciation totals in Dynamics GP […]

    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: