The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_cost_to_insert number := 0;
l_clearing_to_insert number := 0;
select asset_id
, transaction_header_id
, transaction_type_code
, transaction_date_entered
, transaction_name
, source_transaction_header_id
, mass_reference_id
, transaction_subtype
, transaction_key
, amortization_start_date
, calling_interface
, mass_transaction_id
, member_transaction_header_id
, trx_reference_id
, last_update_date
, last_updated_by
, last_update_login
from fa_transaction_headers
where transaction_header_id = px_trans_rec.member_transaction_header_id;
select inbk.transaction_header_id_in
from fa_books inbk
, fa_books outbk
where inbk.transaction_header_id_in = px_trans_rec.member_transaction_header_id
and outbk.asset_id = inbk.asset_id
and outbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.transaction_header_id_out = px_trans_rec.member_transaction_header_id
and inbk.cost = outbk.cost
and nvl(inbk.salvage_value, 0) = nvl(outbk.salvage_value, 0)
and nvl(inbk.allowed_deprn_limit_amount, 0) = nvl(outbk.allowed_deprn_limit_amount, 0)
and inbk.date_placed_in_service <> outbk.date_placed_in_service;
select recognize_gain_loss
from fa_retirements
where transaction_header_id_in = l_thid;
select recognize_gain_loss
from fa_retirements
where transaction_header_id_out = l_thid;
select count(1)
from fa_transaction_headers
where asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id < px_trans_rec.transaction_header_id
and transaction_type_code = 'ADDITION';
select inbk.asset_id
from fa_books inbk
, fa_books outbk
where inbk.transaction_header_id_in = px_trans_rec.member_transaction_header_id
and outbk.asset_id = inbk.asset_id
and outbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.transaction_header_id_out = inbk.transaction_header_id_in
and inbk.group_asset_id is null
and outbk.group_asset_id is not null;
name => 'FA_DEPRN_UPDATE_BONUS_RULE', p_log_level_rec => p_log_level_rec);
select count(*)
into l_deprn_reserve_exists
from fa_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS'
and deprn_reserve <> 0;
update fa_books
set adjustment_required_status = 'GADJ'
where asset_id = l_group_rcl_out_asset
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;
update fa_mc_books
set adjustment_required_status = 'GADJ'
where asset_id = l_group_rcl_out_asset
and book_type_code = p_asset_hdr_rec.book_type_code
and set_of_books_id = p_asset_hdr_rec.set_of_books_id
and transaction_header_id_out is null;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount))
INTO l_deprn_exp_temp
FROM fa_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT') --Bug7409454
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount))
INTO l_deprn_exp_temp
FROM fa_mc_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT') ----Bug7409454
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter
and adj.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount)),
sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.annualized_adjustment,
adj.annualized_adjustment))
INTO l_deprn_exp,l_ann_adj_deprn_exp
FROM fa_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT') --Bug7409454
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount)),
sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.annualized_adjustment,
adj.annualized_adjustment))
INTO l_deprn_exp,l_ann_adj_deprn_exp
FROM fa_mc_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT') ----Bug7409454
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter
and adj.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount)),
sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.annualized_adjustment,
adj.annualized_adjustment))
INTO l_deprn_exp,l_ann_adj_deprn_exp
FROM fa_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT')
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount)),
sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.annualized_adjustment,
adj.annualized_adjustment))
INTO l_deprn_exp,l_ann_adj_deprn_exp
FROM fa_mc_adjustments adj,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code in ('ADDITION', 'ADJUSTMENT')
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.transaction_header_id = th.transaction_header_id
and adj.source_type_code = 'DEPRECIATION'
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter
and adj.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_DEPRN_OVERRIDE ov
SET ov.transaction_header_id = px_trans_rec.transaction_header_id
WHERE ov.book_type_code = p_asset_hdr_rec.book_type_code
AND ov.used_by = 'ADJUSTMENT'
AND ov.status = 'SELECTED'
AND ov.transaction_header_id is null
AND ov.asset_id IN
(SELECT bk.asset_id
FROM fa_books bk
WHERE bk.book_type_code = ov.book_type_code
AND bk.group_asset_id = p_asset_hdr_rec.asset_id
AND bk.date_ineffective IS NULL);
UPDATE FA_DEPRN_OVERRIDE
SET transaction_header_id = px_trans_rec.transaction_header_id
WHERE book_type_code = p_asset_hdr_rec.book_type_code
AND asset_id = p_asset_hdr_rec.asset_id
AND used_by = 'ADJUSTMENT'
AND status = 'SELECTED'
AND transaction_header_id is null;
p_update_books_summary => TRUE,
p_proceeds_of_sale => 0,
p_cost_of_removal => 0,
x_deprn_exp => l_deprn_exp,
x_bonus_deprn_exp => l_bonus_deprn_exp,
x_impairment_exp => l_impairment_exp,
x_deprn_rsv => l_deprn_rsv, p_log_level_rec => p_log_level_rec)) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add('calc_fin_info', 'calling FA_AMORT_PVT.faxama', 'FAILED', p_log_level_rec => p_log_level_rec);
select nvl(sum(decode(debit_credit_flag,
'CR', adjustment_amount,
adjustment_amount * -1)), 0)
into l_clearing
from fa_adjustments
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter_created = p_period_rec.period_counter
and transaction_header_id = px_trans_rec.transaction_header_id
and adjustment_type = 'COST CLEARING';
select nvl(sum(decode(debit_credit_flag,
'CR', adjustment_amount,
adjustment_amount * -1)), 0)
into l_clearing
from fa_mc_adjustments
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter_created = p_period_rec.period_counter
and transaction_header_id = px_trans_rec.transaction_header_id
and adjustment_type = 'COST CLEARING'
and set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
l_cost_to_insert := 0;
l_clearing_to_insert := 0;
l_cost_to_insert := p_asset_fin_rec_adj.cost;
l_clearing_to_insert := p_asset_fin_rec_adj.cost - l_clearing;
p_cost => l_cost_to_insert,
p_clearing => l_clearing_to_insert,
p_deprn_expense => l_deprn_exp + NVL (l_deprn_exp_temp,0), --Bug 13620748
p_bonus_expense => l_bonus_deprn_exp,
p_impair_expense => l_impairment_exp,
p_ann_adj_amt => l_ann_adj_deprn_exp, --0
p_mrc_sob_type_code => p_mrc_sob_type_code,
p_calling_fn => l_calling_fn
, p_log_level_rec => p_log_level_rec) then raise calc_err;
, l_trans_rec.who_info.last_update_date
, l_trans_rec.who_info.last_updated_by
, l_trans_rec.who_info.last_update_login;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
select /*+ leading(conv) use_nl(conv cp) index(cp) */
prorate_date
into px_asset_fin_rec_new.prorate_date
from fa_calendar_periods cp,
fa_conventions conv
where conv.prorate_convention_code = px_asset_fin_rec_new.prorate_convention_code
and conv.start_date <= px_asset_fin_rec_new.date_placed_in_service
and conv.end_date >= px_asset_fin_rec_new.date_placed_in_service
and cp.calendar_type = l_prorate_calendar
and conv.prorate_date >= cp.start_date
and conv.prorate_date <= cp.end_date;
select least(px_asset_fin_rec_new.cost - px_asset_fin_rec_new.salvage_value,
nvl(ce.limit, px_asset_fin_rec_new.cost - px_asset_fin_rec_new.salvage_value))
from fa_ceilings ce
where ce.ceiling_name = px_asset_fin_rec_new.ceiling_name
and px_asset_fin_rec_new.date_placed_in_service
between ce.start_date and
nvl(ce.end_date, px_asset_fin_rec_new.date_placed_in_service);
select ceiling_type
into l_ceiling_type
from fa_ceiling_types
where ceiling_name = px_asset_fin_rec_new.ceiling_name;
select least(px_asset_fin_rec_new.cost - px_asset_fin_rec_new.salvage_value -
px_asset_fin_rec_new.itc_basis * ir.basis_reduction_rate,
nvl(ce.limit, px_asset_fin_rec_new.cost - px_asset_fin_rec_new.salvage_value -
px_asset_fin_rec_new.itc_basis * ir.basis_reduction_rate))
into px_asset_fin_rec_new.recoverable_cost
from fa_ceilings ce,
fa_itc_rates ir
where ir.itc_amount_id = px_asset_fin_rec_new.itc_amount_id
and ce.ceiling_name = px_asset_fin_rec_new.ceiling_name
and px_asset_fin_rec_new.date_placed_in_service
between ce.start_date and
nvl(ce.end_date, px_asset_fin_rec_new.date_placed_in_service);
select px_asset_fin_rec_new.cost - px_asset_fin_rec_new.salvage_value -
px_asset_fin_rec_new.itc_basis * ir.basis_reduction_rate
into px_asset_fin_rec_new.recoverable_cost
from fa_itc_rates ir
where ir.itc_amount_id = px_asset_fin_rec_new.itc_amount_id;
select nvl(sum(allowed_deprn_limit_amount), 0)
into px_asset_fin_rec_new.allowed_deprn_limit_amount
from fa_books bk,
fa_additions_b ad
where bk.transaction_header_id_out is null
and bk.group_asset_id = p_asset_hdr_rec.asset_id
and bk.asset_id = ad.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and ad.asset_type = 'CAPITALIZED';
select nvl(sum(allowed_deprn_limit_amount), 0)
into px_asset_fin_rec_new.allowed_deprn_limit_amount
from fa_mc_books bk,
fa_additions_b ad
where bk.transaction_header_id_out is null
and bk.group_asset_id = p_asset_hdr_rec.asset_id
and bk.asset_id = ad.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and ad.asset_type = 'CAPITALIZED'
and bk.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select itc_amount_rate,
basis_reduction_rate
into l_itc_amount_rate,
l_basis_reduction_rate
from fa_itc_rates
where itc_amount_id = px_asset_fin_rec_new.itc_amount_id;
select limit
into l_ceiling_limit
from fa_ceilings
where ceiling_name = px_asset_fin_rec_new.ceiling_name
and px_asset_fin_rec_new.date_placed_in_service between
start_date and nvl(end_date, px_asset_fin_rec_new.date_placed_in_service);
select nvl(sum(salvage_value), 0)
into px_asset_fin_rec_new.salvage_value
from fa_books bk,
fa_additions_b ad
where bk.transaction_header_id_out is null
and bk.group_asset_id = p_asset_hdr_rec.asset_id
and bk.asset_id = ad.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and ad.asset_type = 'CAPITALIZED';
select nvl(sum(salvage_value), 0)
into px_asset_fin_rec_new.salvage_value
from fa_mc_books bk,
fa_additions_b ad
where bk.transaction_header_id_out is null
and bk.group_asset_id = p_asset_hdr_rec.asset_id
and bk.asset_id = ad.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and ad.asset_type = 'CAPITALIZED'
and bk.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT DISTINCT RATE_SOURCE_RULE
FROM FA_METHODS
WHERE METHOD_CODE = px_asset_fin_rec.deprn_method_code;
select nvl(life_in_months,0),
nvl(life_in_months,0),
prorate_date
from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_desc_rec.parent_asset_id
and transaction_header_id_out is null;
select round
(nvl(sum
(decode (bc.deprn_allocation_code,'E',
1/ct.number_per_fiscal_year,
(cp.end_date + 1 - cp.start_date) /
(fy.end_date + 1 - fy.start_date))),0) * 12, 0)
from fa_calendar_periods cp,
fa_calendar_types ct,
fa_book_controls bc,
fa_fiscal_year fy
where bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.date_ineffective is null
and ct.calendar_type = bc.prorate_calendar
and ct.fiscal_year_name = bc.fiscal_year_name
and cp.calendar_type = ct.calendar_type
and ((cp.start_date >= l_parent_prorate_date and
cp.end_date <= px_asset_fin_rec.prorate_date) )
and fy.fiscal_year_name = bc.fiscal_year_name
and fy.start_date <= cp.start_date
and fy.end_date >= cp.end_date;
p_user_id => p_trans_rec.who_info.last_updated_by,
p_curr_date => p_trans_rec.who_info.last_update_date,
px_new_life => l_new_life,
p_calling_fn => l_calling_fn
, p_log_level_rec => p_log_level_rec) then
raise calc_err;
select bk.depreciate_flag,
bk.transaction_header_id_in
into l_depreciate_flag,
l_dep_flag_thid
from fa_books bk,
fa_transaction_headers th
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_type_code = 'ADDITION'
and th.transaction_header_id = bk.transaction_header_id_in;
select count(*)
into l_count_dep_flag
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and depreciate_flag = 'YES'
and transaction_header_id_in >= l_dep_flag_thid
and transaction_header_id_in <> p_trans_rec.transaction_header_id; /*Bug# 8631034 - For MRC */
select count(1)
into l_first_period
from fa_fiscal_year fy,
fa_deprn_periods dp
where l_fiscal_year_name = fy.fiscal_year_name
and l_current_fiscal_year = fy.fiscal_year
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.calendar_period_open_date = fy.start_date
and p_trans_rec.transaction_date_entered between
dp.calendar_period_open_date and
dp.calendar_period_close_date
and rownum < 2;
select unit_of_measure
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and transaction_header_id_out is NULL;
select count(*)
into l_count
from fa_flat_rates
where method_id = l_method_id
and basic_rate = px_asset_fin_rec_new.basic_rate
and adjusted_rate = px_asset_fin_rec_new.adjusted_rate;
select count(1)
into l_count
from fa_books bk,
fa_methods me
where bk.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_out is null
and me.method_code = bk.deprn_method_code
and me.rate_source_rule = 'PRODUCTION';
select count(*)
into l_count
from fa_bonus_rules
where bonus_rule = px_asset_fin_rec_new.bonus_rule;
SELECT 'Y'
FROM FA_BOOKS
WHERE GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is NULL;
SELECT recognize_gain_loss,
recapture_reserve_flag,
limit_proceeds_flag,
terminal_gain_loss,
exclude_proceeds_from_basis,
retirement_deprn_option,
tracking_method,
allocate_to_fully_rsv_flag,
allocate_to_fully_ret_flag,
excess_allocation_option,
depreciation_option,
member_rollup_flag,
reduction_rate,
reduce_addition_flag,
reduce_adjustment_flag,
reduce_retirement_flag,
disabled_flag, --hh
over_depreciate_option -- hh
FROM FA_BOOKS
WHERE ASSET_ID = px_asset_fin_rec_new.group_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND transaction_header_id_out is null;
fa_debug_pkg.add(l_calling_fn,'selected OD option',l_over_depreciate_option, p_log_level_rec => p_log_level_rec);