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 http://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,
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
Mike,
Great, thank you for sharing this.
-Victoria
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)
Ross,
This would be a pretty complicated report to create. I have not seen anything like this available anywhere.
-Victoria
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
Steve,
That is fabulous! Awesome video and thank you very much for all the shout outs!
-Victoria