[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM FA_TRANSACTION_HEADERS TH, FA_DEPRN_PERIODS DP
WHERE TH.ASSET_ID = X_asset_id AND
TH.BOOK_TYPE_CODE = X_book AND
TH.TRANSACTION_SUBTYPE = 'AMORTIZED'AND
DP.PERIOD_COUNTER = X_period_counter AND
DP.BOOK_TYPE_CODE = X_book AND
DP.PERIOD_CLOSE_DATE < TH.DATE_EFFECTIVE;
* Following 9 columns have been missing from insert statements below.
* ANNUAL_DEPRN_ROUNDING_FLAG,
* PERCENT_SALVAGE_VALUE, ALLOWED_DEPRN_LIMIT,
* ALLOWED_DEPRN_LIMIT_AMOUNT, PERIOD_COUNTER_LIFE_COMPLETE,
* ADJUSTED_RECOVERABLE_COST, EOFY_ADJ_COST,
* EOFY_FORMULA_FACTOR, GROUP_ASSET_ID,
* These are added at the same time fixing bug 2033291 but nothing to
* do with the problem except ANNUAL_DEPRN_ROUNDING_FLAG.
*/
if (X_mrc_sob_type_code = 'R') then
UPDATE FA_BOOKS_MRC_V
SET DATE_INEFFECTIVE = X_sysdate,
TRANSACTION_HEADER_ID_OUT = X_th_id,
LAST_UPDATE_DATE = X_sysdate,
LAST_UPDATED_BY = NVL (X_user_id, -1),
LAST_UPDATE_LOGIN = X_login_id
WHERE ASSET_ID = X_asset_id
AND BOOK_TYPE_CODE = X_book
AND DATE_INEFFECTIVE IS NULL;
INSERT INTO FA_BOOKS_MRC_V (
BOOK_TYPE_CODE, ASSET_ID,
DATE_PLACED_IN_SERVICE, DATE_EFFECTIVE,
DEPRN_START_DATE, DEPRN_METHOD_CODE,
LIFE_IN_MONTHS, RATE_ADJUSTMENT_FACTOR,
ADJUSTED_COST, COST,
ORIGINAL_COST, SALVAGE_VALUE,
PRORATE_CONVENTION_CODE, PRORATE_DATE,
COST_CHANGE_FLAG, ADJUSTMENT_REQUIRED_STATUS,
CAPITALIZE_FLAG, RETIREMENT_PENDING_FLAG,
DEPRECIATE_FLAG, LAST_UPDATE_DATE,
LAST_UPDATED_BY, 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, FULLY_RSVD_REVALS_COUNTER,
IDLED_FLAG, PERIOD_COUNTER_CAPITALIZED,
PERIOD_COUNTER_FULLY_RESERVED, PERIOD_COUNTER_FULLY_RETIRED,
PRODUCTION_CAPACITY, REVAL_AMORTIZATION_BASIS,
REVAL_CEILING, UNIT_OF_MEASURE,
UNREVALUED_COST,
ANNUAL_DEPRN_ROUNDING_FLAG,
PERCENT_SALVAGE_VALUE, ALLOWED_DEPRN_LIMIT,
ALLOWED_DEPRN_LIMIT_AMOUNT, PERIOD_COUNTER_LIFE_COMPLETE,
ADJUSTED_RECOVERABLE_COST, EOFY_ADJ_COST,
EOFY_FORMULA_FACTOR, GROUP_ASSET_ID,
SHORT_FISCAL_YEAR_FLAG, CONVERSION_DATE,
ORIGINAL_DEPRN_START_DATE,
REMAINING_LIFE1, REMAINING_LIFE2,
OLD_ADJUSTED_COST, FORMULA_FACTOR,
SALVAGE_TYPE, DEPRN_LIMIT_TYPE)
SELECT BOOK_TYPE_CODE, ASSET_ID,
DATE_PLACED_IN_SERVICE, X_sysdate,
DEPRN_START_DATE, DEPRN_METHOD_CODE,
LIFE_IN_MONTHS,
decode(h_nbv_flag, 1,
X_new_raf, RATE_ADJUSTMENT_FACTOR),
decode(h_nbv_flag, 1, X_new_adj_cost, ADJUSTED_COST),
COST,
ORIGINAL_COST, SALVAGE_VALUE,
PRORATE_CONVENTION_CODE, PRORATE_DATE,
COST_CHANGE_FLAG, ADJUSTMENT_REQUIRED_STATUS,
CAPITALIZE_FLAG, RETIREMENT_PENDING_FLAG,
DEPRECIATE_FLAG, X_sysdate,
NVL (X_user_id, -1), NULL,
X_th_id, NULL,
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, FULLY_RSVD_REVALS_COUNTER,
IDLED_FLAG, PERIOD_COUNTER_CAPITALIZED,
decode(h_rsv_flag, 1, NULL, PERIOD_COUNTER_FULLY_RESERVED),
PERIOD_COUNTER_FULLY_RETIRED,
PRODUCTION_CAPACITY, REVAL_AMORTIZATION_BASIS,
REVAL_CEILING, UNIT_OF_MEASURE,
UNREVALUED_COST,
decode(X_deprn_basis_formula, 'STRICT_FLAT', ANNUAL_DEPRN_ROUNDING_FLAG,
'ADJ'), -- ANNUAL_DEPRN_ROUNDING_FLAG,
PERCENT_SALVAGE_VALUE, ALLOWED_DEPRN_LIMIT,
ALLOWED_DEPRN_LIMIT_AMOUNT,
decode(h_rsv_flag, 1, NULL,PERIOD_COUNTER_LIFE_COMPLETE),
ADJUSTED_RECOVERABLE_COST, EOFY_ADJ_COST,
EOFY_FORMULA_FACTOR, GROUP_ASSET_ID,
SHORT_FISCAL_YEAR_FLAG, CONVERSION_DATE,
ORIGINAL_DEPRN_START_DATE,
REMAINING_LIFE1, REMAINING_LIFE2,
OLD_ADJUSTED_COST, FORMULA_FACTOR,
SALVAGE_TYPE, DEPRN_LIMIT_TYPE
FROM FA_BOOKS BKS
WHERE ASSET_ID = X_asset_id
AND BOOK_TYPE_CODE = X_book
AND TRANSACTION_HEADER_ID_OUT = X_th_id;
UPDATE FA_BOOKS
SET DATE_INEFFECTIVE = X_sysdate,
TRANSACTION_HEADER_ID_OUT = X_th_id,
LAST_UPDATE_DATE = X_sysdate,
LAST_UPDATED_BY = NVL (X_user_id, -1),
LAST_UPDATE_LOGIN = X_login_id
WHERE ASSET_ID = X_asset_id
AND BOOK_TYPE_CODE = X_book
AND DATE_INEFFECTIVE IS NULL;
INSERT INTO FA_BOOKS (
BOOK_TYPE_CODE, ASSET_ID,
DATE_PLACED_IN_SERVICE, DATE_EFFECTIVE,
DEPRN_START_DATE, DEPRN_METHOD_CODE,
LIFE_IN_MONTHS, RATE_ADJUSTMENT_FACTOR,
ADJUSTED_COST, COST,
ORIGINAL_COST, SALVAGE_VALUE,
PRORATE_CONVENTION_CODE, PRORATE_DATE,
COST_CHANGE_FLAG, ADJUSTMENT_REQUIRED_STATUS,
CAPITALIZE_FLAG, RETIREMENT_PENDING_FLAG,
DEPRECIATE_FLAG, LAST_UPDATE_DATE,
LAST_UPDATED_BY, 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, FULLY_RSVD_REVALS_COUNTER,
IDLED_FLAG, PERIOD_COUNTER_CAPITALIZED,
PERIOD_COUNTER_FULLY_RESERVED, PERIOD_COUNTER_FULLY_RETIRED,
PRODUCTION_CAPACITY, REVAL_AMORTIZATION_BASIS,
REVAL_CEILING, UNIT_OF_MEASURE,
UNREVALUED_COST,
ANNUAL_DEPRN_ROUNDING_FLAG,
PERCENT_SALVAGE_VALUE, ALLOWED_DEPRN_LIMIT,
ALLOWED_DEPRN_LIMIT_AMOUNT, PERIOD_COUNTER_LIFE_COMPLETE,
ADJUSTED_RECOVERABLE_COST, EOFY_ADJ_COST,
EOFY_FORMULA_FACTOR, GROUP_ASSET_ID,
SHORT_FISCAL_YEAR_FLAG, CONVERSION_DATE,
ORIGINAL_DEPRN_START_DATE,
REMAINING_LIFE1, REMAINING_LIFE2,
OLD_ADJUSTED_COST, FORMULA_FACTOR,
SALVAGE_TYPE, DEPRN_LIMIT_TYPE)
SELECT BOOK_TYPE_CODE, ASSET_ID,
DATE_PLACED_IN_SERVICE, X_sysdate,
DEPRN_START_DATE, DEPRN_METHOD_CODE,
LIFE_IN_MONTHS,
decode(h_nbv_flag, 1,
X_new_raf, RATE_ADJUSTMENT_FACTOR),
decode(h_nbv_flag, 1, X_new_adj_cost, ADJUSTED_COST),
COST,
ORIGINAL_COST, SALVAGE_VALUE,
PRORATE_CONVENTION_CODE, PRORATE_DATE,
COST_CHANGE_FLAG, ADJUSTMENT_REQUIRED_STATUS,
CAPITALIZE_FLAG, RETIREMENT_PENDING_FLAG,
DEPRECIATE_FLAG, X_sysdate,
NVL (X_user_id, -1), NULL,
X_th_id, NULL,
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, FULLY_RSVD_REVALS_COUNTER,
IDLED_FLAG, PERIOD_COUNTER_CAPITALIZED,
decode(h_rsv_flag, 1, NULL, PERIOD_COUNTER_FULLY_RESERVED),
PERIOD_COUNTER_FULLY_RETIRED,
PRODUCTION_CAPACITY, REVAL_AMORTIZATION_BASIS,
REVAL_CEILING, UNIT_OF_MEASURE,
UNREVALUED_COST,
decode(X_deprn_basis_formula, 'STRICT_FLAT', ANNUAL_DEPRN_ROUNDING_FLAG,
'ADJ'), -- ANNUAL_DEPRN_ROUNDING_FLAG,
PERCENT_SALVAGE_VALUE, ALLOWED_DEPRN_LIMIT,
ALLOWED_DEPRN_LIMIT_AMOUNT,
decode(h_rsv_flag, 1, NULL,PERIOD_COUNTER_LIFE_COMPLETE),
ADJUSTED_RECOVERABLE_COST, EOFY_ADJ_COST,
EOFY_FORMULA_FACTOR, GROUP_ASSET_ID,
SHORT_FISCAL_YEAR_FLAG, CONVERSION_DATE,
ORIGINAL_DEPRN_START_DATE,
REMAINING_LIFE1, REMAINING_LIFE2,
OLD_ADJUSTED_COST, FORMULA_FACTOR,
SALVAGE_TYPE, DEPRN_LIMIT_TYPE
FROM FA_BOOKS BKS
WHERE ASSET_ID = X_asset_id
AND BOOK_TYPE_CODE = X_book
AND TRANSACTION_HEADER_ID_OUT = X_th_id;
SELECT DP.FISCAL_YEAR, MAX(DP.PERIOD_NUM),MAX(DP.PERIOD_COUNTER)
FROM FA_DEPRN_PERIODS DP
WHERE DP.BOOK_TYPE_CODE = X_fin_info_ptr.book
AND DP.FISCAL_YEAR > X_fiscal_year
AND DP.PERIOD_CLOSE_DATE IS NOT NULL
GROUP BY DP.FISCAL_YEAR
ORDER BY MIN(DP.PERIOD_OPEN_DATE);
last_updated_by integer;
last_update_login integer;
SELECT MAX(DP.PERIOD_COUNTER)
INTO period_ctr_adjusted
FROM FA_DEPRN_PERIODS DP
WHERE DP.BOOK_TYPE_CODE = X_fin_info_ptr.book
AND DP.FISCAL_YEAR = X_fiscal_year;
* Insert the adjustment row for the first fiscal year adjusted
* using the Insert into FA_ADJUSTMENTS function
*/
adj.transaction_header_id := X_fin_info_ptr.transaction_id;
adj.last_update_date := X_fin_info_ptr.current_time;
adj.selection_mode := fa_std_types.FA_AJ_ACTIVE;
* (We don't want to insert any more FA_ADJUSTMENTS rows, and
* don't want to terminate and insert FA_BOOKS rows, for non NBV-based
* assets unless asset is no longer fully reserved.
*/
if deprn_basis_rule = fa_std_types.FAD_DBR_COST then
if (X_was_fully_rsv and new_deprn_rsv < X_fin_info_ptr.rec_cost) then
if not fautubk (
X_fin_info_ptr.book,
X_fin_info_ptr.asset_id,
X_fin_info_ptr.current_time,
user_id,
login_id,
X_fin_info_ptr.transaction_id,
new_rate_adj_factor,
new_adj_cost,
FALSE,
TRUE,
X_mrc_sob_type_code,
X_deprn_basis_formula
) then
raise fautxa_error;
* Call the Insert into FA_ADJUSTMENTS function
* for EXPENSE adjustment; use the CCID from FA_DISTRIBUTION_HISTORY,
adj.last_update_date := X_fin_info_ptr.current_time;
adj.selection_mode := fa_std_types.FA_AJ_ACTIVE;
* terminate the current FA_BOOKS row; and insert a new one, with
* the updated adjusted cost and rate adjustment factor
* we only do this for assets with an NBV-based deprn method
* and if this is not the year of adjustment
*/
if new_deprn_rsv < X_fin_info_ptr.rec_cost then
rsv_flag := TRUE;
,X_last_updated_by number DEFAULT -1
,X_last_update_login number DEFAULT -1
,X_init_message_flag varchar2 default 'NO'
,X_deprn_basis_formula varchar2 default null
) return boolean is
h_new_raf number := 0;
SELECT p_psob_id AS sob_id,
1 AS index_id
FROM dual
UNION
SELECT set_of_books_id AS sob_id,
2 AS index_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_psob_id
AND enabled_flag = 'Y'
ORDER BY 2;
select bk.cost, decode(bk.allowed_deprn_limit,NULL,
(decode(bk.allowed_deprn_limit_amount,NULL,fin_info.rec_cost,
fin_info.cost - bk.allowed_deprn_limit_amount)),
fin_info.cost * bk.allowed_deprn_limit),
bk.formula_factor
into fin_info.old_cost, fin_info.adj_rec_cost, fin_info.formula_factor
from fa_books bk
where bk.asset_id = fin_info.asset_id
and bk.book_type_code = fin_info.book
and bk.date_ineffective is NULL;
select avg_exchange_rate
into l_avg_exchange_rate
from fa_mc_books_rates
where asset_id = X_asset_id
and book_type_code = X_book_type_code
and set_of_books_id = c_rec.sob_id
and transaction_header_id =
(select max(transaction_header_id)
from fa_mc_books_rates
where asset_id = X_asset_id
and book_type_code = X_book_type_code
and set_of_books_id = c_rec.sob_id);
select bk.cost, decode(bk.allowed_deprn_limit,NULL,
(decode(bk.allowed_deprn_limit_amount,NULL,fin_info.rec_cost,
fin_info.cost - bk.allowed_deprn_limit_amount)),
fin_info.cost * bk.allowed_deprn_limit),
bk.formula_factor
into fin_info.old_cost, fin_info.adj_rec_cost, fin_info.formula_factor
from fa_books_mrc_v bk
where bk.asset_id = fin_info.asset_id
and bk.book_type_code = fin_info.book
and bk.date_ineffective is NULL;