SQL view for Fixed Assets depreciation in Dynamics GP


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

21 Responses to “SQL view for Fixed Assets depreciation in Dynamics GP”

  1. 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

    Like

    • 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

      Like

      • 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.

        Like

  2. 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.

    Like

    • 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

      Like

      • 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).

        Like

        • 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

          Like

  3. 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?

    Like

    • 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

      Like

    • 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…

      Like

  4. 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

    Like

  5. 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)

    Like

  6. 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

    Like

Trackbacks/Pingbacks

  1. Creating SmartLists using SmartList Builder and SQL Views - October 12, 2010

    […] is the specific post I referenced in the […]

    Like

  2. Rose Business Solutions Dynamics GP - August 19, 2010

    Creating SmartLists using SmartList Builder and SQL Views…

    There are a number of GP bloggers that often include SQL Query Language as part of their posts. …

    Like

  3. Creating SmartLists using SmartList Builder and SQL Views - Rose Business Solutions Dynamics GP - April 13, 2010

    […] is the specific post I referenced in the video. Published: Tuesday, April 13, 2010, 02:33 […]

    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: