This FA Non-Depreciating Property Report query displays details of capitalized fixed assets that are not depreciated, providing financial and asset classification data for fixed asset oversight.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Lists capitalized fixed assets not subject to depreciation
FBC.BOOK_TYPE_CODE,
FBC.BOOK_CLASS,
FBC.ACCOUNTING_FLEX_STRUCTURE,
FBC.DISTRIBUTION_SOURCE_BOOK,
FC.PRECISION,
-- Company and accounting segment
NVL(GCC.SEGMENT1, 'NONE') COMPANY_CODE,
-- Asset identification details
FAB.ASSET_NUMBER,
FAB.SERIAL_NUMBER,
-- Asset classification
FAB.ASSET_TYPE,
FCB.SEGMENT1 || '-' || FCB.SEGMENT2 CATEGORY,
-- Asset lifecycle info
FB.DATE_PLACED_IN_SERVICE START_DATE,
-- Capitalized cost calculation
GSOB.CURRENCY_CODE CURRENCY,
ROUND(
SUM(
FB.COST * NVL(FDH.UNITS_ASSIGNED, FAH.UNITS) / FAH.UNITS
),
FC.PRECISION
) COST
FROM
FA_BOOK_CONTROLS FBC,
FA_BOOKS FB,
FA_ADDITIONS_B FAB,
FA_ASSET_HISTORY FAH,
FA_CATEGORIES_B FCB,
FA_DISTRIBUTION_HISTORY FDH,
GL_CODE_COMBINATIONS GCC,
GL_SETS_OF_BOOKS GSOB,
FND_CURRENCIES FC
WHERE
1 = 1
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND FB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FB.CAPITALIZE_FLAG = 'YES'
AND FB.DEPRECIATE_FLAG = 'NO'
AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FAB.ASSET_ID = FB.ASSET_ID
AND FCB.CATEGORY_ID = FAB.ASSET_CATEGORY_ID
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND FBC.DISTRIBUTION_SOURCE_BOOK = FDH.BOOK_TYPE_CODE
AND FB.ASSET_ID = FDH.ASSET_ID(+)
AND FDH.DATE_INEFFECTIVE IS NULL
AND GCC.CODE_COMBINATION_ID(+) = FDH.CODE_COMBINATION_ID
AND FAH.ASSET_ID = FB.ASSET_ID
AND FAH.DATE_INEFFECTIVE IS NULL
AND GSOB.SET_OF_BOOKS_ID = FBC.SET_OF_BOOKS_ID
AND FC.CURRENCY_CODE = GSOB.CURRENCY_CODE
GROUP BY
NVL(GCC.SEGMENT1, 'NONE'),
FAB.ASSET_TYPE,
FAB.ASSET_NUMBER,
FAB.SERIAL_NUMBER,
FB.DATE_PLACED_IN_SERVICE,
FBC.BOOK_TYPE_CODE,
FBC.BOOK_CLASS,
FBC.ACCOUNTING_FLEX_STRUCTURE,
FBC.DISTRIBUTION_SOURCE_BOOK,
GSOB.CURRENCY_CODE,
FC.PRECISION,
FCB.SEGMENT1 || '-' || FCB.SEGMENT2,
FAB.ASSET_ID
ORDER BY
1,
2,
3,
4