FA Fixed Assets Book – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Assets · all Assets reports

Overview

Assets held in a depreciation book with their cost, depreciation and net book value as at the end of the selected period.

Replaces the Oracle standard RXi: Fixed Assets Book report. The rows are extracted by the same routine its RX-only: Fixed Assets Book data program uses, so cost and depreciation figures are identical. The two seeded RXi attribute sets, Default and Book Details, are shipped as the templates of the same names; each puts the asset detail on the report sheet and the RXi group subtotals on a pivot sheet beside it.

One row per asset distribution, so an asset split over several cost centers or employees appears once per distribution. Units and Percent give the share of the asset in that distribution; Cost and the depreciation amounts are the distribution’s share, not the asset total.

Cost, YTD Depreciation and LTD Depreciation are the balances of the last depreciation run on or before the selected period. Depreciation Amount is filled only where that run is the selected period itself, and is zero for an asset whose last run is earlier.

NBV at Period End is Cost less LTD Depreciation less impairment reserve, so it is lower than Cost less LTD Depreciation for an impaired asset. NBV at Beginning of Fiscal Year adds YTD Depreciation back.

Assets placed in service after the end of the selected period are excluded, as are assets fully retired before the start of the fiscal year that period falls in.

Drill Down into Group Assets adds the member assets of group assets, which are otherwise represented by their group asset alone.

The segment description columns are read in full from the flexfield value sets. The Oracle report prints the bare code for every value after the first one it looks up per value set, and cuts the descriptions it does print to the segment’s display width.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Book, Period, Drill Down into Group Assets, Balancing Segment From, Balancing Segment To, Expense Account From, Expense Account To, Cost Center From, Cost Center To, Major Category From, Major Category To, Minor Category From, Minor Category To, Category Segment, Category Segment Value From, Category Segment Value To, Property Type, Asset Number From, Asset Number To

Oracle EBS Tables Used

fa_asset_keywords_kfv, fa_locations_kfv, fa_lookups_vl, fa_book_controls, fa_deprn_periods, gl_ledgers, fa_reserve_ledger_gt, fa_distribution_history, fa_additions, fa_asset_history, fa_fiscal_year, fa_category_books, gl_code_combinations, fnd_id_flex_segments, fnd_segment_attribute_values, fa_categories_b_kfv, fa_system_controls, fa_categories_vl, fa_books, fa_category_book_defaults, per_all_people_f, fa_transaction_headers

Report Categories

Enginatics, R12 only

FA Asset Upload, FA Asset Book Details, FA Asset Additions, FA Asset Register, FA Asset Transfers, FA Asset Summary (Germany), FA Additions By Source, FA Asset Retirements, FA CIP Assets and Invoices

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import FA_Fixed_Assets_Book.xml
SQL Source FA Fixed Assets Book.sql
Full Report on Enginatics www.enginatics.com/reports/fa-fixed-assets-book/

Case Study & Technical Analysis: FA Fixed Assets Book

Executive Summary

The FA Fixed Assets Book report lists the assets held in one depreciation book, with their cost, depreciation and net book value as at the end of the selected period. Each row is one asset distribution, showing the balancing segment, expense account, cost center, asset category, employee and location. Fixed asset accountants and controllers use it for period-end reconciliation of the asset register to the general ledger, and for the depreciation review that precedes a close. The report is an Enginatics replacement for the standard Oracle RXi: Fixed Assets Book report, and it uses the same extraction routine as the RX-only data program, so cost and depreciation figures are identical. It runs in Excel via Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. FA_BOOK_CONTROLS is joined to GL_LEDGERS on the set of books, and FA_DEPRN_PERIODS supplies the period close date.
  2. FA_RESERVE_LEDGER_GT is joined to FA_ADDITIONS on asset, to FA_DISTRIBUTION_HISTORY on asset, distribution and code combination, and to FA_ASSET_HISTORY on asset.
  3. FA_CATEGORY_BOOKS and FA_BOOKS are joined on the book and asset, with date-effective conditions on each history row.
  4. The fiscal year joins FA_BOOK_CONTROLS and tests the placed-in-service date against the fiscal year start and end.
  5. Segment descriptions are resolved by inline views over GL_CODE_COMBINATIONS and the category flexfield, using the segment role columns.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does an asset appear more than once in the FA Fixed Assets Book report? A: The report returns one row per asset distribution, so an asset split across several cost centers or employees appears once for each distribution, with its share of units and percent.

Q: Why is depreciation amount zero for some assets? A: The depreciation amount is filled only where the last depreciation run is the selected period itself, so an asset whose last run is earlier shows zero.

Q: How is net book value at period end calculated? A: It is cost less life-to-date depreciation less the impairment reserve, so an impaired asset has a lower value than cost less depreciation alone.

© Enginatics GmbH