This FA Assets Transfers query displays fixed asset transfer transactions with associated cost and depreciation details for specified periods to track asset movements and financial impacts.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Fetches fixed asset transfer details with related cost and location data
-- Book and Ledger Information
FBC.BOOK_TYPE_NAME BOOK_TYPE_NAME,
FBC.BOOK_TYPE_CODE BOOK_TYPE, --Unique book identification name.
FBC.BOOK_CLASS BOOK_CLASS,
GL.NAME LEDGER,
GL.ALC_LEDGER_TYPE_CODE ADVANCED_LEDGER_CONFIGURATION_TYPE, --Reporting currency type (NONE, TARGET, and SOURCE).
-- Asset Information
FAB.ASSET_TYPE ASSET_TYPE,
FAB.ASSET_NUMBER ASSET_NUMBER,
FAB.TAG_NUMBER TAG_NUMBER,
FAB.MANUFACTURER_NAME MANUFACTURER_NAME,
FAB.SERIAL_NUMBER SERIAL_NUMBER,
FAB.MODEL_NUMBER MODEL_NUMBER,
FAB.PROPERTY_TYPE_CODE PROPERTY_TYPE_CODE,
FAB.IN_USE_FLAG IN_USE_FLAG,
FAB.OWNED_LEASED OWNED_LEASED,
FAB.NEW_USED NEW_USED,
FAB.INVENTORIAL INVENTORIAL,
-- Transaction Information
FTH.TRANSACTION_NAME TRANSACTION_NAME,
FTH.TRANSACTION_TYPE_CODE TRANSACTION_TYPE,
FTH.TRANSACTION_DATE_ENTERED TRANSFERRED_DATE,
FTH.TRANSACTION_DATE_ENTERED START_DATE,
DECODE (
(
DECODE (
FTH.TRANSACTION_HEADER_ID,
FDH.TRANSACTION_HEADER_ID_IN, 1,
FDH.TRANSACTION_HEADER_ID_OUT, 0
)
),
1, 'TO',
0, 'FROM'
) TRANSFER_DIRECTION,
-- Depreciation / Period Details
FDP.PERIOD_NAME PERIOD_NAME,
FDP.PERIOD_COUNTER PERIOD_COUNTER,
FDP.PERIOD_NUM PERIOD_NUMBER,
FDP.FISCAL_YEAR FISCAL_YEAR,
-- Transfer Financials
GL.CURRENCY_CODE CURRENCY_CODE,
NVL (
DECODE (
FA.ADJUSTMENT_TYPE,
'RESERVE', NULL,
NVL (FA.ADJUSTMENT_AMOUNT, 0) * DECODE (
FA.DEBIT_CREDIT_FLAG,
'CR', -1,
'DR', 1
)
),
0
) COST_TRANSFERRED,
NVL (
DECODE (
FA.ADJUSTMENT_TYPE,
'RESERVE',
NVL (FA.ADJUSTMENT_AMOUNT, 0) * DECODE (
NVL (FA.DEBIT_CREDIT_FLAG, 'CR'),
'CR', 1,
'DR', -1
),
NULL
),
0
) DEPRECIATION_RESERVE_TRANSFERRED,
(
DECODE (
FTH.TRANSACTION_HEADER_ID,
FDH.TRANSACTION_HEADER_ID_IN, 1,
FDH.TRANSACTION_HEADER_ID_OUT, -1
)
) * FDH.UNITS_ASSIGNED UNITS,
-- Location and Legal Entity
FL.SEGMENT1 STATE,
FL.SEGMENT2 LEGAL_ENTITY,
FL.SEGMENT3 LOCATION,
(
FL.SEGMENT1 || '.' || FL.SEGMENT2 || '.' || FL.SEGMENT3
) LOCATION_DETAILS,
-- Accounting Info
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
-- Assignment / User Info
PU.USERNAME ASSIGNED_TO_USER,
PAPF.PERSON_NUMBER PERSON_NUMBER
FROM
FA_ADDITIONS_B FAB,
FA_BOOK_CONTROLS FBC,
GL_LEDGERS GL,
FA_TRANSACTION_HEADERS FTH,
FA_DEPRN_PERIODS FDP,
FA_DISTRIBUTION_HISTORY FDH,
FA_ADJUSTMENTS FA,
FA_LOCATIONS FL,
PER_ALL_PEOPLE_F PAPF,
GL_CODE_COMBINATIONS GCC,
PER_USERS PU
WHERE
1 = 1
AND PAPF.PERSON_ID = PU.PERSON_ID
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND FAB.ASSET_ID = FTH.ASSET_ID
AND ( FTH.TRANSACTION_HEADER_ID = FDH.TRANSACTION_HEADER_ID_IN OR FTH.TRANSACTION_HEADER_ID = FDH.TRANSACTION_HEADER_ID_OUT )
AND FTH.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FL.LOCATION_ID = FDH.LOCATION_ID
AND PAPF.PERSON_ID = FDH.ASSIGNED_TO
AND FA.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
AND FTH.TRANSACTION_TYPE_CODE = 'TRANSFER' --Moves the asset from one location, cost center, department, or employee to another
AND FTH.DATE_EFFECTIVE >= FDP.PERIOD_OPEN_DATE
AND FTH.DATE_EFFECTIVE <= NVL (FDP.PERIOD_CLOSE_DATE, SYSDATE)
AND FTH.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FTH.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FA.ASSET_ID = FTH.ASSET_ID
AND FTH.BOOK_TYPE_CODE = FA.BOOK_TYPE_CODE
AND FA.TRANSACTION_HEADER_ID = FTH.TRANSACTION_HEADER_ID
AND FA.SOURCE_TYPE_CODE = FTH.TRANSACTION_TYPE_CODE
AND FTH.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND GL.LEDGER_ID = FBC.SET_OF_BOOKS_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN FDH.DATE_EFFECTIVE AND NVL(FDH.DATE_INEFFECTIVE,SYSDATE)
AND ( FA.ADJUSTMENT_TYPE IN ('COST', 'CIP COST') -- Include only cost-related and CIP cost adjustments
OR FA.ADJUSTMENT_TYPE = 'RESERVE' -- Include depreciation reserve adjustments
)
;