Wednesday, February 18, 2009

11i Oracle Fixed Asset Technical Document

In Oracle Fixed Assets it is important to remember that most of the processes are c programs. All current asset records from the tables have an NULL DATE_INEFFECTIVE and/or NULL TRANSACTION_HEADER_ID_OUT. And the application is governed by books which point to a GL Set Of Books.

FA ADDITIONS
Additions can come into 2 ways. Directly adding into the system manually or by entry through the Mass Additions tables.

Manual Entry - User enters data through the form and the information is inserted into the following tables
FA_ADDITIONS
FA_BOOKS
FA_ASSET_HISTORY
FA_DISTRIBUTION_HISTORY
FA_TRANSACTION_HEADERS
FA_DEPRN_SUMMARY
FA_DEPRN_DETAIL
FA_TRANSFER_DETAILS

Mass Additions come into the Oracle Assets by a spreadsheet entry through ADI (Applications Desktop Integrator) or through a concurrent process called Create Mass Additions (This can be from Payables or from Projects depending on the nature of the business)

Data is inserted into the following tables
FA_MASS_ADDITIONS (asset details)
FA_MASSADD_DISTRIBUTIONS (invoice and invoice distribution details)

The data is reviewed and updated with Category, Location and Depreciation Expense account by the user. Upon completion, a concurrent process is submitted called Post Mass Additions. Any record marked as POST will be picked up and added to the tables listed above in the Manual Entry section.

FA GENERATE ACCOUNTS

This is a concurrent program that can be submitted both stand-alone or it will submit as part of the run depreciation process.

This process when submitted looks at the FA_DISTRIBUTION_ACCOUNTS table for every active asset distribution in the book that the process is running. If there is no account listed, this process creates a new account in this table.

DEPRECIATION - BEHIND THE SCENES
When you run depreciation, Oracle Assets processes each asset according to the transactions that you have performed on the asset since the last depreciation.

FADEPR uses the following tables:
FA_DEPRN_DETAIL
For each depreciable asset, Oracle Assets inserts one row per distribution line that was active at any time during the current period.

BOOK_TYPE_CODE
ASSET_ID
PERIOD_COUNTER
DISTRIBUTION_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
ADDITION_COST_TO_CLEAR
COST
DEPRN_ADJUSTMENT_AMOUNT
DEPRN_EXPENSE_JE_LINE_NUM
DEPRN_RESERVE_JE_LINE_NUM
REVAL_AMORT_JE_LINE_NUM
REVAL_RESERVE_JE_LINE_NUM
JE_HEADER_ID
REVAL_AMORTIZATION
REVAL_DEPRN_EXPENSE
REVAL_RESERVE YTD

FA_DEPRN_SUMMARY
Oracle Assets inserts one row per depreciable asset.

BOOK_TYPE_CODE
ASSET_ID
DEPRN_RUN_DATE
DEPRN_AMOUNT
YTD_DEPRN
DEPRN_RESERVE
DEPRN_SOURCE_CODE
ADJUSTED_COST
BONUS_RATE
LTD_PRODUCTION
PERIOD_COUNTER

FA_BOOK_CONTROLS
If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'C' (Completed) or 'E' (Error), the form submits the concurrent request and sets the DEPRN_STATUS = 'S' (Submitted). Oracle Assets now locks this row to prevent you from entering any transaction when depreciation is running. If DEPRN_STATUS in the FA_BOOK_CONTROLS table is either 'R' (Running) or 'S' (Submitted), then Oracle Assets displays the errors message "CHECK_BOOK_STATUS" or "Failed to obtain lock on FA_BOOK_CONTROLS row for book."

Oracle Assets also checks if the depreciation request is for the current open period. If the LAST_PERIOD_COUNTER in the FA_BOOK_CONTROLS is that of the last period, the program proceeds. Oracle Assets updates the LAST_PERIOD_COUNTER, LAST_DEPRN_RUN_DATE, DEPRN_REQUEST_ID, DEPRN_STATUS, and CURRENT_FISCAL_YEAR for the book.

FA_DEPRN_PERIODS
Oracle Assets closes the row corresponding to the current period (by entering a PERIOD_CLOSE_DATE) and inserts a new row for the book and the new period.

FA_FISCAL_YEARS
If the new fiscal year has not been created, Oracle Assets automatically extends the fiscal year definition.

