This FA Asset Inventory Report - Booked Cost and Depreciation Summary query displays cost, depreciation reserve, and net book value for currently assigned fixed assets, helping users assess asset value and employee allocation.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query displays financial and assignment details of active fixed assets
-- Asset Info
FDH.BOOK_TYPE_CODE BOOK,
FAB.ASSET_NUMBER,
FAB.TAG_NUMBER,
FAB.SERIAL_NUMBER,
FAT.DESCRIPTION ASSET_DESCRIPTION,
DECODE(
FAB.ASSET_TYPE,
'CIP',
'C',
'EXPENSED',
'E',
''
) AS ASSET_TYPE,
SUM(FDH.UNITS_ASSIGNED) UNITS,
-- Employee Info
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNNFV.FULL_NAME EMPLOYEE_NAME,
PU.USERNAME EMPLOYEE_USER_NAME,
-- Assignment Info
PAAM.ASSIGNMENT_NUMBER,
PAAM.ASSIGNMENT_STATUS_TYPE,
PJF.NAME JOB_NAME,
-- GL Information
FL.SEGMENT1 || '.' || FL.SEGMENT2 || '.' || FL.SEGMENT3 LOCATION,
GCC.SEGMENT1 COMPANY,
GCC.SEGMENT2 DEPARTMENT,
GCC.segment1 || '.' || GCC.segment2 || '.' || GCC.segment3 || '.' || GCC.segment4 || '.' || GCC.segment5|| '.' || GCC.segment6 GL_ACCOUNT,
-- Financials
SUM(
DECODE(
FDD.DEPRN_SOURCE_CODE,
'B',
FDD.ADDITION_COST_TO_CLEAR,
FDD.COST
)
) CURRENT_COST,
SUM(FDD.DEPRN_RESERVE) RESERVE,
SUM(
DECODE(
FDD.DEPRN_SOURCE_CODE,
'B',
FDD.ADDITION_COST_TO_CLEAR,
FDD.COST
)
) - SUM(FDD.DEPRN_RESERVE) NET_BOOK_VALUE
FROM
FA_DEPRN_DETAIL FDD,
FA_DISTRIBUTION_HISTORY FDH,
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FAT,
FA_LOCATIONS FL,
GL_CODE_COMBINATIONS GCC,
PER_ALL_PEOPLE_F PAPF,
PER_USERS PU,
PER_PERSON_NAMES_F_V PPNNFV,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_JOBS PJF
WHERE
FDD.ASSET_ID = FDH.ASSET_ID
AND FDD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FDD.DISTRIBUTION_ID = FDH.DISTRIBUTION_ID
AND FDH.ASSET_ID = FAB.ASSET_ID
AND FAT.ASSET_ID = FAB.ASSET_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND FDH.LOCATION_ID = FL.LOCATION_ID
AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND FDH.ASSIGNED_TO = PAPF.PERSON_ID -- Identifier of the employee to whom the asset is assigned.
AND PAPF.PERSON_ID = PU.PERSON_ID
AND PPNNFV.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.PRIMARY_FLAG = 'Y' --If set to 'Yes', record represents the primary assignment associated to the primary Work Relationship and primary set of Employment/Placement Terms.
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y' --Date Effective Entity: 'Y' indicates that this row represents the latest change in the day.
AND PAAM.JOB_ID = PJF.JOB_ID
AND FDH.DATE_EFFECTIVE BETWEEN NVL( PAPF.EFFECTIVE_START_DATE,FDH.DATE_EFFECTIVE)
AND NVL( PAPF.EFFECTIVE_END_DATE,FDH.DATE_EFFECTIVE)
AND SYSDATE BETWEEN PPNNFV.EFFECTIVE_START_DATE AND PPNNFV.EFFECTIVE_END_DATE
AND FDH.DATE_INEFFECTIVE IS NULL
GROUP BY
FAB.ASSET_NUMBER,
FAB.TAG_NUMBER,
FAB.SERIAL_NUMBER,
PAPF.PERSON_NUMBER,
PPNNFV.FULL_NAME,
FAT.DESCRIPTION,
DECODE(
FAB.ASSET_TYPE,
'CIP',
'C',
'EXPENSED',
'E',
''
),
PU.USERNAME,
FL.SEGMENT1 || '.' || FL.SEGMENT2 || '.' || FL.SEGMENT3,
FDH.BOOK_TYPE_CODE,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.segment1 || '.' || GCC.segment2 || '.' || GCC.segment3 || '.' || GCC.segment4 || '.' || GCC.segment5|| '.' || GCC.segment6 ,
FDH.BOOK_TYPE_CODE,
PAAM.ASSIGNMENT_NUMBER,
PAAM.ASSIGNMENT_STATUS_TYPE,
PJF.NAME
ORDER BY PAPF.PERSON_NUMBER,
FAB.ASSET_NUMBER
;