You’re offline. This is a read only version of the page.
Hi, I need to run a report, smartlist etc to get depreciation detail for YTD. Preferably in the following format
Asset ID Period/Year Date Posted Amount
I recall doing this before but some time ago. We have just installed GP2013.
Let me know if you need further info.
*This post is locked for comments
Have you looked at the FA Detail Activity report? You can select a range of 'Transaction Account Type' from 'Depreciation' to 'Depreciation'. Is this what you are looking for?
Sorry, I really don't know anything about SQL. Not sure if we even have the capability. I believe all the data I need is in the FATRX batches but I cannot find a way to send them to excel.
Here is missing View.
CREATE VIEW [dbo].[Flash_FADepreciationDetails]
AS
SELECT fm.ASSETID AS Asset_ID, fm.ASSETIDSUF AS Asset_Suffix, fm.ASSETDESC AS Asset_Description, b.BOOKID AS Book_ID, d.FAPERIOD AS Depr_Month,
d.FAYEAR AS Depr_Year, d.AMOUNT AS Depreciation, d.GLINTTRXDATE AS GL_Trx_Date, fm.Master_Asset_ID, fm.ASSETCLASSID AS Asset_Class,
fm.STRUCTUREID AS Stucture_ID, fm.LOCATNID AS Location_ID, fm.ACQDATE AS Acquisition_Date, fm.Acquisition_Cost, fm.Physical_Location_ID,
fm.Asset_Label, fm.ASSETINDEX, fm.ASSETQTY
FROM dbo.FA00902 AS d LEFT OUTER JOIN
dbo.FA00100 AS fm ON fm.ASSETINDEX = d.ASSETINDEX INNER JOIN
dbo.FA40200 AS b ON b.BOOKINDX = d.BOOKINDX
WHERE (d.TRANSACCTTYPE = 2)
Below SQL Query I used to create SQL Reports. IF you need SQL Report please let me know.
SELECT TOP (100) PERCENT dbo.Flash_FADepreciationDetails.Asset_ID, dbo.Flash_FADepreciationDetails.Asset_Suffix, dbo.Flash_FADepreciationDetails.Asset_Description,
dbo.Flash_FADepreciationDetails.Book_ID, dbo.Flash_FADepreciationDetails.Depr_Month, dbo.Flash_FADepreciationDetails.Depr_Year,
dbo.Flash_FADepreciationDetails.Depreciation, dbo.Flash_FADepreciationDetails.GL_Trx_Date, dbo.Flash_FADepreciationDetails.Asset_Class,
dbo.Flash_FADepreciationDetails.Acquisition_Date, dbo.Flash_FADepreciationDetails.Acquisition_Cost, dbo.Flash_FADepreciationDetails.Asset_Label,
dbo.FA00200.PLINSERVDATE, dbo.FA00200.COSTBASIS, dbo.FA00200.LTDDEPRAMT, dbo.FA00200.CURRUNDEPRAMT, dbo.FA00200.NETBOOKVALUE,
dbo.FA00200.PERDEPRRATE, dbo.FA00200.DEPRECIATIONMETHOD,
CASE WHEN FA00200.DEPRECIATIONMETHOD = 1 THEN 'Straight-Line Org Life' WHEN FA00200.DEPRECIATIONMETHOD = 2 THEN 'Straight-Line Reg Life' WHEN FA00200.DEPRECIATIONMETHOD
= 3 THEN '125% DB' WHEN FA00200.DEPRECIATIONMETHOD = 4 THEN '150% DB' WHEN FA00200.DEPRECIATIONMETHOD = 5 THEN '175% DB' WHEN FA00200.DEPRECIATIONMETHOD
= 6 THEN '200% DB' WHEN FA00200.DEPRECIATIONMETHOD = 7 THEN 'SOY Digit' WHEN FA00200.DEPRECIATIONMETHOD = 8 THEN 'Remaining Life' WHEN FA00200.DEPRECIATIONMETHOD
= 9 THEN 'Amortization' WHEN FA00200.DEPRECIATIONMETHOD = 10 THEN 'ACRS Personal' WHEN FA00200.DEPRECIATIONMETHOD = 11 THEN 'ACRS Real' WHEN
FA00200.DEPRECIATIONMETHOD = 12 THEN 'ACRS Real MSL' WHEN FA00200.DEPRECIATIONMETHOD = 13 THEN 'ACRS LIH' WHEN FA00200.DEPRECIATIONMETHOD
= 14 THEN 'ACRS Foreign Real' WHEN FA00200.DEPRECIATIONMETHOD = 15 THEN 'No Depreciaton' WHEN FA00200.DEPRECIATIONMETHOD = 16 THEN 'Decline Balance'
END AS DepMethod, dbo.FA00200.ORIGINALLIFEYEARS, dbo.FA00200.ORIGINALLIFEDAYS, dbo.FA00400.ASSETCOSTACCTINDX,
Accounts_2.[Account Number] AS AssetCostAct, dbo.FA00400.DEPREXPACCTINDX, dbo.Accounts.[Account Number] AS DepExpAct, dbo.FA00400.DEPRRESVACCTINDX,
Accounts_1.[Account Number] AS DepResvAct, dbo.Flash_FADepreciationDetails.ASSETQTY
FROM dbo.Flash_FADepreciationDetails LEFT OUTER JOIN
dbo.FA00200 ON dbo.Flash_FADepreciationDetails.ASSETINDEX = dbo.FA00200.ASSETINDEX LEFT OUTER JOIN
dbo.FA00400 ON dbo.Flash_FADepreciationDetails.ASSETINDEX = dbo.FA00400.ASSETINDEX LEFT OUTER JOIN
dbo.Accounts AS Accounts_2 ON dbo.FA00400.ASSETCOSTACCTINDX = Accounts_2.[Account Index] LEFT OUTER JOIN
dbo.Accounts AS Accounts_1 ON dbo.FA00400.DEPRRESVACCTINDX = Accounts_1.[Account Index] LEFT OUTER JOIN
dbo.Accounts ON dbo.FA00400.DEPREXPACCTINDX = dbo.Accounts.[Account Index]
ORDER BY dbo.Flash_FADepreciationDetails.Asset_ID
Thanks
Sandip
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (