Mass Additions

Mass Additions from Baker Khader Abdallah, PMP

Fixed Assets Period Closing

Oracle Assets Period Closing from Baker Khader Abdallah, PMP

Fixed Assets Issues and Resolutions

R12 Fixed Assets Issues and Resolutions from Baker Khader Abdallah, PMP

R12 Fixed Assets General Questions

R12 Fixed Assets General Questions from Baker Khader Abdallah, PMP

Group Asset

A group asset is a collection of member assets. Member assets are individual assets that belong to a group asset. You can add member assets to a group asset, or delete member assets from a group asset. You can transfer member assets in or out of a group asset, as well as transfer member assets between group assets. The group asset cost is equal to the sum of all the member asset costs.

Group Asset Cost = Sum of all Member Asset Costs

Generally, the member asset cost is posted to General Ledger for the individual member asset, and the member asset accumulated depreciation is only maintained and reported at the group level. Group assets may contain member assets that were added in different years or accounting periods.

The asset type of the group is used only for group assets.

Set Up Group Assets in Book Controls

To set up group assets in the Book Controls window:

• Navigate to the Book Controls window.

• In the Class field, select Corporate. You can only create group assets in a corporate book.

• Select the Accounting Rules tabbed region.

• Allow Amortized Changes: This option does not affect group or member assets, or individual assets that previously belonged to a group asset. 

• You must check the Allow Group Depreciation check box before creating group assets in the book.

• Optionally check the Allow CIP Members in Group Assets check box to enable adding CIP assets to group assets. If this check box is not checked, you will not be able to add a CIP asset to a group asset. You must check the Allow CIP Depreciation in Group Assets check box to depreciate the CIP asset cost for member assets.

• Optionally check the Allow Member Asset Tracking check box to enable member asset tracking for the depreciation book. Once the check box is checked and saved, you cannot uncheck it.

• You must check the Allow Intercompany Member Asset Assignments check box if you want to allow member assets to have balancing segment values that are different than the balancing segment value of the group asset. If the check box is unchecked for a particular depreciation book, you cannot set up member asset tracking for that book.

Create Group Assets Using Detail Additions

• Navigate to the Asset Workbench window.

• Select the Additions button to create a new group asset.

• In the Asset Number field, optionally enter a unique asset number for the group asset. If no value is entered, the system will automatically assign a numeric number using the asset number sequencing. Group assets use the same automatic number sequencing as individual assets.

• In the Description field, enter a description for the group asset. This is a required field.

• In the Asset Type field, select Group as the asset type when creating a group asset.

• In the Category field, enter a category for the group asset. This is a required field.

• In the Units field, accept the default value of 1, or enter the desired value. This is a required field.

• Note: You cannot assign group or member assets as parent or child assets.

• The Physical Inventory function is not supported for group assets, and you cannot check the Physical Inventory check box when the asset type is Group.

• Select the Continue button to enter information in the Books window for the new group asset.

To define financial information for a new group asset:

• In the Books window, enter information for the group asset.

• Note: For group assets, some financial information, such as current cost, original cost, recoverable cost and net book value cannot be updated.

• During the addition period, the group asset year to date depreciation expense and accumulated depreciation can be adjusted after assigning a member asset to the group asset.

• In the Book field, enter the corporate book of the new group asset.

• Note: You cannot update the Cost field for a group asset.

• In the Salvage Value field, there are two options to calculate the salvage value for a group asset. You can select the following:

• Percentage: Enter a salvage value percentage for the group asset. The salvage value is calculated as a percentage of group asset cost, as shown in the example below:

• Group Asset Salvage Value = Group Asset Cost * Group Asset Salvage Value Percentage.

• Sum of Member Assets: The group asset salvage value is the total of all member asset salvage values. You can subsequently change the salvage value type.

• In the YTD Depreciation field, enter the year to date depreciation for the group asset. You can only update this field during the addition period of the group asset and if at least one member asset has been assigned to the group asset.

• In the Accumulated Depreciation field, enter the accumulated depreciation for the group asset. You can only update this field during the addition period of the group asset and if at least one member asset has been assigned.

• Note: You cannot update the Revaluation Ceiling or Revaluation Reserve field for a group asset.

• To continue adding your group asset using the Detail Additions process, you must do one of the following:

• Choose Continue to continue adding your asset. 

• Optionally, enter or override depreciation information using the tabbed regions in the Books window. Instructions for adding this information are included in the following tasks.

