Monday, 7 July 2025

Oracle Fusion Fixed Assets Tables – Key Tables and Sample Queries

Oracle Fusion Fixed Assets (FA) is a comprehensive module used for managing an organization's fixed assets lifecycle—from acquisition to retirement. 

For technical developers and report builders, understanding the underlying Fusion FA tables is crucial to extract and analyze data accurately.

In this blog, we will dive into:

  • Key Oracle Fusion Fixed Assets Tables

  • Table Relationships

  • Sample SQL Queries

  • Real-world Use Cases

Key Oracle Fusion Fixed Assets Tables

Table NameDescription
FA_ASSET_HISTORYStores asset-level information like asset number, description, and tag.
FA_BOOKSMaintains asset financial details per asset book (e.g., depreciation method, cost, YTD depreciation).
FA_CATEGORIES_BContains asset category definitions.
FA_ADDITIONS_BUsed during the asset addition process.
FA_DISTRIBUTION_HISTORYTracks accounting and distribution information by cost center/project.
FA_RETIREMENTSRecords asset retirement data.
FA_DEPRN_SUMMARYStores summarized depreciation data for each asset per period.
FA_LOCATIONSHolds the location details where assets are deployed.

Table Relationships with Other tables

  • FA_ASSET_HISTORY ➝ Asset core info
    joins to FA_BOOKS on ASSET_ID
    joins to FA_CATEGORIES_B on CATEGORY_ID
    joins to FA_LOCATIONS on LOCATION_ID
    joins to FA_DISTRIBUTION_HISTORY for cost center info
    joins to FA_DEPRN_SUMMARY for depreciation data

Sample SQL Queries

Below are some of the Sample Queries.

1. Basic Asset Details

SELECT 
  fah.asset_number,
  fah.description,
  fb.book_type_code,
  fb.date_placed_in_service,
  fb.cost,
  fb.ytd_deprn,
  fcb.category_description,
  fl.location_description
FROM
  fa_asset_history fah,
  fa_books fb,
  fa_categories_b fcb,
  fa_locations fl 
WHERE fah.asset_id = fb.asset_id
AND fb.category_id = fcb.category_id
AND fah.location_id = fl.location_id
AND fb.book_type_code = 'CORPORATE'
  AND fb.date_placed_in_service BETWEEN TO_DATE('2024-01-01','YYYY-MM-DD') AND TO_DATE('2024-12-31','YYYY-MM-DD');

2. Asset Retirement Report

SELECT 
  fah.asset_number,
  fb.book_type_code,
  fr.retirement_date,
  fr.retirement_type_code,
  fr.proceeds_of_sale,
  fr.gain_loss
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_retirements fr  
WHERE fah.asset_id = fb.asset_id
AND fb.asset_id = fr.asset_id
AND 
  fr.retirement_date >= TO_DATE('2024-01-01','YYYY-MM-DD');

3. Depreciation Summary for a Period

SELECT 
  fah.asset_number,
  fb.book_type_code,
  fds.period_name,
  fds.deprn_amount,
  fds.ytd_deprn
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_deprn_summary fds
WHERE fah.asset_id = fb.asset_id
AND  fb.asset_id = fds.asset_id
AND 
  fds.period_name = 'JUN-24'
  AND fb.book_type_code = 'CORPORATE';

4. Assets by Cost Center or Project


SELECT 
  fah.asset_number,
  fb.cost,
  fdh.code_combination_id,
  gl.segment1 AS cost_center
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_distribution_history fdh,
  gl_code_combinations gl  
WHERE fah.asset_id = fb.asset_id
AND fb.asset_id = fdh.asset_id
AND fdh.code_combination_id = gl.code_combination_id
AND 
  gl.segment1 = '1001'; -- Replace with your cost center

Use Cases for These Queries

  • Internal Audits: Asset existence, placement, depreciation status

  • Finance Reporting: Capital expenditure, asset additions/retirements

  • Cost Allocation: Cost center or project-based distribution

  • Fixed Asset Reconciliation: Verify system balances with physical checks

Notes : 

  • Always validate the Book Type Code (CORPORATE, TAX, etc.).

  • Use Asset Status to filter retired, transferred, or fully depreciated assets.


Posted by at