FA_ADJUSTMENTS
Retroactive transactions and expensed depreciation adjustments.

TRANSACTION_HEADER_ID
SOURCE_TYPE_CODE
ADJUSTMENT_TYPE
CODE_COMBINATION_ID
BOOK_TYPE_CODE
ASSET_ID
ADJUSTMENT_AMOUNT
DISTRIBUTION_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
ANNUALIZED_ADJUSTMENT
JE_HEADER_ID
JE_LINE_NUM
PERIOD_COUNTER
ADJUSTED PERIOD_COUNTER
ASSET_INVOICE_ID

FA_BOOKS
Look up information needed for depreciation calculation and check the following:
PERIOD_FULLY_RESERVED = NULL
PERIOD_FULLY_RETIRED = NULL
DEPRECIATE_FLAG = YES
ADJUSTMENT_REQUIRED_STATUS is not NONE or TFR (Prior Period Transfer)
DATE_INEFFECTIVE = NULL

BOOK_TYPE_CODE
ASSET_ID
DATE_PLACED_IN_SERVICE
DATE_EFFECTIVE
DEPRN_START_DATE
DEPRN_METHOD
LIFE_IN_MONTHS
RATE_ADJUSTMENT_FACTOR
ADJUSTED_COST
COST
ORIGINAL_COST
SALVAGE_VALUE
PRORATE_CONVENTION
PRORATE_DATE
DATE_INEFFECTIVE
TRANSACTION_HEADER_ID_IN
TRANSACTION_HEADER_ID_OUT
ITC_AMOUNT_ID
ITC_AMOUNT
RETIREMENT_ID
TAX_REQUEST_ID
ITC_BASIS BASIC_RATE
ADJUSTED_RATE BONUS_RULE
CEILING_NAME
RECOVERABLE_COST
LAST_UPDATE_LOGIN
ADJUSTED_CAPACITY
FULLY_RSVD_REVALS_COUNTER
PERIOD_COUNTER_CAPITALIZED
PERIOD_COUNTER_FULLY_RESERVED
PERIOD_COUNTER_FULLY_RETIRED PRODUCTION_CAPACITY REVAL_AMORTIZATION_BASIS
REVAL_CEILING UNIT_OF_MEASURE
PERIOD_COUNTER_LIFE_COMPLETE

FA_DISTRIBUTION_HISTORY
Look up active distributions.

FA_CEILINGS
Look up ceiling information.

FA_CALENDAR_PERIODS
Look up period information.

FA_CONVENTIONS
Look up prorate convention information.

THE BOTTOM LINE
~~~~~~~~~~~~~~~
Historical depreciation calculations (DEPRN_EXPENSE, ACCUMULATED_DEPRN) can be found in FA_DEPRN_DETAIL and FA_DEPRN_SUMMARY. The main difference between these tables is that FA_DEPRN_DETAIL shows depreciation information for each distribution line (i.e. each active row in FA_DISTRIBUTION_HISTORY), whereas FA_DEPRN_SUMMARY shows summary depreciation information for an asset.

NOTE: Because referential integrity is not maintained at the RDBMS level, the depreciation tables do not reference FA_DISTRIBUTION_HISTORY or FA_ADJUSTMENTS.

COMMON DEPRECIATION ERRORS
~~~~~~~~~~~~~~~~~~~~~~~~~~
APP-48260 Module CHECK_BOOK_STATUS ended with error.
Cause: The MASS_REQUEST_ID for this book is NOT NULL.
Resolution: Check to verify that all mass requests for this book have
completed normal. NULL the MASS_REQUEST_ID in the FA_BOOK_CONTROLS table.

APP-00988 ORA-1403 in fadccs.
Cause: fadccs failed due to ORA-01403 no data found.

APP-47670 Unable to validate depreciation periods.
Cause: You ran depreciation for the first time in your book. The problem
is that there is a missing row in FA_DEPRN_PERIODS. FADEPR expects a row in
FA_DEPRN_PERIODS for one period less than the active period.
Resolution: Insert the missing row in FA_DEPRN_PERIODS table. Contact
Oracle Support Services for the datafix script.

APP-47984 in fazccp and ORA-1403 in fazgtcp.
Cause: The depreciation calendar needs to go as far back as the oldest DPIS,
or if you have changed the calendar, there may be gaps or period overlaps.
Resolution: Correct the calendar.

