Sets of Books and Charts of Accounts govern Oracle’s General Ledger. The set of books keeps what currency, chart of accounts structure and calendar that the book will use. The main tables for this are
GL_SETS_OF_BOOKS
FND_ID_FLEX_STRUCTURES
JOURNAL ENTRIES
Journal Entries can be added several ways. By upload from and external source, upload through ADI, transfer from the various modules, or manually keyed into the application from the form.
Manual Entry - The responsibility that the user is in for the entry determines the chart of accounts structure and set of books. The user will enter a Batch name and period (which will default to the current open period). The user will then enter a header record with a description, and a journal category chosen from the list of valid journal categories. The user may also override the currency defaulted from the set of books. This will cause the entry to be booked to that currency but it will translate to the functional currency of the set of books. To translate, it will use the data from the GL_DAILY_RATES table. The user will last enter the lines for the journal. The main tables needed for this are:
GL_CODE_COMBINATIONS
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_CATEGORIES
GL_DAILY_RATES (If needed)
Imported Journals - Journals can also come into the general ledger from the other financial modules or by an integration tool (either custom built of ADI). The path for all of these types of entries is the same. Data flows through the GL_INTERFACE where is groups the source into batches, headers and lines based on where the data originates. When the Journal Import process is submitted either manually as a concurrent process or by a transfer process from the other modules. A journal batch, header and lines are created in the base tables exactly like a fully manual journal entry. The processes that are submitted from the other modules to transfer data into the GL are
Payables Import to General Ledger
Create Journal Entries (Fixed Assets)
PRC: Interface Revenue to General Ledger (Projects)
Interface Receivables to General Ledger
Main GL tables
GL_INTERFACE
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_JE_SOURCES
GL_JE_CATEGORIES
GL_SETS_OF_BOOKS
GL_CODE_COMBINATIONS
GL_PERIODS
GL_CURRENCIES
JOURNAL POSTING
When the user has completed a balance journal entry or the import process completes with no errors, the entries can be posted. The posting is a concurrent process submitted by the post form. When the entry is posted, the data is copied from the base tables into a temporary table called GL_POSTING_INTERIM. Here the program will use the information to group all lines with the same Currency, Code Combination, Set of Books, and Period to be used to update GL_BALANCES. The balances table will be updated with the debits and credits. The batches, headers and lines tables will also be updated for posting status. When the post is complete, the posting interim table is cleared.
Main tables
GL_POSTING_INTERIM
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_BALANCES
BALANCE TRANSLATIONS
Balances that require translation into one currency for reporting and/or consolidation purposes must run the translation process. This process requires that there be a period rate entered for set of books functional currency to the translation currency, i.e. EUR to USD. This rate is entered manually using the period rates form for each set of books. The user will then submit a process using the translation form to translate the balances to the new currency. The process will perform the following query to determine the balances available for translation.
SELECT DISTINCT CC.SEGMENT1
FROM GL_CODE_COMBINATIONS CC, GL_BALANCES GBAL
WHERE GBAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND GBAL.ACTUAL_FLAG = (user entered)
AND GBAL.SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND GBAL.PERIOD_NAME = (user entered)
AND GBAL.CURRENCY_CODE = (functional currency for the set of books)
AND GBAL.TEMPLATE_ID IS NULL
AND CC.DETAIL_POSTING_ALLOWED_FLAG = 'Y'
SELECT EARLIEST_EVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NAME,
EARLIEST_NEVER_PERIOD_NUM,
EARLIEST_NEVER_PERIOD_YEAR
FROM GL_TRANSLATION_TRACKING
WHERE BAL_SEG_VALUE = :bal_seg_value
AND TARGET_CURRENCY = (user entered)
AND SET_OF_BOOKS_ID = (from the set of books assigned to that responsibility)
AND AVERAGE_TRANSLATION_FLAG = 'N'
AND ACTUAL_FLAG = 'A'
The process will also retrieve the historical rates for equity accounts from the GL_HISTORICAL_RATES table for the prior period and insert new records based on a profile option set for the module (Year to Date or Period To Date).
The process will then insert into a temporary table all of the balances to be translated called the GL_TRANSLATION_INTERIM. From here the process will translate the balances and insert those records into the GL_XLATE_POSTING_INTERIM table. Last the data will be inserted into or updated in the GL_BALANCES table.
Main tables
GL_PERIOD_RATES
GL_HISTORICAL_RATES
GL_TRANSLATION_TRACKING
GL_TRANSALATION_INTERIM
GL_XLATE_POSTING_INTERIM
GL_BALANCES
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.
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.
11i Oracle Purchasing Technical Document
Oracle Purchasing is multi-org sensitive. The complexity depends on the set up.
Requisition Entry
Requisitions can be entered manually by the user or from the Requisition Import process.
Manual entry – Users will enter the data using the Requisitions form. At the header level, the user will enter the requisition number (if the app is set up to accept manual numbering), a requisition type and a description. At the line level, the user will enter the line type (Goods, Service, Labor etc.), and item (not required), a category (required), a description of the item or category, the units and the amount, the requestor, inventory org and location. Vendor and Vendor Site are optional. The user can enter multiple lines. After the line is entered, the user will enter the distributions for each line. Each line must have at least one distribution, but can consist of multiple. The distribution will be the code combination and/or project information for the purchase. When the user saves, the PR Account Generator is called to validate the code combination or if customized build the code combination based on auto-accounting rules from Oracle Projects. When the entry is saved, records are created in the following tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Import Entry – In order to import requisitions, the source must be mapped to the fields needed in the requisition interface. Coding will need to be done to utilize this functionality. Data will import using a concurrent process called Requisition Import. The data is brought into Oracle Purchasing and creates the header, lines and distributions. The main tables are:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Requisition Approval
Requisitions are approved based on specific application set-ups. The main set ups consist of determining of Approval Groups that determine the dollar value by document and/or account combination. The approval group is then assigned to a job title set up in the Oracle HR system. The amount assigned to the title determines who can have final approval authority. The user will submit the requisition from approval. The workflow then calls a process to build the initial approval list based on employee/supervisor (or position hierarchy if the system is set up that way). The list will include all approvers necessary to approve the requisition total. If there is no person with a high enough authority to approve, the requisition goes back to an incomplete status and the users will determine where the gap is and correct the assignments. If the requisition is on the incorrect path, the approver may forward the requisition to the correct person. The workflow will then call a procedure to rebuild the approval list based upon the forward to person’s employee/supervisor relationship. The procedure ends the current approval list header and creates a new header and lines. The history and status of the requisition is kept in the action history, which is viewable from the requisition form or from the requisition notification. The main tables are:
PER_ALL_PEOPLE_F
PER_JOBS
PER_ALL_ASSIGNMENTS_F
GL_CODE_COMBINATIONS
PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_CONTROL_FUNCTIONS
PO_POSITION_CONTROLS_ALL
PO_ACTION_HISTORY
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
Purchase Order Entry
Purchase orders can be created 2 ways, autocreating from an approved requisition or by manual entry into the from.
Autocreate – In the autocreate form, the user will enter a requisition or search criteria to pull multiple requisitions available to be turned into purchase orders. The query will produce the lines available and the user can select all or any combination to create the purchase order. Example, if there are 3 requisitions approved for one vendor, the user can create one purchase order with all of the requisition information. The main tables are
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Manual entry – The user enters into the Purchase Order form the same information as entry for the requisitions. The tables listed above are the main tables.
Requisition Entry
Requisitions can be entered manually by the user or from the Requisition Import process.
Manual entry – Users will enter the data using the Requisitions form. At the header level, the user will enter the requisition number (if the app is set up to accept manual numbering), a requisition type and a description. At the line level, the user will enter the line type (Goods, Service, Labor etc.), and item (not required), a category (required), a description of the item or category, the units and the amount, the requestor, inventory org and location. Vendor and Vendor Site are optional. The user can enter multiple lines. After the line is entered, the user will enter the distributions for each line. Each line must have at least one distribution, but can consist of multiple. The distribution will be the code combination and/or project information for the purchase. When the user saves, the PR Account Generator is called to validate the code combination or if customized build the code combination based on auto-accounting rules from Oracle Projects. When the entry is saved, records are created in the following tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Import Entry – In order to import requisitions, the source must be mapped to the fields needed in the requisition interface. Coding will need to be done to utilize this functionality. Data will import using a concurrent process called Requisition Import. The data is brought into Oracle Purchasing and creates the header, lines and distributions. The main tables are:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
Requisition Approval
Requisitions are approved based on specific application set-ups. The main set ups consist of determining of Approval Groups that determine the dollar value by document and/or account combination. The approval group is then assigned to a job title set up in the Oracle HR system. The amount assigned to the title determines who can have final approval authority. The user will submit the requisition from approval. The workflow then calls a process to build the initial approval list based on employee/supervisor (or position hierarchy if the system is set up that way). The list will include all approvers necessary to approve the requisition total. If there is no person with a high enough authority to approve, the requisition goes back to an incomplete status and the users will determine where the gap is and correct the assignments. If the requisition is on the incorrect path, the approver may forward the requisition to the correct person. The workflow will then call a procedure to rebuild the approval list based upon the forward to person’s employee/supervisor relationship. The procedure ends the current approval list header and creates a new header and lines. The history and status of the requisition is kept in the action history, which is viewable from the requisition form or from the requisition notification. The main tables are:
PER_ALL_PEOPLE_F
PER_JOBS
PER_ALL_ASSIGNMENTS_F
GL_CODE_COMBINATIONS
PO_CONTROL_RULES
PO_CONTROL_GROUPS_ALL
PO_CONTROL_FUNCTIONS
PO_POSITION_CONTROLS_ALL
PO_ACTION_HISTORY
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_APPROVAL_LIST_HEADERS
PO_APPROVAL_LIST_LINES
Purchase Order Entry
Purchase orders can be created 2 ways, autocreating from an approved requisition or by manual entry into the from.
Autocreate – In the autocreate form, the user will enter a requisition or search criteria to pull multiple requisitions available to be turned into purchase orders. The query will produce the lines available and the user can select all or any combination to create the purchase order. Example, if there are 3 requisitions approved for one vendor, the user can create one purchase order with all of the requisition information. The main tables are
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Manual entry – The user enters into the Purchase Order form the same information as entry for the requisitions. The tables listed above are the main tables.
11i Oracle Payables Technical Information
Expense Report/Invoice Entry
Expense reports are entered using Web Expenses. This is multi-org sensitive, therefore each instance is set up with a template. The template is stored in AP_EXPENSE_REPORTS_ALL. The expense reports are submitted for approval and routed using workflow. When the expense report is approved, the payables department reviews and approved the expense report. Expense Report information is stored in the following tables
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL
The accounts payable department runs a concurrent process call “Payables Invoice Import”. This process turns approved expense reports into invoices. The process populates the following tables
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_AL.
These are the exact tables that are populated when an invoice is entered directly into the AP Invoice Entry form. Invoices must be approved and accounted. Approval can be done on individual invoices through the form or by running a concurrent process called “Payables Approval”. Once the invoice is approved, the accounting process can be run. This can also be done by through the AP Invoice Entry form or by running the concurrent process “Payables Accounting Process”. The process populates the following tables
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
These are the tables that are used to create the journal entries that will go to the General Ledger. This process is called “Payables Transfer to General Ledger”. The process takes all lines in the AP_AE tables (whose GL_TRANSFER_FLAG = N and are within the parameters set by the user submitting the program) and inserts them into the GL_INTERFACE table. If the user marks Journal Import to Y when submitting, the completion of the transfer to GL process will kick off the Journal Import. This will then create the data in the GL_JOURNAL_BATCHES, GL_JOURNAL_HEADERS, and
GL_JOURNAL_LINES tables.
Creating Payments
Creating payments is the same basic navigation as creating invoices. An Invoice must be approved (does not have to have the accounting run yet). The payment can be created in the Invoice Entry form after the invoice is approved. The payments can also be created in batches. From the Payment Batches form, the user will enter the criteria to build the payment batch. The user will then choose actions and build the batch. The data will be inserted into the following tables:
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
After the batch is built, formatted and confirmed, the user will need to create accounting and transfer to GL, as explained above.
Expense reports are entered using Web Expenses. This is multi-org sensitive, therefore each instance is set up with a template. The template is stored in AP_EXPENSE_REPORTS_ALL. The expense reports are submitted for approval and routed using workflow. When the expense report is approved, the payables department reviews and approved the expense report. Expense Report information is stored in the following tables
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL
The accounts payable department runs a concurrent process call “Payables Invoice Import”. This process turns approved expense reports into invoices. The process populates the following tables
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_AL.
These are the exact tables that are populated when an invoice is entered directly into the AP Invoice Entry form. Invoices must be approved and accounted. Approval can be done on individual invoices through the form or by running a concurrent process called “Payables Approval”. Once the invoice is approved, the accounting process can be run. This can also be done by through the AP Invoice Entry form or by running the concurrent process “Payables Accounting Process”. The process populates the following tables
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
These are the tables that are used to create the journal entries that will go to the General Ledger. This process is called “Payables Transfer to General Ledger”. The process takes all lines in the AP_AE tables (whose GL_TRANSFER_FLAG = N and are within the parameters set by the user submitting the program) and inserts them into the GL_INTERFACE table. If the user marks Journal Import to Y when submitting, the completion of the transfer to GL process will kick off the Journal Import. This will then create the data in the GL_JOURNAL_BATCHES, GL_JOURNAL_HEADERS, and
GL_JOURNAL_LINES tables.
Creating Payments
Creating payments is the same basic navigation as creating invoices. An Invoice must be approved (does not have to have the accounting run yet). The payment can be created in the Invoice Entry form after the invoice is approved. The payments can also be created in batches. From the Payment Batches form, the user will enter the criteria to build the payment batch. The user will then choose actions and build the batch. The data will be inserted into the following tables:
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
After the batch is built, formatted and confirmed, the user will need to create accounting and transfer to GL, as explained above.
Tuesday, February 17, 2009
How to remove page breaks from 6i reports
Navigate to System Administrator Responisbility and query for the report name in the concurrent define section...
System Administrator -> Concurrent -> Program -> Define
Use the Printer style as BACS and check the Style Required box.
Once the above steps are done save the changes and navigate to the concerned responsibility to run the report. The report output will not have page breaks anymore. Generally this style is used for creating output files through reports.
System Administrator -> Concurrent -> Program -> Define
Use the Printer style as BACS and check the Style Required box.
Once the above steps are done save the changes and navigate to the concerned responsibility to run the report. The report output will not have page breaks anymore. Generally this style is used for creating output files through reports.
Sunday, February 15, 2009
How to authorize an employee to enter expense reports for another employee
You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate. There are 2 ways this could be done:
1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.
1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.
Thursday, February 5, 2009
How to make a Oracle form 'Query Only'
Follow the following steps inorder to make a form as Query only/Read Only.
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..
Example: MODE="PROJECT" QUERY_ONLY=YES
Tuesday, February 3, 2009
ORA-29280: invalid directory path
ORA-29280: invalid directory path
Before moving furthur in this post, please check if the directory has full permissions (Read, Write, Execute) or not. The error will also occour when there are no permissions to the directory. Once you set full permissions to the directory, try to run the program and if it still errors with the 'invalid directory path' error then please proceed furthur below for some more solutions....
Cause: A corresponding directory object does not exist.
Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.
We can derieve the directory object parameter from the following sql query:
-------------------------------------
SELECT VALUE
FROM v$parameter
WHERE NAME = 'utl_file_dir';
-------------------------------------
We can also use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.
Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init.ora file. In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.
Steps to create a example directory entry:
sql> Connect apps/apps
sql> create directory CESDIR_COMMON as '/home/common';
sql> grant read on directory CESDIR_COMMON to public;
Before moving furthur in this post, please check if the directory has full permissions (Read, Write, Execute) or not. The error will also occour when there are no permissions to the directory. Once you set full permissions to the directory, try to run the program and if it still errors with the 'invalid directory path' error then please proceed furthur below for some more solutions....
Cause: A corresponding directory object does not exist.
Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.
We can derieve the directory object parameter from the following sql query:
-------------------------------------
SELECT VALUE
FROM v$parameter
WHERE NAME = 'utl_file_dir';
-------------------------------------
We can also use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.
Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init.ora file. In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.
Steps to create a example directory entry:
sql> Connect apps/apps
sql> create directory CESDIR_COMMON as '/home/common';
sql> grant read on directory CESDIR_COMMON to public;
Validating notification response
I received a requirement from one of my clients to force the user(approver) to enter the comments value before approving the notification..This notification contains 2 responses (1. Approve, 2. Reject), so when the approver rejects the notification we need to make the Comments field in the notification as mandatatory and the notification should not go to the next stage untill he/she enters the comments field before clicking the Reject button in the workflow notification.
Fix: To implement this we need to write a Post notification function which validates the response and checks whether there is a value included in the response attribute. This post notification function needs to be placed in the workflow package and we need to call it the pl/sql block of the notification.
The code in the Post notification function would looks like this.
--------------------------------------------------------------------
PROCEDURE set_reject_comment (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2
)
IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,
'APPROVAL_COMMENT'
);
/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
BEGIN
IF ( funcmode = 'RESPOND'
AND v_response = 'REJECTED'
AND l_user_comment IS NULL
)
THEN
RAISE approval_comment;
END IF;
EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
);
RAISE;
END;
COMMIT;
END set_reject_comment;
--------------------------------------------------------------------
Fix: To implement this we need to write a Post notification function which validates the response and checks whether there is a value included in the response attribute. This post notification function needs to be placed in the workflow package and we need to call it the pl/sql block of the notification.
The code in the Post notification function would looks like this.
--------------------------------------------------------------------
PROCEDURE set_reject_comment (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT VARCHAR2
)
IS
v_response VARCHAR2 (2000);
l_user_comment VARCHAR2 (2000);
approval_comment EXCEPTION;
BEGIN
/*===================================================================================
This will derive the button by looking at the response from the notification. the WF_ENGINE.CONTEXT_NID variable will tell you the notification ID that is being responded to, and we can derive the response from there.
====================================================================================*/
v_response :=
wf_notification.getattrtext (wf_engine.context_nid, 'RESULT');
/*===================================================================================
This will retrieve the value which is stored in the Approval Comments field by the approver when the reject button is pressed.
====================================================================================*/
l_user_comment :=
wf_notification.getattrtext (wf_engine.context_nid,
'APPROVAL_COMMENT'
);
/*===================================================================================
This block will be fired when the user has pressed the 'Reject' Button and the comments is null and then it will raise the user defined error. And the workflow will not continue untill the user enters value in the comments field for rejection.
===================================================================================*/
BEGIN
IF ( funcmode = 'RESPOND'
AND v_response = 'REJECTED'
AND l_user_comment IS NULL
)
THEN
RAISE approval_comment;
END IF;
EXCEPTION
WHEN approval_comment
THEN
raise_application_error
(-20010,
'Approval Comment Field cannot be Null. Please enter reason for rejection and click reject.'
);
RAISE;
END;
COMMIT;
END set_reject_comment;
--------------------------------------------------------------------
Subscribe to:
Posts (Atom)