To define retirement options for a group asset:

Select the Advanced Rules tabbed region.



Important: You can only update retirement rules information during the addition period of the group asset, and before assigning a member asset.



Retirement Options Region


In the Recognize Gain and Loss field, there are two options available for member asset retirements, which include the following:


Do Not Recognize: The gain and loss is not recognized. This is the default value.


Immediately When Retired: The gain and loss is recognized at retirement. The retirement treatment for the member asset is defaulted from the group asset setup, found in the Retirement tabbed region.


Check the Recapture Excess Reserve check box if you want to indicate that the accumulated depreciation of a group asset can exceed its recoverable cost (NBV less than 0) without triggering the recognition of a gain.


The Limit Net Proceeds to Cost check box is available if Recognize Gain and Loss is set to Do Not Recognize. If the check box is checked, the amount of proceeds (net of cost of removal) that may be added to accumulated depreciation is limited to the recoverable cost of the retiring member asset.


For the Terminal Gain and Loss field, you can choose from three option. The three options available include the following:


Recognize Immediately (default value)


Defer Recognition to End of Fiscal Year


Do Not Recognize

When the last member asset is retired in a group asset, and no additional assets are added into the group asset, the remaining group accumulated depreciation balance is defined as a terminal gain or loss.

To enter distribution information for a group asset:

In the Assignments window, enter distribution information for a group asset.

In the Units field, enter the number of units for the group asset.

In the Depreciation Expense field, enter a valid depreciation expense account for the group asset. This is a required field. No inter-company asset transfer is allowed for a group asset, unless Allow Intercompany Member Asset Assignment is enabled during book controls setup.

Important: For a group asset with multiple distribution lines, you can only specify different balancing segment values if you checked the Allow Intercompany Member Asset Assignments check box in the Book Controls window.

In the Location field, enter a location for the group asset. This is a required field.

Select the Done button to save the new group asset.

Group Depreciation

The Group Depreciation feature enables you to set up logical groupings of assets based on regulatory requirements and your own business needs. These logical groupings of assets are referred to as Group Assets. You can reduce data entry requirements by defining depreciation parameters for group assets rather than at the individual asset level. Group Depreciation also handles complex transactions for group assets and their member assets.

In many countries, local tax regulations require companies to depreciate assets in a composite or aggregate form, rather individually, for each asset. Group Depreciation addresses the requirements of depreciating assets in a composite or aggregate form.

Due to the large volume of assets in a major corporate enterprises, collections of similar assets are often pooled together to ease financial reporting. A group may contain many individual assets that were placed in service in different years, but only one depreciation amount is maintained for the group. Depreciation is computed and stored at the group level, and is known as Group Depreciation.

Group Depreciation aids accommodating many of the composite or aggregate depreciation requirements imposed by the following global regulatory requirements:


United States Telecomm (FCC) and Utility (FERC) compliance reporting.


United Kingdom: Writing Down Allowance (WDA) compliance reporting.


Canada Capital Cost Allowance (CCA) compliance reporting.


Japanese group asset financial and compliance reporting.


Indian group asset management and compliance reporting.

Group and Member Asset Rules

• A group asset and the associated member assets must belong to the same corporate book.

• The depreciation rules defined for the group asset generally supersede those of the associated member assets. Depreciation is generally calculated and stored for the group asset only.

• The addition of a member asset will be treated as an amortized cost adjustment to the group asset.

• Member asset transactions, such as additions, adjustments and group reclassifications, are treated as amortized adjustments. Expensed adjustments are not allowed for group assets.

• The group asset date placed in service is used to determine when depreciation starts for the group asset. This date cannot be updated after depreciation has started for the group asset.

• Member assets can be added with a date placed in service that is different than that of its group asset, but a member asset date placed in service may not be older than the date placed in service of the group asset.

• If a member asset is added with a prior period date placed in service, the system will treat the member asset addition as an amortized cost adjustment to the group asset from the date placed in service of the member asset. Also, Oracle Assets automatically submits the Process Group Adjustments concurrent program to calculate the prior period depreciation expense for the group asset. You must acknowledge the message containing the request number of the program submission.

• When you add a CIP member asset to a group asset, the CIP member asset cost is not added to the group asset until the CIP member asset is capitalized, with one important exception. The exception occurs when you check the Allow CIP Depreciation in the Group Assets check box on the Book Controls window. You may add the CIP member asset cost to the group asset by setting the Depreciate in Group option on the Source Lines window.

