This FA Asset Disposal Report query displays asset disposal and retirement financial details for fixed assets over relevant depreciation periods to analyze disposal impacts and asset retirement costs.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Retrieves detailed asset disposal and retirement financial data.
-- Asset Identification
FAB.ASSET_NUMBER ,
FAB.TAG_NUMBER ,
FAB.SERIAL_NUMBER ,
FB.DATE_PLACED_IN_SERVICE,
-- Organizational Details
FDH.BOOK_TYPE_CODE,
FDP.PERIOD_NAME,
GCC.SEGMENT2 COST_CENTRE,
-- Transaction Details
ABS(FDH.TRANSACTION_UNITS) UNITS,
FR.DATE_RETIRED,
GSOB.CURRENCY_CODE CURRENCY,
MIN(
ROUND(
DECODE(
FTH.TRANSACTION_TYPE_CODE,
'TRANSFER',
(
FB.COST * ABS(FDH.TRANSACTION_UNITS) / FAH.UNITS
),
(
NVL(FMR.COST_RETIRED, FR.COST_RETIRED) / FR.UNITS * ABS(FDH.TRANSACTION_UNITS)
)
),
(
SELECT
FC.PRECISION
FROM
FA_BOOK_CONTROLS FBC,
GL_SETS_OF_BOOKS GSOB,
FND_CURRENCIES FC
WHERE
FBC.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND GSOB.SET_OF_BOOKS_ID = FBC.SET_OF_BOOKS_ID
AND GSOB.CURRENCY_CODE = FC.CURRENCY_CODE
)
)
) DISPOSED_COST,
-- Retirement Details
FMR.COST_RETIRED,
FMR.NBV_RETIRED,
FMR.GAIN_LOSS_AMOUNT,
FMR.PROCEEDS_OF_SALE,
-- Financial Metrics
NVL(FMR.PROCEEDS_OF_SALE, 0) NET_PROCEEDS,
NVL(FMR.GAIN_LOSS_AMOUNT, 0) REALIZED_GAIN_LOSS,
MIN(FDD.DEPRN_RESERVE) DEPRECIATION_RESERVED,
MIN(FDD.COST) ACQUIRED_VALUE,
-- Net Book Value
MIN(
ROUND(
DECODE(
FTH.TRANSACTION_TYPE_CODE,
'TRANSFER',
(
FB.COST * ABS(FDH.TRANSACTION_UNITS) / FAH.UNITS - (
NVL(FDD.DEPRN_RESERVE, 0) * ABS(FDH.TRANSACTION_UNITS) / FDH.UNITS_ASSIGNED
)
),
(
NVL(FMR.NBV_RETIRED, NVL(FR.NBV_RETIRED, 0)) / FR.UNITS * ABS(FDH.TRANSACTION_UNITS)
)
),
(
SELECT
FC.PRECISION
FROM
FA_BOOK_CONTROLS FBC,
GL_SETS_OF_BOOKS GSOB,
FND_CURRENCIES FC
WHERE
FBC.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND GSOB.SET_OF_BOOKS_ID = FBC.SET_OF_BOOKS_ID
AND GSOB.CURRENCY_CODE = FC.CURRENCY_CODE
)
)
) NET_BOOK_VALUE,
-- Gain/Loss
MIN(
ROUND(
DECODE(
FTH.TRANSACTION_TYPE_CODE,
'TRANSFER',
(
-1 * (
FB.COST * ABS(FDH.TRANSACTION_UNITS) / FAH.UNITS - (
NVL(FDD.DEPRN_RESERVE, 0) * ABS(FDH.TRANSACTION_UNITS) / FDH.UNITS_ASSIGNED
)
)
),
(
NVL(
FMR.GAIN_LOSS_AMOUNT,
NVL(FR.GAIN_LOSS_AMOUNT, 0)
) / FR.UNITS * ABS(FDH.TRANSACTION_UNITS)
)
),
(
SELECT
FC.PRECISION
FROM
FA_BOOK_CONTROLS FBC,
GL_SETS_OF_BOOKS GSOB,
FND_CURRENCIES FC
WHERE
FBC.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND GSOB.SET_OF_BOOKS_ID = FBC.SET_OF_BOOKS_ID
AND GSOB.CURRENCY_CODE = FC.CURRENCY_CODE
)
)
) GAIN_LOSS,
-- Transaction Type Flag
MIN(
DECODE(
FTH.TRANSACTION_TYPE_CODE,
'TRANSFER OUT',
DECODE(FR.TRANSACTION_HEADER_ID_OUT, NULL, 'P', '*'),
'TRANSFER',
'T',
DECODE(FR.TRANSACTION_HEADER_ID_OUT, NULL, NULL, '*')
)
) TYPE
FROM
FA_MC_RETIREMENTS FMR,
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_DISTRIBUTION_HISTORY FDH,
FA_RETIREMENTS FR,
FA_TRANSACTION_HEADERS FTH,
FA_ASSET_HISTORY FAH,
FA_DEPRN_PERIODS FDP,
FA_DISTRIBUTION_HISTORY FDH2,
FA_LOCATIONS FL,
FA_DEPRN_DETAIL FDD,
FA_BOOK_CONTROLS FBC,
GL_SETS_OF_BOOKS GSOB,
GL_CODE_COMBINATIONS GCC,
PER_ALL_PEOPLE_F PAPF
WHERE 1=1
AND FTH.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FAH.ASSET_ID = FTH.ASSET_ID
AND FDH.ASSET_ID = FTH.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FMR.RETIREMENT_ID = FR.RETIREMENT_ID
AND FDH.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID
AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FDH2.TRANSACTION_HEADER_ID_IN = FDH.TRANSACTION_HEADER_ID_IN
AND FAB.ASSET_ID = FDH.ASSET_ID
AND FB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FB.ASSET_ID = FTH.ASSET_ID
AND FL.LOCATION_ID = FDH.LOCATION_ID
AND PAPF.PERSON_ID = FDH.ASSIGNED_TO
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND FBC.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND GSOB.SET_OF_BOOKS_ID = FBC.SET_OF_BOOKS_ID
AND FB.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND FTH.DATE_EFFECTIVE BETWEEN (
SELECT PERIOD_OPEN_DATE
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND PERIOD_NAME = FDP.PERIOD_NAME
)
AND (
SELECT NVL(PERIOD_CLOSE_DATE, SYSDATE)
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND PERIOD_NAME = FDP.PERIOD_NAME
)
AND FTH.TRANSACTION_TYPE_CODE IN ('FULL RETIREMENT', 'TRANSFER', 'TRANSFER OUT')
AND FTH.TRANSACTION_HEADER_ID BETWEEN FAH.TRANSACTION_HEADER_ID_IN AND NVL(FAH.TRANSACTION_HEADER_ID_OUT, FTH.TRANSACTION_HEADER_ID)
AND NVL(FDH.TRANSACTION_HEADER_ID_OUT, 0) = DECODE(
FTH.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', NVL(FDH.TRANSACTION_HEADER_ID_OUT, 0),
FTH.TRANSACTION_HEADER_ID
)
AND NVL(FDH.TRANSACTION_UNITS, 0) < 0
AND FR.DATE_EFFECTIVE BETWEEN (
SELECT PERIOD_OPEN_DATE
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND PERIOD_NAME = FDP.PERIOD_NAME
)
AND (
SELECT NVL(PERIOD_CLOSE_DATE, SYSDATE)
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND PERIOD_NAME = FDP.PERIOD_NAME
)
AND ( FDH2.CODE_COMBINATION_ID != FDH.CODE_COMBINATION_ID
OR FTH.TRANSACTION_TYPE_CODE != 'TRANSFER' )
AND FTH.TRANSACTION_HEADER_ID > FB.TRANSACTION_HEADER_ID_IN
AND FTH.TRANSACTION_HEADER_ID <= NVL(FB.TRANSACTION_HEADER_ID_OUT, FTH.TRANSACTION_HEADER_ID)
GROUP BY
FMR.NBV_RETIRED,
FMR.GAIN_LOSS_AMOUNT,
FMR.PROCEEDS_OF_SALE,
FAB.SERIAL_NUMBER,
FAB.TAG_NUMBER,
FAB.ASSET_NUMBER,
FB.DATE_PLACED_IN_SERVICE,
FDH.BOOK_TYPE_CODE,
ABS(FDH.TRANSACTION_UNITS),
FR.DATE_RETIRED,
FMR.COST_RETIRED,
FDP.PERIOD_NAME,
GCC.SEGMENT2,
GSOB.CURRENCY_CODE
ORDER BY FAB.ASSET_NUMBER ;