[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_period_update_flag VARCHAR2(1) DEFAULT NULL;
select itf.deprn_reserve
, nvl(itf.impairment_reserve,0)
, itf.period_counter
from fa_itf_impairments itf
, fa_impairments imp
where imp.impairment_id = itf.impairment_id
and imp.status = 'POSTED'
and itf.asset_id = rule_in.asset_id
and itf.book_type_code = rule_in.book_type_code
and itf.period_counter <= rule_in.period_counter
order by period_counter desc;
select itf.deprn_reserve
, nvl(itf.impairment_reserve,0)
, itf.period_counter
from fa_mc_itf_impairments itf
, fa_mc_impairments imp
where imp.impairment_id = itf.impairment_id
and imp.status = 'POSTED'
and imp.set_of_books_id = rule_in.set_of_books_id
and itf.asset_id = rule_in.asset_id
and itf.book_type_code = rule_in.book_type_code
and itf.period_counter <= rule_in.period_counter
and itf.set_of_books_id = rule_in.set_of_books_id
order by period_counter desc;
l_period_update_flag := fa_cache_pkg.fazcdrd_record.period_update_flag;
element=>'l_period_update_flag',
value=> l_period_update_flag, p_log_level_rec => p_log_level_rec);
element =>'Updated reduction_rate',
value => g_rule_in.reduction_rate, p_log_level_rec => p_log_level_rec);
select bk.deprn_method_code, bk.salvage_value, bk.cost -- bug 6378955 (added salvage value)
into l_old_method_code, l_old_salvage_value, l_old_cost -- l_old_cost added for Japan overlapped
from FA_BOOKS bk
where bk.asset_id = g_rule_in.asset_id
and bk.book_type_code = g_rule_in.book_type_code
and bk.transaction_header_id_out is null;
select bk.deprn_method_code
into l_old_method_code
from FA_BOOKS bk
where bk.asset_id = g_rule_in.asset_id
and bk.book_type_code = g_rule_in.book_type_code
and bk.transaction_header_id_out is null;
select nvl(allowed_deprn_limit_amount,0)
into l_deprn_amt
from fa_books
where asset_id = g_rule_in.asset_id
and transaction_header_id_out is null;
IF g_rule_in.eofy_flag ='Y' OR l_period_update_flag='Y' then
if (g_rule_in.calc_basis = 'COST') then
g_rule_out.new_adjusted_cost :=g_rule_in.old_adjusted_cost;
SELECT rate_in_use, deprn_method_code,cost,nbv_at_switch -- Added deprn_method_code for bug fix 6717680
INTO l_rate_in_use, l_old_method_code,l_old_cost,l_nbv_at_switch
FROM fa_books
WHERE asset_id = g_rule_in.asset_id
AND book_type_code = g_rule_in.book_type_code
AND transaction_header_id_out is null;
SELECT rate_in_use, deprn_method_code,cost,nbv_at_switch -- Added deprn_method_code for bug fix 6717680
INTO l_rate_in_use, l_old_method_code,l_old_cost,l_nbv_at_switch
FROM fa_books fb
, fa_book_controls fc
WHERE fb.asset_id = g_rule_in.asset_id
AND fc.book_type_code = g_rule_in.book_type_code
AND fc.distribution_source_book = fb.book_type_code
AND fb.transaction_header_id_out is null;
select program_short_name
into l_request_short_name
from FND_CONC_REQ_SUMMARY_V
where request_id = fnd_global.conc_request_id;
END IF; -- End deprn_end_perd_flag ='Y' OR l_period_update_flag='Y'
select count(*)
into l_last_trx_count
from fa_books bks,
fa_deprn_periods dp
where bks.asset_id = l_asset_id
and bks.book_type_code = l_book_type_code
and bks.date_ineffective is null
and dp.book_type_code = l_book_type_code
and bks.date_effective between
dp.period_open_date and nvl(dp.period_close_date, sysdate)
and dp.fiscal_year = fa_cache_pkg.fazcbc_record.current_fiscal_year;
or (g_rule_in.event_type ='AFTER_DEPRN' and (g_rule_in.eofy_flag ='Y' or l_period_update_flag='Y')))
then
IF NOT fa_track_member_pvt.update_deprn_basis
(p_group_rule_in => g_rule_in,
p_apply_reduction_flag => g_rule_in.apply_reduction_flag,
p_mode => g_rule_in.used_by_adjustment, p_log_level_rec => p_log_level_rec)
THEN
if p_log_level_rec.statement_level then
fa_debug_pkg.add(fname=>'faxcdb',
element=>'fa_track_member_pvt.update_deprn_basis',
value=> 'False', p_log_level_rec => p_log_level_rec);
select fy.start_date fy_start_date,
fy.end_date fy_end_date,
fy.mid_year_date fy_mid_year_date,
dp.calendar_period_open_date cp_start_date,
dp.calendar_period_close_date cp_end_date
from FA_FISCAL_YEAR fy,
FA_DEPRN_PERIODS dp,
FA_BOOK_CONTROLS bc
where bc.book_type_code = dp.book_type_code
and fy.fiscal_year = dp.fiscal_year
and bc.fiscal_year_name = fy.fiscal_year_name
and dp.book_type_code= p_book_type_code
and dp.period_counter = p_period_counter;
select fy.start_date fy_start_date,
fy.end_date fy_end_date,
fy.mid_year_date fy_mid_year_date,
dp.calendar_period_open_date cp_start_date,
dp.calendar_period_close_date cp_end_date
from FA_FISCAL_YEAR fy,
FA_MC_DEPRN_PERIODS dp,
FA_MC_BOOK_CONTROLS mbc,
FA_BOOK_CONTROLS BC
where bc.book_type_code = dp.book_type_code
and mbc.book_type_code = dp.book_type_code
and mbc.set_of_books_id = p_set_of_books_id
and fy.fiscal_year = dp.fiscal_year
and bc.fiscal_year_name = fy.fiscal_year_name
and dp.book_type_code= p_book_type_code
and dp.period_counter = p_period_counter
and dp.set_of_books_id = p_set_of_books_id;
select sum(BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0)) change_in_cost,--bug#15897249
sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0))
change_in_cost_to_reduce,
sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and RET.STATUS(+) = 'PROCESSED' --bug#15897249
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and nvl(BK2.REDUCTION_RATE,0) >0;
select sum(BK2.COST - nvl(BK1.COST,0)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date;
select sum(BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0)) change_in_cost, --bug#15897249
sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0))
change_in_cost_to_reduce,
sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_BOOKS BK3,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and RET.STATUS(+) = 'PROCESSED' --bug#15897249
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and nvl(BK3.REDUCTION_RATE,0) >0
and exists (select BK3.ASSET_ID
from FA_BOOKS BK4
where BK3.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4. DATE_INEFFECTIVE is null);
select sum(BK2.COST - nvl(BK1.COST,0)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_BOOKS BK3,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and exists (select BK3.ASSET_ID
from FA_BOOKS BK4
where BK3.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4. DATE_INEFFECTIVE is null);
select sum(BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0)) change_in_cost,
sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0))
change_in_cost_to_reduce, -- bug#15897249
sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and RET.STATUS(+) = 'PROCESSED' --bug#15897249
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and ret.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and nvl(BK2.REDUCTION_RATE,0) >0;
select sum(BK2.COST - nvl(BK1.COST,0)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and ret.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date;
select sum(BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0)) change_in_cost, --bug#15897249
sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0))
change_in_cost_to_reduce,
sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_BOOKS BK3,
FA_MC_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and RET.STATUS(+) = 'PROCESSED' --bug#15897249
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and BK3.set_of_books_id = p_set_of_books_id
and ret.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and nvl(BK3.REDUCTION_RATE,0) >0
and exists (select BK3.ASSET_ID
from FA_MC_BOOKS BK4
where BK3.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4.DATE_INEFFECTIVE is null and
BK4.set_of_books_id = p_set_of_books_id);
select sum(BK2.COST - nvl(BK1.COST,0)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_BOOKS BK3,
FA_MC_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.ASSET_ID= p_asset_id
and BK2.BOOK_TYPE_CODE = p_book_type_code
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and BK3.set_of_books_id = p_set_of_books_id
and ret.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and exists (select BK3.ASSET_ID
from FA_MC_BOOKS BK4
where BK3.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4.DATE_INEFFECTIVE is null and
BK4.set_of_books_id = p_set_of_books_id);
select nvl(REDUCTION_RATE,0)
from FA_BOOKS
where TRANSACTION_HEADER_ID_IN = l_transaction_header_id;
select nvl(REDUCTION_RATE,0)
from FA_MC_BOOKS
where TRANSACTION_HEADER_ID_IN = l_transaction_header_id
and set_of_books_id = p_set_of_books_id;
select count('Y')
from FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = l_transaction_header_id
and TH.ASSET_ID = p_asset_id
and TH.BOOK_TYPE_CODE = p_book_type_code;
select BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0) cur_change_in_cost,
(BK2.COST - nvl(BK1.COST,0)+ nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)
cur_change_in_cost_to_reduce,
nvl(RET.NBV_RETIRED,0) cur_net_proceeds,
nvl(RET.NBV_RETIRED,0)*nvl(BK2.REDUCTION_RATE,0) cur_net_proceeds_to_reduce
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.TRANSACTION_HEADER_ID_IN = mem_transaction_header_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date;
select BK2.COST - nvl(BK1.COST,0) + nvl(RET.COST_RETIRED,0) cur_change_in_cost,
(BK2.COST - nvl(BK1.COST,0)+ nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)
cur_change_in_cost_to_reduce,
nvl(RET.NBV_RETIRED,0) cur_net_proceeds,
nvl(RET.NBV_RETIRED,0)*nvl(BK2.REDUCTION_RATE,0) cur_net_proceeds_to_reduce
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_RETIREMENTS RET,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
and BK2.TRANSACTION_HEADER_ID_IN = mem_transaction_header_id
and TH.TRANSACTION_DATE_ENTERED >= t_start_date
and TH.TRANSACTION_DATE_ENTERED <= t_end_date
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and RET.set_of_books_id = p_set_of_books_id;
SELECT cp.start_date,
cp.end_date,
fy.start_date,
fy.end_date,
fy.mid_year_date
INTO l_cp_start_date,
l_cp_end_date,
l_fy_start_date,
l_fy_end_date,
l_fy_mid_year_date
FROM fa_calendar_periods cp,
fa_fiscal_year fy,
fa_calendar_types cal_ty
WHERE fy.fiscal_year =
decode(mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR),0,
(floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)-1),
floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)
)
AND fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
AND cal_ty.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
AND cp.calendar_type = cal_ty.calendar_type
AND cp.start_date BETWEEN fy.start_date AND fy.end_date
AND cp.end_date BETWEEN fy.start_date AND fy.end_date
and period_num =
decode(mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR),0,
cal_ty.NUMBER_PER_FISCAL_YEAR, mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR)
);
SELECT cp.start_date,
cp.end_date,
fy.start_date,
fy.end_date,
fy.mid_year_date
INTO l_cp_start_date,
l_cp_end_date,
l_fy_start_date,
l_fy_end_date,
l_fy_mid_year_date
FROM fa_calendar_periods cp,
fa_fiscal_year fy,
fa_calendar_types cal_ty
WHERE fy.fiscal_year =
decode(mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR),0,
(floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)-1),
floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)
)
AND fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
AND cal_ty.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
AND cp.calendar_type = cal_ty.calendar_type
AND cp.start_date BETWEEN fy.start_date AND fy.end_date
AND cp.end_date BETWEEN fy.start_date AND fy.end_date
and period_num =
decode(mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR),0,
cal_ty.NUMBER_PER_FISCAL_YEAR, mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR)
);
element=>'updated reduction_rate',
value=> fa_calc_deprn_basis1_pkg.g_rule_in.reduction_rate, p_log_level_rec => p_log_level_rec);
select sum(BK2.RECOVERABLE_COST -nvl(BK1.RECOVERABLE_COST,0)) recoverable_cost,
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0)) salvage_value
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.ASSET_ID = p_asset_id
and TH.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED <= l_period_close_date;
select sum(BK2.RECOVERABLE_COST -nvl(BK1.RECOVERABLE_COST,0)) recoverable_cost,
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0)) salvage_value
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and TH.ASSET_ID = p_asset_id
and TH.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED <= l_period_close_date
and BK1.set_of_books_id (+)= p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id ;
select sum(BK2.COST -nvl(BK1.COST,0))
- decode(BK3.SALVAGE_TYPE,
'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) recoverable_cost,
decode(BK3.SALVAGE_TYPE,
'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) salvage_value
from FA_BOOKS BK1,
FA_BOOKS BK2,
FA_BOOKS BK3,
FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS CP
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and BK2.BOOK_TYPE_CODE = p_book_type_code
and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbc_record.deprn_calendar
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
and CP.END_DATE <= fa_cache_pkg.fazcfy_record.end_date
and CP.END_DATE >= fa_cache_pkg.fazcfy_record.start_date
and CP.PERIOD_NUM = p_period_num
and exists (select TH.ASSET_ID
from FA_BOOKS BK4
where TH.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4. DATE_INEFFECTIVE is null)
and BK3.TRANSACTION_HEADER_ID_IN=
(select max(BK.TRANSACTION_HEADER_ID_IN)
from FA_BOOKS BK,
FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS CP
where BK.ASSET_ID= p_asset_id
and BK.BOOK_TYPE_CODE = p_book_type_code
and BK.TRANSACTION_HEADER_ID_IN =TH.TRANSACTION_HEADER_ID
and BK.ASSET_ID= TH.ASSET_ID
and BK.BOOK_TYPE_CODE= TH.BOOK_TYPE_CODE
and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbc_record.deprn_calendar
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
and CP.END_DATE <= fa_cache_pkg.fazcfy_record.end_date
and CP.END_DATE >= fa_cache_pkg.fazcfy_record.start_date
and CP.PERIOD_NUM = p_period_num
)
group by BK3.SALVAGE_TYPE,BK3.PERCENT_SALVAGE_VALUE;
select sum(BK2.COST -nvl(BK1.COST,0))
- decode(BK3.SALVAGE_TYPE,
'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) recoverable_cost,
decode(BK3.SALVAGE_TYPE,
'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) salvage_value
from FA_MC_BOOKS BK1,
FA_MC_BOOKS BK2,
FA_MC_BOOKS BK3,
FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS CP
where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
and BK2.BOOK_TYPE_CODE = p_book_type_code
and BK1.set_of_books_id = p_set_of_books_id
and BK2.set_of_books_id = p_set_of_books_id
and BK3.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbcs_record.deprn_calendar
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
and CP.END_DATE <= fa_cache_pkg.fazcfy_record.end_date
and CP.END_DATE >= fa_cache_pkg.fazcfy_record.start_date
and CP.PERIOD_NUM = p_period_num
and exists (select TH.ASSET_ID
from FA_MC_BOOKS BK4
where TH.ASSET_ID = BK4.ASSET_ID and
BK4.BOOK_TYPE_CODE = p_book_type_code and
BK4.GROUP_ASSET_ID = p_asset_id and
BK4. DATE_INEFFECTIVE is null and
BK4.set_of_books_id = p_set_of_books_id)
and BK3.TRANSACTION_HEADER_ID_IN=
(select max(BK.TRANSACTION_HEADER_ID_IN)
from FA_MC_BOOKS BK,
FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS CP,
FA_MC_BOOK_CONTROLS BC
where BK.ASSET_ID= p_asset_id
and BK.BOOK_TYPE_CODE = p_book_type_code
and BK.TRANSACTION_HEADER_ID_IN =TH.TRANSACTION_HEADER_ID
and BK.ASSET_ID= TH.ASSET_ID
and BK.BOOK_TYPE_CODE= TH.BOOK_TYPE_CODE
and BK.set_of_books_id = p_set_of_books_id
and BC.BOOK_TYPE_CODE = p_book_type_code
and BC.set_of_books_id = p_set_of_books_id
and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbcs_record.deprn_calendar
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
and CP.END_DATE <= fa_cache_pkg.fazcfy_record.end_date
and CP.END_DATE >= fa_cache_pkg.fazcfy_record.start_date
and CP.PERIOD_NUM = p_period_num
)
group by BK3.SALVAGE_TYPE,BK3.PERCENT_SALVAGE_VALUE;
select CP.END_DATE
from FA_CALENDAR_PERIODS CP,
FA_CALENDAR_TYPES CT,
FA_FISCAL_YEAR FY,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
and CP.END_DATE <= FY.END_DATE
and CP.END_DATE >= FY.START_DATE
and BC.BOOK_TYPE_CODE= p_book_type_code
and FY.FISCAL_YEAR = p_fiscal_year
and CP.PERIOD_NUM = p_period_num;
select CP.END_DATE
from FA_CALENDAR_PERIODS CP,
FA_CALENDAR_TYPES CT,
FA_FISCAL_YEAR FY,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
and BC.set_of_books_id = p_set_of_books_id
and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
and CP.END_DATE <= FY.END_DATE
and CP.END_DATE >= FY.START_DATE
and BC.BOOK_TYPE_CODE= p_book_type_code
and FY.FISCAL_YEAR = p_fiscal_year
and CP.PERIOD_NUM = p_period_num;
select BK.RECOVERABLE_COST,
BK.SALVAGE_VALUE
from FA_BOOKS BK
where BK.ASSET_ID = p_asset_id
and BK.BOOK_TYPE_CODE = p_book_type_code
and BK.TRANSACTION_HEADER_ID_OUT is null;
select BK.RECOVERABLE_COST,
BK.SALVAGE_VALUE
from FA_MC_BOOKS BK
where BK.ASSET_ID = p_asset_id
and BK.BOOK_TYPE_CODE = p_book_type_code
and BK.TRANSACTION_HEADER_ID_OUT is null
and BK.set_of_books_id = p_set_of_books_id;
select CT.number_per_fiscal_year
from FA_CALENDAR_TYPES CT,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE
and BC.BOOK_TYPE_CODE= p_book_type_code;
select CT.number_per_fiscal_year
from FA_CALENDAR_TYPES CT,
FA_MC_BOOK_CONTROLS MBC,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE
and BC.BOOK_TYPE_CODE= p_book_type_code
and MBC.BOOK_TYPE_CODE= p_book_type_code
and MBC.set_of_books_id = p_set_of_books_id;
select bs.recoverable_cost
, bs.salvage_value
from fa_books_summary bs
where bs.asset_id = p_asset_id
and bs.book_type_code = p_book_type_code
and bs.period_counter = c_period_counter;
select bs.recoverable_cost
, bs.salvage_value
from fa_mc_books_summary bs
where bs.asset_id = p_asset_id
and bs.book_type_code = p_book_type_code
and bs.period_counter = c_period_counter
and bs.set_of_books_id = p_set_of_books_id;
select ah.asset_type
into h_rule_in.asset_type
from fa_asset_history ah
where h_rule_in.asset_id = ah.asset_id
and ah.date_ineffective is null;
SELECT fth.transaction_type_code
, fth.transaction_header_id
INTO l_trx_type
, l_old_trx_id
FROM fa_retirements fr
, fa_books fb
, fa_transaction_headers fth
WHERE fr.transaction_header_id_out = p_trans_rec.transaction_header_id
AND fr.transaction_header_id_in = fb.transaction_header_id_in
AND fr.transaction_header_id_in = fth.transaction_header_id
AND fr.asset_id = fb.asset_id
AND fr.book_type_code = fb.book_type_code;
SELECT (fb.adjusted_cost * p_asset_fin_rec_new.cost) / fb.COST
INTO px_new_adjusted_cost
FROM fa_retirements fr
, fa_books fb
WHERE fr.transaction_header_id_out = p_trans_rec.transaction_header_id
AND fr.transaction_header_id_in = fb.transaction_header_id_in
AND fr.asset_id = fb.asset_id
AND fr.book_type_code = fb.book_type_code;
SELECT fb.adjusted_cost
INTO px_new_adjusted_cost
FROM fa_books fb
WHERE fb.transaction_header_id_out = l_old_trx_id;
select fy.start_date fy_start_date,
dp.calendar_period_close_date cp_end_date
from FA_FISCAL_YEAR fy,
FA_DEPRN_PERIODS dp,
FA_BOOK_CONTROLS bc
where bc.book_type_code = dp.book_type_code
and fy.fiscal_year = dp.fiscal_year
and bc.fiscal_year_name = fy.fiscal_year_name
and dp.book_type_code= p_book_type_code
and dp.period_counter = p_period_counter;
select fy.start_date fy_start_date,
dp.calendar_period_close_date cp_end_date
from FA_FISCAL_YEAR fy,
FA_MC_DEPRN_PERIODS dp,
FA_MC_BOOK_CONTROLS mbc,
FA_BOOK_CONTROLS BC
where bc.book_type_code = dp.book_type_code
and mbc.book_type_code = dp.book_type_code
and mbc.set_of_books_id = p_set_of_books_id
and fy.fiscal_year = dp.fiscal_year
and bc.fiscal_year_name = fy.fiscal_year_name
and dp.book_type_code= p_book_type_code
and dp.period_counter = p_period_counter
and dp.set_of_books_id = p_set_of_books_id;
select nvl(sum(ret.nbv_retired),0) ldt_proceeds
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ldt_proceeds
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ldt_proceeds
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ldt_proceeds
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ytd_proceeds
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ytd_proceeds
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ytd_proceeds
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.nbv_retired),0) ytd_proceeds
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select FY.START_DATE,
CP.END_DATE
from FA_CALENDAR_PERIODS CP,
FA_CALENDAR_TYPES CT,
FA_FISCAL_YEAR FY,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
and CP.END_DATE <= FY.END_DATE
and CP.END_DATE >= FY.START_DATE
and BC.BOOK_TYPE_CODE= p_book_type_code
and FY.FISCAL_YEAR = p_fiscal_year
and CP.PERIOD_NUM = p_period_num;
select FY.START_DATE,
CP.END_DATE
from FA_CALENDAR_PERIODS CP,
FA_CALENDAR_TYPES CT,
FA_FISCAL_YEAR FY,
FA_MC_BOOK_CONTROLS MBC,
FA_BOOK_CONTROLS BC
where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
and CP.END_DATE <= FY.END_DATE
and CP.END_DATE >= FY.START_DATE
and BC.BOOK_TYPE_CODE= p_book_type_code
and MBC.BOOK_TYPE_CODE= p_book_type_code
and MBC.SET_OF_BOOKS_ID = p_set_of_books_id
and FY.FISCAL_YEAR = p_fiscal_year
and CP.PERIOD_NUM = p_period_num;
select nvl(sum(ret.cost_retired),0)
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select nvl(sum(ret.cost_retired),0)
from FA_MC_RETIREMENTS ret,
FA_TRANSACTION_HEADERS th
where th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and ret.status in ('PROCESSED','PENDING')
and ret.transaction_header_id_in = th.member_transaction_header_id
and ret.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered >= p_fy_start_date
and th.transaction_date_entered <= p_period_end_date;
select TH.TRANSACTION_TYPE_CODE
from FA_TRANSACTION_HEADERS TH
where TH.TRANSACTION_HEADER_ID = p_member_transaction_header_id;
select nvl(RET.NBV_RETIRED,0),recognize_gain_loss
from FA_RETIREMENTS RET
where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
select nvl(RET.NBV_RETIRED,0),recognize_gain_loss
from FA_MC_RETIREMENTS RET
where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id
and ret.set_of_books_id = p_set_of_books_id;
select BK.REDUCTION_RATE
from FA_BOOKS BK
where BK.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
select BK.REDUCTION_RATE
from FA_MC_BOOKS BK
where BK.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id
and BK.set_of_books_id = p_set_of_books_id ;
select (bk.adjusted_cost-bk.recoverable_cost) adj_cost_diff, th.amortization_start_date
from fa_books bk, fa_transaction_headers th
where bk.asset_id = th.asset_id
and bk.book_type_code = p_book_type_code
and bk.asset_id = p_asset_id
and th.transaction_header_id = bk.transaction_header_id_in
and th.transaction_key = 'BA'
order by th.transaction_header_id desc;
select min(amortization_start_date)
from fa_books bk, fa_transaction_headers th
where bk.asset_id = th.asset_id
and bk.book_type_code = p_book_type_code
and bk.asset_id = p_asset_id
and th.transaction_header_id = bk.transaction_header_id_in
and th.transaction_key <> 'BA';