Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Wednesday, March 18, 2015

Fixed Assets - Historical Depreciation Ledger Report


Historical Reporting is always a must in all companies, and clients always ask about such reports at the very early stages to make sure that can do proper reporting not only for the current period, but also for historical periods.

In Dynamics GP, there is a report for depreciation ledger that can be printed as of a specific period of time, this was a new feature presented in Dynamics GP 2013. As derived from this report, the below script can be deployed to retrieve such details.

Here is the data set view:

Depreciation Ledger Report

 

Helping Note
This report works for Dynamics GP 2013 and later, since there are predefined canned SQL functions used as part of the script.

/*--------------------------------------------------------------------------

Creation Date: 18th of March , 2015

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to provide a historical "As of" depreciation ledger report for

Fixed Asset module

 

Revision History:

Revision No.            RevisionDate    Description

1                       18/03/2014      Original Version

------------------------------------------------------------------------- */

 

DECLARE @As_of_Date AS DATETIME

DECLARE @BookIndex AS INT

SET @As_of_Date = '2017-12-31 00:00:00.000'

SET @BookIndex = ( SELECT   CORPBOOKINDX

                   FROM     FA49900

                 )

 

SELECT  ASSETID AS AssetID ,

        ASSETIDSUF AS Suffix ,

        ASSETCLASSID ,

        PlaceInServiceDate ,

        DepreciationExpense ,

        AccumulatedDepreciation ,

        AssetCostDescription ,

        FullyDepreciatedFlag ,

        FullyDepreciatedDate ,

        REMAININGLIFEYEARS AS AS_of_Remaining_Life_Year ,

        REMAININGLIFEDAYS AS AS_of_Remaining_Life_Dyas ,

        COSTBASIS AS CostBasic ,

        CURRUNDEPRAMT AS CurrentDepreciationAmount ,

        YTDDEPRAMT AS AS_OF_YTD_Amount ,

        LTDDEPRAMT AS AS_OF_LTD_Amount

