[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select asset_number anum
, book_type_code book
, allowed_deprn_limit_amount limit_amt
from fa_adjustments_t
where extended_deprn_flag = 'Y'
and batch_id = p_batch_id
order by asset_number;
select asset_id aid
, fat.asset_number anum
, book_type_code book
from fa_adjustments_t fat
, fa_additions_b fab
where batch_id = p_batch_id
and fat.asset_number = fab.asset_number
order by book_type_code;
select fadp.period_name name
, fat.asset_number num
, fat.book_type_code book
from fa_deprn_periods fadp
, fa_adjustments_t fat
where fadp.period_counter = fat.period_counter_fully_reserved
and fadp.book_type_code = fat.book_type_code
and fat.batch_id = p_batch_id;
l_query := 'select asset_number,
asset_type,
attribute_category_code,
bonus_rule,
book_type_code,
ceiling_name,
depreciate_flag,
deprn_limit_type,
deprn_method_code,
description,
extended_deprn_flag,
period_name,
prorate_convention_code,
short_fiscal_year_flag,
transaction_name,
adjusted_rate,
allowed_deprn_limit,
allowed_deprn_limit_amount,
basic_rate,
cost,
deprn_reserve,
extended_depreciation_period,
fully_rsvd_revals_counter,
group_asset_id,
itc_amount_id,
life_in_months,
original_cost,
period_counter_fully_reserved,
production_capacity,
reval_amortization_basis,
reval_ceiling,
reval_reserve,
salvage_value,
unrevalued_cost,
ytd_deprn,
ytd_reval_deprn_expense,
amortization_start_date,
conversion_date,
date_placed_in_service,
original_deprn_start_date,
impairment_reserve,
'||p_batch_id||'
from fa_extended_deprn_v
where '|| w_clause ||' ';
insert into fa_adjustments_t
(
asset_number,
asset_type,
attribute_category_code,
bonus_rule,
book_type_code,
ceiling_name,
depreciate_flag,
deprn_limit_type,
deprn_method_code,
description,
extended_deprn_flag,
period_name,
prorate_convention_code,
short_fiscal_year_flag,
transaction_name,
adjusted_rate,
allowed_deprn_limit,
allowed_deprn_limit_amount,
basic_rate,
cost,
deprn_reserve,
extended_depreciation_period,
fully_rsvd_revals_counter,
group_asset_id,
itc_amount_id,
life_in_months,
original_cost,
period_counter_fully_reserved,
production_capacity,
reval_amortization_basis,
reval_ceiling,
reval_reserve,
salvage_value,
unrevalued_cost,
ytd_deprn,
ytd_reval_deprn_expense,
amortization_start_date,
conversion_date,
date_placed_in_service,
original_deprn_start_date,
impairment_reserve,
batch_id,
request_id
)
values
(
l_asset_number(j),
l_asset_type(j),
l_attribute_category_code(j),
l_bonus_rule(j),
l_book_type_code(j),
l_ceiling_name(j),
l_depreciate_flag(j),
l_deprn_limit_type(j),
l_deprn_method_code(j),
l_description(j),
l_extended_deprn_flag(j),
l_period_name(j),
l_prorate_convention_code(j),
l_short_fiscal_year_flag(j),
l_transaction_name(j),
l_adjusted_rate(j),
l_allowed_deprn_limit(j),
l_allowed_deprn_limit_amount(j),
l_basic_rate(j),
l_cost(j),
l_deprn_reserve(j),
l_extended_depreciation_period(j),
l_fully_rsvd_revals_counter(j),
l_group_asset_id(j),
l_itc_amount_id(j),
l_life_in_months(j),
l_original_cost(j),
l_period_counter_fully_rsv(j),
l_production_capacity(j),
l_reval_amortization_basis(j),
l_reval_ceiling(j),
l_reval_reserve(j),
l_salvage_value(j),
l_unrevalued_cost(j),
l_ytd_deprn(j),
l_ytd_reval_deprn_expense(j),
l_amortization_start_date(j),
l_conversion_date(j),
l_date_placed_in_service(j),
l_original_deprn_start_date(j),
l_imp_reserve(j),
l_batch_id(j),
-1*l_batch_id(j));
select cp.period_name name
into l_name
from fa_book_controls bc
, fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
, fa_adjustments_t fat
where bc.book_type_code = i.book
and fat.batch_id = p_batch_id
and fat.asset_number = i.anum
and bc.deprn_calendar = ct.calendar_type
and cp.calendar_type = ct.calendar_type
and bc.fiscal_year_name = ct.fiscal_year_name
and fy.fiscal_year_name = ct.fiscal_year_name
and cp.period_num = 1
and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
and fy.fiscal_year = (fat.extended_depreciation_period-1)/ct.number_per_fiscal_year
and cp.start_date = fy.start_date;
update fa_adjustments_t
set extended_deprn_period_name = l_name
, extended_deprn_limit = i.limit_amt
where book_type_code = i.book
and asset_number = i.anum
and batch_id = p_batch_id;
update fa_adjustments_t
set period_name = i.name
where book_type_code = i.book
and asset_number = i.num
and batch_id = p_batch_id;
select cp.period_name name
, fy.fiscal_year*ct.number_per_fiscal_year + 1 pctr
into l_name
, l_pctr
from fa_books bks
, fa_book_controls bc
, fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where bc.book_type_code = i.book
and bks.asset_id = i.aid
and bc.book_type_code = bks.book_type_code
and bc.deprn_calendar = ct.calendar_type
and cp.calendar_type = ct.calendar_type
and bc.fiscal_year_name = ct.fiscal_year_name
and fy.fiscal_year_name = ct.fiscal_year_name
and cp.period_num = 1
and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
and fy.fiscal_year = decode(sign(2007 -
decode(mod(bks.period_counter_fully_reserved,ct.number_per_fiscal_year)
, 0 , (bks.period_counter_fully_reserved-1)/ct.number_per_fiscal_year
, bks.period_counter_fully_reserved/ct.number_per_fiscal_year))
, 1, (select min(fiscal_year)
from fa_fiscal_year
where fiscal_year_name = ct.fiscal_year_name
and start_date >= to_date('01/04/2007', 'DD/MM/RRRR'))
/* Bug 7229538. The existing Hard coded year 2007 is removed.
Because it is not guarantee that fiscal year always starts from 01-Apr-2007.
In some cases the year might have started from 01-Jan-207.
In that case if you hardcode to 2007 This query never returns any row for those assets
which are already fully reserved before 01-Apr-2007. So changed to get the immediate fiscal year
which is after 31-Mar-2007. If the year starts from 01-Apr-2007 then it returns 2007 otherwise it returns 2008*/
, ceil((bks.period_counter_fully_reserved)/ct.number_per_fiscal_year))
and cp.start_date = fy.start_date
and bks.period_counter_fully_reserved is not null
and bks.transaction_header_id_out is null;
update fa_adjustments_t
set extended_deprn_period_name = l_name
, extended_depreciation_period = l_pctr
, posting_status = 'POST'
, extended_deprn_flag = 'Y'
, extended_deprn_limit = 1
where book_type_code = i.book
and asset_number = i.anum
and batch_id = p_batch_id;
update fa_adjustments_t
set posting_status = 'POST'
, extended_deprn_flag = action_flag
where batch_id = p_batch_id;