Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

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

5 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