FROM    ( SELECT    A.ASSETINDEX ,

                    D.ASSETID ,

                    D.ASSETIDSUF ,

                    D.ASSETCLASSID ,

                    E.DEPREXPACCTINDX ,

                    H.ACTDESCR AS DepreciationExpense ,

                    E.DEPRRESVACCTINDX ,

                    G.ACTDESCR AS AccumulatedDepreciation ,

                    E.ASSETCOSTACCTINDX ,

                    F.ACTDESCR AS AssetCostDescription ,

                    A.BOOKINDX ,

                    A.PLINSERVDATE AS PlaceInServiceDate ,

                    A.DELETEDATE ,

                    A.DEPRBEGDATE ,

                    A.FULLYDEPRFLAG FullyDepreciatedFlag ,

                    A.FULLYDEPRDATE FullyDepreciatedDate ,

                    A.ORIGINALLIFEYEARS ,

                    A.ORIGINALLIFEDAYS ,

                    CASE WHEN ( A.DEPRTODATE > @As_of_Date )

                         THEN ISNULL(dbo.FA_RWCalcRemainingLife

                          (@As_of_Date,

                          A.PLINSERVDATE,

                          A.DEPRBEGDATE,

                          A.ORIGINALLIFEYEARS,

                          A.ORIGINALLIFEDAYS,

                          0,

                          A.AVERAGINGCONV),

                          0)

                         ELSE A.REMAININGLIFEYEARS

                    END AS REMAININGLIFEYEARS ,

                    CASE WHEN ( A.DEPRTODATE > @As_of_Date )

                         THEN ISNULL(dbo.FA_RWCalcRemainingLife

                                     (@As_of_Date,

                                     A.PLINSERVDATE,

                                     A.DEPRBEGDATE,

                                     A.ORIGINALLIFEYEARS,

                                     A.ORIGINALLIFEDAYS,

                                     1,

                                     A.AVERAGINGCONV),

                                     1)

                         ELSE A.REMAININGLIFEDAYS

                    END AS REMAININGLIFEDAYS ,

                    A.DEPRTODATE ,

                    A.LASTRECALCDATE ,

                    A.LASTRECALCDATEFISYR ,

                    A.BEGINYEARCOST ,

                    A.BAGINSALVAGE ,

                    A.BEGINRESERVE ,

                    ABS(A.COSTBASIS - A.COSTBFRETORDEL)

                                         AS COSTBASIS ,

                    A.SALVAGEVALUE ,

                    A.DEPRECIATIONMETHOD ,

                    A.AVERAGINGCONV ,

                    A.SWITCHOVER ,

                    A.SWITCHFM1METHOD ,

                    A.SWITCHFM1AMOUNT ,

                    A.SWITCHFM1DATE ,

                    A.DLYDEPRRATE ,

                    A.PERDEPRRATE ,

                    A.YRLYDEPRRATE ,

                    A.AMORTIZATIONCODE ,

                    A.AMORTIZATIONAMOUNT ,

                    ISNULL(dbo.FA_CalcCurrentDepreciationDetail

                                                (A.ASSETINDEX,

                            A.BOOKINDX,

                            @As_of_Date,

                            '99991231', 0),

                           0) AS CURRUNDEPRAMT ,

                    ISNULL(dbo.FA_CalcYTDDepreciationSummary

                                                (A.ASSETINDEX,

                            A.BOOKINDX,

                                           @As_of_Date,

                             '99991231', 0), 0) AS YTDDEPRAMT ,

                    ISNULL(dbo.FA_CalcLTDDepreciationSummary

                                                (A.ASSETINDEX,

                            A.BOOKINDX,

                            @As_of_Date,

                            '99991231', 0), 0) AS LTDDEPRAMT ,

                    ISNULL(dbo.FA_CalcNetBookValueSummary

                                                (A.ASSETINDEX,

                            A.BOOKINDX,

                            @As_of_Date,

                            '99991231', 0), 0) AS NETBOOKVALUE ,

                    A.DEPRTODATEBFRET ,

                    A.RECGAINLOSS ,

                    A.NONRECGAINLOSS ,

                    A.STLINEDEPRATRET ,

                    A.COSTBFRETORDEL ,

                    ISNULL(C.RETIREMENTDATE, '') AS RETIREMENTDATE ,

                    ISNULL(C.ASSETINDXAFTRET, 0) AS ASSETINDXAFTRET

          FROM      FA00200 A

          LEFT OUTER JOIN FA00700 C

                     ON A.ASSETINDEX = C.ASSETINDXAFTRET

          LEFT OUTER JOIN FA00100 AS D

                     ON A.ASSETINDEX = D.ASSETINDEX

          LEFT OUTER JOIN FA00400 AS E

                     ON A.ASSETINDEX = E.ASSETINDEX

          INNER JOIN GL00100 AS F

                     ON E.ASSETCOSTACCTINDX = F.ACTINDX

          INNER JOIN GL00100 AS G

                     ON E.DEPRRESVACCTINDX = G.ACTINDX

          INNER JOIN GL00100 AS H

                     ON E.DEPREXPACCTINDX = H.ACTINDX

          WHERE     A.BOOKINDX = @BookIndex

        ) AS RPT

 

The script can be downloaded from here >> Download Link

Best Regards,
Mahmoud M. AlSaadi

7 comments:

  1. Hi Mahmoud! Sorry not really a report guru here but would like to test this. Can you please tell me what to do to test the SQL for the historical as of report?

    ReplyDelete
    Replies
    1. You do need to check with your DBA, this is designed to be run on SQL Management Studio in order to retrieve the required data.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Hi Mahmood,
    Our office has a request to provide Detail Historical Fixed Asset information for the Financial years of 2014, 2015 & 2016. Have a copy of the Depreciation Ledger By Class Summary and need to tie back. We are currently on GP 2010, this version doesn't have the ability to generate Historical Fixed Assets reports.
    Would you have any suggestions on getting that data?

    Regards,
    Kevin

    ReplyDelete
    Replies
    1. Go to Reports > Fixed Assets > Depreciation. Choose the "Depreciation by Ledger" report which provides you with the ability to print historical depreciation ledger reports

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  3. Where is the report above found in GP 3015? Is this an SSRS report?

    ReplyDelete
  4. Hi Mahmood:

    Great SQL script to review Fixed Assets historical data.
    Thank you!

    ReplyDelete
  5. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete