Skip to main content

Notifications

Dynamics 365 Community / Blogs / Dynamics GP Essentials / Fixed Assets - Historical D...

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

Comments

*This post is locked for comments