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:
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
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?
ReplyDeleteYou do need to check with your DBA, this is designed to be run on SQL Management Studio in order to retrieve the required data.
DeleteBest Regards,
Mahmoud M. AlSaadi
Hi Mahmood,
ReplyDeleteOur 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
Go to Reports > Fixed Assets > Depreciation. Choose the "Depreciation by Ledger" report which provides you with the ability to print historical depreciation ledger reports
DeleteBest Regards,
Mahmoud M. AlSaadi
Where is the report above found in GP 3015? Is this an SSRS report?
ReplyDeleteHi Mahmood:
ReplyDeleteGreat SQL script to review Fixed Assets historical data.
Thank you!
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou 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