[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_update_override_status in boolean, -- default ture,
P_period_of_addition in varchar2, -- default NULL,
P_transaction_date_entered in date, -- default null,
P_mode in varchar2, -- default NULL,
P_mrc_sob_type_code in varchar2, -- default 'N',
X_new_deprn_amount out nocopy number,
X_new_bonus_amount out nocopy number,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return number is
-- variables for the input and output parameters
x_counter number;
select 1
from FA_TRACK_MEMBERS
where group_asset_id = P_group_asset_id
and period_counter = h_period_counter - 1
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select period_counter
from fa_deprn_periods
where book_type_code=P_book_type_code
and fiscal_year=P_fiscal_year
and period_num=P_period_counter;
select period_counter
from fa_mc_deprn_periods
where book_type_code=P_book_type_code
and fiscal_year=P_fiscal_year
and period_num=P_period_counter
and set_of_books_id = h_set_of_books_id;
select deprn_calendar into h_deprn_calendar
from fa_book_controls
where book_type_code=P_Book_Type_code;
select deprn_calendar into h_deprn_calendar
from fa_book_controls
where book_type_code=P_Book_Type_code;
P_update_override_status => P_update_override_status,
P_mrc_sob_type_code => p_mrc_sob_type_code,
P_mode => P_mode,
X_new_deprn_amount => X_new_deprn_amount,
X_new_bonus_amount => X_new_bonus_amount,
p_log_level_rec => p_log_level_rec) then
raise main_err;
delete fa_track_members;
P_update_override_status in boolean, -- default true,
P_mrc_sob_type_code in varchar2, -- default 'N',
P_mode in varchar2, -- default NULL,
X_new_deprn_amount out nocopy number,
X_new_bonus_amount out nocopy number,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
-- variables for the input and output parameters
h_book_type_code varchar2(15);
select bk.asset_id,
bk.cost,
decode(h_excl_sv,'N',bk.adjusted_cost,bk.adjusted_cost+bk.salvage_value) adjusted_cost,
bk.salvage_value,
bk.recoverable_cost,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_retired,
bk.period_counter_fully_reserved,
bk.eofy_reserve,
nvl(ds.period_counter,0) period_counter,
nvl(ds.deprn_reserve,0) deprn_reserve,
nvl(ds.ytd_deprn,0) ytd_deprn,
nvl(ds.bonus_deprn_reserve,0) bonus_deprn_reserve,
nvl(ds.bonus_ytd_deprn,0) bonus_ytd_deprn
from fa_books bk,
fa_deprn_summary ds,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.book_type_code = h_book_type_code
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and ds.book_type_code = h_book_type_code
and ds.asset_id = bk.asset_id
and ds.period_counter =
(select max(ds1.period_counter)
from fa_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.period_counter <= h_period_counter - 1)
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select nvl(sum(decode(aj.adjustment_type, 'RESERVE',decode(aj.debit_credit_flag, 'DR', -1, 1),
'EXPENSE',decode(aj.debit_credit_flag, 'CR', -1, 1))* aj.adjustment_amount), 0)
from fa_adjustments aj
where aj.asset_id = c_asset_id
and aj.book_type_code = h_book_type_code
and aj.period_counter_created = h_period_counter
and aj.adjustment_type in ('RESERVE', 'EXPENSE');
select nvl(sum(decode(aj.adjustment_type, 'RESERVE',decode(aj.debit_credit_flag, 'DR', -1, 1),
'EXPENSE',decode(aj.debit_credit_flag, 'CR', -1, 1))* aj.adjustment_amount), 0)
from fa_adjustments_mrc_v aj
where aj.asset_id = c_asset_id
and aj.book_type_code = h_book_type_code
and aj.period_counter_created = h_period_counter
and aj.adjustment_type in ('RESERVE', 'EXPENSE');
select bk.asset_id,
bk.cost,
decode(h_excl_sv,'N',bk.adjusted_cost,bk.adjusted_cost+bk.salvage_value) adjusted_cost,
bk.salvage_value,
bk.recoverable_cost,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_retired,
bk.period_counter_fully_reserved,
bk.eofy_reserve,
nvl(ds.period_counter,0) period_counter,
nvl(ds.deprn_reserve,0) deprn_reserve,
nvl(ds.ytd_deprn,0) ytd_deprn,
nvl(ds.bonus_deprn_reserve,0) bonus_deprn_reserve,
nvl(ds.bonus_ytd_deprn,0) bonus_ytd_deprn
from fa_mc_books bk,
fa_mc_deprn_summary ds,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.book_type_code = h_book_type_code
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and bk.set_of_books_id = h_set_of_books_id
and ds.book_type_code = h_book_type_code
and ds.asset_id = bk.asset_id
and ds.period_counter =
(select max(ds1.period_counter)
from fa_mc_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.set_of_books_id = h_set_of_books_id
and ds1.period_counter <= h_period_counter - 1)
and ds.set_of_books_id(+) = h_set_of_books_id
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and bk.book_type_code = h_book_type_code
and ad.asset_id = bk.asset_id
and ad.asset_type='CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_mc_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.book_type_code = h_book_type_code
and bk.set_of_books_id = h_set_of_books_id
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_retired is null
and bk.book_type_code = h_book_type_code
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_mc_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_retired is null
and bk.book_type_code = h_book_type_code
and bk.set_of_books_id = h_set_of_books_id
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_reserved is null
and bk.book_type_code = h_book_type_code
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_mc_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_reserved is null
and bk.book_type_code = h_book_type_code
and bk.set_of_books_id = h_set_of_books_id
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_retired is null
and bk.period_counter_fully_reserved is null
and bk.book_type_code = h_book_type_code
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select sum(bk.cost),
decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
sum(bk.recoverable_cost)
from fa_mc_books bk,
fa_additions_b ad
where bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.period_counter_fully_retired is null
and bk.period_counter_fully_reserved is null
and bk.book_type_code = h_book_type_code
and bk.set_of_books_id = h_set_of_books_id
and bk.depreciate_flag = 'YES'
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED';
select 'Y'
from fa_track_members
where group_asset_id=h_group_asset_id
and member_asset_id=h_member_asset_id
and period_counter=h_period_counter
and (fully_reserved_flag is not null
or
fully_retired_flag is not null)
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select tr.member_asset_id,
tr.cost,
tr.allocation_basis,
nvl(tr.override_flag,'N') override_flag,
bk.adjusted_recoverable_cost,
bk.recoverable_cost,
bk.salvage_value,
ds.deprn_reserve,
ds.ytd_deprn,
ds.bonus_deprn_reserve,
ds.bonus_ytd_deprn
from fa_track_members tr,
fa_books bk,
fa_deprn_summary ds,
fa_deprn_periods dp,
fa_additions_b ad
where tr.group_asset_id = h_group_asset_id
and tr.period_counter = h_period_counter
and tr.fiscal_year = h_fiscal_year
and nvl(tr.set_of_books_id,-99) = nvl(h_set_of_books_id,-99)
and nvl(tr.fully_reserved_flag,'N') <> 'Y'
-- and nvl(tr.override_flag,'N') <> 'Y'
and bk.book_type_code = h_book_type_code
and bk.asset_id = tr.member_asset_id
and bk.date_effective <= nvl(dp.period_close_date,sysdate)
and nvl(bk.date_ineffective,sysdate) >= nvl(dp.period_close_date,sysdate)
and dp.book_type_code = bk.book_type_code
and dp.period_counter = h_period_counter
and ds.book_type_code = bk.book_type_code
and ds.period_counter = h_period_counter - 1
and ds.asset_id = bk.asset_id
and ad.asset_id = bk.asset_id
order by ad.asset_number;
select tr.member_asset_id,
tr.cost,
tr.allocation_basis,
nvl(tr.override_flag,'N') override_flag,
bk.adjusted_recoverable_cost,
bk.recoverable_cost,
bk.salvage_value,
ds.deprn_reserve,
ds.ytd_deprn,
ds.bonus_deprn_reserve,
ds.bonus_ytd_deprn
from fa_track_members tr,
fa_mc_books bk,
fa_mc_deprn_summary ds,
fa_mc_deprn_periods dp,
fa_additions_b ad
where tr.group_asset_id = h_group_asset_id
and tr.period_counter = h_period_counter
and tr.fiscal_year = h_fiscal_year
and nvl(tr.set_of_books_id,-99) = nvl(h_set_of_books_id,-99)
and nvl(tr.fully_reserved_flag,'N') <> 'Y'
-- and nvl(tr.override_flag,'N') <> 'Y'
and bk.book_type_code = h_book_type_code
and bk.asset_id = tr.member_asset_id
and bk.set_of_books_id = h_set_of_books_id
and bk.date_effective <= nvl(dp.period_close_date,sysdate)
and nvl(bk.date_ineffective,sysdate) >= nvl(dp.period_close_date,sysdate)
and dp.book_type_code = bk.book_type_code
and dp.period_counter = h_period_counter
and dp.set_of_books_id = h_set_of_books_id
and ds.book_type_code = bk.book_type_code
and ds.period_counter = h_period_counter - 1
and ds.asset_id = bk.asset_id
and ds.set_of_books_id = h_set_of_books_id
and ad.asset_id = bk.asset_id
order by ad.asset_number;
select decode(h_excl_sv,'N',bk.adjusted_cost,bk.adjusted_cost+bk.salvage_value) adjusted_cost,
bk.recoverable_cost recoverable_cost
from fa_books bk
where bk.book_type_code=h_book_type_code
and bk.asset_id = h_group_asset_id
and bk.date_ineffective is null;
select decode(h_excl_sv,'N',bk.adjusted_cost,bk.adjusted_cost+bk.salvage_value) adjusted_cost,
bk.recoverable_cost recoverable_cost
from fa_mc_books bk
where bk.book_type_code=h_book_type_code
and bk.asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.set_of_books_id = h_set_of_books_id;
select period_num
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = p_period_counter;
select period_num
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and period_counter = p_period_counter
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = nvl(p_member_asset_id,th2.asset_id);
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and adj.set_of_books_id = h_set_of_books_id
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = nvl(p_member_asset_id,th2.asset_id);
select member_asset_id
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and (nvl(fully_reserved_flag,'N') = 'Y' or nvl(override_flag,'N') = 'Y');
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_unplanned_member_asset
and th2.transaction_type_code = 'ADJUSTMENT'
and th2.transaction_key in ('UA','UE');
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj,
fa_transaction_headers th
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_unplanned_member_asset
and adj.period_counter_adjusted = h_period_counter
and adj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'ADJUSTMENT'
and th.transaction_key in ('UA','UE');
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and adj.set_of_books_id = h_set_of_books_id
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_unplanned_member_asset
and th2.transaction_type_code = 'ADJUSTMENT'
and th2.transaction_key in ('UA','UE');
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj,
fa_transaction_headers th
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.set_of_books_id = h_set_of_books_id
and adj.period_counter_adjusted = h_period_counter
and adj.transaction_header_id = th.transaction_header_id
and th.transaction_type_code = 'ADJUSTMENT'
and th.transaction_key in ('UA','UE');
select member_asset_id
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select db.rule_name
from fa_deprn_basis_rules db
, fa_methods mt
, fa_books bk
where db.deprn_basis_rule_id = mt.deprn_basis_rule_id
and mt.method_code = bk.deprn_method_code
and nvl(mt.life_in_months, -99) = nvl(bk.life_in_months, -99)
and bk.book_type_code = h_book_type_code
and bk.asset_id = P_group_asset_id
and bk.transaction_header_id_out is null;
P_update_override_status => P_update_override_status,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => P_mrc_sob_type_code,
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
'Updated current period row into p_track_member_table', '***', p_log_level_rec);
select fiscal_year into h_ds_fy
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = mem.period_counter;
P_update_override_status => P_update_override_status,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => 'P',
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
update fa_books -- ENERGY
set adjusted_cost = adjusted_cost - nvl(l_track_member_out.allocated_deprn_amount, 0) -- ENERGY
where transaction_header_id_out is null -- ENERGY
and asset_id = mem.asset_id -- ENERGY
and book_type_code = h_book_type_code; -- ENERGY
select fiscal_year into h_ds_fy
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and period_counter = mem.period_counter
and set_of_books_id = h_set_of_books_id;
P_update_override_status => P_update_override_status,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => 'R',
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
update fa_books_mrc_v -- ENERGY
set adjusted_cost = adjusted_cost - nvl(l_track_member_out.allocated_deprn_amount, 0) -- ENERGY
where transaction_header_id_out is null -- ENERGY
and asset_id = mem.asset_id -- ENERGY
and book_type_code = h_book_type_code; -- ENERGY
select nvl(sum(system_deprn_amount),0),
nvl(sum(system_bonus_amount),0)
into x_sum_of_deprn_amount,x_sum_of_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and member_asset_id <> l_track_member_in.member_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select fully_reserved_flag
into x_fully_reserved_flag
from fa_track_members
where group_asset_id = P_group_asset_id
and member_asset_id = l_track_member_in.member_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
fa_debug_pkg.add(l_calling_fn,'+++ Just before update table or PL/SQL table +++', P_mode, p_log_level_rec);
'In final asset rounding, Updated following row into p_track_member_table',
l_last_asset_index, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'before update ', '1', p_log_level_rec);
update FA_TRACK_MEMBERS
set allocated_deprn_amount = x_allocated_deprn_amount,
allocated_bonus_amount = x_allocated_bonus_amount,
fully_reserved_flag = x_fully_reserved_flag,
system_deprn_amount = h_system_deprn_amount,
system_bonus_amount = h_system_bonus_amount,
deprn_reserve = h_deprn_reserve,
ytd_deprn = h_ytd_deprn,
bonus_deprn_reserve = h_bonus_deprn_reserve,
bonus_ytd_deprn = h_bonus_ytd_deprn
where group_asset_id = P_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(decode(nvl(P_subtraction_flag,'N'),'N',
decode(nvl(override_flag,'N'),'Y',system_deprn_amount,allocated_deprn_amount), -- Periodic Case
system_deprn_amount)), -- Subtraction Case
sum(decode(nvl(P_subtraction_flag,'N'),'N',
decode(nvl(override_flag,'N'),'Y',system_bonus_amount,allocated_bonus_amount), -- Periodic Case
system_bonus_amount)) -- Subtraction Case
into x_total_allocated_deprn_amount,x_total_allocated_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = h_set_of_books_id;
select nvl(sum(decode(nvl(P_subtraction_flag,'N'),'N',
decode(nvl(fully_reserved_flag,'N'),'Y',
allocated_deprn_amount,
system_deprn_amount), -- Normal Case
system_deprn_amount)), -- Subtraction Case
0),
nvl(sum(decode(nvl(P_subtraction_flag,'N'),'N',
decode(nvl(fully_reserved_flag,'N'),'Y',
allocated_bonus_amount,
system_bonus_amount), -- Normal Case
system_deprn_amount)), -- Subtraction Case
0)
into h_fixed_deprn_amount,h_fixed_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and (nvl(fully_reserved_flag,'N') = 'Y' or nvl(override_flag,'N') = 'Y');
Select nvl(sum(allocation_basis),0) into h_total_allocation_basis
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(fully_reserved_flag,'N') <> 'Y'
and nvl(override_flag,'N') <> 'Y';
P_update_override_status => P_update_override_status,
P_member_override_flag => h_member_override_flag,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => 'P',
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
'In reallocation logic, Updated following row into p_track_member_table',
l_processed_number, p_log_level_rec);
P_update_override_status => P_update_override_status,
P_member_override_flag => h_member_override_flag,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => 'P',
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
P_update_override_status => P_update_override_status,
P_member_override_flag => h_member_override_flag,
PX_difference_deprn_amount => h_difference_deprn_amount,
PX_difference_bonus_amount => h_difference_bonus_amount,
X_system_deprn_amount => h_system_deprn_amount,
X_system_bonus_amount => h_system_bonus_amount,
X_track_member_out => l_track_member_out,
P_mrc_sob_type_code => 'R',
P_mode => P_mode,
P_rec_cost_for_odda => h_rec_cost_for_odda,
P_sv_for_odda => h_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
raise allocate_err;
select nvl(sum(system_deprn_amount),0),nvl(sum(system_bonus_amount),0)
into x_sum_of_deprn_amount,x_sum_of_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and member_asset_id <> l_track_member_in.member_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(fully_reserved_flag,'N') <> 'Y'
and nvl(override_flag,'N') <> 'Y'
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
fa_debug_pkg.add(l_calling_fn,'+++ Just before update table or PL/SQL table (Realloc) +++', P_mode, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'before update ', '2', p_log_level_rec);
update FA_TRACK_MEMBERS
set allocated_deprn_amount = x_allocated_deprn_amount,
allocated_bonus_amount = x_allocated_bonus_amount,
fully_reserved_flag = x_fully_reserved_flag,
system_deprn_amount = h_system_deprn_amount,
system_bonus_amount = h_system_bonus_amount,
deprn_reserve = h_deprn_reserve,
ytd_deprn = h_ytd_deprn,
bonus_deprn_reserve = h_bonus_deprn_reserve,
bonus_ytd_deprn = h_bonus_ytd_deprn
where group_asset_id = P_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(allocated_deprn_amount),sum(allocated_bonus_amount)
into x_total_allocated_deprn_amount,x_total_allocated_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(allocated_deprn_amount),sum(allocated_bonus_amount)
into x_total_allocated_deprn_amount,x_total_allocated_bonus_amount
from fa_track_members
where group_asset_id = P_group_asset_id
and period_counter = P_period_counter
and fiscal_year = P_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
fa_debug_pkg.add(l_calling_fn, 'Inserted new row into p_track_member_table',
l_processed_number, p_log_level_rec);
UPDATE FA_BOOKS_SUMMARY
SET DEPRN_AMOUNT = h_unplanned_expense + DEPRN_AMOUNT,
YTD_DEPRN = h_unplanned_expense + YTD_DEPRN
WHERE BOOK_TYPE_CODE = h_book_type_code
AND PERIOD_COUNTER = h_period_counter
AND ASSET_ID = h_unplanned_member_asset;
UPDATE FA_BOOKS_SUMMARY_MRC_V
SET DEPRN_AMOUNT = h_unplanned_expense + DEPRN_AMOUNT,
YTD_DEPRN = h_unplanned_expense + YTD_DEPRN
WHERE BOOK_TYPE_CODE = h_book_type_code
AND PERIOD_COUNTER = h_period_counter
AND ASSET_ID = h_unplanned_member_asset;
P_update_override_status in boolean, -- default true,
P_member_override_flag in varchar2, -- default null,
PX_difference_deprn_amount in out nocopy number,
PX_difference_bonus_amount in out nocopy number,
X_system_deprn_amount out nocopy number,
X_system_bonus_amount out nocopy number,
X_track_member_out out nocopy track_member_struct,
P_mrc_sob_type_code in varchar2, -- default 'N',
P_mode in Varchar2,
P_rec_cost_for_odda in number,
P_sv_for_odda in number,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
-- variables
x_calc_done varchar2(1);
allocation_main_update_err exception;
select 1
from fa_track_members
where group_asset_id = h_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = h_period_counter
and fiscal_year = h_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select period_num
from fa_deprn_periods
where book_type_code = P_book_type_code
and period_counter = p_per_counter;
select period_num
from fa_mc_deprn_periods
where book_type_code = P_book_type_code
and period_counter = p_per_counter
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = p_group_asset_id
and adj.book_type_code = p_book_type_code
and adj.period_counter_adjusted = p_period_counter
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = nvl(p_member_asset_id,th2.asset_id);
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = p_group_asset_id
and adj.book_type_code = p_book_type_code
and adj.period_counter_adjusted = p_period_counter
and adj.set_of_books_id = h_set_of_books_id
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = nvl(p_member_asset_id,th2.asset_id);
p_update_override_status => P_update_override_status,
p_mrc_sob_type_code => P_mrc_sob_type_code,
p_recoverable_cost => P_rec_cost_for_odda,
p_salvage_value => P_sv_for_odda,
p_log_level_rec => p_log_level_rec) then
rollback to member_override;
raise allocation_main_update_err;
fa_debug_pkg.add('allocation_main','insert/update check', x_dummy, p_log_level_rec);
Insert into FA_TRACK_MEMBERS(group_asset_id,
member_asset_id,
period_counter,
fiscal_year,
set_of_books_id,
allocation_basis,
total_allocation_basis,
allocated_deprn_amount,
allocated_bonus_amount,
fully_reserved_flag,
system_deprn_amount,
system_bonus_amount,
cost,
adjusted_cost,
salvage_value,
recoverable_cost,
adjusted_recoverable_cost,
override_flag,
deprn_reserve,
ytd_deprn,
bonus_deprn_reserve,
bonus_ytd_deprn,
deprn_override_flag)
values
(h_group_asset_id,
h_member_asset_id,
h_period_counter,
h_fiscal_year,
h_set_of_books_id,
h_allocation_basis,
h_total_allocation_basis,
nvl(x_allocated_deprn_amount, 0), --bug6923135
nvl(x_allocated_bonus_amount, 0), --bug6923135
x_fully_reserved_flag,
nvl(X_system_deprn_amount, 0), --bug6923135
nvl(X_system_bonus_amount, 0), --bug6923135
h_cost,
h_adjusted_cost,
h_salvage_value,
h_recoverable_cost,
h_adjusted_recoverable_cost,
x_override_flag,
x_deprn_reserve,
x_ytd_deprn,
x_bonus_deprn_reserve,
x_bonus_ytd_deprn,
h_deprn_override);
else -- Need to update
if nvl(P_member_override_flag,'N') <> 'Y' then
Update FA_TRACK_MEMBERS
set allocation_basis = h_allocation_basis,
total_allocation_basis = h_total_allocation_basis,
allocated_deprn_amount = x_allocated_deprn_amount,
allocated_bonus_amount = x_allocated_bonus_amount,
fully_reserved_flag = x_fully_reserved_flag,
system_deprn_amount = x_system_deprn_amount,
system_bonus_amount = x_system_bonus_amount,
deprn_reserve = x_deprn_reserve,
ytd_deprn = x_ytd_deprn,
bonus_deprn_reserve = x_bonus_deprn_reserve,
bonus_ytd_deprn = x_bonus_ytd_deprn
where group_asset_id = h_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = h_period_counter
and fiscal_year = h_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
Update FA_TRACK_MEMBERS
set system_deprn_amount = x_system_deprn_amount,
system_bonus_amount = x_system_bonus_amount
where group_asset_id = h_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = h_period_counter
and fiscal_year = h_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
when allocation_main_update_err then
rollback to member_override;
name => 'FA_CANNOT_UPDATE_OVERRIDE',
p_log_level_rec => p_log_level_rec);
FUNCTION update_deprn_basis(p_group_rule_in in fa_std_types.fa_deprn_rule_in_struct,
p_apply_reduction_flag in varchar2, -- default NULL,
p_mode in varchar2,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) -- default NULL)
return boolean is
--* Structure to call Deprn Basis Rule
fa_rule_in fa_std_types.fa_deprn_rule_in_struct;
h_memory_update_status varchar2(3);
l_calling_fn varchar2(40) := 'fa_track_member_pvt.update_deprn_basis';
select bk.group_asset_id group_asset_id,
bk.asset_id member_asset_id,
bk.cost cost,
bk.salvage_value salvage_value,
bk.recoverable_cost recoverable_cost,
bk.adjusted_cost adjusted_cost,
bk.adjusted_recoverable_cost adjusted_recoverable_cost,
bk.period_counter_fully_retired fully_retired_flag,
bk.period_counter_fully_reserved fully_reserved_flag,
bk.eofy_reserve bk_eofy_reserve,
bk.eofy_adj_cost eofy_adj_cost,
ds.period_counter ds_period_counter,
ds.deprn_reserve ds_deprn_reserve,
ds.ytd_deprn ds_ytd_deprn,
ds.bonus_deprn_reserve ds_bonus_deprn_reserve,
ds.bonus_ytd_deprn ds_bonus_ytd_deprn,
temp.deprn_reserve temp_deprn_reserve,
temp.ytd_deprn temp_ytd_deprn,
temp.bonus_deprn_reserve temp_bonus_deprn_reserve,
temp.bonus_ytd_deprn temp_bonus_ytd_deprn,
temp.prior_year_reserve temp_prior_year_reserve,
temp.eofy_recoverable_cost temp_eofy_recoverable_cost,
temp.eop_recoverable_cost temp_eop_recoverable_cost,
temp.eofy_salvage_value temp_eofy_salvage_value,
temp.eop_salvage_value temp_eop_salvage_value
from fa_books bk,
fa_deprn_summary ds,
fa_track_members temp
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and ds.book_type_code = bk.book_type_code
and ds.asset_id = bk.asset_id
and (ds.period_counter =
(select max(ds1.period_counter)
from fa_deprn_summary ds1
where ds1.book_type_code=h_book_type_code
and ds1.asset_id=bk.asset_id
and ds1.period_counter <= h_period_counter - 1)
or
ds.period_counter = nvl(bk.period_counter_fully_reserved,-99))
and temp.member_asset_id (+) = bk.asset_id
and temp.period_counter (+) = h_period_counter
and temp.fiscal_year (+) = h_fiscal_year
and temp.set_of_books_id (+) = nvl(h_set_of_books_id,-99);
select bk.group_asset_id group_asset_id,
bk.asset_id member_asset_id,
bk.cost cost,
bk.salvage_value salvage_value,
bk.recoverable_cost recoverable_cost,
bk.adjusted_cost adjusted_cost,
bk.adjusted_recoverable_cost adjusted_recoverable_cost,
bk.period_counter_fully_retired fully_retired_flag,
bk.period_counter_fully_reserved fully_reserved_flag,
bk.eofy_reserve bk_eofy_reserve,
bk.eofy_adj_cost eofy_adj_cost,
ds.period_counter ds_period_counter,
ds.deprn_reserve ds_deprn_reserve,
ds.ytd_deprn ds_ytd_deprn,
ds.bonus_deprn_reserve ds_bonus_deprn_reserve,
ds.bonus_ytd_deprn ds_bonus_ytd_deprn,
temp.deprn_reserve temp_deprn_reserve,
temp.ytd_deprn temp_ytd_deprn,
temp.bonus_deprn_reserve temp_bonus_deprn_reserve,
temp.bonus_ytd_deprn temp_bonus_ytd_deprn,
temp.prior_year_reserve temp_prior_year_reserve,
temp.eofy_recoverable_cost temp_eofy_recoverable_cost,
temp.eop_recoverable_cost temp_eop_recoverable_cost,
temp.eofy_salvage_value temp_eofy_salvage_value,
temp.eop_salvage_value temp_eop_salvage_value
from fa_mc_books bk,
fa_mc_deprn_summary ds,
fa_track_members temp
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.set_of_books_id = h_set_of_books_id
and ds.book_type_code = bk.book_type_code
and ds.asset_id = bk.asset_id
-- and ds.period_counter = h_period_counter - 1
and (ds.period_counter =
(select max(ds1.period_counter)
from fa_mc_deprn_summary ds1
where ds1.book_type_code=h_book_type_code
and ds1.asset_id=bk.asset_id
and ds1.set_of_books_id = h_set_of_books_id
and ds1.period_counter <= h_period_counter - 1)
or
ds.period_counter = nvl(bk.period_counter_fully_reserved,-99))
and ds.set_of_books_id = h_set_of_books_id
and temp.member_asset_id (+) = bk.asset_id
and temp.period_counter (+) = h_period_counter
and temp.fiscal_year (+) = h_fiscal_year
and nvl(temp.set_of_books_id (+),-99) = nvl(h_set_of_books_id,-99);
select temp.cost,
temp.salvage_value,
temp.recoverable_cost,
temp.adjusted_cost,
temp.adjusted_recoverable_cost
from fa_track_members temp
where temp.group_asset_id = h_group_asset_id
and temp.member_asset_id = h_member_asset_id
and temp.period_counter = h_period_counter
and nvl(temp.set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select 1
from fa_track_members
where group_asset_id = h_group_asset_id
and member_asset_id = h_member_asset_id
and period_counter = h_period_counter
and fiscal_year = h_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_member_asset_id;
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.period_counter_adjusted = h_period_counter;
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and adj.set_of_books_id = h_set_of_books_id
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_member_asset_id;
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_mc_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.set_of_books_id = h_set_of_books_id
and adj.period_counter_adjusted = h_period_counter;
select new_bk.cost - old_bk.cost,
decode(new_bk.salvage_type,'AMT',
decode(old_bk.salvage_type,'AMT', new_bk.salvage_value - old_bk.salvage_value,
new_bk.salvage_value)),
decode(new_bk.deprn_limit_type,'AMT',
decode(old_bk.deprn_limit_type,'AMT',
new_bk.adjusted_recoverable_cost - old_bk.adjusted_recoverable_cost,
new_bk.adjusted_recoverable_cost)),
new_bk.salvage_type,
decode(new_bk.salvage_type,'PCT',new_bk.percent_salvage_value),
new_bk.deprn_limit_type,
decode(new_bk.deprn_limit_type,'PCT',new_bk.allowed_deprn_limit)
from fa_books new_bk,
fa_books old_bk
where new_bk.book_type_code = p_group_rule_in.book_type_code
and new_bk.asset_id = h_member_asset_id
and new_bk.transaction_header_id_in = h_adj_member_trans_header_id
and old_bk.book_type_code = new_bk.book_type_code
and old_bk.asset_id = new_bk.asset_id
and old_bk.transaction_header_id_out = new_bk.transaction_header_id_in;
select new_bk.cost - old_bk.cost,
decode(new_bk.salvage_type,'AMT',
decode(old_bk.salvage_type,'AMT', new_bk.salvage_value - old_bk.salvage_value,
new_bk.salvage_value)),
decode(new_bk.deprn_limit_type,'AMT',
decode(old_bk.deprn_limit_type,'AMT',
new_bk.adjusted_recoverable_cost - old_bk.adjusted_recoverable_cost,
new_bk.adjusted_recoverable_cost)),
new_bk.salvage_type,
decode(new_bk.salvage_type,'PCT',new_bk.percent_salvage_value),
new_bk.deprn_limit_type,
decode(new_bk.deprn_limit_type,'PCT',new_bk.allowed_deprn_limit)
from fa_mc_books new_bk,
fa_mc_books old_bk
where new_bk.book_type_code = p_group_rule_in.book_type_code
and new_bk.asset_id = h_member_asset_id
and new_bk.transaction_header_id_in = h_adj_member_trans_header_id
and new_bk.set_of_books_id = h_set_of_books_id
and old_bk.book_type_code = new_bk.book_type_code
and old_bk.asset_id = new_bk.asset_id
and old_bk.transaction_header_id_out = new_bk.transaction_header_id_in
and old_bk.set_of_books_id = h_set_of_books_id;
select sum(decode(adj.adjustment_type,'RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
nvl(sum(decode(adj.adjustment_type,'PROCEEDS CLR',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),0) -
nvl(sum(decode(adj.adjustment_type,'REMOVALCOST CLR',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),0)
from fa_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_member_asset_id
and th2.transaction_header_id <> nvl(h_adj_member_trans_header_id,0);
select sum(decode(adj.adjustment_type,'RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
nvl(sum(decode(adj.adjustment_type,'PROCEEDS CLR',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),0) -
nvl(sum(decode(adj.adjustment_type,'REMOVALCOST CLR',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),0)
from fa_adjustments adj,
fa_transaction_headers th1
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_member_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and th1.asset_id = adj.asset_id
and th1.transaction_header_id <> nvl(h_adj_member_trans_header_id,0);
select sum(decode(adj.adjustment_type,'RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
nvl(sum(decode(adj.adjustment_type,'PROCEEDS CLR',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),0) -
nvl(sum(decode(adj.adjustment_type,'REMOVALCOST CLR',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),0)
from fa_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.period_counter_adjusted = h_period_counter
and nvl(adj.track_member_flag, 'N') = 'N'; -- ENERGY
select sum(decode(adj.adjustment_type,'RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
nvl(sum(decode(adj.adjustment_type,'PROCEEDS CLR',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),0) +
nvl(sum(decode(adj.adjustment_type,'REMOVALCOST CLR',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),0)
from fa_mc_adjustments adj,
fa_transaction_headers th1,
fa_transaction_headers th2
where adj.transaction_header_id = th1.transaction_header_id
and adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = h_period_counter
and adj.set_of_books_id = h_set_of_books_id
and th1.asset_id = adj.asset_id
and th1.member_transaction_header_id = th2.transaction_header_id
and th2.asset_id = h_member_asset_id
and th2.transaction_header_id <> nvl(h_adj_member_trans_header_id,0);
select sum(decode(adj.adjustment_type,'RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS RESERVE',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),
nvl(sum(decode(adj.adjustment_type,'PROCEEDS CLR',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),0) +
nvl(sum(decode(adj.adjustment_type,'REMOVALCOST CLR',
decode(adj.debit_credit_flag,
'CR',adj.adjustment_amount,
'DR', -1 * adj.adjustment_amount))),0)
from fa_mc_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.set_of_books_id = h_set_of_books_id
and adj.period_counter_adjusted = h_period_counter
and nvl(adj.track_member_flag, 'N') = 'N'; -- ENERGY
select ad.asset_type
from fa_transaction_headers th,
fa_additions_b ad
where th.transaction_header_id = p_group_rule_in.adj_transaction_header_id
and ad.asset_id = th.asset_id;
select new_bk.cost,
new_bk.salvage_value,
new_bk.adjusted_recoverable_cost,
new_bk.salvage_type,
new_bk.percent_salvage_value,
new_bk.deprn_limit_type,
new_bk.allowed_deprn_limit
from fa_books new_bk
where new_bk.book_type_code = p_group_rule_in.book_type_code
and new_bk.asset_id = h_member_asset_id
and new_bk.transaction_header_id_in = h_adj_member_trans_header_id;
select new_bk.cost,
new_bk.salvage_value,
new_bk.adjusted_recoverable_cost,
new_bk.salvage_type,
new_bk.percent_salvage_value,
new_bk.deprn_limit_type,
new_bk.allowed_deprn_limit
from fa_mc_books new_bk
where new_bk.book_type_code = p_group_rule_in.book_type_code
and new_bk.asset_id = h_member_asset_id
and new_bk.transaction_header_id_in = h_adj_member_trans_header_id
and new_bk.set_of_books_id = h_set_of_books_id;
select exclude_fully_rsv_flag
from fa_books
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null;
select exclude_fully_rsv_flag
from fa_books_mrc_v
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null;
fa_debug_pkg.add(l_calling_fn, '+++ Update Depreciable Basis for member assets +++', '+++', p_log_level_rec);
select asset_id,transaction_type_code,transaction_key,nvl(amortization_start_date,transaction_date_entered)
into h_asset_id,h_transaction_type_code,h_transaction_key,l_transaction_date_entered
from fa_transaction_headers
where transaction_header_id = h_adj_member_trans_header_id;
select fiscal_year_name, deprn_calendar
into h_fiscal_year_name,h_calendar_type
from fa_book_controls
where book_type_code=h_book_type_code;
select fiscal_year into h_trans_fiscal_year
from fa_fiscal_year
where fiscal_year_name = h_fiscal_year_name
and start_date <= l_transaction_date_entered
and end_date >= l_transaction_date_entered;
select period_num into h_trans_period_num
from fa_calendar_periods
where calendar_type = h_calendar_type
and start_date <= l_transaction_date_entered
and end_date >= l_transaction_date_entered;
select recognize_gain_loss,nvl(eofy_reserve,0),(-1)*nvl(reserve_retired,0)
into h_recognize_gain_loss,h_adj_eofy_reserve,h_reserve_retired
from fa_retirements
where transaction_header_id_in = h_adj_member_trans_header_id;
select recognize_gain_loss,nvl(eofy_reserve,0),(-1)*nvl(reserve_retired,0)
into h_recognize_gain_loss,h_adj_eofy_reserve,h_reserve_retired
from fa_mc_retirements
where transaction_header_id_in = h_adj_member_trans_header_id
and set_of_books_id = h_set_of_books_id;
select asset_type into x_asset_type
from fa_additions_b
where asset_id = l_track_member.member_asset_id;
fa_debug_pkg.add(l_calling_fn, '++ Finally updated p_track_member_table ', i, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'period_Counter to be updated', (h_period_counter + 1), p_log_level_rec);
select asset_type into x_asset_type
from fa_additions_b
where asset_id = h_member_asset_id;
fa_debug_pkg.add(l_calling_fn, 'update_deprn:h_new_eofy_reserve', h_new_eofy_reserve, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Updated fully reserved member asset', h_member_asset_id, p_log_level_rec);
else -- Regular Mode: Periodic Update after depreciation
if p_group_rule_in.mrc_sob_type_code <> 'R' then
For member in ALL_MEMBERS loop
h_member_asset_id := member.member_asset_id;
select asset_id,transaction_type_code
into h_asset_id,h_transaction_type_code
from fa_transaction_headers
where transaction_header_id = h_adj_member_trans_header_id;
select asset_type into x_asset_type
from fa_additions_b
where asset_id = member.member_asset_id;
select fiscal_year into h_ds_fy
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = member.ds_period_counter;
fa_debug_pkg.add(l_calling_fn, '++ update FA_BOOKS for ',member.member_asset_id, p_log_level_rec);
update fa_books
set adjusted_cost = fa_rule_out.new_adjusted_cost,
eofy_adj_cost = h_eofy_adj_cost,
eofy_reserve = h_new_eofy_reserve,
eop_adj_cost = h_eop_adj_cost,
prior_eofy_reserve = h_prior_eofy_reserve,
adjustment_required_status='NONE'
where book_type_code = p_group_rule_in.book_type_code
and asset_id = member.member_asset_id
and date_ineffective is null;
select asset_id,transaction_type_code
into h_asset_id,h_transaction_type_code
from fa_transaction_headers
where transaction_header_id = h_adj_member_trans_header_id;
select asset_type into x_asset_type
from fa_additions_b
where asset_id = member.member_asset_id;
select fiscal_year into h_ds_fy
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = member.ds_period_counter;
fa_debug_pkg.add(l_calling_fn, '++ update FA_BOOKS for ',member.member_asset_id, p_log_level_rec);
update fa_mc_books
set adjusted_cost = fa_rule_out.new_adjusted_cost,
eofy_adj_cost = h_eofy_adj_cost,
eofy_reserve = h_new_eofy_reserve,
eop_adj_cost = h_eop_adj_cost,
prior_eofy_reserve = h_prior_eofy_reserve
where book_type_code = p_group_rule_in.book_type_code
and asset_id = member.member_asset_id
and date_ineffective is null
and set_of_books_id = h_set_of_books_id;
end update_deprn_basis;
select member_asset_id,
allocated_deprn_amount,
fully_reserved_flag
from fa_track_members
where group_asset_id = p_group_asset_id
and period_counter = p_period_counter
and fiscal_year = p_fiscal_year
and nvl(set_of_books_id,-99) = nvl(h_set_of_books_id,-99);
select fa_transaction_headers_s.nextval
into p_transaction_header_id
from dual;
FA_TRANSACTION_HEADERS_PKG.Insert_Row
(X_Rowid => l_rowid,
X_Transaction_Header_Id => p_transaction_header_id,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Transaction_Type_Code => 'ADJUSTMENT',
X_Transaction_Date_Entered => p_transaction_date_entered,
X_Date_Effective => sysdate,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => -1,
X_Transaction_Name => NULL,
X_Invoice_Transaction_Id => NULL,
X_Source_Transaction_Header_Id => NULL,
X_Mass_Reference_Id => NULL,
X_Last_Update_Login => -1,
X_Transaction_Subtype => 'AMORTIZED',
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category_Code => null,
X_Transaction_Key => l_transaction_key,
X_Amortization_Start_Date => NULL,
X_Calling_Interface => null,
X_Mass_Transaction_ID => null,
X_Return_Status => l_status,
X_Calling_Fn => 'fa_track_member_pvt.ins_dd_adj',
p_log_level_rec => p_log_level_rec
);
fa_books_pkg.insert_row
(X_Rowid => l_rowid,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Date_Placed_In_Service => x_asset_fin_rec.date_placed_in_service,
X_Date_Effective => sysdate,
X_Deprn_Start_Date => x_asset_fin_rec.deprn_start_date,
X_Deprn_Method_Code => x_asset_fin_rec.deprn_method_code,
X_Life_In_Months => x_asset_fin_rec.life_in_months,
X_Rate_Adjustment_Factor => x_asset_fin_rec.rate_adjustment_factor,
X_Adjusted_Cost => x_asset_fin_rec.adjusted_cost,
X_Cost => x_asset_fin_rec.cost,
X_Original_Cost => x_asset_fin_rec.original_cost,
X_Salvage_Value => x_asset_fin_rec.salvage_value,
X_Prorate_Convention_Code => x_asset_fin_rec.prorate_convention_code,
X_Prorate_Date => x_asset_fin_rec.prorate_date,
X_Cost_Change_Flag => x_asset_fin_rec.cost_change_flag,
X_Adjustment_Required_Status => x_asset_fin_rec.adjustment_required_status,
X_Capitalize_Flag => x_asset_fin_rec.capitalize_flag,
X_Retirement_Pending_Flag => x_asset_fin_rec.retirement_pending_flag,
X_Depreciate_Flag => x_asset_fin_rec.depreciate_flag,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => -1,
X_Date_Ineffective => NULL,
X_Transaction_Header_Id_In => p_transaction_header_id,
X_Transaction_Header_Id_Out => NULL,
X_Itc_Amount_Id => x_asset_fin_rec.itc_amount_id,
X_Itc_Amount => x_asset_fin_rec.itc_amount,
X_Retirement_Id => x_asset_fin_rec.retirement_id,
X_Tax_Request_Id => x_asset_fin_rec.tax_request_id,
X_Itc_Basis => x_asset_fin_rec.itc_basis,
X_Basic_Rate => x_asset_fin_rec.basic_rate,
X_Adjusted_Rate => x_asset_fin_rec.adjusted_rate,
X_Bonus_Rule => x_asset_fin_rec.bonus_rule,
X_Ceiling_Name => x_asset_fin_rec.ceiling_name,
X_Recoverable_Cost => x_asset_fin_rec.recoverable_cost,
X_Last_Update_Login => -1,
X_Adjusted_Capacity => x_asset_fin_rec.adjusted_capacity,
X_Fully_Rsvd_Revals_Counter => x_asset_fin_rec.fully_rsvd_revals_counter,
X_Idled_Flag => x_asset_fin_rec.idled_flag,
X_Period_Counter_Capitalized => x_asset_fin_rec.period_counter_capitalized,
X_PC_Fully_Reserved => x_asset_fin_rec.period_counter_fully_reserved,
X_Period_Counter_Fully_Retired => x_asset_fin_rec.period_counter_fully_retired,
X_Production_Capacity => x_asset_fin_rec.production_capacity,
X_Reval_Amortization_Basis => x_asset_fin_rec.reval_amortization_basis,
X_Reval_Ceiling => x_asset_fin_rec.reval_ceiling,
X_Unit_Of_Measure => x_asset_fin_rec.unit_of_measure,
X_Unrevalued_Cost => x_asset_fin_rec.unrevalued_cost,
X_Annual_Deprn_Rounding_Flag => 'ADJ',
X_Percent_Salvage_Value => x_asset_fin_rec.percent_salvage_value,
X_Allowed_Deprn_Limit => x_asset_fin_rec.allowed_deprn_limit,
X_Allowed_Deprn_Limit_Amount => x_asset_fin_rec.allowed_deprn_limit_amount,
X_Period_Counter_Life_Complete => x_asset_fin_rec.period_counter_life_complete,
X_Adjusted_Recoverable_Cost => x_asset_fin_rec.adjusted_recoverable_cost,
X_Short_Fiscal_Year_Flag => x_asset_fin_rec.short_fiscal_year_flag,
X_Conversion_Date => x_asset_fin_rec.conversion_date,
X_Orig_Deprn_Start_Date => x_asset_fin_rec.orig_deprn_start_date,
X_Remaining_Life1 => x_asset_fin_rec.remaining_life1,
X_Remaining_Life2 => x_asset_fin_rec.remaining_life2,
X_Old_Adj_Cost => x_asset_fin_rec.old_adjusted_cost,
X_Formula_Factor => x_asset_fin_rec.formula_factor,
X_gf_Attribute1 => x_asset_fin_rec.global_attribute1,
X_gf_Attribute2 => x_asset_fin_rec.global_attribute2,
X_gf_Attribute3 => x_asset_fin_rec.global_attribute3,
X_gf_Attribute4 => x_asset_fin_rec.global_attribute4,
X_gf_Attribute5 => x_asset_fin_rec.global_attribute5,
X_gf_Attribute6 => x_asset_fin_rec.global_attribute6,
X_gf_Attribute7 => x_asset_fin_rec.global_attribute7,
X_gf_Attribute8 => x_asset_fin_rec.global_attribute8,
X_gf_Attribute9 => x_asset_fin_rec.global_attribute9,
X_gf_Attribute10 => x_asset_fin_rec.global_attribute10,
X_gf_Attribute11 => x_asset_fin_rec.global_attribute11,
X_gf_Attribute12 => x_asset_fin_rec.global_attribute12,
X_gf_Attribute13 => x_asset_fin_rec.global_attribute13,
X_gf_Attribute14 => x_asset_fin_rec.global_attribute14,
X_gf_Attribute15 => x_asset_fin_rec.global_attribute15,
X_gf_Attribute16 => x_asset_fin_rec.global_attribute16,
X_gf_Attribute17 => x_asset_fin_rec.global_attribute17,
X_gf_Attribute18 => x_asset_fin_rec.global_attribute18,
X_gf_Attribute19 => x_asset_fin_rec.global_attribute19,
X_gf_Attribute20 => x_asset_fin_rec.global_attribute20,
X_global_attribute_category => x_asset_fin_rec.global_attribute_category,
X_group_asset_id => x_asset_fin_rec.group_asset_id,
X_salvage_type => x_asset_fin_rec.salvage_type,
X_deprn_limit_type => x_asset_fin_rec.deprn_limit_type,
X_over_depreciate_option => x_asset_fin_rec.over_depreciate_option,
X_super_group_id => x_asset_fin_rec.super_group_id,
X_reduction_rate => x_asset_fin_rec.reduction_rate,
X_reduce_addition_flag => x_asset_fin_rec.reduce_addition_flag,
X_reduce_adjustment_flag => x_asset_fin_rec.reduce_adjustment_flag,
X_reduce_retirement_flag => x_asset_fin_rec.reduce_retirement_flag,
X_recognize_gain_loss => x_asset_fin_rec.recognize_gain_loss,
X_recapture_reserve_flag => x_asset_fin_rec.recapture_reserve_flag,
X_limit_proceeds_flag => x_asset_fin_rec.limit_proceeds_flag,
X_terminal_gain_loss => x_asset_fin_rec.terminal_gain_loss,
X_tracking_method => x_asset_fin_rec.tracking_method,
X_allocate_to_fully_rsv_flag => x_asset_fin_rec.allocate_to_fully_rsv_flag,
X_allocate_to_fully_ret_flag => x_asset_fin_rec.allocate_to_fully_ret_flag,
X_exclude_fully_rsv_flag => x_asset_fin_rec.exclude_fully_rsv_flag,
X_excess_allocation_option => x_asset_fin_rec.excess_allocation_option,
X_depreciation_option => x_asset_fin_rec.depreciation_option,
X_member_rollup_flag => x_asset_fin_rec.member_rollup_flag,
X_ytd_proceeds => x_asset_fin_rec.ytd_proceeds,
X_ltd_proceeds => x_asset_fin_rec.ltd_proceeds,
X_eofy_reserve => x_asset_fin_rec.eofy_reserve,
X_cip_cost => x_asset_fin_rec.cip_cost,
X_terminal_gain_loss_amount => x_asset_fin_rec.terminal_gain_loss_amount,
X_ltd_cost_of_removal => x_asset_fin_rec.ltd_cost_of_removal,
X_exclude_proceeds_from_basis => x_asset_fin_rec.exclude_proceeds_from_basis,
X_retirement_deprn_option => x_asset_fin_rec.retirement_deprn_option,
X_terminal_gain_loss_flag => x_asset_fin_rec.terminal_gain_loss_flag,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_Return_Status => l_status,
X_Calling_Fn => 'fa_track_member_pvt.ins_dd_adj',
p_log_level_rec => p_log_level_rec
);
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.selection_thid := 0;
l_adj.selection_retid := 0;
l_adj.last_update_date := sysdate;
select sum(nvl(deprn_adjustment_amount, 0))
into l_deprn_adjustment_amount
from fa_deprn_detail_mrc_v
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code;
select sum(nvl(deprn_adjustment_amount, 0))
into l_deprn_adjustment_amount
from fa_deprn_detail
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code;
FA_DEPRN_SUMMARY_PKG.Update_Row
(X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Ytd_Deprn => l_asset_deprn_rec.ytd_deprn,
X_Deprn_Reserve => l_asset_deprn_rec.deprn_reserve,
X_Period_Counter => p_period_counter - 1,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_Calling_Fn => 'fa_track_member_pvt.ins_dd_adj',
p_log_level_rec => p_log_level_rec
);
select mth.asset_id
from fa_transaction_headers mth
-- , fa_transaction_headers gth
-- where gth.transaction_header_id = p_transaction_header_id
-- and mth.transaction_header_id = gth.member_transaction_header_id;
select nvl(calendar_period_close_date,sysdate)
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = h_loop_period_counter;
select nvl(calendar_period_close_date,sysdate)
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and period_counter = h_loop_period_counter
and set_of_books_id = h_set_of_books_id;
select nvl(amortization_start_date,transaction_date_entered), asset_id, transaction_type_code
from fa_transaction_headers
where book_type_code = h_book_type_code
and transaction_header_id = p_transaction_header_id;
select bk.asset_id
from fa_books bk,
fa_additions_b ad
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.date_placed_in_service <= h_date
and bk.depreciate_flag = 'YES'
and bk.date_ineffective is null
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
and bk.asset_id = nvl(l_member_asset_id, bk.asset_id)
order by ad.asset_number;
select bk.asset_id
from fa_mc_books bk,
fa_additions_b ad
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.set_of_books_id = h_set_of_books_id
and bk.date_placed_in_service <= h_date
and bk.depreciate_flag = 'YES'
and bk.date_ineffective is null
and bk.set_of_books_id = h_set_of_books_id
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select bk.cost,
bk.adjusted_cost,
bk.recoverable_cost,
bk.salvage_value,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_reserved,
bk.period_counter_fully_retired,
decode(ds.deprn_source_code,'BOOKS',ds.deprn_reserve - ds.ytd_deprn,
decode(dp1.fiscal_year,h_fiscal_year,ds.deprn_reserve - ds.ytd_deprn,
ds.deprn_reserve)), -- bk.eofy_reserve,
ds.deprn_reserve,
decode(dp1.fiscal_year,h_fiscal_year,ds.ytd_deprn,0),
ds.bonus_deprn_reserve,
decode(dp1.fiscal_year,h_fiscal_year,ds.bonus_ytd_deprn,0),
dp1.fiscal_year
from fa_books bk,
fa_deprn_periods dp,
fa_deprn_periods dp1,
fa_deprn_summary ds,
fa_additions_b ad
where dp.book_type_code = h_book_type_code
and dp.period_counter = h_loop_period_counter
and bk.book_type_code = dp.book_type_code
and bk.asset_id = p_member_asset_id
and (bk.transaction_header_id_out = p_transaction_header_id or
bk.transaction_header_id_out = h_last_trans_id or
(bk.date_ineffective is null and
bk.transaction_header_id_in <> nvl(p_transaction_header_id,-1) and
not exists (select 'y'
from fa_books bk1
where bk1.book_type_code = bk.book_type_code
and bk1.asset_id = bk.asset_id
and bk1.transaction_header_id_out = nvl(p_transaction_header_id,-1))))
and bk.depreciate_flag = 'YES'
and ds.book_type_code = bk.book_type_code
and ds.period_counter =
(select min(period_counter)
from fa_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.period_counter >= h_period_counter - 1)
and ds.asset_id = bk.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.period_counter = ds.period_counter
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select bk.cost,
bk.adjusted_cost,
bk.recoverable_cost,
bk.salvage_value,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_reserved,
bk.period_counter_fully_retired,
decode(ds.deprn_source_code,'BOOKS',ds.deprn_reserve - ds.ytd_deprn,
decode(dp1.fiscal_year,h_fiscal_year,ds.deprn_reserve - ds.ytd_deprn,
ds.deprn_reserve)), -- bk.eofy_reserve,
ds.deprn_reserve,
decode(dp1.fiscal_year,h_fiscal_year,ds.ytd_deprn,0),
ds.bonus_deprn_reserve,
decode(dp1.fiscal_year,h_fiscal_year,ds.bonus_ytd_deprn,0),
dp1.fiscal_year
from fa_mc_books bk,
fa_mc_deprn_periods dp,
fa_mc_deprn_periods dp1,
fa_mc_deprn_summary ds,
fa_additions_b ad
where dp.book_type_code = h_book_type_code
and dp.period_counter = h_loop_period_counter
and dp.set_of_books_id = h_set_of_books_id
and bk.book_type_code = dp.book_type_code
and bk.asset_id = p_member_asset_id
and bk.set_of_books_id = h_set_of_books_id
and (bk.transaction_header_id_out = p_transaction_header_id or
bk.transaction_header_id_out = h_last_trans_id or
(bk.date_ineffective is null and
bk.transaction_header_id_in <> nvl(p_transaction_header_id,-1) and
not exists (select 'y'
from fa_books_mrc_v bk1
where bk1.book_type_code = bk.book_type_code
and bk1.asset_id = bk.asset_id
and bk1.transaction_header_id_out = nvl(p_transaction_header_id,-1))))
and bk.depreciate_flag = 'YES'
and ds.book_type_code = bk.book_type_code
and ds.period_counter =
(select min(period_counter)
from fa_mc_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.period_counter >= h_period_counter - 1)
and ds.set_of_books_id = h_set_of_books_id
and ds.asset_id = bk.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.period_counter = ds.period_counter
and dp1.set_of_books_id = h_set_of_books_id
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select bk.cost,
bk.adjusted_cost,
bk.recoverable_cost,
bk.salvage_value,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_reserved,
bk.period_counter_fully_retired,
decode(ds.deprn_source_code,'BOOKS',ds.deprn_reserve - ds.ytd_deprn,
decode(dp.fiscal_year,h_fiscal_year,ds.deprn_reserve - ds.ytd_deprn,
ds.deprn_reserve)), -- bk.eofy_reserve,
ds.deprn_reserve,
ds.ytd_deprn,
ds.bonus_deprn_reserve,
ds.bonus_ytd_deprn,
dp.fiscal_year
from fa_books bk,
fa_deprn_periods dp,
fa_deprn_summary ds,
fa_additions_b ad
where dp.book_type_code = h_book_type_code
and dp.period_counter = h_loop_period_counter
and bk.book_type_code = dp.book_type_code
and bk.asset_id = p_member_asset_id
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and ds.book_type_code = bk.book_type_code
and ds.period_counter =
(select min(period_counter)
from fa_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.period_counter >= h_period_counter - 1)
and ds.asset_id = bk.asset_id
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select bk.cost,
bk.adjusted_cost,
bk.recoverable_cost,
bk.salvage_value,
bk.adjusted_recoverable_cost,
bk.period_counter_fully_reserved,
bk.period_counter_fully_retired,
decode(ds.deprn_source_code,'BOOKS',ds.deprn_reserve - ds.ytd_deprn,
decode(dp.fiscal_year,h_fiscal_year,ds.deprn_reserve - ds.ytd_deprn,
ds.deprn_reserve)), -- bk.eofy_reserve,
ds.deprn_reserve,
ds.ytd_deprn,
ds.bonus_deprn_reserve,
ds.bonus_ytd_deprn,
dp.fiscal_year
from fa_mc_books bk,
fa_mc_deprn_periods dp,
fa_mc_deprn_summary ds,
fa_additions_b ad
where dp.book_type_code = h_book_type_code
and dp.period_counter = h_loop_period_counter
and dp.set_of_books_id = h_set_of_books_id
and bk.book_type_code = dp.book_type_code
and bk.asset_id = p_member_asset_id
and bk.set_of_books_id = h_set_of_books_id
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and ds.book_type_code = bk.book_type_code
and ds.period_counter =
(select min(period_counter)
from fa_mc_deprn_summary ds1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = bk.asset_id
and ds1.period_counter >= h_period_counter - 1)
and ds.set_of_books_id = h_set_of_books_id
and ds.asset_id = bk.asset_id
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number;
select bk.recoverable_cost,
bk.salvage_value
from fa_books bk,
fa_deprn_periods dp
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_member_asset_id
and bk.date_effective <= nvl(dp.period_close_date,sysdate)
and nvl(bk.date_ineffective,sysdate) >= nvl(dp.period_close_date,sysdate)
and dp.book_type_code = bk.book_type_code
and dp.fiscal_year = h_fiscal_year - 1
and dp.period_num = (select max(period_num) from fa_deprn_periods dp1
where dp1.book_type_code = h_book_type_code
and dp1.fiscal_year = h_fiscal_year - 1);
select bk.recoverable_cost,
bk.salvage_value
from fa_mc_books bk,
fa_mc_deprn_periods dp
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_member_asset_id
and bk.set_of_books_id = h_set_of_books_id
and bk.date_effective <= nvl(dp.period_close_date,sysdate)
and nvl(bk.date_ineffective,sysdate) >= nvl(dp.period_close_date,sysdate)
and dp.book_type_code = bk.book_type_code
and dp.fiscal_year = h_fiscal_year - 1
and dp.period_num = (select max(period_num) from fa_mc_deprn_periods dp1
where dp1.book_type_code = h_book_type_code
and dp1.fiscal_year = h_fiscal_year - 1
and dp1.set_of_books_id = h_set_of_books_id)
and dp.set_of_books_id = h_set_of_books_id;
select period_counter + 1
from fa_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_loop_end_year
and period_num = h_loop_end_period;
select period_counter + 1
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_loop_end_year
and period_num = h_loop_end_period
and set_of_books_id = h_set_of_books_id;
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_retirements ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and exists
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods dp1,
fa_deprn_periods dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and nvl(th1.amortization_start_date,th1.transaction_date_entered) >= dp1.calendar_period_open_date
and nvl(th1.amortization_start_date,th1.transaction_date_entered) <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and th1.transaction_header_id = ret.transaction_header_id_in);
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_mc_retirements ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and ret.set_of_books_id = h_set_of_books_id
and exists
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods dp1,
fa_deprn_periods dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and nvl(th1.amortization_start_date,th1.transaction_date_entered) >= dp1.calendar_period_open_date
and nvl(th1.amortization_start_date,th1.transaction_date_entered) <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and th1.transaction_header_id = ret.transaction_header_id_in);
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and nvl(adj.track_member_flag, 'N') = 'N' -- ENERGY
and adj.period_counter_adjusted in
(select dp2.period_counter
from fa_deprn_periods dp1,
fa_deprn_periods dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.period_counter <= dp1.period_counter);
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_mc_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and adj.set_of_books_id = h_set_of_books_id
and nvl(adj.track_member_flag, 'N') = 'N' -- ENERGY
and adj.period_counter_adjusted in
(select dp2.period_counter
from fa_mc_deprn_periods dp1,
fa_mc_deprn_periods dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp1.set_of_books_id = h_set_of_books_id
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.set_of_books_id = h_set_of_books_id
and dp2.period_counter <= dp1.period_counter);
select ds.deprn_reserve,
ds.ytd_deprn
from fa_deprn_summary ds
where ds.book_type_code=h_book_type_code
and ds.asset_id=h_member_asset_id
and ds.deprn_source_code='BOOKS';
select ds.deprn_reserve,
ds.ytd_deprn
from fa_mc_deprn_summary ds
where ds.book_type_code=h_book_type_code
and ds.asset_id=h_member_asset_id
and ds.deprn_source_code='BOOKS'
and ds.set_of_books_id=h_set_of_books_id;
select nvl(amortization_start_date,transaction_date_entered)
from fa_transaction_headers
where transaction_type_code = 'ADDITION'
and asset_id = h_member_asset_id;
select TH.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP,
FA_FISCAL_YEAR FY
where DP.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and FY.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and TH.asset_id = p_member_asset_id
and TH.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN', 'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT','REINSTATEMENT')
and nvl(TH.amortization_start_date,TH.transaction_date_entered) between DP.start_date and DP.end_date
and DP.start_date >= FY.start_date
and DP.end_date <= FY.end_date
and TH1.transaction_header_id = p_trans_id
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) between DP.start_date and DP.end_date
and nvl(TH.amortization_start_date,TH.transaction_date_entered) <= nvl(TH1.amortization_start_date,TH1.transaction_date_entered)
order by nvl(TH.amortization_start_date,TH.transaction_date_entered), TH.transaction_header_id desc;
select deprn_method_code,life_in_months
into h_method_code,h_life_in_months
from fa_books
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null;
select DP.end_date into h_transaction_date
from FA_FISCAL_YEAR FY,
FA_CALENDAR_PERIODS DP
where DP.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and DP.period_num = p_period_counter - p_fiscal_year*h_perd_per_fiscal_year
and FY.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and DP.start_date >= FY.start_date
and DP.end_date <= FY.end_date;
select deprn_method_code,life_in_months
into h_method_code,h_life_in_months
from fa_mc_books
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null
and set_of_books_id = h_set_of_books_id;
select DP.end_date into h_transaction_date
from FA_FISCAL_YEAR FY,
FA_CALENDAR_PERIODS DP
where DP.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and DP.period_num = p_period_counter - p_fiscal_year*h_perd_per_fiscal_year
and FY.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and DP.start_date >= FY.start_date
and DP.end_date <= FY.end_date;
select TH.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH,
FA_FISCAL_YEAR FY,
FA_CALENDAR_PERIODS DP
where DP.calendar_type = h_calendar_type
and DP.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and TH.asset_id = p_member_asset_id
and TH.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN', 'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT','REINSTATEMENT')
and nvl(th.amortization_start_date,TH.transaction_date_entered) <= DP.end_date
and DP.start_date >= FY.start_date
and DP.end_date <= FY.end_date
order by nvl(th.amortization_start_date,TH.transaction_date_entered), TH.transaction_header_id asc;
select BK_IN.COST - nvl(BK_OUT.COST,0) delta_cost,
BK_IN.RECOVERABLE_COST - nvl(BK_OUT.RECOVERABLE_COST,0) delta_rec_cost,
BK_IN.DEPRN_LIMIT_TYPE new_limit_type,
BK_OUT.DEPRN_LIMIT_TYPE old_limit_type,
BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT new_deprn_limit_amount,
BK_IN.DEPRECIATE_FLAG depreciate_flag
from FA_BOOKS BK_IN,
FA_BOOKS BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select BK_IN.COST - nvl(BK_OUT.COST,0) delta_cost,
BK_IN.RECOVERABLE_COST - nvl(BK_OUT.RECOVERABLE_COST,0) delta_rec_cost,
BK_IN.DEPRN_LIMIT_TYPE new_limit_type,
BK_OUT.DEPRN_LIMIT_TYPE old_limit_type,
BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT old_deprn_limit,
BK_IN.DEPRECIATE_FLAG depreciate_flag
from FA_BOOKS_MRC_V BK_IN,
FA_BOOKS_MRC_V BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select distinct bk.asset_id member_asset_id, ad.asset_number
from fa_books bk,
fa_additions_b ad
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.depreciate_flag = 'YES'
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK.book_type_code
and DP1.calendar_type = h_calendar_type
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.end_date <= FY.end_date
and BK.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID)
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number asc;
select distinct bk.asset_id member_asset_id, ad.asset_number
from fa_books_mrc_v bk,
fa_additions_b ad
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.depreciate_flag = 'YES'
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK.book_type_code
and DP1.calendar_type = h_calendar_type
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.end_date <= FY.end_date
and BK.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID)
and ad.asset_id = bk.asset_id
and ad.asset_type = 'CAPITALIZED'
order by ad.asset_number asc;
select BK_IN.group_asset_id
from fa_books BK_IN
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = p_member_asset_id
and BK_IN.transaction_header_id_in = p_thid;
select BK_IN.group_asset_id
from fa_books_mrc_v BK_IN
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = p_member_asset_id
and BK_IN.transaction_header_id_in = p_thid;
select deprn_source_code,
ytd_deprn,
deprn_reserve
from fa_deprn_summary
where book_type_code = h_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter;
select deprn_source_code,
ytd_deprn,
deprn_reserve
from fa_deprn_summary_mrc_v
where book_type_code = h_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter;
p_track_member_table.delete;
select fiscal_year_name, deprn_calendar
into h_fiscal_year_name,h_calendar_type
from fa_book_controls
where book_type_code = h_book_type_code;
select ASSET_TYPE
into fa_rule_in.asset_type
from fa_additions_b
where asset_id = h_member_asset_id;
select ASSET_TYPE
into fa_rule_in.asset_type
from fa_additions_b
where asset_id = h_member_asset_id;
FUNCTION update_member_books(p_trans_rec in FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
p_dpr_in in FA_STD_TYPES.dpr_struct,
p_mrc_sob_type_code in varchar2,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) -- default 'N'
return boolean is
--* Host related variables
h_book_type_code varchar2(15);
l_calling_fn varchar2(45) := 'fa_track_member_pvt.update_member_books';
update_member_err exception;
select mth.asset_id
from fa_transaction_headers mth
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id;
select period_counter
from fa_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_fiscal_year
and period_num = h_period_num;
select period_counter
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_fiscal_year
and period_num = h_period_num
and set_of_books_id = h_set_of_books_id;
select bk.asset_id,
bk.group_asset_id
from fa_books bk
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
and bk.asset_id = nvl(l_member_asset_id, bk.asset_id)
order by asset_id;
select bk.asset_id,
bk.group_asset_id
from fa_books_mrc_v bk
where bk.book_type_code = h_book_type_code
and bk.group_asset_id = h_group_asset_id
and bk.date_ineffective is null
and bk.depreciate_flag = 'YES'
order by asset_id;
fa_debug_pkg.add('fa_track_member_pvt', '*** update_member_books Started', '***', p_log_level_rec);
For update_member in ALL_MEMBERS loop
h_member_asset_id := update_member.asset_id;
Update FA_BOOKS set adjusted_cost = h_adjusted_cost,
eofy_reserve = h_eofy_reserve,
last_update_date = sysdate,
last_updated_by = -1
where book_type_code = h_book_type_code
and asset_id = h_member_asset_id
and group_asset_id = h_group_asset_id
and date_ineffective is null;
For update_member in ALL_MEMBERS_MRC loop
h_member_asset_id := update_member.asset_id;
Update FA_BOOKS_MRC_V set adjusted_cost = h_adjusted_cost,
eofy_reserve = h_eofy_reserve,
last_update_date = sysdate,
last_updated_by = -1
where book_type_code = h_book_type_code
and asset_id = h_member_asset_id
and group_asset_id = h_group_asset_id
and date_ineffective is null;
when update_member_err then
fa_srvr_msg.add_message (calling_fn => l_calling_fn,
p_log_level_rec => p_log_level_rec);
end update_member_books;
select period_counter
from fa_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_fiscal_year
and period_num = h_period_num;
select period_counter
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and fiscal_year = h_fiscal_year
and period_num = h_period_num
and set_of_books_id = h_set_of_books_id;
p_track_member_eofy_table.delete;
fa_debug_pkg.add(l_calling_fn, 'p_track_member_eofy_table is deleted', 1, p_log_level_rec);
select FY.fiscal_year,
DP.period_num
from fa_fiscal_year FY,
fa_calendar_periods DP
where FY.fiscal_year_name = h_fiscal_year_name
and DP.calendar_type = h_deprn_calendar
and DP.end_date <= FY.end_date
and p_date >= DP.start_date
and p_date <= DP.end_date
and p_date >= FY.start_date
and p_date <= FY.end_date;
select distinct BK.ASSET_ID, AD.asset_number
from FA_BOOKS BK,
FA_ADDITIONS_B AD
where BK.book_type_code = h_book_type_code
and BK.group_asset_id = h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK.book_type_code
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and BK.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID)
and AD.asset_id = BK.asset_id
and AD.asset_type = 'CAPITALIZED'
order by AD.asset_number asc;
select distinct BK.ASSET_ID, AD.asset_number
from FA_BOOKS_MRC_V BK,
FA_ADDITIONS_B AD
where BK.book_type_code = h_book_type_code
and BK.group_asset_id = h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK.book_type_code
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and TH1.TRANSACTION_HEADER_ID = BK.TRANSACTION_HEADER_ID_IN)
and AD.asset_id = BK.asset_id
and AD.asset_type = 'CAPITALIZED'
order by AD.asset_number asc;
select max(BK1.transaction_header_id_in)
from FA_BOOKS BK1
where BK1.book_type_code = h_book_type_code
and BK1.asset_id = p_member_asset_id
and BK1.group_asset_id = h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK1.book_type_code
and TH1.asset_id = BK1.asset_id
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and BK1.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID);
select max(BK1.transaction_header_id_in)
from FA_BOOKS BK1
where BK1.book_type_code = h_book_type_code
and BK1.asset_id = p_member_asset_id
and BK1.group_asset_id <> h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK1.book_type_code
and TH1.asset_id = BK1.asset_id
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and BK1.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID);
select max(BK1.transaction_header_id_in)
from FA_BOOKS_MRC_V BK1
where BK1.book_type_code = h_book_type_code
and BK1.asset_id = p_member_asset_id
and BK1.group_asset_id = h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK1.book_type_code
and TH1.asset_id = BK1.asset_id
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and BK1.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID);
select max(BK1.transaction_header_id_in)
from FA_BOOKS_MRC_V BK1
where BK1.book_type_code = h_book_type_code
and BK1.asset_id = p_member_asset_id
and BK1.group_asset_id <> h_group_asset_id
and exists
(select TH1.TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS TH1,
FA_CALENDAR_PERIODS DP1,
FA_FISCAL_YEAR FY
where TH1.book_type_code = BK1.book_type_code
and TH1.asset_id = BK1.asset_id
and DP1.calendar_type = h_deprn_calendar
and DP1.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and nvl(TH1.amortization_start_date,TH1.transaction_date_entered) <= DP1.end_date
and DP1.start_date >= FY.start_date
and DP1.end_date <= FY.end_date
and BK1.TRANSACTION_HEADER_ID_IN = TH1.TRANSACTION_HEADER_ID);
select bk.allocate_to_fully_ret_flag,
bk.allocate_to_fully_rsv_flag,
bk.period_counter_fully_retired,
bk.period_counter_fully_reserved
from fa_books bk
where bk.book_type_code = h_book_type_code
and bk.asset_id = p_asset_id
and bk.date_ineffective is null;
select bk.allocate_to_fully_ret_flag,
bk.allocate_to_fully_rsv_flag,
bk.period_counter_fully_retired,
bk.period_counter_fully_reserved
from fa_books_mrc_v bk
where bk.book_type_code = h_book_type_code
and bk.asset_id = p_asset_id
and bk.date_ineffective is null;
select TH.TRANSACTION_HEADER_ID, TH.TRANSACTION_TYPE_CODE
from FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS DP,
FA_FISCAL_YEAR FY
where DP.calendar_type = h_deprn_calendar
and DP.period_num = p_period_num
and FY.fiscal_year_name = h_fiscal_year_name
and FY.fiscal_year = p_fiscal_year
and TH.asset_id = p_member_asset_id
and TH.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN', 'TRANSFER', 'TRANSFER IN/VOID',
--toru
-- 'RECLASS', 'UNIT ADJUSTMENT','REINSTATEMENT')
'RECLASS', 'UNIT ADJUSTMENT')
and nvl(TH.amortization_start_date,TH.transaction_date_entered) between DP.start_date and DP.end_date
and DP.start_date >= FY.start_date
and DP.end_date <= FY.end_date
order by nvl(TH.amortization_start_date,TH.transaction_date_entered), TH.transaction_header_id asc;
select BK_IN.COST - nvl(BK_OUT.COST,0) delta_cost,
BK_IN.RECOVERABLE_COST - nvl(BK_OUT.RECOVERABLE_COST,0) delta_rec_cost,
BK_IN.DEPRN_LIMIT_TYPE new_limit_type,
BK_OUT.DEPRN_LIMIT_TYPE old_limit_type,
BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT new_deprn_limit_amount,
BK_IN.DEPRECIATE_FLAG depreciate_flag,
BK_IN.group_asset_id group_asset_id,
BK_IN.period_counter_fully_retired period_counter_fully_retired
from FA_BOOKS BK_IN,
FA_BOOKS BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.group_asset_id = h_group_asset_id
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.group_asset_id(+) = BK_IN.group_Asset_id
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select BK_IN.COST - nvl(BK_OUT.COST,0) delta_cost,
BK_IN.RECOVERABLE_COST - nvl(BK_OUT.RECOVERABLE_COST,0) delta_rec_cost,
BK_IN.DEPRN_LIMIT_TYPE new_limit_type,
BK_OUT.DEPRN_LIMIT_TYPE old_limit_type,
BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT old_deprn_limit,
BK_IN.DEPRECIATE_FLAG depreciate_flag,
BK_IN.group_Asset_id group_asset_id,
BK_IN.period_counter_fully_retired period_counter_fully_retired
from FA_BOOKS_MRC_V BK_IN,
FA_BOOKS_MRC_V BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.group_asset_id = h_group_asset_id
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.group_asset_id(+) = BK_IN.group_Asset_id
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select recognize_gain_loss,
nvl(eofy_reserve,0),
(-1)*nvl(reserve_retired,0)
from fa_retirements
where transaction_header_id_in = p_thid;
select recognize_gain_loss,
nvl(eofy_reserve,0),
(-1)*nvl(reserve_retired,0)
from fa_retirements_mrc_v
where transaction_header_id_in = p_thid;
select recognize_gain_loss,
nvl(eofy_reserve,0),
nvl(reserve_retired,0)
from fa_retirements
where transaction_header_id_out = p_thid;
select recognize_gain_loss,
nvl(eofy_reserve,0),
nvl(reserve_retired,0)
from fa_retirements_mrc_v
where transaction_header_id_out = p_thid;
select deprn_source_code,
ytd_deprn,
deprn_reserve
from fa_deprn_summary
where book_type_code = h_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter;
select deprn_source_code,
ytd_deprn,
deprn_reserve
from fa_deprn_summary_mrc_v
where book_type_code = h_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter;
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments adj
where adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = p_period_counter;
select sum(decode(adj.adjustment_type,'EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))),
sum(decode(adj.adjustment_type,'BONUS EXPENSE',
decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)))
from fa_adjustments_mrc_v adj
where adj.asset_id = h_group_asset_id
and adj.book_type_code = h_book_type_code
and adj.period_counter_adjusted = p_period_counter;
select FISCAL_YEAR,
PERIOD_NUM,
CALENDAR_PERIOD_OPEN_DATE,
CALENDAR_PERIOD_CLOSE_DATE,
COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_RECOVERABLE_COST,
ADJUSTED_COST,
DEPRN_METHOD_CODE,
LIFE_IN_MONTHS,
BONUS_RULE,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
DEPRN_OVERRIDE_FLAG,
-- EOFY_RECOVERABLE_COST,
-- EOFY_SALVAGE_VALULE,
EOFY_RESERVE,
SYSTEM_DEPRN_AMOUNT,
SYSTEM_BONUS_DEPRN_AMOUNT
from FA_BOOKS_SUMMARY
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and period_counter = p_period_counter;
select FISCAL_YEAR,
PERIOD_NUM,
CALENDAR_PERIOD_OPEN_DATE,
CALENDAR_PERIOD_CLOSE_DATE,
COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_RECOVERABLE_COST,
ADJUSTED_COST,
DEPRN_METHOD_CODE,
LIFE_IN_MONTHS,
BONUS_RULE,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
BONUS_DEPRN_AMOUNT,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
DEPRN_OVERRIDE_FLAG,
-- EOFY_RECOVERABLE_COST,
-- EOFY_SALVAGE_VALULE,
EOFY_RESERVE,
SYSTEM_DEPRN_AMOUNT,
SYSTEM_BONUS_DEPRN_AMOUNT
from FA_BOOKS_SUMMARY_MRC_V
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and period_counter = p_period_counter;
select sum(nvl(SYSTEM_DEPRN_AMOUNT,0)),
sum(nvl(SYSTEM_BONUS_DEPRN_AMOUNT,0))
from FA_BOOKS_SUMMARY
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and fiscal_year = p_fiscal_year
and period_counter <= p_period_counter_end;
select sum(nvl(SYSTEM_DEPRN_AMOUNT,0)),
sum(nvl(SYSTEM_BONUS_DEPRN_AMOUNT,0))
from FA_BOOKS_SUMMARY_MRC_V
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and fiscal_year = p_fiscal_year
and period_counter <= p_period_counter_end;
select SALVAGE_VALUE,
RECOVERABLE_COST
from FA_BOOKS_SUMMARY
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and fiscal_year = p_fiscal_year
and period_num = p_period_num;
select SALVAGE_VALUE,
RECOVERABLE_COST
from FA_BOOKS_SUMMARY_MRC_V
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and fiscal_year = p_fiscal_year
and period_num = p_period_num;
select COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_COST,
ADJUSTED_RECOVERABLE_COST,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
YTD_DEPRN,
BONUS_YTD_DEPRN,
EOFY_RESERVE
from FA_BOOKS_SUMMARY
where book_type_code = h_book_type_code
and group_asset_id = h_group_asset_id
and period_counter = h_processing_period_counter -1
and asset_id = h_member_asset_id;
select COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_COST,
ADJUSTED_RECOVERABLE_COST,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
YTD_DEPRN,
BONUS_YTD_DEPRN,
EOFY_RESERVE
from FA_BOOKS_SUMMARY_MRC_V
where book_type_code = h_book_type_code
and group_asset_id = h_group_asset_id
and period_counter = h_processing_period_counter -1
and asset_id = h_member_asset_id;
select BK_IN.group_asset_id group_asset_id
from FA_BOOKS BK_IN,
FA_BOOKS BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.group_asset_id(+) = h_group_Asset_id
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select BK_IN.group_Asset_id group_asset_id
from FA_BOOKS_MRC_V BK_IN,
FA_BOOKS_MRC_V BK_OUT
where BK_IN.book_type_code = h_book_type_code
and BK_IN.asset_id = h_member_asset_id
and BK_IN.transaction_header_id_in = p_transaction_header_id
and BK_OUT.book_type_code(+) = BK_IN.book_type_code
and BK_OUT.group_asset_id(+) = h_group_Asset_id
and BK_OUT.asset_id(+) = BK_IN.asset_id
and BK_OUT.transaction_header_id_out(+) = BK_IN.transaction_header_id_in;
select deprn_calendar, fiscal_year_name
into h_deprn_calendar, h_fiscal_year_name
from fa_book_controls
where book_type_code = h_book_type_code;
select period_counter
into h_cur_period_Counter
from fa_deprn_periods
where book_type_Code = h_book_type_code
and period_close_date is null;
select exclude_fully_rsv_flag,recognize_gain_loss
into h_exclude_fully_rsv_flag,h_group_recognize_gain_loss
from fa_books
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null;
select period_counter
into h_cur_period_Counter
from fa_deprn_periods_mrc_v
where book_type_Code = h_book_type_code
and period_close_date is null;
select exclude_fully_rsv_flag,recognize_gain_loss
into h_exclude_fully_rsv_flag,h_group_recognize_gain_loss
from fa_books_mrc_v
where book_type_code = h_book_type_code
and asset_id = h_group_asset_id
and date_ineffective is null;
select ASSET_TYPE
into fa_rule_in.asset_type
from fa_additions_b
where asset_id = h_member_asset_id;
select ASSET_TYPE
into fa_rule_in.asset_type
from fa_additions_b
where asset_id = h_member_asset_id;
fa_debug_pkg.add(l_calling_fn, 'Updated fully reserved member asset',
p_track_member_table(t).member_asset_id, p_log_level_rec);
select asset_id
from fa_transaction_headers
where transaction_header_id = p_trans_rec.member_transaction_header_id;
select period_counter, fiscal_year, period_num
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_close_date is null;
select period_counter, fiscal_year, period_num
from fa_deprn_periods_mrc_v
where book_type_code = h_book_type_code
and period_close_date is null;
select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
from fa_deprn_summary ds1,
fa_deprn_periods dp1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = h_member_asset_id
and dp1.book_type_code = ds1.book_type_Code
and dp1.period_counter = ds1.period_counter
and ds1.period_counter =
(select max(period_counter)
from fa_deprn_summary ds2
where ds2.book_type_code = h_book_type_code
and ds2.asset_id = h_member_asset_id
and period_counter <= h_period_counter);
select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
from fa_deprn_summary_mrc_v ds1,
fa_deprn_periods_mrc_v dp1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = h_member_asset_id
and dp1.book_type_code = ds1.book_type_Code
and dp1.period_counter = ds1.period_counter
and ds1.period_counter =
(select max(period_counter)
from fa_deprn_summary ds2
where ds2.book_type_code = h_book_type_code
and ds2.asset_id = h_member_asset_id
and period_counter <= h_period_counter);
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_retirements ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and exists
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods dp1,
fa_deprn_periods dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and nvl(th1.amortization_start_date,th1.transaction_date_entered) >= dp1.calendar_period_open_date
and nvl(th1.amortization_start_date,th1.transaction_date_entered) <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and th1.transaction_header_id = ret.transaction_header_id_in);
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_retirements_mrc_v ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and exists
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods_mrc_v dp1,
fa_deprn_periods_mrc_v dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods_mrc_v dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and nvl(th1.amortization_start_date,th1.transaction_date_entered) >= dp1.calendar_period_open_date
and nvl(th1.amortization_start_date,th1.transaction_date_entered) <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and th1.transaction_header_id = ret.transaction_header_id_in);
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and nvl(adj.track_member_flag, 'N') = 'N' -- ENERGY
and adj.source_type_code = 'ADJUSTMENT'
and exists
(select dp2.period_counter
from fa_deprn_periods dp1,
fa_deprn_periods dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.period_counter <= dp1.period_counter
and dp2.period_counter = adj.period_counter_adjusted);
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_adjustments_mrc_v adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and nvl(adj.track_member_flag, 'N') = 'N' -- ENERGY
and adj.source_type_code = 'ADJUSTMENT'
and exists
(select dp2.period_counter
from fa_deprn_periods_mrc_v dp1,
fa_deprn_periods_mrc_v dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.period_counter <= dp1.period_counter
and dp2.period_counter = adj.period_counter_adjusted);
select eofy_reserve into h_eofy_reserve
from fa_books
where book_type_code = h_book_type_code
and asset_id = h_member_asset_id
and date_ineffective is null;
select eofy_reserve into h_eofy_reserve
from fa_books_mrc_v
where book_type_code = h_book_type_code
and asset_id = h_member_asset_id
and date_ineffective is null;
p_track_member_table_for_deprn.delete;
p_track_member_table.delete;
FUNCTION create_update_bs_table(p_trans_rec in FA_API_TYPES.trans_rec_type,
p_book_type_code in varchar2,
p_group_asset_id in varchar2,
p_mrc_sob_type_code in varchar2,
p_calling_fn in varchar2,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
--* Local variables
h_book_type_code varchar2(15);
select 'Y'
from fa_books_summary
where book_type_code = h_book_type_code
-- and group_asset_id = h_group_asset_id
and asset_id = h_member_asset_id
and period_counter = h_period_counter;
select 'Y'
from fa_books_summary_mrc_v
where book_type_code = h_book_type_code
-- and group_asset_id = h_group_asset_id
and asset_id = h_member_asset_id
and period_counter = h_period_counter;
l_calling_fn varchar2(50) := 'FA_TRACK_MEMBER_PVT.create_update_bs_table';
fa_debug_pkg.add(l_calling_fn, '## Start to insert/update fa_books_summary for member assets','####', p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Newly insert following assets into fa_books_summary','###', p_log_level_rec);
INSERT INTO FA_BOOKS_SUMMARY
(ASSET_ID,
GROUP_ASSET_ID,
BOOK_TYPE_CODE,
PERIOD_COUNTER,
COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_COST,
ADJUSTED_RECOVERABLE_COST,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
YTD_DEPRN,
BONUS_YTD_DEPRN,
EOFY_RESERVE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (h_member_asset_id,
h_group_asset_id,
h_book_type_code,
h_period_counter,
p_track_member_table(i).cost,
p_track_member_table(i).salvage_value,
p_track_member_table(i).recoverable_cost,
p_track_member_table(i).adjusted_cost,
p_track_member_table(i).adjusted_recoverable_cost,
nvl(p_track_member_table(i).allocated_deprn_amount,0),
nvl(p_track_member_table(i).allocated_bonus_amount,0),
nvl(p_track_member_table(i).deprn_reserve,0),
nvl(p_track_member_table(i).bonus_deprn_reserve,0),
nvl(p_track_member_table(i).ytd_deprn,0),
nvl(p_track_member_table(i).bonus_ytd_deprn,0),
nvl(p_track_member_table(i).eofy_reserve,0),
p_trans_rec.who_info.last_update_date, -- sysdate,
p_trans_rec.who_info.last_updated_by, -- -1,
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login);
fa_debug_pkg.add(l_calling_fn, 'Inserted new row for', h_member_asset_id||':'||h_period_counter, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Update following assets into fa_books_summary:','####', p_log_level_rec);
UPDATE FA_BOOKS_SUMMARY
SET COST = p_track_member_table(i).cost
, SALVAGE_VALUE = p_track_member_table(i).salvage_value
, RECOVERABLE_COST = p_track_member_table(i).recoverable_cost
, ADJUSTED_RECOVERABLE_COST = p_track_member_table(i).adjusted_recoverable_cost
, ADJUSTED_COST = p_track_member_table(i).adjusted_cost
, DEPRN_AMOUNT = nvl(p_track_member_table(i).allocated_deprn_amount,0)
, YTD_DEPRN = nvl(p_track_member_table(i).ytd_deprn,0)
, DEPRN_RESERVE = nvl(p_track_member_table(i).deprn_reserve,0)
, BONUS_DEPRN_AMOUNT = nvl(p_track_member_table(i).allocated_bonus_amount,0)
, BONUS_YTD_DEPRN = nvl(p_track_member_table(i).bonus_ytd_deprn,0)
, BONUS_DEPRN_RESERVE = nvl(p_track_member_table(i).bonus_deprn_reserve,0)
, EOFY_RESERVE = nvl(p_track_member_table(i).eofy_reserve,0)
, LAST_UPDATE_DATE = p_trans_rec.who_info.last_update_date
, LAST_UPDATED_BY = p_trans_rec.who_info.last_updated_by
, LAST_UPDATE_LOGIN = p_trans_rec.who_info.last_update_login
WHERE GROUP_ASSET_ID = h_group_asset_id
AND ASSET_ID = h_member_asset_id
AND BOOK_TYPE_CODE = h_book_type_code
AND PERIOD_COUNTER = h_period_counter;
fa_debug_pkg.add(l_calling_fn, 'Updated existing row for', h_member_asset_id||':'||h_period_counter, p_log_level_rec);
INSERT INTO FA_MC_BOOKS_SUMMARY
(SET_OF_BOOKS_ID,
ASSET_ID,
GROUP_ASSET_ID,
BOOK_TYPE_CODE,
PERIOD_COUNTER,
COST,
SALVAGE_VALUE,
RECOVERABLE_COST,
ADJUSTED_COST,
ADJUSTED_RECOVERABLE_COST,
DEPRN_AMOUNT,
BONUS_DEPRN_AMOUNT,
DEPRN_RESERVE,
BONUS_DEPRN_RESERVE,
YTD_DEPRN,
BONUS_YTD_DEPRN,
EOFY_RESERVE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (h_set_of_books_id,
h_member_asset_id,
h_group_asset_id,
h_book_type_code,
h_period_counter,
p_track_member_table(i).cost,
p_track_member_table(i).salvage_value,
p_track_member_table(i).recoverable_cost,
p_track_member_table(i).adjusted_cost,
p_track_member_table(i).adjusted_recoverable_cost,
nvl(p_track_member_table(i).allocated_deprn_amount,0),
nvl(p_track_member_table(i).allocated_bonus_amount,0),
nvl(p_track_member_table(i).deprn_reserve,0),
nvl(p_track_member_table(i).bonus_deprn_reserve,0),
nvl(p_track_member_table(i).ytd_deprn,0),
nvl(p_track_member_table(i).bonus_ytd_deprn,0),
nvl(p_track_member_table(i).eofy_reserve,0),
p_trans_rec.who_info.last_update_date, -- sysdate,
p_trans_rec.who_info.last_updated_by, -- -1,
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login);
fa_debug_pkg.add(l_calling_fn, 'Inserted new row for', h_member_asset_id||':'||h_period_counter, p_log_level_rec);
UPDATE FA_BOOKS_SUMMARY_MRC_V
SET COST = p_track_member_table(i).cost
, SALVAGE_VALUE = p_track_member_table(i).salvage_value
, RECOVERABLE_COST = p_track_member_table(i).recoverable_cost
, ADJUSTED_RECOVERABLE_COST = p_track_member_table(i).adjusted_recoverable_cost
, ADJUSTED_COST = p_track_member_table(i).adjusted_cost
, DEPRN_AMOUNT = nvl(p_track_member_table(i).allocated_deprn_amount,0)
, YTD_DEPRN = nvl(p_track_member_table(i).ytd_deprn,0)
, DEPRN_RESERVE = nvl(p_track_member_table(i).deprn_reserve,0)
, BONUS_DEPRN_AMOUNT = nvl(p_track_member_table(i).allocated_bonus_amount,0)
, BONUS_YTD_DEPRN = nvl(p_track_member_table(i).bonus_ytd_deprn,0)
, BONUS_DEPRN_RESERVE = nvl(p_track_member_table(i).bonus_deprn_reserve,0)
, EOFY_RESERVE = nvl(p_track_member_table(i).eofy_reserve,0)
, LAST_UPDATE_DATE = p_trans_rec.who_info.last_update_date
, LAST_UPDATED_BY = p_trans_rec.who_info.last_updated_by
, LAST_UPDATE_LOGIN = p_trans_rec.who_info.last_update_login
WHERE
-- GROUP_ASSET_ID = h_group_asset_id
-- AND
ASSET_ID = h_member_asset_id
AND BOOK_TYPE_CODE = h_book_type_code
AND PERIOD_COUNTER = h_period_counter;
fa_debug_pkg.add(l_calling_fn, 'Updated existing row for', h_member_asset_id||':'||h_period_counter, p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, '## End of insert/update fa_books_summary for member assets','####', p_log_level_rec);
end create_update_bs_table;
SELECT deprn_amount, bonus_deprn_amount, subtract_ytd_flag, deprn_override_id
FROM FA_DEPRN_OVERRIDE
WHERE
book_type_code = p_book_type_code and
asset_id = p_member_asset_id and
period_name = h_period_name and
used_by = 'DEPRECIATION' and
status = 'POSTED';
/* select the corresponding period_counter for the current period: fyctr, perd_ctr */
h_calendar_type:= fa_cache_pkg.fazcbc_record.deprn_calendar;
select cp.period_name
into h_period_name
from fa_calendar_periods cp, fa_fiscal_year fy
where cp.calendar_type = h_calendar_type and
cp.period_num = p_period_num and
cp.start_date >= fy.start_date and
cp.end_date <= fy.end_date and
fy.fiscal_year_name = h_fy_name and
fy.fiscal_year = p_fiscal_year;