• Unplanned depreciation is only available for member assets with the Member Asset Tracking option set up for the associated group asset.

• Cost is stored and tracked at the member asset level and summarized to the group asset.

• Accumulated depreciation is generally stored and tracked for the group asset only.

• The asset cost is posted to General Ledger in the period the member asset is added. Depreciation expense is posted to General Ledger from the group asset only, even when member asset tracking is enabled.

Disabling Group Assets

You can disable a group asset on the Books window of the Asset Workbench by checking the Disable check box on the Depreciation tabbed region of the Books window. Disabling a group asset for one book, does not affect its status in another book. For example, if you disable a group asset in the corporate book, this will not affect its enabled status in the tax book.

Before you can disable a group asset, all of its member assets must be fully retired or removed from the group asset via group reclassification. Also, the cost of a group asset must be zero before it can be disabled.

You can re-enable a disabled group asset at any time.

You can view disabled group assets on the View Financial Information window and the Asset Workbench by selecting the Show Disabled Groups check box on the Find Assets window.

You need to consider the following restrictions when disabling group assets:

• You cannot perform any adjustment transactions on a disabled group asset and its member assets in the book in which the group has been disabled.

• No depreciation will be calculated for disabled group assets.

• You cannot add member assets to disabled group assets.

• Disabled group assets are excluded from the group asset list of values on the Asset Workbench, Mass Additions, Mass Change, and Mass Reclassifications windows.

If you try to add an asset to a category in which a disabled group asset is set as the default group asset on the Default Depreciation Rules window on the Categories form, you need to first manually remove the category default. Otherwise, you will receive an error when trying to add an asset via the Asset Workbench or Mass Additions window.
To disable a group asset:

• Choose Asset:Asset Workbench from the Navigator window.

• Find the group asset you want to disable.

• Choose Books to open the Books window.

• In the Book field, enter the book in which you want to disable the group asset.

• Check the Disable check box on the Depreciation tabbed region to disable this group asset.

• Choose Done to save this transaction.

Re-enabling a Disabled Group Asset

You can re-enable a disabled group asset at any time. After you re-enable a group asset, all functional restrictions are restored. The group asset can be displayed on the Asset Workbench, Mass Additions and Mass Transactions forms. Oracle Assets calculates depreciation for the group asset going forward and you can perform transactions on the group asset and its member assets. You can also set the re-enabled group asset as the default group asset on the Default Depreciation Rules window on the Categories form.

1. Choose Asset:Asset Workbench from the Navigator window.
2. Find the group asset you want to re-enable.
3. Choose Books to open the Books window.
4. In the Book field, enter the Book in which you want to re-enable the group asset.
5. Uncheck the Disable check box in the Depreciation tabbed region to re-enable this group asset.
6. Choose Done to save this transaction.

Assigning Member Assets to a Group Asset

You can assign a member asset to a group asset using the following three options:

• You can use the Asset Workbench to assign an asset to a group asset.
• You can use an asset category that is assigned to a group asset.
• You can use Prepare Mass Addition to assign an asset to a group asset.

If you create an asset in the period of addition, you can assign the asset to a group asset by manually entering the group asset number in the Asset Workbench. The asset is added as a member asset of the group asset you manually specified.

Note: The Depreciate check box for a member asset has no impact on the group asset depreciation calculation. In General, the Depreciate check box for a member asset has no impact on the group asset depreciation calculation. However, if you have set up the Member Asset Tracking option to be Calculate Member Asset Amount for the group asset, the system will not calculate member asset depreciation when the Depreciate check box is not checked for the member asset.

To enter group asset information for a member asset:

1. Detail Additions: Enter the group asset number under the Group Asset tabbed region in the Books window.
   QuickAdditions: Enter the group asset number in the Book region of the QuickAdditions window.
   Prepare Mass Additions: Enter the group asset number in the Mass Additions window.
2. If this member asset belongs to a group asset, the Group Asset field will display the group asset number. The group asset number can be numeric or character based.
3. The Description field is a display only field. When a valid group asset number is displayed, the group asset description will automatically display the description of the group asset.
4. The Reduction Rate field is enabled for the member asset whenever it is enabled for the group asset, and it defaults to the reduction rate setup of the group asset. However, you can override the default reduction rate for each transaction. The reduction rate is enabled on the Asset Workbench Books window in the Advanced Rules tabbed region. Refer to the group asset advanced rules for details on the reduction rate default setup. 

Assigning Group Assets via Category

After a group asset is created, you can enter the group asset number in the category default rules. All new assets for this category will be member assets of the group asset entered in the category. These member assets will inherit the group asset membership as part of the default rules. You can change the group asset membership by using the Asset Workbench or Mass Change. See: Changing Financial and Depreciation Information.

Transactions: Adding Member Asset Cost

When you add a member asset to a group asset, the cost of the member asset is added to the group asset cost as an amortized adjustment.

Group Asset Cost = Sum of all Member Asset Costs

Fixed Assets Setup Check List

Step No.	Required	Step
Step 1	Required	Define Your Ledgers
Step 2	Optional	Define Your Unit of Measure Classes
Step 3	Optional	Define Your Units of Measure
Step 4	Optional	Define Your Employees
Step 5	Optional	Define Your Descriptive Flexfields
Step 6	Optional	Set Up Oracle Subledger Accounting
Step 7	Optional	Decide How to Use the Account Generator
Step 8	Required with defaults	Define Additional Journal Entry Sources
Step 9	Required with defaults	Define Additional Journal Entry Categories
Step 10	Optional	Define Your Supplier and Employee Numbering Schemes
Step 11	Optional	Define Your Suppliers
Step 12	Required	Define Your Asset Key Flexfield
Step 13	Required	Define Your Asset Category Flexfield
Step 14	Required	Define Your Location Flexfield
Step 15	Required	Define Your System Controls
Step 16	Optional	Define Your Locations
Step 17	Optional	Define Your Asset Keys
Step 18	Required with defaults	Define Your Standard Asset Descriptions and Other QuickCode Values
Step 19	Required	Define Your Fiscal Years
Step 20	Required	Define Your Calendars
Step 21	Optional	Set Up Security by Book
Step 22	Required	Define Your Book Controls
Step 23	Required with defaults	Define Additional Depreciation Methods and Rates
Step 24	Optional	Define Your Depreciation Ceilings
Step 25	Optional	Define Your Investment Tax Credits
Step 26	Required	Define Your Prorate and Retirement Conventions
Step 27	Optional	Define Your Price Indexes
Step 28	Required	Define Your Asset Categories
Step 29	Optional	Define Distribution Sets
Step 30	Optional	Enter Leases
Step 31	Optional	Define Warranties
Step 32	Optional	Set Profile Options
Step 33	Optional	Define Asset Insurance

Definiton of Prorate Conventions

prorate convention is simply a mechanism to determine how much depreciation the system will calculate and take in the first and last year of an asset's life, based on when you place the asset in service.

prorate calendar determines over how many periods to spread the annual depreciation amount (or) else in simple terms it specifies when an asset should be depreciated.
Prorate convention code determines the starting period of depreciation. 

for example:
Prorate Convention code='FULL MONTH'
Date Placed in Service='15-Feb-2009'
So now depreciation will start from FEB-2009
But if Convention Code = 'MID-MONTH' 

then depreciation will start from JAN-2009

Prorate Conventions SQL Query

select
ct.prorate_convention_code,
ct.description prorate_convention_description,
ct.fiscal_year_name,
ct.depr_when_acquired_flag,
c.start_date,
c.end_date,
c.prorate_date

from
fa_convention_types ct,
fa_conventions c

where
ct.prorate_convention_code=c.prorate_convention_code

New Mass Additions SQL Query

select
description asset_description,
date_placed_in_service,
fixed_assets_cost,
invoice_number,
invoice_date,
po_number

from
fa_mass_additions

where
posting_status='NEW'

Asset Financial Information SQL Query

select
fa.asset_number,
fa.description,
fc.current_cost original_cost,
fd.deprn_reserve accum_deprn,
fc.current_cost-fd.deprn_reserve current_value

from
fa_additions fa,
fa_asset_keywords_kfv fk,
fa_financial_inquiry_cost_v fc,
fa_deprn_summary fd,
fa_categories fca,
(select asset_id,max(transaction_header_id_in) header_id from fa_financial_inquiry_cost_v group by asset_id) max_fc,
(select asset_id,max(deprn_run_date) deprn_run_date from fa_deprn_summary group by asset_id) max_fd

where
fc.asset_id=fd.asset_id
and fa.asset_category_id=fca.category_id
and fc.asset_id=fa.asset_id
and fa.asset_key_ccid=fk.code_combination_id
and fc.asset_id=max_fc.asset_id
and fc.transaction_header_id_in=max_fc.header_id
and fd.asset_id=max_fd.asset_id
and fd.deprn_run_date=max_fd.deprn_run_date
and fd.deprn_source_code='DEPRN'

Fixed Assets Tables

FA_ACE_BOOKS Financial information for updating ACE book


FA_ADDITIONS_B Descriptive information about assets (base MLS table)

FA_ADDITIONS_TL Descriptive information about assets (translated MLS table) 

FA_ADD_WARRANTIES Warranty information

FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in general ledger

FA_AMORT_SCHEDULES Amortization schedules

FA_ARCHIVE_HISTORY Archive, purge, and restore request information

FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments

FA_ASSET_INVOICES Accounts payable and purchasing information for each asset

FA_ASSET_KEYWORDS Asset key flexfield segment combinations

FA_BALANCES_REPORT Temporary storage area for the balance reports

FA_BALANCES_REP_ITF Interface table for Report eXchange reports

FA_BONUS_RATES Bonus rate information used by the Depreciation program

FA_BOOKS Financial information of each asset

FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book

FA_BUDGET_DISTRIBUTION Capital budget information entered manually or uploaded from the FA_BUDGET_INTERFACE table

FA_BUDGET_INTERFACE Capital budget information to be uploaded into Oracle Assets

FA_CALENDAR_PERIODS Detailed calendar information

FA_CALENDAR_TYPES General calendar information

FA_CAPITAL_BUDGET Budget assets used to run depreciation projections and reports

FA_CATEGORIES_B Default financial information for asset categories (base MLS table)

FA_CATEGORIES_TL Default financial information for asset categories (base MLS table)Default financial information for asset categories (translated MLS table)

FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination

FA_CATEGORY_BOOK_DEFAULTS Default depreciation rules for asset category and book combination for a particular date placed in service range

FA_CEILINGS Seeded and user–defined ceilings for depreciation expense, investment tax credit cost basis, and depreciation cost basis

FA_CEILING_TYPES General ceiling information

FA_CONVENTIONS Detailed prorate convention information

FA_CONVENTION_TYPES Names and descriptions for prorate conventions

FA_DEFERRED_DEPRN Deferred depreciation calculated for each distribution line

FA_DELETED_MASS_ADDITIONS Invoice information of mass additions you deleted from the

FA_MASS_ADDITIONS table using the Delete Mass Additions form

FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line

FA_DEPRN_PERIODS Information about each depreciation period

FA_DEPRN_SUMMARY Depreciation information at the asset level

FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book

FA_DISTRIBUTION_DEFAULTS Distribution set information

FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset

FA_DISTRIBUTION_SETS Header information for distribution sets

FA_FISCAL_YEAR Range of dates for all fiscal years

FA_FLAT_RATES Annual depreciation rates for all flat–rate methods

FA_FORMULAS Depreciation rates for formula–based methods

FA_INS_LINES Stores policy line information

FA_INS_MST_POLS Master policy table for insurance

FA_INS_POLICIES Contains asset insurance policy information

FA_INS_VALUES Stores insurance indexation details

FA_INVENTORY Physical Inventory entry

FA_INVOICE_TRANSACTIONS Information about transactions made to invoice lines within Oracle Assets

FA_INV_INTERFACE Open Interface table to Physical Inventory

FA_ITC_RATES Investment tax credit (ITC) rate information

FA_ITC_RECAPTURE_RATES Percentage of Investment Tax Credit (ITC) to recapture for early retirements

FA_LEASES Lease information for leased assets

FA_LEASE_PAYMENTS Payment schedule detail information

FA_LEASE_SCHEDULES Payment schedule information

FA_LOCATIONS Location flexfield segment value combinations

FA_LOOKUPS_B Seeded and user–defined values for QuickCodes (base MLS table)

FA_LOOKUPS_TL Seeded and user–defined values for QuickCodes (translated MLS table)

FA_LOOKUP_TYPES_B Information about QuickCodes (base MLS table)

FA_LOOKUP_TYPES_TL Information about QuickCodes (translated MLS table)

FA_MAINT_EVENTS Stores scheduled events

FA_MAINT_REP_ITF Interface table for Report eXchange reports

FA_MAINT_SCHEDULE_DTL Stores detail information for scheduling

FA_MAINT_SCHEDULE_HDR Stores information about maintenance scheduling requests

