FA Asset Cost – 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
Asset Costs Summary/Detail Report
Equivalent to Oracle Standard Reports: Cost Summary Report Cost Detail Report
DB package: XXEN_FA_FAS_XMLP
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
Set of Books, Book, From Period, To Period, Show Depreciation Reserve
Oracle EBS Tables Used
msc_form_query, fa_system_controls, gl_ledgers, fnd_currencies, fa_asset_history, fa_category_books, fa_additions, gl_code_combinations, fa_additions_b, fa_asset_keywords_kfv
Report Categories
Related Reports
FA Depreciation Reserve, FA Revaluation Reserve, FA CIP Cost, FA Asset Additions, FA Cost Adjustments, FA Asset Summary (Germany), FA Fixed Assets Book, FA Asset Book Details, FA Asset Register
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 | FA Asset Cost - Asset Cost Detail 20-Jul-2023 032253.xlsx |
| Blitz Report™ XML Import | FA_Asset_Cost.xml |
| SQL Source | FA Asset Cost.sql |
| Full Report on Enginatics | www.enginatics.com/reports/fa-asset-cost/ |
Case Study & Technical Analysis: FA Asset Cost Report
Executive Summary
The FA Asset Cost report is the definitive tool for reconciling Fixed Asset subledger balances. It provides a detailed “Roll Forward” analysis of asset costs, tracking movements from the beginning balance through additions, adjustments, retirements, revaluations, and transfers to the ending balance. This report is indispensable for Period Close activities and external audits, ensuring that the Fixed Assets register aligns perfectly with the General Ledger.
Business Challenge
Reconciling Fixed Assets is often a manual and error-prone process:
- Static Reporting: Standard Oracle “Cost Summary” and “Cost Detail” reports are PDF-based or text-based, making it difficult to perform variance analysis in Excel.
- Data Integrity: Identifying specific assets that cause out-of-balance conditions between the subledger and GL requires tedious line-by-line comparison.
- Fragmented View: Users often have to run separate reports for Cost and Depreciation Reserve, making it hard to see the net book value impact in one place.
The Solution
This report modernizes the standard asset reconciliation process by extracting the core data into a flat, pivot-ready format.
- Automated Roll-Forward: Clearly displays the formula:
Beginning Balance + Additions + Adjustments - Retirements +/- Transfers = Ending Balance. - Exception Handling: Automatically calculates and highlights “Out of Balance” amounts, allowing accountants to instantly focus on problem assets.
- Unified Detail: Combines the high-level summary with asset-level details (Asset Number, Description, Cost Center), eliminating the need to cross-reference multiple reports.
Technical Architecture (High Level)
The report leverages Oracle’s standard reconciliation logic to ensure 100% accuracy while enhancing the data presentation.
Primary Tables
FA_BALANCES_REPORT_GT: A Global Temporary Table that acts as the primary data source. It is populated by the standard Oracle logic (similar to the standard Cost Summary Report) before the query runs.FA_ADDITIONS: Provides master data like Asset Number and Description.FA_ASSET_HISTORY: Used to track historical changes to asset distributions (Cost Centers).GL_CODE_COMBINATIONS: Decodes the accounting flexfields for Balancing Segments and Asset Accounts.
Logical Relationships
- Standard Logic Wrapper: The report calls a PL/SQL package (
XXEN_FA_FAS_XMLPor standardFA_FASCOSTS_XMLP_PKG) to populate the temporary table. This ensures that the numbers reported match the standard Oracle reports exactly. - Pivot Logic: The SQL uses
SUM(DECODE(source_type_code, ...))to transform the transactional rows in the temporary table (which stores movements as separate rows) into a single row per asset with columns for each movement type (Beginning, Addition, Retirement, etc.). - Dynamic Flexfields: It uses
fnd_flex_xml_publisher_apis.process_kff_combination_1to dynamically retrieve segment values (like Cost Center) based on the client’s specific Chart of Accounts setup.
Parameters & Filtering
- Book: Selects the Depreciation Book (Corporate, Tax, etc.).
- From Period / To Period: Defines the reconciliation range.
- Show Depreciation Reserve: A powerful option that, when enabled, adds columns for Depreciation Reserve movements, effectively turning the report into a full “Net Book Value” roll forward.
Performance & Optimization
- Leveraging Standard Code: By using the standard Oracle temporary table population, the report guarantees consistency with official records.
- Aggregation: The query aggregates data at the Asset and Cost Center level, reducing the volume of data transferred to Excel while maintaining sufficient detail for reconciliation.
FAQ
Q: Why does this report match the standard Oracle Cost Summary Report? A: It uses the exact same underlying PL/SQL logic to calculate the balances. The difference is only in how the data is presented (Excel vs. PDF).
Q: What does the “Out of Balance Amount” column mean?
A: This column checks the mathematical integrity of the asset’s history. If Begin + Activity != End, it shows the difference. This usually indicates a data corruption issue or a bug in a previous transaction that needs IT investigation.
Q: Can I use this for Tax Books? A: Yes, simply select the desired Tax Book in the “Book” parameter.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH