[Home] [Help]
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;
name => 'FA_DEPRN_UPDATE_BONUS_RULE');
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;
/*Bug 6950629 -> Only Contract_id adjustment, adjusted_cost calc is not updated*/
if not nvl(p_asset_fin_rec_adj.contract_change_flag,FALSE) then /*Bug 7712756 added nvl */
-- Bug7017134: There is no need to call calc_raf_adj_cost in case of
-- GC, MR, MS.
--Bug7446301: added nvl
if (nvl(px_trans_rec.transaction_key, 'XX') not in ('GC', 'MR', 'MS')) then
if not FA_AMORT_PVT.calc_raf_adj_cost
(p_trans_rec => px_trans_rec,
p_asset_hdr_rec => p_asset_hdr_rec,
p_asset_desc_rec => p_asset_desc_rec,
p_asset_type_rec => p_asset_type_rec,
p_asset_fin_rec_old => p_asset_fin_rec_old,
px_asset_fin_rec_new => px_asset_fin_rec_new,
p_asset_deprn_rec_adj => p_asset_deprn_rec_adj,
p_asset_deprn_rec_new => px_asset_deprn_rec_new,
p_period_rec => p_period_rec,
p_group_reclass_options_rec => p_group_reclass_options_rec,
p_mrc_sob_type_code => p_mrc_sob_type_code,
p_log_level_rec => p_log_level_rec
) then
raise calc_err;
SELECT sum(decode(adj.debit_credit_flag,'DR',
-1 * adj.adjustment_amount,
adj.adjustment_amount))
INTO l_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))
INTO l_deprn_exp
FROM fa_adjustments_mrc_v 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
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))
INTO l_deprn_exp
FROM fa_adjustments_mrc_v 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;
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_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');
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_adjustments_mrc_v
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';
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,
p_bonus_expense => l_bonus_deprn_exp,
p_deprn_reserve => l_deprn_rsv,
p_bonus_reserve => l_bonus_deprn_rsv,
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_books_mrc_v 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 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_books_mrc_v 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 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;
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
and deprn_method_code = px_asset_fin_rec_new.deprn_method_code;
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(*)
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;