[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_period_update_flag VARCHAR2(1) DEFAULT NULL;
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;
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 -- Added deprn_method_code for bug fix 6780736
INTO l_rate_in_use, l_old_method_code
FROM fa_books
WHERE asset_id = g_rule_in.asset_id
AND book_type_code = g_rule_in.book_type_code
AND date_ineffective is null;
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_DEPRN_PERIODS_MRC_V dp,
FA_BOOK_CONTROLS_MRC_V 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 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,
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 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)+nvl(RET.COST_RETIRED,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,
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 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)+nvl(RET.COST_RETIRED,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,
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_MRC_V BK1,
FA_BOOKS_MRC_V BK2,
FA_RETIREMENTS_MRC_V 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
and nvl(BK2.REDUCTION_RATE,0) >0;
select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_BOOKS_MRC_V BK1,
FA_BOOKS_MRC_V BK2,
FA_RETIREMENTS_MRC_V 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,
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_MRC_V BK1,
FA_BOOKS_MRC_V BK2,
FA_BOOKS_MRC_V BK3,
FA_RETIREMENTS_MRC_V 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 nvl(BK3.REDUCTION_RATE,0) >0
and exists (select BK3.ASSET_ID
from FA_BOOKS_MRC_V 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)) total_change_in_cost,
sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
from FA_BOOKS_MRC_V BK1,
FA_BOOKS_MRC_V BK2,
FA_BOOKS_MRC_V BK3,
FA_RETIREMENTS_MRC_V 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_MRC_V 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 nvl(REDUCTION_RATE,0)
from FA_BOOKS
where TRANSACTION_HEADER_ID_IN = l_transaction_header_id;
select nvl(REDUCTION_RATE,0)
from FA_BOOKS_MRC_V
where TRANSACTION_HEADER_ID_IN = l_transaction_header_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 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 = 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 = 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 = 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 = 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_BOOKS_MRC_V BK1,
FA_BOOKS_MRC_V 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.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 <= l_fy_end_date
and CP.END_DATE >= l_fy_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 <= l_fy_end_date
and CP.END_DATE >= l_fy_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_BOOKS_MRC_V BK1,
FA_BOOKS_MRC_V BK2,
FA_BOOKS_MRC_V 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.fazcbcs_record.deprn_calendar
and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
and CP.END_DATE <= l_fy_end_date
and CP.END_DATE >= l_fy_start_date
and CP.PERIOD_NUM = p_period_num
and exists (select TH.ASSET_ID
from FA_BOOKS_MRC_V 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_MRC_V BK,
FA_TRANSACTION_HEADERS TH,
FA_CALENDAR_PERIODS CP,
FA_BOOK_CONTROLS_MRC_V 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 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 <= l_fy_end_date
and CP.END_DATE >= l_fy_start_date
and CP.PERIOD_NUM = p_period_num
)
group by BK3.SALVAGE_TYPE,BK3.PERCENT_SALVAGE_VALUE;
select start_date
, end_date
from fa_fiscal_year
where fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and fiscal_year = p_fiscal_year;
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_MRC_V 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 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_BOOKS_MRC_V 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 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_BOOK_CONTROLS_MRC_V BC
where BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE
and BC.BOOK_TYPE_CODE= p_book_type_code;
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_books_summary_mrc_v 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 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 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_DEPRN_PERIODS_MRC_V dp,
FA_BOOK_CONTROLS_MRC_V 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 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_RETIREMENTS_MRC_V 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_MRC_V 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) 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_RETIREMENTS_MRC_V 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_MRC_V 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 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_BOOK_CONTROLS_MRC_V 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 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_RETIREMENTS_MRC_V 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_MRC_V 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_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_RETIREMENTS_MRC_V 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_MRC_V 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 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)
from FA_RETIREMENTS RET
where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
select nvl(RET.NBV_RETIRED,0)
from FA_RETIREMENTS_MRC_V RET
where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_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_BOOKS_MRC_V BK
where BK.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;