FA Asset Inventory 11i – 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

Imported Oracle standard asset inventory report Source: Asset Inventory Report (XML) Short Name: FAS410_XML DB package: FA_FAS410_XMLP_PKG

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, From Cost Center, To Cost Center, From Date Placed in Service, To Date Placed in Service

Oracle EBS Tables Used

fa_deprn_periods, fa_book_controls, gl_sets_of_books, fa_deprn_summary, fa_system_controls, fa_deprn_detail, per_people_f, fa_additions, fa_locations, gl_code_combinations, fa_books, fa_distribution_history, fa_adjustments, fa_lookups

Report Categories

Enginatics

FA Asset Register, FA Asset Upload, FA Asset Additions, FA Asset Transfers, FA Asset Inventory, FA Fixed Assets Book, FA Asset Book Details, FA Asset Summary (Germany)

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_Asset_Inventory_11i.xml
SQL Source FA Asset Inventory 11i.sql
Full Report on Enginatics www.enginatics.com/reports/fa-asset-inventory-11i/

Case Study & Technical Analysis: FA Asset Inventory 11i

Executive Summary

FA Asset Inventory 11i reproduces the Oracle Assets standard Asset Inventory Report (FAS410XML) as a spreadsheet. It lists the fixed assets of a depreciation book with their cost, reserve and net book value, their location, cost center, responsible employee, units, serial and tag numbers and whether the asset was placed in service within the chosen date range. Fixed asset accountants, asset managers and controllers use it for physical verification, year-end asset reconciliations and reviews of assets assigned to each cost center.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. The book-level subqueries in FA_SYSTEM_CONTROLS derive the current period, the accounting structure and the book class from FA_BOOK_CONTROLS and FA_DEPRN_PERIODS.
  2. FA_DISTRIBUTION_HISTORY is joined to FA_ADDITIONS, FA_LOCATIONS and GL_CODE_COMBINATIONS on asset, location and code combination, with only active distributions.
  3. FA_DEPRN_DETAIL is joined on asset and distribution and restricted to the latest period counter for each asset.
  4. FA_BOOKS is joined on asset and limited to assets in the selected book that are not fully retired.
  5. PER_PEOPLE_F is joined as an outer join on the assigned employee, effective on the distribution date.
  6. The second branch joins FA_ADJUSTMENTS to FA_LOOKUPS and the distribution history, keyed to the current period counter.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How is net book value calculated in this report? A: Net book value is the cost of the asset minus the depreciation reserve, both summed from the depreciation and adjustment records of the selected book.

Q: Which period does the report use for cost and reserve? A: It uses the latest period counter for each distribution, so the values reflect the current open period of the selected book.

Q: How do I list only assets placed in service in a given quarter? A: Enter the start and end dates in the From and To Date Placed in Service parameters. The New column marks assets whose placed-in-service date falls within that range.

© Enginatics GmbH