This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script returns a list of active fixed assets including the linked G/L Accounts.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
['Asset General Information Master'].ASSETID
,['Asset General Information Master'].ASSETIDSUF
,['Asset General Information Master'].SHRTNAME
,['Asset General Information Master'].ASSETDESC
,['Asset General Information Master'].EXTASSETDESC
,FORMAT(GETDATE(), 'dd/MM/yyyy') AS DATEFOREVERYTHING
,['Asset General Information Master'].ASSETCLASSID
,['Asset General Information Master'].LOCATNID
,['Asset General Information Master'].Physical_Location_ID
,['Asset General Information Master'].ASSETQTY
,['Asset Book Master'].REMAININGLIFEYEARS
,['Asset Book Master'].REMAININGLIFEDAYS
,['Asset Book Master'].COSTBASIS
,['Asset Book Master'].DEPRECIATIONMETHOD
,['Asset Book Master'].AVERAGINGCONV
,['Asset Book Master'].SWITCHOVER
,['Account Index Master - Depreciation Expense Account'].ACTNUMST AS 'Depreciation Expense Account'
,['Account Index Master - Depreciation Reserve Account'].ACTNUMST AS 'Depreciation Reserve Account'
,['Account Index Master - Prior Year Depreciation Account'].ACTNUMST AS 'Prior Year Depreciation Account'
,['Account Index Master - Asset Cost Account'].ACTNUMST AS 'Asset Cost Account'
,['Account Index Master - Proceeds Account'].ACTNUMST AS 'Proceeds Account'
,['Account Index Master - Recognized Gain Loss Account'].ACTNUMST AS 'Recognized Gain Loss Account'
,['Account Index Master - Non Recognized Gain Loss Account'].ACTNUMST AS 'Non Recognized Gain Loss Account'
,['Account Index Master - Clearing Account'].actnumst AS 'Clearing Account'
FROM
FA00100 AS ['Asset General Information Master'] --Asset General Information Master (FA00100)
LEFT JOIN
FA00200 AS ['Asset Book Master'] --Asset Book Master (FA00200)
ON
['Asset Book Master'].[ASSETINDEX] = ['Asset General Information Master'].[ASSETINDEX]
LEFT JOIN
FA19900 AS ['User Data Master'] --User Data Master (FA19900)
ON
['User Data Master'].[ASSETINDEX] = ['Asset General Information Master'].[ASSETINDEX]
LEFT JOIN
FA00400 AS ['Asset Account Master'] --Asset Account Master (FA00400)
ON
['Asset Account Master'].[ASSETINDEX] = ['Asset General Information Master'].[ASSETINDEX]
LEFT JOIN
GL00105 AS ['Account Index Master - Depreciation Expense Account'] --Account Index Master (GL00105)
ON
['Account Index Master - Depreciation Expense Account'].ACTINDX = ['Asset Account Master'].DEPREXPACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Depreciation Reserve Account']
ON
['Account Index Master - Depreciation Reserve Account'].ACTINDX = ['Asset Account Master'].DEPRRESVACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Prior Year Depreciation Account']
ON
['Account Index Master - Prior Year Depreciation Account'].ACTINDX = ['Asset Account Master'].PRIORYRDEPRACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Asset Cost Account']
ON
['Account Index Master - Asset Cost Account'].ACTINDX = ['Asset Account Master'].ASSETCOSTACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Proceeds Account']
ON
['Account Index Master - Proceeds Account'].ACTINDX = ['Asset Account Master'].PROCEEDSACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Recognized Gain Loss Account']
ON
['Account Index Master - Recognized Gain Loss Account'].ACTINDX = ['Asset Account Master'].RECGAINLOSSACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Non Recognized Gain Loss Account']
ON
['Account Index Master - Non Recognized Gain Loss Account'].ACTINDX = ['Asset Account Master'].NONRECGAINLOSSACCTINDX
LEFT JOIN
GL00105 AS ['Account Index Master - Clearing Account']
ON
['Account Index Master - Clearing Account'].ACTINDX = ['Asset Account Master'].CLEARINGACCTINDX
WHERE
['Asset General Information Master'].ASSETSTATUS = 1 --Active
ORDER BY
['Asset General Information Master'].ASSETIDSUF
Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.