FA_MASSADD_DISTRIBUTIONS Distribution information for each mass addition line

FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system

FA_MASS_CHANGES Mass change request information

FA_MASS_RECLASS Mass reclass request information

FA_MASS_RECLASS_ITF Interface table for Report eXchange reports

FA_MASS_RETIREMENTS contains information about your mass retirement and mass reinstatement processes.

FA_MASS_RET_EXCEPTS Information about assets that could not be retired as part of a mass retirement transaction

FA_MASS_REVALUATIONS Mass revaluation request status information

FA_MASS_REVALUATION_RULES Mass revaluation request rule information

FA_MASS_TAX_ADJUSTMENTS Mass depreciation adjustment request information

FA_MASS_TRANSFERS Mass transfer request information

FA_MC_ADJUSTMENTS Stores information to create adjustment journal entries in the reporting currency

FA_MC_ASSET_INVOICES Accounts payable and purchasing information for each asset, in the reporting currency

FA_MC_BOOKS Financial information for each asset in the reporting currency

FA_MC_BOOKS_RATES Exchange rate inormation

FA_MC_BOOK_CONTROLS Control information that affects all assets in a reporting depreciation book

FA_MC_CONVERSION_HISTORY Table to store the conversion history of primary to reporting books

FA_MC_CONVERSION_RATES Table to store the exchange rate information to convert assets from primary to reporting book

FA_MC_DEFERRED_DEPRN Deferred depreciation calculated for each distribution line in the reporting currency

FA_MC_DEPRN_DETAIL Depreciation amounts in the reporting currency, charged to the depreciation expense account in each distribution line

FA_MC_DEPRN_PERIODS Information about each depreciation period

FA_MC_DEPRN_SUMMARY Depreciation information at the asset level

FA_MC_MASS_RATES Exchange rate information for mass additions process

FA_MC_RETIREMENTS Currency and financial information about retirements in each reporting book

FA_METHODS Depreciation method information

FA_PERIODIC_PRODUCTION Production information for units of production assets entered manually or uploaded from

FA_PRODUCTION_INTERFACE

FA_PERIOD_MAPS Static information about period mapping; used by the Budget–To–Actual Report

FA_PRICE_INDEXES General price index information

FA_PRICE_INDEX_VALUES Price index values of each price index 

FA_PRODUCTION_INTERFACE Production information to be uploaded into Oracle Assets

FA_PROJ_INTERIM Stores depreciation projections

FA_RATES Annual depreciation rates for life–based depreciation methods

FA_RESERVE_LEDGER Temporary storage area for the reserve ledger reports

FA_RETIREMENTS Information about asset retirements and reinstatements 

FA_RX_ATTRSETS_B Stores attribute set information for RX reports (base MLS table)

FA_RX_ATTRSETS_TL Stores Attribute Set information for RX reports (translated MLS table)

FA_RX_DYNAMIC_COLUMNS Stores information about dynamic columns for RX reports

FA_RX_LOV

FA_RX_MULTIFORMAT_REPS Stores information about Multiple format reports for use with RXi

FA_RX_REPORTS Stores Report eXchange report definitions

FA_RX_REP_COLUMNS_B Stores attribute definitions for Report eXchange report attributes (base MLS table)

FA_RX_REP_COLUMNS_TL Stores attribute definitions for Report eXchange report attributes (translated MLS table)

FA_RX_REP_PARAMETERS Stores parameter definitions for Report eXchange reports

FA_RX_SECURITY Stores security assignments for Report eXchange Attribute sets

FA_RX_SUMMARY_TL Stores summary definitions for Report eXchange report attributes (base MLS table) 

FA_SHORT_TAX_RESERVES Stores accumulated depreciation amounts for assets added in short tax years in their tax books

FA_SYSTEM_CONTROLS System–wide controls that affect all assets

FA_TRANSACTION_HEADERS Information about all transactions performed within Oracle Assets

FA_TRANSFER_DETAILS Information that defines the historic relationship between active FA_BOOKS and FA_DISTRIBUTION_HISTORY rows

FA_WARRANTIES Warranty information

FA_WHATIF_ITF Interface table for What–If Depreciation Analysis Report

Assets Details SQL Query