APP-00988 ORACLE error 1403 in fadubc
APP-47649 Error: Unable to set depreciation status in FA_BOOK_CONTROLS table
APP-47640 Error: Unable to update FA_BOOK_CONTROLS table
Cause: You ran depreciation for a book with no assets and you are in the
last period of the fiscal year.
Resolution: You need to apply patch for BUG 605315 or higher.

APP-00988 Oracle error 1555 in faddep
Cause: faddep failed due to ORA-01555: snapshot too old (rollback segment
too small).
Resolution: Increase the rollback segment size.

APP-47191 and ORA-1403 in fazgtbc
Cause: Depreciation is submitted with number of parallel requests set to
more than 1 (FA:Number of Parallel Requests) and the BOOK_TYPE_CODE has a
space in between 2 words. Example: US CORPORATE
Resolution: This is BUG 456936. Apply Patch 605315 or higher. As a
workaround, you need to submit depreciation in single mode.

APP-00988 ORACLE error 1 in faenicp
Cause: faenicp failed due to ORA-00001:unique constraint
(FA.FA_CALENDAR_PERIODS_U3) violated.
Resolution:

1) Make sure that the version of faeofy.lpc in FADEPR is version 70.15
or Higher.

2) Check the calendar periods for gaps or overlap.

3) Check that the Fiscal Start and End Dates match the Start and End Dates
of the first and last periods of the fiscal year respectively.

4) Make sure that there are no gaps or overlap between Fiscal Year Start
and End Dates.

5) Be sure that all of the periods for a Fiscal Year have been created.

ORA-1 in fadaid/fadais
Common causes:

- Updating DEPRN_STATUS in FA_BOOK_CONTROLS to C (Completed).
- Partially committed or incomplete transfers, adjustments, or partial
retirements.
- Transfers that happened after depreciation has errored in a book.
- Orphaned adjustment records.
- Orphaned distributions.

Resolution: Run the provided ORA-1 diagnostic scripts. Contact Oracle
Support Services for any required datafix scripts.


ORA-1 DIAGNOSTIC SCRIPTS
========================
Script to check for adjustment rows with invalid distribution_id:

select aj.asset_id, aj.distribution_id
from fa.fa_distribution_history dh, fa.fa_adjustments aj
where aj.period_counter_created = &PCounter
and aj.book_type_code = '&BOOK'
and NOT exists (select dh.asset_id
from FA.fa_distribution_history dh
where dh.distribution_id = aj.distribution_id
and dh.asset_id = aj.asset_id);


Another script to check for adjustment rows with invalid distribution_id:

select aj.asset_id
from fa.fa_transaction_headers th,
fa.fa_distribution_history dh,
fa.fa_adjustments aj,
fa.fa_deprn_periods dp
where dp.book_type_code = '&BOOK'
and dp.period_close_date is null
and dp.period_counter = aj.period_counter_created
and dp.book_type_code = aj.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'TRANSFER'
and aj.distribution_id = dh.distribution_id(+)
and dh.code_combination_id is null;


The best script to check for adjustment rows with invalid distribution_id:

select distinct aj.asset_id
from fa.fa_adjustments aj, fa.fa_deprn_detail dd
where aj.book_type_code = &book
and aj.distribution_id = dd.distribution_id(+)
and aj.period_counter_created = dd.period_counter(+)
and aj.book_type_code = dd.book_type_code(+)
and dd.deprn_amount is null
and exists (select 'fine' from fa.fa_deprn_detail dd2
where dd2.asset_id = aj.asset_id
and dd2.book_type_code = aj.book_type_code
and dd2.period_counter = aj.period_counter_created);


Script to check for invalid rows in FA_DISTRIBUTION_HISTORY:

select dh.asset_id
from fa.fa_distribution_history dh1,
fa.fa_distribution_history dh
where dh.transaction_header_id_out is not null
and dh.transaction_header_id_out = dh1.transaction_header_id_in(+)
and dh1.code_combination_id is null;


GENERAL INFORMATION
===================

DEPRECIATION CALENDAR
The depreciation calendar determines the number of accounting periods in
your fiscal year.

PRORATE CALENDAR
The prorate calendar determines what rate Oracle Assets uses to calculate annual depreciation by mapping each date to a prorate period, which corresponds to a set of rates in the rate table.

