In this article we will discuss on fixed asset FA tables in Oracle Fusion . Fixed asset tables help in the storage of several aspects of asset information in Oracle Fusion. When generating sql queries or developing fixed asset reports with the Oracle Fusion BIP tool, these fixed asset tables come in handy. There are several fixed asset tables in Oracle Fusion, but certain of them are particularly significant since they store the most relevant fixed asset information. I’ll go over some of the most essential fixed asset tables in Oracle Fusion in the sections below.
Fixed Assets FA table Details in Oracle Fusion ERP Application
FA_ADDITIONS_B
FA_ADDITIONS_B contains descriptive data to help in the identification of your assets. When Oracle Fusion Assets adds a new asset, it adds a row to this table as well as the FA_ASSET_HISTORY table.
FA_ADJUSTMENTS
FA_ADJUSTMENTS is a table that Oracle Assets uses to construct transaction journal entries. The posting software uses the information in FA_DEPRN_DETAIL to construct journal entries for regular depreciation expenditure. For each debit and credit side of a financial transaction, Oracle Assets adds a row to this table.
FA_ASSET_HISTORY
FA_ASSET_HISTORY records changes in asset categories, capitalizations, unit modifications, and partial unit retirements. Oracle Fusion Assets inserts one row into the table when you first add an asset, and this row becomes the active row for the asset. When you update the category or unit of an asset, Oracle Fusion Assets deletes the old row and replaces it with a new one.
FA_ASSET_INVOICES
To track the source asset information, FA_ASSET_INVOICES stores invoice information from Oracle Fusion Payables and Oracle Fusion Purchasing for the assets. This table also contains all project-related costs, such as labour and expenses.
FA_BOOKS
The information needed to calculate depreciation is found in FA_BOOKS. Oracle Fusion Assets inserts one row into the table when you first add an asset, and that row becomes the active row for the asset. When you modify the depreciation information for an asset, or if you retire or reinstate it, Oracle Fusion Assets adds a new row to the database, which becomes the new active row, and the previous row is marked as obsolete.
FA_CALENDAR_PERIODS
FA_CALENDAR_PERIODS is a subset of FA_CALENDAR_TYPES that contains more information. This information is used by the depreciation program to set the CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE in FA_DEPRN_PERIODS variables, as well as to check transaction dates.
FA_CATEGORY_BOOKS
FA_CATEGORY_BOOKS is a table that lists the cost and reserve accounts for a specific book and category combination. For each book to which the asset category is allocated, a row is added to this table. Oracle Fusion Assets, for example, adds three rows into this database if you designate a category for three depreciation books.
FA_DEPRN_DETAIL
The depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line are stored in FA_DEPRN_DETAIL. This information is used by Oracle Fusion Assets to construct depreciation expense journal entries. Each depreciation process run, the depreciation program inserts one row per distribution line for an asset.
FA_DEPRN_SUMMARY
The depreciation information for the assets is stored in FA_DEPRN_SUMMARY. The depreciation software adds one row to this table for each asset each time it runs.
FA_INVOICE_TRANSACTIONS
FA_INVOICE_TRANSACTIONS Oracle Assets keeps track of all the transactions you make on invoice lines. When you perform a transaction on an invoice line, Oracle Assets adds rows to this table and FA_TRANSACTION_HEADERS.
FA_MASS_ADDITIONS
FA_MASS_ADDITIONS is a table that maintains information about assets that you want to add to Oracle Assets automatically from another system. This table can be used to load assets for system conversions. Oracle Assets inserts one row into this table for each invoice line it selects from Oracle Payables when you perform the mass additions create procedure.
FA_MC_RETIREMENTS
FA_MC_RETIREMENTS stores currency and financial data on asset retirements in each reporting book, which is linked to the FA_RETIREMENTS parent record. The information in this table is used by Oracle Fusion Assets to calculate the profits and losses on asset retirements in the reporting book. FA_RETIREMENTS has an MRC sub-table called FA_RETIREMENTS.
SQL Query to Fetch Fixed Assets FA Details in Oracle Fusion ERP Application
SELECT fa.asset_number,
fai.fixed_assets_cost,
fai.invoice_transaction_id_in,
fai.invoice_line_number,
fai.invoice_id,
aia.invoice_id,
fai.invoice_number,
fai.ap_distribution_line_number,
aila.line_number,
aida.invoice_distribution_id
FROM AP_INVOICES_ALL aia,
AP_INVOICE_LINES_ALL aila,
AP_INVOICE_DISTRIBUTIONS_ALL aida,
FA_ADDITIONS fa,
FA_ASSET_INVOICES fai
WHERE fai.invoice_id = aia.invoice_id
AND aia.invoice_id = aila.invoice_id
AND aia.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND fai.invoice_line_number = aila.line_number
AND fai.invoice_distribution_id = aida.invoice_distribution_id
AND fai.asset_id = fa.asset_id
AND fa.asset_number = :P_ASSET_NO