select
fa1.asset_id,
fa1.asset_number,
fa1.asset_key_ccid,
fas.concatenated_segments asset_key,
fa1.current_units,
fa1.asset_type,
fa1.tag_number,
fa1.description,
fa1.asset_category_id,
fc.segment1||'.'||fc.segment2 asset_category,
fc.description category_description,
fa1.parent_asset_id,
fa2.asset_number parent_asset_number,
fa2.description parent_asset_description,
fa1.manufacturer_name,
fa1.serial_number,
fa1.model_number,
fa1.in_use_flag,
fa1.owned_leased,
fa1.new_used,
fa1.inventorial,
fa1.unit_adjustment_flag,
fa1.add_cost_je_flag,
fa1.lease_id,
fa1.last_update_date,
fa1.last_updated_by,
fa1.created_by,
fa1.creation_date

from
fa_additions fa1,
fa_additions fa2,
fa_asset_keywords_kfv fas,
fa_categories fc

where
fa2.asset_id (+) =fa1.parent_asset_id 

and fas.code_combination_id (+)=fa1.asset_key_ccid 

and fc.category_id (+)=fa1.asset_category_id

Pending Mass Additions

select
description asset_description,
date_placed_in_service,
fixed_assets_cost,
invoice_number,
invoice_date,
po_number
from
fa_mass_additions
where
posting_status='NEW'

Untransferred Assets Accounting Lines

select
h.je_category_name,
h.accounting_date,
h.accounting_entry_status_code,
h.description,
h.gl_transfer_status_code,
l.accounting_class_code,
l.description,
c.segment1||'-'||c.segment2||'-'||c.segment3||'-'||c.segment4||'-'||c.segment5||'-'||c.segment6||c.segment7 Account,
l.accounted_dr,
l.accounted_cr
from
xla_ae_headers h,
xla_ae_lines l,
gl_code_combinations c
where
h.application_id=140
and h.ae_header_id=l.ae_header_id
and c.code_combination_id=l.code_combination_id
and h.gl_transfer_status_code<>'Y'

Assets GL Reconcilation

select
account_id,
account_number,
sum(sl_balance) sl_balance,
sum(gl_balance) gl_balance,
sum(sl_balance-gl_balance) recon_diff
from
(select
c.code_combination_id account_id,
c.concatenated_segments account_number,
sum(nvl(l.accounted_dr,0)-nvl(l.accounted_cr,0)) sl_balance,
0 gl_balance
from
xla_ae_headers h,
xla_ae_lines l,
gl_code_combinations_kfv c
where
h.application_id=140
and h.ae_header_id=l.ae_header_id
and c.code_combination_id=l.code_combination_id
and balance_type_code='A'
and l.accounting_date>'31-MAY-2014'
group by
c.code_combination_id,
c.concatenated_segments

union all

select
c.code_combination_id account_id,
c.concatenated_segments account_number,
0 sl_balance,
sum(nvl(l.accounted_dr,0)-nvl(l.accounted_cr,0)) gl_balance
from
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv c
where
h.je_source='Assets'
and h.je_header_id=l.je_header_id
and c.code_combination_id=l.code_combination_id
and h.actual_flag='A'
and h.default_effective_date>'31-MAY-2014'
group by
c.code_combination_id,
c.concatenated_segments)
group by
account_id,
account_number

Asset Accounting Details

select
h.je_category_name,
h.accounting_date,
h.accounting_entry_status_code,
h.description,
h.gl_transfer_status_code,
l.accounting_class_code,
l.description,
c.segment1||'-'||c.segment2||'-'||c.segment3||'-'||c.segment4||'-'||c.segment5||'-'||c.segment6||c.segment7 Account,
l.accounted_dr,
l.accounted_cr
from
xla_ae_headers h,
xla_ae_lines l,
gl_code_combinations c
where
h.application_id=140
and h.ae_header_id=l.ae_header_id
and c.code_combination_id=l.code_combination_id

Asset Books SQL Query

select
fb.book_type_code,
fb.asset_id,
fa.asset_number,
fa.description,
fb.date_placed_in_service,
fb.date_effective,
fb.deprn_start_date,
fb.deprn_method_code,
fb.adjusted_cost,
fb.cost,
fb.original_cost,
fb.salvage_value,
fb.prorate_convention_code,
fb.prorate_date,
fb.capitalize_flag,
fb.depreciate_flag,
fb.date_ineffective,
fb.basic_rate,
fb.adjusted_rate,
fb.recoverable_cost,
fb.unrevalued_cost,
fb.adjusted_recoverable_cost

from
fa_books fb,
fa_additions fa

where
fb.asset_id=fa.asset_id (+)