PERIOD CLOSE
Oracle Assets automatically closes the book's current period and opens the next when you run the depreciation program. You cannot have more than one open period for a given depreciation book.

YEAR-END PROCESSING
You can close the year independently in each depreciation book. The depreciation program automatically resets year-to-date amounts on a book the first time the depreciation program is run on that book in a fiscal year. Oracle Assets automatically creates the depreciation and prorate periods for your new year when you run depreciation for the last period of the previous fiscal year.

SUSPEND DEPRECIATION
You can suspend depreciation by un-checking Depreciate flag in the Books form. If you suspend depreciation of an asset when you add the asset, Oracle Assets expenses the missed depreciation in the period you start depreciating the asset.

For table and calculated methods, Oracle Assets calculates depreciation expense for the asset based on an asset life that includes the periods you did not depreciate it. If you suspend depreciation after an asset has started depreciating, Oracle Assets catches up the missed depreciation expense in the last period of life.

For flat-rate methods, Oracle Assets continues calculating depreciation expense for the asset based on the flat-rate. For flat-rate methods that use net book value, Oracle Assets uses the asset net book value at the beginning of the fiscal year in which you resume depreciation. The asset continues depreciating until it becomes fully reserved.

RECOVERABLE COST
For depreciation methods with a calculation basis of cost, Oracle Assets calculates depreciation using the recoverable cost. The recoverable cost is calculated as the lesser of either the cost less the salvage value less the investment tax credit basis reduction amount, or the cost ceiling.

Oracle Assets depreciates the asset until the accumulated depreciation equals the recoverable cost.

ADJUSTMENTS
The following are some examples of financial adjustments you can expense or amortize:
- Recoverable Cost Adjustments
- Depreciation Method Adjustments
- Life Adjustments
- Rate Adjustments
- Capacity Adjustments

PRIOR PERIOD TRANSACTIONS
- Prior Period Additions
If you enter an asset with a date placed in service before the current accounting period, Oracle Assets automatically calculates the missed depreciation and adjusts the accumulated depreciation on the next depreciation run. If you provide accumulated depreciation when you add the asset, Oracle Assets does not recalculate the accumulated depreciation. It accepts the amount you entered. For table and calculated methods, even if the entered accumulated depreciation differs from what Oracle Assets would have calculated, Oracle Assets does not depreciate the asset beyond the recoverable cost. If the accumulated depreciation is too low, Oracle Assets takes additional depreciation in the last period of the asset's life so that the asset becomes fully reserved. If the asset's accumulated depreciation is too high, Oracle Assets stops depreciating the asset when it becomes fully reserved, effectively shortening the asset life.

- Prior Period Transfers
If you backdate an asset transfer, Oracle Assets automatically reallocates depreciation expense by reversing some of the depreciation charged to the from account, and redistributing it proportionally to the to accounts. Retroactive transfers do not impact the total depreciation. You cannot backdate a transfer to a prior fiscal year.

- Prior Period Retirements / Reinstatements
If you backdate a retirement, Oracle Assets automatically adjusts the depreciation for the year by the appropriate amount, resulting in a one-time adjustment in depreciation expense for the period. Oracle Assets then computes the gain or loss using the resulting net book value. You cannot backdate a retirement to a previous fiscal year, nor can you reinstate a retirement performed in a previous fiscal year.

- Prior Period Amortized Adjustments
If you backdate an amortized adjustment, Oracle Assets automatically calculates depreciation from the retroactive amortization start date, and adds the retroactive depreciation to the current period.

- Negative Cost (Credit) Assets
You can enter a credit asset as an asset with a negative cost and Oracle Assets credits depreciation expense and debits accumulated depreciation each period for the life of the asset.

6 comments:

Nitya Ryali said...

Very useful post.

Sasidhar said...

Thank you Arun. Good information on FA.
---Sasidhar.Parepalli

dudara said...

Great article - thanks

Anonymous said...

Awesome post...Thanks a lot

Rajesh Singh said...

Hi Arun, Thanks for given very good informations on Fixed Asset.

MM said...

great info and very easy to follow and understand thanks

- do you by any chance have TE050 scripts for FA - if so could you please share it with me - will certainly appreciate it.
Thank you
Sincerely

Manoj