This FA Leased Assets Report query displays financial and operational details of leased fixed assets over their active depreciation period, helping users to manage compliance, cost tracking, and reporting.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Shows leased asset details including cost and depreciation.
FAB.ASSET_NUMBER ASSET_NUMBER,
FAT.DESCRIPTION ASSET_DESCRIPTION,
FAB.ASSET_TYPE,
FAB.TAG_NUMBER, --Unique user-defined tracking number for an asset.
FAB.MANUFACTURER_NAME, --Name of the manufacturer of the asset.
FAB.SERIAL_NUMBER, --Serial number assigned to the asset.
FAB.MODEL_NUMBER, --Model number assigned to the asset.
FAB.PROPERTY_TYPE_CODE PROPERTY_TYPE, --Option indicating whether the asset is considered personal, real, or residential property.
FAB.NEW_USED, --Option indicating whether asset is new or used.
FAB.IN_USE_FLAG IN_USE, --Indicator used to determine whether the asset is in use
FAB.CREATION_DATE ASSET_CREATION_DATE,
FAB.CREATED_BY,
FAB.INVENTORIAL, --Indicates whether the asset should be included in physical inventory.
-- Lease Information
FL.LEASE_NUMBER, --User-defined lease number of the lease.
FL.DESCRIPTION LEASE_DESCRIPTION,
FL.FASB_LEASE_TYPE LEASE_TYPE,
FL.ASSET_LIFE, --Expected useful life of the asset.
FL.LEASE_TERM, --Stipulated length of the lease contract.
FL.TRANSFER_OWNERSHIP, --Ownership transfer at the end of lease contract.
-- Depreciation & Costing Details
FDD.PERIOD_COUNTER, --Identifier of the depreciation period.
FDD.DEPRN_RUN_DATE DEPRECIATION_RUN_DATE, --System date when depreciation was run.
FDD.DEPRN_AMOUNT DEPRECIATION_AMOUNT,
FDD.ADDITION_COST_TO_CLEAR,
(
DECODE (
FDD.DEPRN_SOURCE_CODE,
'D',
FDD.COST,
FDD.ADDITION_COST_TO_CLEAR
)
) COST,
FDD.DEPRN_RESERVE DEPRECIATION_RESERVE,
(FDD.COST - FDD.DEPRN_RESERVE) NET_BOOK_VALUE,
-- Book Information
FB.DATE_PLACED_IN_SERVICE,
FB.DEPRN_START_DATE DEPRECIATION_START_DATE,
GL.CURRENCY_CODE,
GL.ALC_LEDGER_TYPE_CODE , --Reporting currency type (NONE, TARGET, and SOURCE).
FB.ORIGINAL_COST,
FB.ADJUSTED_COST,
FB.RECOVERABLE_COST,
FB.DEPRECIATE_FLAG,
FBC.BOOK_TYPE_CODE,
FBC.BOOK_TYPE_NAME,
FBC.BOOK_CLASS,
FBC.DISTRIBUTION_SOURCE_BOOK,
-- Category & Account Information
FCB.SEGMENT1 || '.' || FCB.SEGMENT2 CATEGORY,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) GL_ACCOUNT
FROM
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FAT,
FA_BOOK_CONTROLS FBC,
GL_LEDGERS GL,
FA_LEASES FL,
FA_DEPRN_DETAIL FDD,
FA_CATEGORIES_B FCB,
FA_BOOKS FB,
FA_DISTRIBUTION_HISTORY FDH,
GL_CODE_COMBINATIONS GCC
WHERE
FAB.ASSET_ID = FAT.ASSET_ID
AND FL.LEASE_ID = FAB.LEASE_ID
AND (
FCB.OWNED_LEASED = 'LEASED' --Indicates whether the assets in this category are owned or leased
OR FCB.CATEGORY_TYPE = 'LEASE' --Indicates the category type Oracle Assets uses to classify assets in this asset category
)
AND FAT.LANGUAGE = USERENV('LANG')
AND FAB.ASSET_CATEGORY_ID = FCB.CATEGORY_ID
AND FB.ASSET_ID = FAB.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDH.ASSET_ID = FB.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FDD.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GL.LEDGER_ID = FBC.SET_OF_BOOKS_ID
AND FDD.PERIOD_COUNTER = (
SELECT
MAX (DD_SUB.PERIOD_COUNTER)
FROM
FA_DEPRN_DETAIL DD_SUB
WHERE
DD_SUB.ASSET_ID = FAB.ASSET_ID
AND DD_SUB.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND DD_SUB.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
)
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE