[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fcp.period_num +( fct.number_per_fiscal_year * ffy.fiscal_year)
INTO l_pc
FROM fa_calendar_periods fcp, fa_calendar_types fct, fa_fiscal_year ffy
WHERE pp_date BETWEEN fcp.start_date AND fcp.end_date
AND pp_date BETWEEN ffy.start_date AND ffy.end_date
AND fct.calendar_type = fcp.calendar_type
AND ffy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
AND fct.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar;
select period_counter
, cost
-- , salvage_value
, member_salvage_value
, allowed_deprn_limit_amount
, adjusted_rate
, rate_adjustment_factor
, deprn_adjustment_amount
, deprn_amount
, ytd_deprn
, reserve_adjustment_amount
, deprn_reserve
from fa_books_summary
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and period_counter >= nvl(p_period_counter, 1);
select period_counter
, period_name
, period_open_date
, period_close_date
, calendar_period_open_date
, calendar_period_close_date
, fiscal_year
, period_num
from fa_deprn_periods
where book_type_code = p_book_type_code
and to_date (to_char (p_jdate), 'J') between calendar_period_open_date
and calendar_period_close_date;
select period_counter
, period_name
, period_open_date
, period_close_date
, calendar_period_open_date
, calendar_period_close_date
, fiscal_year
, period_num
from fa_mc_deprn_periods
where set_of_books_id = p_set_of_books_id
and book_type_code = p_book_type_code
and to_date (to_char (p_jdate), 'J') between calendar_period_open_date
and calendar_period_close_date;
select start_date
, end_date
from fa_calendar_periods
where calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and to_date (to_char (p_jdate), 'J') between start_date
and end_date;
select bs.period_counter
, bs.calendar_period_close_date
, bs.fiscal_year
, bs.period_num
from fa_mc_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.set_of_books_id = p_asset_hdr_rec.set_of_books_id
order by period_counter desc;
select bs.period_counter
, bs.calendar_period_close_date
, bs.fiscal_year
, bs.period_num
from fa_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
order by period_counter desc;
select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
, cp.start_date calendar_period_open_date
, cp.end_date calendar_period_close_date
, cp.period_num period_num
, fy.fiscal_year fiscal_year
, 'N'
from fa_book_controls bc
, fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and bc.last_period_counter + 1 >= fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and cp.end_date >= c_start_date
order by period_counter;
fa_debug_pkg.add(l_calling_fn, 'Insert into', 'FA_BOOKS_SUMMARY', p_log_level_rec => p_log_level_rec);
INSERT INTO FA_MC_BOOKS_SUMMARY(
SET_OF_BOOKS_ID
, ASSET_ID
, BOOK_TYPE_CODE
, PERIOD_COUNTER
, FISCAL_YEAR
, PERIOD_NUM
, CALENDAR_PERIOD_OPEN_DATE
, CALENDAR_PERIOD_CLOSE_DATE
, RESET_ADJUSTED_COST_FLAG
, CHANGE_IN_COST
, CHANGE_IN_ADDITIONS_COST
, CHANGE_IN_ADJUSTMENTS_COST
, CHANGE_IN_RETIREMENTS_COST
, CHANGE_IN_CIP_COST
, COST
, CIP_COST
, SALVAGE_TYPE
, PERCENT_SALVAGE_VALUE
, SALVAGE_VALUE
, MEMBER_SALVAGE_VALUE
, RECOVERABLE_COST
, DEPRN_LIMIT_TYPE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, MEMBER_DEPRN_LIMIT_AMOUNT
, ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST
, DEPRECIATE_FLAG
, DISABLED_FLAG
, DATE_PLACED_IN_SERVICE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_RATE
, FORMULA_FACTOR
, BONUS_RULE
, ADJUSTED_CAPACITY
, PRODUCTION_CAPACITY
, UNIT_OF_MEASURE
, REMAINING_LIFE1
, REMAINING_LIFE2
, UNREVALUED_COST
, REVAL_CEILING
, CEILING_NAME
, REVAL_AMORTIZATION_BASIS
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, GROUP_ASSET_ID
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, TERMINAL_GAIN_LOSS_AMOUNT
, TERMINAL_GAIN_LOSS_FLAG
, DEPRN_AMOUNT
, YTD_DEPRN
, DEPRN_RESERVE
, BONUS_DEPRN_AMOUNT
, BONUS_YTD_DEPRN
, BONUS_DEPRN_RESERVE
, IMPAIRMENT_AMOUNT
, impairment_reserve
, YTD_IMPAIRMENT
, LTD_PRODUCTION
, YTD_PRODUCTION
, PRODUCTION
, REVAL_AMORTIZATION
, REVAL_DEPRN_EXPENSE
, REVAL_RESERVE
, YTD_REVAL_DEPRN_EXPENSE
, DEPRN_OVERRIDE_FLAG
, SYSTEM_DEPRN_AMOUNT
, YTD_PROCEEDS_OF_SALE
, LTD_PROCEEDS_OF_SALE
, YTD_COST_OF_REMOVAL
, LTD_COST_OF_REMOVAL
, DEPRN_ADJUSTMENT_AMOUNT
, EXPENSE_ADJUSTMENT_AMOUNT
, UNPLANNED_AMOUNT
, RESERVE_ADJUSTMENT_AMOUNT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
)
SELECT p_asset_hdr_rec.set_of_books_id
, p_asset_hdr_rec.asset_id
, p_asset_hdr_rec.book_type_code
, tbs_period_counter(i)
, tbs_fiscal_year(i)
, tbs_period_num(i)
, tbs_calendar_period_open_date(i)
, tbs_calendar_period_close_date(i)
, tbs_reset_adjusted_cost_flag(i)
, 0 --CHANGE_IN_COST
, 0 --CHANGE_IN_ADDITIONS_COST
, 0 --CHANGE_IN_ADJUSTMENTS_COST
, 0 --CHANGE_IN_RETIREMENTS_COST
, 0 --CHANGE_IN_CIP_COST
, BS.COST
, BS.CIP_COST
, BS.SALVAGE_TYPE
, BS.PERCENT_SALVAGE_VALUE
, BS.SALVAGE_VALUE
, BS.MEMBER_SALVAGE_VALUE
, BS.RECOVERABLE_COST
, BS.DEPRN_LIMIT_TYPE
, BS.ALLOWED_DEPRN_LIMIT
, BS.ALLOWED_DEPRN_LIMIT_AMOUNT
, BS.MEMBER_DEPRN_LIMIT_AMOUNT
, BS.ADJUSTED_RECOVERABLE_COST
, BS.ADJUSTED_COST
, BS.DEPRECIATE_FLAG
, BS.DISABLED_FLAG
, BS.DATE_PLACED_IN_SERVICE
, BS.DEPRN_METHOD_CODE
, BS.LIFE_IN_MONTHS
, BS.RATE_ADJUSTMENT_FACTOR
, BS.ADJUSTED_RATE
, BS.FORMULA_FACTOR
, BS.BONUS_RULE
, BS.ADJUSTED_CAPACITY
, BS.PRODUCTION_CAPACITY
, BS.UNIT_OF_MEASURE
, BS.REMAINING_LIFE1
, BS.REMAINING_LIFE2
, BS.UNREVALUED_COST
, BS.REVAL_CEILING
, BS.CEILING_NAME
, BS.REVAL_AMORTIZATION_BASIS
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_ADJ_COST, BS.ADJUSTED_COST) --EOFY_ADJ_COST
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_FORMULA_FACTOR, BS.FORMULA_FACTOR) --EOFY_FORMULA_FACTOR
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_RESERVE,
tbs_fiscal_year(i) - 1, BS.DEPRN_RESERVE, 0) --EOFY_RESERVE
, BS.ADJUSTED_COST --EOP_ADJ_COST
, BS.FORMULA_FACTOR --EOP_FORMULA_FACTOR
, BS.SHORT_FISCAL_YEAR_FLAG
, BS.GROUP_ASSET_ID
, BS.SUPER_GROUP_ID
, BS.OVER_DEPRECIATE_OPTION
, 0 --TERMINAL_GAIN_LOSS_AMOUNT
, 'N' --TERMINAL_GAIN_LOSS_FLAG
, 0 --DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_DEPRN, 0) --YTD_DEPRN
, BS.DEPRN_RESERVE
, 0 --BONUS_DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.BONUS_YTD_DEPRN, 0) --BONUS_YTD_DEPRN
, BS.BONUS_DEPRN_RESERVE
, 0 --IMPAIRMENT_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_IMPAIRMENT, 0) --YTD_IMPAIRMENT
, BS.impairment_reserve
, BS.LTD_PRODUCTION
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_PRODUCTION, 0) --YTD_PRODUCTION
, null --PRODUCTION
, 0 --REVAL_AMORTIZATION
, 0 --REVAL_DEPRN_EXPENSE
, BS.REVAL_RESERVE
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_REVAL_DEPRN_EXPENSE, 0) --YTD_REVAL_DEPRN_EXPENSE
, 'N' --DEPRN_OVERRIDE_FLAG
, 0 --SYSTEM_DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_PROCEEDS_OF_SALE, 0) --YTD_PROCEEDS_OF_SALE
, BS.LTD_PROCEEDS_OF_SALE
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_COST_OF_REMOVAL, 0) --YTD_COST_OF_REMOVAL
, BS.LTD_COST_OF_REMOVAL
, 0 --DEPRN_ADJUSTMENT_AMOUNT
, 0 --EXPENSE_ADJUSTMENT_AMOUNT
, 0 --UNPLANNED_AMOUNT
, 0 --RESERVE_ADJUSTMENT_AMOUNT
, p_trans_rec.who_info.creation_date
, p_trans_rec.who_info.created_by
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
FROM FA_MC_BOOKS_SUMMARY BS
WHERE BS.ASSET_ID = p_asset_hdr_rec.asset_id
AND BS.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BS.period_counter = l_last_period_counter
AND BS.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
INSERT INTO FA_BOOKS_SUMMARY(
ASSET_ID
, BOOK_TYPE_CODE
, PERIOD_COUNTER
, FISCAL_YEAR
, PERIOD_NUM
, CALENDAR_PERIOD_OPEN_DATE
, CALENDAR_PERIOD_CLOSE_DATE
, RESET_ADJUSTED_COST_FLAG
, CHANGE_IN_COST
, CHANGE_IN_ADDITIONS_COST
, CHANGE_IN_ADJUSTMENTS_COST
, CHANGE_IN_RETIREMENTS_COST
, CHANGE_IN_CIP_COST
, COST
, CIP_COST
, SALVAGE_TYPE
, PERCENT_SALVAGE_VALUE
, SALVAGE_VALUE
, MEMBER_SALVAGE_VALUE
, RECOVERABLE_COST
, DEPRN_LIMIT_TYPE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, MEMBER_DEPRN_LIMIT_AMOUNT
, ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST
, DEPRECIATE_FLAG
, DISABLED_FLAG
, DATE_PLACED_IN_SERVICE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_RATE
, FORMULA_FACTOR
, BONUS_RULE
, ADJUSTED_CAPACITY
, PRODUCTION_CAPACITY
, UNIT_OF_MEASURE
, REMAINING_LIFE1
, REMAINING_LIFE2
, UNREVALUED_COST
, REVAL_CEILING
, CEILING_NAME
, REVAL_AMORTIZATION_BASIS
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, GROUP_ASSET_ID
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, TERMINAL_GAIN_LOSS_AMOUNT
, TERMINAL_GAIN_LOSS_FLAG
, DEPRN_AMOUNT
, YTD_DEPRN
, DEPRN_RESERVE
, BONUS_DEPRN_AMOUNT
, BONUS_YTD_DEPRN
, BONUS_DEPRN_RESERVE
, IMPAIRMENT_AMOUNT
, impairment_reserve
, YTD_IMPAIRMENT
, LTD_PRODUCTION
, YTD_PRODUCTION
, PRODUCTION
, REVAL_AMORTIZATION
, REVAL_DEPRN_EXPENSE
, REVAL_RESERVE
, YTD_REVAL_DEPRN_EXPENSE
, DEPRN_OVERRIDE_FLAG
, SYSTEM_DEPRN_AMOUNT
, YTD_PROCEEDS_OF_SALE
, LTD_PROCEEDS_OF_SALE
, YTD_COST_OF_REMOVAL
, LTD_COST_OF_REMOVAL
, DEPRN_ADJUSTMENT_AMOUNT
, EXPENSE_ADJUSTMENT_AMOUNT
, UNPLANNED_AMOUNT
, RESERVE_ADJUSTMENT_AMOUNT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
)
SELECT p_asset_hdr_rec.asset_id
, p_asset_hdr_rec.book_type_code
, tbs_period_counter(i)
, tbs_fiscal_year(i)
, tbs_period_num(i)
, tbs_calendar_period_open_date(i)
, tbs_calendar_period_close_date(i)
, tbs_reset_adjusted_cost_flag(i)
, 0 --CHANGE_IN_COST
, 0 --CHANGE_IN_ADDITIONS_COST
, 0 --CHANGE_IN_ADJUSTMENTS_COST
, 0 --CHANGE_IN_RETIREMENTS_COST
, 0 --CHANGE_IN_CIP_COST
, BS.COST
, BS.CIP_COST
, BS.SALVAGE_TYPE
, BS.PERCENT_SALVAGE_VALUE
, BS.SALVAGE_VALUE
, BS.MEMBER_SALVAGE_VALUE
, BS.RECOVERABLE_COST
, BS.DEPRN_LIMIT_TYPE
, BS.ALLOWED_DEPRN_LIMIT
, BS.ALLOWED_DEPRN_LIMIT_AMOUNT
, BS.MEMBER_DEPRN_LIMIT_AMOUNT
, BS.ADJUSTED_RECOVERABLE_COST
, BS.ADJUSTED_COST
, BS.DEPRECIATE_FLAG
, BS.DISABLED_FLAG
, BS.DATE_PLACED_IN_SERVICE
, BS.DEPRN_METHOD_CODE
, BS.LIFE_IN_MONTHS
, BS.RATE_ADJUSTMENT_FACTOR
, BS.ADJUSTED_RATE
, BS.FORMULA_FACTOR
, BS.BONUS_RULE
, BS.ADJUSTED_CAPACITY
, BS.PRODUCTION_CAPACITY
, BS.UNIT_OF_MEASURE
, BS.REMAINING_LIFE1
, BS.REMAINING_LIFE2
, BS.UNREVALUED_COST
, BS.REVAL_CEILING
, BS.CEILING_NAME
, BS.REVAL_AMORTIZATION_BASIS
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_ADJ_COST, BS.ADJUSTED_COST) --EOFY_ADJ_COST
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_FORMULA_FACTOR, BS.FORMULA_FACTOR) --EOFY_FORMULA_FACTOR
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.EOFY_RESERVE,
tbs_fiscal_year(i) - 1, BS.DEPRN_RESERVE, 0) --EOFY_RESERVE
, BS.ADJUSTED_COST --EOP_ADJ_COST
, BS.FORMULA_FACTOR --EOP_FORMULA_FACTOR
, BS.SHORT_FISCAL_YEAR_FLAG
, BS.GROUP_ASSET_ID
, BS.SUPER_GROUP_ID
, BS.OVER_DEPRECIATE_OPTION
, 0 --TERMINAL_GAIN_LOSS_AMOUNT
, 'N' --TERMINAL_GAIN_LOSS_FLAG
, 0 --DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_DEPRN, 0) --YTD_DEPRN
, BS.DEPRN_RESERVE
, 0 --BONUS_DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.BONUS_YTD_DEPRN, 0) --BONUS_YTD_DEPRN
, BS.BONUS_DEPRN_RESERVE
, 0 --IMPAIRMENT_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_IMPAIRMENT, 0) --YTD_IMPAIRMENT
, BS.impairment_reserve
, BS.LTD_PRODUCTION
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_PRODUCTION, 0) --YTD_PRODUCTION
, null --PRODUCTION
, 0 --REVAL_AMORTIZATION
, 0 --REVAL_DEPRN_EXPENSE
, BS.REVAL_RESERVE
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_REVAL_DEPRN_EXPENSE, 0) --YTD_REVAL_DEPRN_EXPENSE
, 'N' --DEPRN_OVERRIDE_FLAG
, 0 --SYSTEM_DEPRN_AMOUNT
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_PROCEEDS_OF_SALE, 0) --YTD_PROCEEDS_OF_SALE
, BS.LTD_PROCEEDS_OF_SALE
, DECODE(BS.FISCAL_YEAR, tbs_fiscal_year(i), BS.YTD_COST_OF_REMOVAL, 0) --YTD_COST_OF_REMOVAL
, BS.LTD_COST_OF_REMOVAL
, 0 --DEPRN_ADJUSTMENT_AMOUNT
, 0 --EXPENSE_ADJUSTMENT_AMOUNT
, 0 --UNPLANNED_AMOUNT
, 0 --RESERVE_ADJUSTMENT_AMOUNT
, p_trans_rec.who_info.creation_date
, p_trans_rec.who_info.created_by
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
FROM FA_BOOKS_SUMMARY BS
WHERE BS.ASSET_ID = p_asset_hdr_rec.asset_id
AND BS.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BS.period_counter = l_last_period_counter;
UPDATE FA_MC_BOOKS_SUMMARY
SET DEPRECIATE_FLAG = p_asset_fin_rec_new.depreciate_flag
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET DEPRECIATE_FLAG = p_asset_fin_rec_new.depreciate_flag
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code;
UPDATE FA_MC_BOOKS_SUMMARY
SET DISABLED_FLAG = p_asset_fin_rec_new.disabled_flag
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET DISABLED_FLAG = p_asset_fin_rec_new.disabled_flag
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code;
fa_debug_pkg.add(l_calling_fn||'()-', '# of records inserted', tbs_period_counter.COUNT);
fa_amort_pvt.tmd_period_counter.delete;
fa_amort_pvt.tmd_cost.delete;
fa_amort_pvt.tm_cost.delete;
fa_amort_pvt.tmd_cip_cost.delete;
fa_amort_pvt.tm_cip_cost.delete;
fa_amort_pvt.tmd_salvage_value.delete;
fa_amort_pvt.tm_salvage_value.delete;
fa_amort_pvt.tmd_deprn_limit_amount.delete;
fa_amort_pvt.tm_deprn_limit_amount.delete;
fa_amort_pvt.t_period_counter.delete;
fa_amort_pvt.t_fiscal_year.delete;
fa_amort_pvt.t_period_num.delete;
fa_amort_pvt.t_calendar_period_open_date.delete;
fa_amort_pvt.t_calendar_period_close_date.delete;
fa_amort_pvt.t_reset_adjusted_cost_flag.delete;
fa_amort_pvt.t_change_in_cost.delete;
fa_amort_pvt.t_cost.delete;
fa_amort_pvt.t_cip_cost.delete;
fa_amort_pvt.t_salvage_type.delete;
fa_amort_pvt.t_percent_salvage_value.delete;
fa_amort_pvt.t_salvage_value.delete;
fa_amort_pvt.t_member_salvage_value.delete;
fa_amort_pvt.t_recoverable_cost.delete;
fa_amort_pvt.t_deprn_limit_type.delete;
fa_amort_pvt.t_allowed_deprn_limit.delete;
fa_amort_pvt.t_allowed_deprn_limit_amount.delete;
fa_amort_pvt.t_member_deprn_limit_amount.delete;
fa_amort_pvt.t_adjusted_recoverable_cost.delete;
fa_amort_pvt.t_adjusted_cost.delete;
fa_amort_pvt.t_depreciate_flag.delete;
fa_amort_pvt.t_date_placed_in_service.delete;
fa_amort_pvt.t_deprn_method_code.delete;
fa_amort_pvt.t_life_in_months.delete;
fa_amort_pvt.t_rate_adjustment_factor.delete;
fa_amort_pvt.t_adjusted_rate.delete;
fa_amort_pvt.t_bonus_rule.delete;
fa_amort_pvt.t_adjusted_capacity.delete;
fa_amort_pvt.t_production_capacity.delete;
fa_amort_pvt.t_unit_of_measure.delete;
fa_amort_pvt.t_remaining_life1.delete;
fa_amort_pvt.t_remaining_life2.delete;
fa_amort_pvt.t_formula_factor.delete;
fa_amort_pvt.t_unrevalued_cost.delete;
fa_amort_pvt.t_reval_amortization_basis.delete;
fa_amort_pvt.t_reval_ceiling.delete;
fa_amort_pvt.t_ceiling_name.delete;
fa_amort_pvt.t_eofy_adj_cost.delete;
fa_amort_pvt.t_eofy_formula_factor.delete;
fa_amort_pvt.t_eofy_reserve.delete;
fa_amort_pvt.t_eop_adj_cost.delete;
fa_amort_pvt.t_eop_formula_factor.delete;
fa_amort_pvt.t_short_fiscal_year_flag.delete;
fa_amort_pvt.t_group_asset_id.delete;
fa_amort_pvt.t_super_group_id.delete;
fa_amort_pvt.t_over_depreciate_option.delete;
fa_amort_pvt.t_deprn_amount.delete;
fa_amort_pvt.t_ytd_deprn.delete;
fa_amort_pvt.t_deprn_reserve.delete;
fa_amort_pvt.t_bonus_deprn_amount.delete;
fa_amort_pvt.t_bonus_ytd_deprn.delete;
fa_amort_pvt.t_bonus_deprn_reserve.delete;
fa_amort_pvt.t_bonus_rate.delete;
fa_amort_pvt.t_impairment_amount.delete;
fa_amort_pvt.t_ytd_impairment.delete;
fa_amort_pvt.t_impairment_reserve.delete;
fa_amort_pvt.t_ltd_production.delete;
fa_amort_pvt.t_ytd_production.delete;
fa_amort_pvt.t_production.delete;
fa_amort_pvt.t_reval_amortization.delete;
fa_amort_pvt.t_reval_deprn_expense.delete;
fa_amort_pvt.t_reval_reserve.delete;
fa_amort_pvt.t_ytd_reval_deprn_expense.delete;
fa_amort_pvt.t_deprn_override_flag.delete;
fa_amort_pvt.t_system_deprn_amount.delete;
fa_amort_pvt.t_system_bonus_deprn_amount.delete;
fa_amort_pvt.t_ytd_proceeds_of_sale.delete;
fa_amort_pvt.t_ltd_proceeds_of_sale.delete;
fa_amort_pvt.t_ytd_cost_of_removal.delete;
fa_amort_pvt.t_ltd_cost_of_removal.delete;
fa_amort_pvt.t_deprn_adjustment_amount.delete;
fa_amort_pvt.t_expense_adjustment_amount.delete;
fa_amort_pvt.t_reserve_adjustment_amount.delete;
select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM period_counter
, cp.start_date calendar_period_open_date
, cp.end_date calendar_period_close_date
, cp.period_num period_num
, fy.fiscal_year fiscal_year
, 'N'
from fa_fiscal_year fy
, fa_calendar_types ct
, fa_calendar_periods cp
where ct.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and ct.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and fa_cache_pkg.fazcbc_record.last_period_counter + 1 >=
fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
and cp.end_date >= p_asset_fin_rec.date_placed_in_service
order by period_counter;
DELETE FROM FA_MC_BOOKS_SUMMARY
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
DELETE FROM FA_BOOKS_SUMMARY
WHERE ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code;
fa_debug_pkg.add(l_calling_fn||'()-', '# of records will be inserted',
tbs_period_counter.COUNT);
fa_debug_pkg.add(l_calling_fn, 'Insert into', 'FA_BOOKS_SUMMARY', p_log_level_rec => p_log_level_rec);
INSERT INTO FA_MC_BOOKS_SUMMARY(
SET_OF_BOOKS_ID
, ASSET_ID
, BOOK_TYPE_CODE
, PERIOD_COUNTER
, FISCAL_YEAR
, PERIOD_NUM
, CALENDAR_PERIOD_OPEN_DATE
, CALENDAR_PERIOD_CLOSE_DATE
, RESET_ADJUSTED_COST_FLAG
, CHANGE_IN_COST
, CHANGE_IN_ADDITIONS_COST
, CHANGE_IN_ADJUSTMENTS_COST
, CHANGE_IN_RETIREMENTS_COST
, CHANGE_IN_CIP_COST
, COST
, CIP_COST
, SALVAGE_TYPE
, PERCENT_SALVAGE_VALUE
, SALVAGE_VALUE
, MEMBER_SALVAGE_VALUE
, RECOVERABLE_COST
, DEPRN_LIMIT_TYPE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, MEMBER_DEPRN_LIMIT_AMOUNT
, ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST
, DEPRECIATE_FLAG
, DISABLED_FLAG
, DATE_PLACED_IN_SERVICE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_RATE
, FORMULA_FACTOR
, BONUS_RULE
, ADJUSTED_CAPACITY
, PRODUCTION_CAPACITY
, UNIT_OF_MEASURE
, REMAINING_LIFE1
, REMAINING_LIFE2
, UNREVALUED_COST
, REVAL_CEILING
, CEILING_NAME
, REVAL_AMORTIZATION_BASIS
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, GROUP_ASSET_ID
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, TERMINAL_GAIN_LOSS_AMOUNT
, TERMINAL_GAIN_LOSS_FLAG
, DEPRN_AMOUNT
, YTD_DEPRN
, DEPRN_RESERVE
, BONUS_DEPRN_AMOUNT
, BONUS_YTD_DEPRN
, BONUS_DEPRN_RESERVE
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, LTD_PRODUCTION
, YTD_PRODUCTION
, PRODUCTION
, REVAL_AMORTIZATION
, REVAL_DEPRN_EXPENSE
, REVAL_RESERVE
, YTD_REVAL_DEPRN_EXPENSE
, DEPRN_OVERRIDE_FLAG
, SYSTEM_DEPRN_AMOUNT
, YTD_PROCEEDS_OF_SALE
, LTD_PROCEEDS_OF_SALE
, YTD_COST_OF_REMOVAL
, LTD_COST_OF_REMOVAL
, DEPRN_ADJUSTMENT_AMOUNT
, EXPENSE_ADJUSTMENT_AMOUNT
, UNPLANNED_AMOUNT
, RESERVE_ADJUSTMENT_AMOUNT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
)
VALUES( p_asset_hdr_rec.set_of_books_id
, p_asset_hdr_rec.asset_id
, p_asset_hdr_rec.book_type_code
, tbs_period_counter(i)
, tbs_fiscal_year(i)
, tbs_period_num(i)
, tbs_calendar_period_open_date(i)
, tbs_calendar_period_close_date(i)
, tbs_reset_adjusted_cost_flag(i)
, 0 --CHANGE_IN_COST
, 0 --CHANGE_IN_ADDITIONS_COST
, 0 --CHANGE_IN_ADJUSTMENTS_COST
, 0 --CHANGE_IN_RETIREMENTS_COST
, 0 --CHANGE_IN_CIP_COST
, 0 --COST
, 0 --CIP_COST
, p_asset_fin_rec.salvage_type --SALVAGE_TYPE
, p_asset_fin_rec.percent_salvage_value --PERCENT_SALVAGE_VALUE
, 0 --SALVAGE_VALUE
, 0 --MEMBER_SALVAGE_VALUE
, 0 --RECOVERABLE_COST
, p_asset_fin_rec.deprn_limit_type --DEPRN_LIMIT_TYPE
, p_asset_fin_rec.allowed_deprn_limit --ALLOWED_DEPRN_LIMIT
, 0 --ALLOWED_DEPRN_LIMIT_AMOUNT
, 0 --MEMBER_DEPRN_LIMIT_AMOUNT
, 0 --ADJUSTED_RECOVERABLE_COST
, 0 --ADJUSTED_COST
, p_asset_fin_rec.depreciate_flag --DEPRECIATE_FLAG
, p_asset_fin_rec.disabled_flag --DISABLED_FLAG
, p_asset_fin_rec.date_placed_in_service --DATE_PLACED_IN_SERVICE
, p_asset_fin_rec.deprn_method_code --DEPRN_METHOD_CODE
, p_asset_fin_rec.life_in_months --LIFE_IN_MONTHS
, 1 --RATE_ADJUSTMENT_FACTOR
, p_asset_fin_rec.adjusted_rate --ADJUSTED_RATE
, 1 --FORMULA_FACTOR
, p_asset_fin_rec.bonus_rule --BONUS_RULE
, p_asset_fin_rec.adjusted_capacity --ADJUSTED_CAPACITY
, p_asset_fin_rec.production_capacity --PRODUCTION_CAPACITY
, p_asset_fin_rec.unit_of_measure --UNIT_OF_MEASURE
, p_asset_fin_rec.remaining_life1 --REMAINING_LIFE1
, p_asset_fin_rec.remaining_life2 --REMAINING_LIFE2
, 0 --UNREVALUED_COST
, p_asset_fin_rec.reval_ceiling --REVAL_CEILING
, p_asset_fin_rec.ceiling_name --CEILING_NAME
, 0 --REVAL_AMORTIZATION_BASIS
, 0 --EOFY_ADJ_COST
, 1 --EOFY_FORMULA_FACTOR
, 0 --EOFY_RESERVE
, 0 --EOP_ADJ_COST
, 1 --EOP_FORMULA_FACTOR
, 'NO' --SHORT_FISCAL_YEAR_FLAG
, null --GROUP_ASSET_ID
, p_asset_fin_rec.super_group_id --SUPER_GROUP_ID
, p_asset_fin_rec.over_depreciate_option --OVER_DEPRECIATE_OPTION
, 0 --TERMINAL_GAIN_LOSS_AMOUNT
, 'N' --TERMINAL_GAIN_LOSS_FLAG
, 0 --DEPRN_AMOUNT
, 0 --YTD_DEPRN
, 0 --DEPRN_RESERVE
, 0 --BONUS_DEPRN_AMOUNT
, 0 --BONUS_YTD_DEPRN
, 0 --BONUS_DEPRN_RESERVE
, 0 --IMPAIRMENT_AMOUNT
, 0 --YTD_IMPAIRMENT
, 0 --impairment_reserve
, null --LTD_PRODUCTION
, null --YTD_PRODUCTION
, null --PRODUCTION
, 0 --REVAL_AMORTIZATION
, 0 --REVAL_DEPRN_EXPENSE
, 0 --REVAL_RESERVE
, 0 --YTD_REVAL_DEPRN_EXPENSE
, 'N' --DEPRN_OVERRIDE_FLAG
, 0 --SYSTEM_DEPRN_AMOUNT
, 0 --YTD_PROCEEDS_OF_SALE
, 0 --LTD_PROCEEDS_OF_SALE
, 0 --YTD_COST_OF_REMOVAL
, 0 --LTD_COST_OF_REMOVAL
, 0 --DEPRN_ADJUSTMENT_AMOUNT
, 0 --EXPENSE_ADJUSTMENT_AMOUNT
, 0 --UNPLANNED_AMOUNT
, 0 --RESERVE_ADJUSTMENT_AMOUNT
, p_trans_rec.who_info.creation_date
, p_trans_rec.who_info.created_by
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
);
INSERT INTO FA_BOOKS_SUMMARY(
ASSET_ID
, BOOK_TYPE_CODE
, PERIOD_COUNTER
, FISCAL_YEAR
, PERIOD_NUM
, CALENDAR_PERIOD_OPEN_DATE
, CALENDAR_PERIOD_CLOSE_DATE
, RESET_ADJUSTED_COST_FLAG
, CHANGE_IN_COST
, CHANGE_IN_ADDITIONS_COST
, CHANGE_IN_ADJUSTMENTS_COST
, CHANGE_IN_RETIREMENTS_COST
, CHANGE_IN_CIP_COST
, COST
, CIP_COST
, SALVAGE_TYPE
, PERCENT_SALVAGE_VALUE
, SALVAGE_VALUE
, MEMBER_SALVAGE_VALUE
, RECOVERABLE_COST
, DEPRN_LIMIT_TYPE
, ALLOWED_DEPRN_LIMIT
, ALLOWED_DEPRN_LIMIT_AMOUNT
, MEMBER_DEPRN_LIMIT_AMOUNT
, ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST
, DEPRECIATE_FLAG
, DISABLED_FLAG
, DATE_PLACED_IN_SERVICE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, ADJUSTED_RATE
, FORMULA_FACTOR
, BONUS_RULE
, ADJUSTED_CAPACITY
, PRODUCTION_CAPACITY
, UNIT_OF_MEASURE
, REMAINING_LIFE1
, REMAINING_LIFE2
, UNREVALUED_COST
, REVAL_CEILING
, CEILING_NAME
, REVAL_AMORTIZATION_BASIS
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, GROUP_ASSET_ID
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, TERMINAL_GAIN_LOSS_AMOUNT
, TERMINAL_GAIN_LOSS_FLAG
, DEPRN_AMOUNT
, YTD_DEPRN
, DEPRN_RESERVE
, BONUS_DEPRN_AMOUNT
, BONUS_YTD_DEPRN
, BONUS_DEPRN_RESERVE
, IMPAIRMENT_AMOUNT
, YTD_IMPAIRMENT
, impairment_reserve
, LTD_PRODUCTION
, YTD_PRODUCTION
, PRODUCTION
, REVAL_AMORTIZATION
, REVAL_DEPRN_EXPENSE
, REVAL_RESERVE
, YTD_REVAL_DEPRN_EXPENSE
, DEPRN_OVERRIDE_FLAG
, SYSTEM_DEPRN_AMOUNT
, YTD_PROCEEDS_OF_SALE
, LTD_PROCEEDS_OF_SALE
, YTD_COST_OF_REMOVAL
, LTD_COST_OF_REMOVAL
, DEPRN_ADJUSTMENT_AMOUNT
, EXPENSE_ADJUSTMENT_AMOUNT
, UNPLANNED_AMOUNT
, RESERVE_ADJUSTMENT_AMOUNT
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
)
VALUES( p_asset_hdr_rec.asset_id
, p_asset_hdr_rec.book_type_code
, tbs_period_counter(i)
, tbs_fiscal_year(i)
, tbs_period_num(i)
, tbs_calendar_period_open_date(i)
, tbs_calendar_period_close_date(i)
, tbs_reset_adjusted_cost_flag(i)
, 0 --CHANGE_IN_COST
, 0 --CHANGE_IN_ADDITIONS_COST
, 0 --CHANGE_IN_ADJUSTMENTS_COST
, 0 --CHANGE_IN_RETIREMENTS_COST
, 0 --CHANGE_IN_CIP_COST
, 0 --COST
, 0 --CIP_COST
, p_asset_fin_rec.salvage_type --SALVAGE_TYPE
, p_asset_fin_rec.percent_salvage_value --PERCENT_SALVAGE_VALUE
, 0 --SALVAGE_VALUE
, 0 --MEMBER_SALVAGE_VALUE
, 0 --RECOVERABLE_COST
, p_asset_fin_rec.deprn_limit_type --DEPRN_LIMIT_TYPE
, p_asset_fin_rec.allowed_deprn_limit --ALLOWED_DEPRN_LIMIT
, 0 --ALLOWED_DEPRN_LIMIT_AMOUNT
, 0 --MEMBER_DEPRN_LIMIT_AMOUNT
, 0 --ADJUSTED_RECOVERABLE_COST
, 0 --ADJUSTED_COST
, p_asset_fin_rec.depreciate_flag --DEPRECIATE_FLAG
, p_asset_fin_rec.disabled_flag --DISABLED_FLAG
, p_asset_fin_rec.date_placed_in_service --DATE_PLACED_IN_SERVICE
, p_asset_fin_rec.deprn_method_code --DEPRN_METHOD_CODE
, p_asset_fin_rec.life_in_months --LIFE_IN_MONTHS
, 1 --RATE_ADJUSTMENT_FACTOR
, p_asset_fin_rec.adjusted_rate --ADJUSTED_RATE
, 1 --FORMULA_FACTOR
, p_asset_fin_rec.bonus_rule --BONUS_RULE
, p_asset_fin_rec.adjusted_capacity --ADJUSTED_CAPACITY
, p_asset_fin_rec.production_capacity --PRODUCTION_CAPACITY
, p_asset_fin_rec.unit_of_measure --UNIT_OF_MEASURE
, p_asset_fin_rec.remaining_life1 --REMAINING_LIFE1
, p_asset_fin_rec.remaining_life2 --REMAINING_LIFE2
, 0 --UNREVALUED_COST
, p_asset_fin_rec.reval_ceiling --REVAL_CEILING
, p_asset_fin_rec.ceiling_name --CEILING_NAME
, 0 --REVAL_AMORTIZATION_BASIS
, 0 --EOFY_ADJ_COST
, 1 --EOFY_FORMULA_FACTOR
, 0 --EOFY_RESERVE
, 0 --EOP_ADJ_COST
, 1 --EOP_FORMULA_FACTOR
, 'NO' --SHORT_FISCAL_YEAR_FLAG
, null --GROUP_ASSET_ID
, p_asset_fin_rec.super_group_id --SUPER_GROUP_ID
, p_asset_fin_rec.over_depreciate_option --OVER_DEPRECIATE_OPTION
, 0 --TERMINAL_GAIN_LOSS_AMOUNT
, 'N' --TERMINAL_GAIN_LOSS_FLAG
, 0 --DEPRN_AMOUNT
, 0 --YTD_DEPRN
, 0 --DEPRN_RESERVE
, 0 --BONUS_DEPRN_AMOUNT
, 0 --BONUS_YTD_DEPRN
, 0 --BONUS_DEPRN_RESERVE
, 0 --IMPAIRMENT_AMOUNT
, 0 --YTD_IMPAIRMENT
, 0 --impairment_reserve
, null --LTD_PRODUCTION
, null --YTD_PRODUCTION
, null --PRODUCTION
, 0 --REVAL_AMORTIZATION
, 0 --REVAL_DEPRN_EXPENSE
, 0 --REVAL_RESERVE
, 0 --YTD_REVAL_DEPRN_EXPENSE
, 'N' --DEPRN_OVERRIDE_FLAG
, 0 --SYSTEM_DEPRN_AMOUNT
, 0 --YTD_PROCEEDS_OF_SALE
, 0 --LTD_PROCEEDS_OF_SALE
, 0 --YTD_COST_OF_REMOVAL
, 0 --LTD_COST_OF_REMOVAL
, 0 --DEPRN_ADJUSTMENT_AMOUNT
, 0 --EXPENSE_ADJUSTMENT_AMOUNT
, 0 --UNPLANNED_AMOUNT
, 0 --RESERVE_ADJUSTMENT_AMOUNT
, p_trans_rec.who_info.creation_date
, p_trans_rec.who_info.created_by
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
);
fa_debug_pkg.add(l_calling_fn||'()-', '# of records inserted', tbs_period_counter.COUNT);
select period_counter,
0, 0, 0, 0, 0, null, 0, null, 0, 0, 0
from fa_books_summary
where asset_id = c_group_asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and calendar_period_close_date >= c_date
order by period_counter;
select th.transaction_header_id
, th.transaction_type_code
, th.transaction_subtype
, th.transaction_key
, bs.period_counter
from fa_transaction_headers th
, fa_books_summary bs
, fa_transaction_headers mth
, fa_books bk
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and th.asset_id = mth.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and bs.asset_id = p_group_asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_in = th.transaction_header_id
and decode(th.transaction_subtype,
'EXPENSED', greatest(decode(bk.group_asset_id,
null,p_date_placed_in_service,
nvl(th.amortization_start_date,
th.transaction_date_entered)), p_date_placed_in_service),
greatest(nvl(th.amortization_start_date,
th.transaction_date_entered), p_date_placed_in_service))
between bs.calendar_period_open_date
and bs.calendar_period_close_date
and (th.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN',
'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT',
'REINSTATEMENT')
or (th.transaction_type_code = 'REINSTATEMENT' and
th.transaction_header_id = p_trans_rec.member_transaction_header_id));
select mth.transaction_header_id
, mth.transaction_type_code
, mth.transaction_subtype
, mth.transaction_key
, bs.period_counter
from fa_books_summary bs
, fa_transaction_headers mth
, fa_books bk
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and bs.asset_id = p_group_asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_in = mth.transaction_header_id
and decode(mth.transaction_subtype,
'EXPENSED', greatest(decode(bk.group_asset_id,
null,p_date_placed_in_service,
nvl(mth.amortization_start_date,
mth.transaction_date_entered)), p_date_placed_in_service),
greatest(nvl(mth.amortization_start_date,
mth.transaction_date_entered), p_date_placed_in_service))
between bs.calendar_period_open_date
and bs.calendar_period_close_date
and (mth.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN',
'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT',
'REINSTATEMENT')
or (mth.transaction_type_code = 'REINSTATEMENT' and
mth.transaction_header_id = p_trans_rec.member_transaction_header_id));
select ret.date_retired
, ret.cost_retired
, nvl(ret.reserve_retired, 0)
, ret.proceeds_of_sale
, ret.cost_of_removal
from fa_retirements ret
where ret.transaction_header_id_in = c_transaction_header_id
and ret.transaction_header_id_out is null;
select ret.date_retired
, ret.cost_retired
, nvl(ret.reserve_retired, 0)
, ret.proceeds_of_sale
, ret.cost_of_removal
from fa_mc_retirements ret
where ret.transaction_header_id_in = c_transaction_header_id
and ret.transaction_header_id_out is null
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select bs.period_counter
from fa_books_summary bs
, fa_retirements rt
where bs.asset_id = p_group_asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and rt.date_retired between bs.calendar_period_open_date and bs.calendar_period_close_date
and rt.transaction_header_id_out = c_transaction_header_id;
select bs.period_counter
from fa_mc_books_summary bs
, fa_mc_retirements rt
where bs.asset_id = p_group_asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and rt.date_retired between bs.calendar_period_open_date and bs.calendar_period_close_date
and rt.transaction_header_id_out = c_transaction_header_id
and rt.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select p_reclass_multiplier * (inbk.cost - nvl(outbk.cost, 0))
, p_reclass_multiplier * (nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0))
, p_reclass_multiplier * (inbk.salvage_value - nvl(outbk.salvage_value, 0))
, p_reclass_multiplier * (nvl(decode(inbk.deprn_limit_type, 'NONE', inbk.salvage_value,
inbk.allowed_deprn_limit_amount), 0) -
nvl(decode(outbk.deprn_limit_type, 'NONE', outbk.salvage_value,
outbk.allowed_deprn_limit_amount), 0)
)
, inbk.salvage_type
, outbk.salvage_type
, inbk.percent_salvage_value
, outbk.percent_salvage_value
, inbk.deprn_limit_type
, outbk.deprn_limit_type
, inbk.allowed_deprn_limit
, outbk.allowed_deprn_limit
from fa_books inbk,
fa_books outbk
, fa_transaction_headers mth
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and inbk.asset_id = mth.asset_id
and outbk.asset_id(+) = inbk.asset_id
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.book_type_code(+) = inbk.book_type_code
and inbk.transaction_header_id_in = c_transaction_header_id
and outbk.transaction_header_id_out(+) = inbk.transaction_header_id_in;
select p_reclass_multiplier * (inbk.cost - nvl(outbk.cost, 0))
, p_reclass_multiplier * (nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0))
, p_reclass_multiplier * (inbk.salvage_value - nvl(outbk.salvage_value, 0))
, p_reclass_multiplier *
(decode(inbk.deprn_limit_type,
outbk.deprn_limit_type,
nvl(decode(inbk.deprn_limit_type, 'NONE', inbk.salvage_value,
inbk.allowed_deprn_limit_amount), 0) -
nvl(decode(outbk.deprn_limit_type, 'NONE', outbk.salvage_value,
outbk.allowed_deprn_limit_amount), 0),
nvl(decode(inbk.deprn_limit_type, 'NONE', inbk.salvage_value,
inbk.allowed_deprn_limit_amount), 0)))
, inbk.salvage_type
, outbk.salvage_type
, inbk.percent_salvage_value
, outbk.percent_salvage_value
, inbk.deprn_limit_type
, outbk.deprn_limit_type
, inbk.allowed_deprn_limit
, outbk.allowed_deprn_limit
from fa_mc_books inbk,
fa_mc_books outbk
, fa_transaction_headers mth
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and inbk.asset_id = mth.asset_id
and outbk.asset_id(+) = inbk.asset_id
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.book_type_code(+) = inbk.book_type_code
and inbk.transaction_header_id_in = c_transaction_header_id
and outbk.transaction_header_id_out(+) = inbk.transaction_header_id_in
and inbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and outbk.set_of_books_id(+) = p_asset_hdr_rec.set_of_books_id;
select inbk.salvage_value - nvl(outbk.salvage_value, 0)
, nvl(inbk.allowed_deprn_limit_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0)
from fa_books inbk,
fa_books outbk
where outbk.asset_id(+) = inbk.asset_id
and outbk.book_type_code(+) = inbk.book_type_code
and inbk.transaction_header_id_in = p_trans_rec.member_transaction_header_id
and outbk.transaction_header_id_out(+) = inbk.transaction_header_id_in;
select inbk.salvage_value - nvl(outbk.salvage_value, 0)
, nvl(inbk.allowed_deprn_limit_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0)
from fa_mc_books inbk,
fa_mc_books outbk
where outbk.asset_id(+) = inbk.asset_id
and outbk.book_type_code(+) = inbk.book_type_code
and inbk.transaction_header_id_in = p_trans_rec.member_transaction_header_id
and outbk.transaction_header_id_out(+) = inbk.transaction_header_id_in
and inbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and outbk.set_of_books_id(+) = p_asset_hdr_rec.set_of_books_id; --Bug 9099329
select ret.proceeds_of_sale
, ret.cost_of_removal
, -1 * nvl(ret.reserve_retired, 0)
, -1 * nbv_retired
from fa_retirements ret
where ret.transaction_header_id_in = c_transaction_header_id
and ret.transaction_header_id_out is null;
select ret.proceeds_of_sale
, ret.cost_of_removal
, -1 * nvl(ret.reserve_retired, 0)
, -1 * nbv_retired
from fa_mc_retirements ret
where ret.transaction_header_id_in = c_transaction_header_id
and ret.transaction_header_id_out is null
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select -1 * ret.proceeds_of_sale
, -1 * ret.cost_of_removal
, nvl(ret.reserve_retired, 0)
, nbv_retired
from fa_retirements ret
, fa_transaction_headers mth
where mth.transaction_header_id = c_transaction_header_id
and mth.asset_id = ret.asset_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out = c_transaction_header_id;
select -1 * ret.proceeds_of_sale
, -1 * ret.cost_of_removal
, nvl(ret.reserve_retired, 0)
, nbv_retired
from fa_mc_retirements ret
, fa_transaction_headers mth
where mth.transaction_header_id = c_transaction_header_id
and mth.asset_id = ret.asset_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out = c_transaction_header_id
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select bs.period_counter
from fa_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter = p_period_rec.period_counter;
select bs.period_counter
from fa_mc_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter = p_period_rec.period_counter
and bs.set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
UPDATE FA_MC_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = 'Y'
, SALVAGE_TYPE = px_asset_fin_rec_new.salvage_type
, PERCENT_SALVAGE_VALUE = px_asset_fin_rec_new.percent_salvage_value
, SALVAGE_VALUE = px_asset_fin_rec_new.salvage_value
, RECOVERABLE_COST = px_asset_fin_rec_new.recoverable_cost
, DEPRN_LIMIT_TYPE = px_asset_fin_rec_new.deprn_limit_type
, ALLOWED_DEPRN_LIMIT = px_asset_fin_rec_new.allowed_deprn_limit
, ALLOWED_DEPRN_LIMIT_AMOUNT = px_asset_fin_rec_new.allowed_deprn_limit_amount
, ADJUSTED_RECOVERABLE_COST = px_asset_fin_rec_new.adjusted_recoverable_cost
, ADJUSTED_COST = px_asset_fin_rec_new.adjusted_cost
, DEPRECIATE_FLAG = px_asset_fin_rec_new.depreciate_flag
, DISABLED_FLAG = px_asset_fin_rec_new.disabled_flag
, DEPRN_METHOD_CODE = px_asset_fin_rec_new.deprn_method_code
, LIFE_IN_MONTHS = px_asset_fin_rec_new.life_in_months
, RATE_ADJUSTMENT_FACTOR = px_asset_fin_rec_new.rate_adjustment_factor
, ADJUSTED_RATE = px_asset_fin_rec_new.adjusted_rate
, BONUS_RULE = px_asset_fin_rec_new.bonus_rule
, ADJUSTED_CAPACITY = px_asset_fin_rec_new.adjusted_capacity
, PRODUCTION_CAPACITY = px_asset_fin_rec_new.production_capacity
, UNIT_OF_MEASURE = px_asset_fin_rec_new.unit_of_measure
, REMAINING_LIFE1 = px_asset_fin_rec_new.remaining_life1
, REMAINING_LIFE2 = px_asset_fin_rec_new.remaining_life2
, FORMULA_FACTOR = px_asset_fin_rec_new.formula_factor
, CEILING_NAME = px_asset_fin_rec_new.ceiling_name
, SHORT_FISCAL_YEAR_FLAG = px_asset_fin_rec_new.short_fiscal_year_flag
, SUPER_GROUP_ID = px_asset_fin_rec_new.super_group_id
, OVER_DEPRECIATE_OPTION = px_asset_fin_rec_new.over_depreciate_option
, DEPRN_AMOUNT = DEPRN_AMOUNT + l_expense_amount
, YTD_DEPRN = YTD_DEPRN + l_expense_amount
, DEPRN_RESERVE = DEPRN_RESERVE + l_expense_amount + l_reserve_amount
, YTD_PROCEEDS_OF_SALE = nvl(YTD_PROCEEDS_OF_SALE, 0) + p_proceeds_of_sale
, LTD_PROCEEDS_OF_SALE = nvl(LTD_PROCEEDS_OF_SALE, 0) + p_proceeds_of_sale
, YTD_COST_OF_REMOVAL = nvl(YTD_COST_OF_REMOVAL, 0) + p_cost_of_removal
, LTD_COST_OF_REMOVAL = nvl(LTD_COST_OF_REMOVAL, 0) + p_cost_of_removal
, UNPLANNED_AMOUNT = UNPLANNED_AMOUNT + l_unplanned_amount
, EXPENSE_ADJUSTMENT_AMOUNT = EXPENSE_ADJUSTMENT_AMOUNT + l_expense_amount
, RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reserve_amount
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = 'Y'
, SALVAGE_TYPE = px_asset_fin_rec_new.salvage_type
, PERCENT_SALVAGE_VALUE = px_asset_fin_rec_new.percent_salvage_value
, SALVAGE_VALUE = px_asset_fin_rec_new.salvage_value
, RECOVERABLE_COST = px_asset_fin_rec_new.recoverable_cost
, DEPRN_LIMIT_TYPE = px_asset_fin_rec_new.deprn_limit_type
, ALLOWED_DEPRN_LIMIT = px_asset_fin_rec_new.allowed_deprn_limit
, ALLOWED_DEPRN_LIMIT_AMOUNT = px_asset_fin_rec_new.allowed_deprn_limit_amount
, ADJUSTED_RECOVERABLE_COST = px_asset_fin_rec_new.adjusted_recoverable_cost
, ADJUSTED_COST = px_asset_fin_rec_new.adjusted_cost
, DEPRECIATE_FLAG = px_asset_fin_rec_new.depreciate_flag
, DISABLED_FLAG = px_asset_fin_rec_new.disabled_flag
, DEPRN_METHOD_CODE = px_asset_fin_rec_new.deprn_method_code
, LIFE_IN_MONTHS = px_asset_fin_rec_new.life_in_months
, RATE_ADJUSTMENT_FACTOR = px_asset_fin_rec_new.rate_adjustment_factor
, ADJUSTED_RATE = px_asset_fin_rec_new.adjusted_rate
, BONUS_RULE = px_asset_fin_rec_new.bonus_rule
, ADJUSTED_CAPACITY = px_asset_fin_rec_new.adjusted_capacity
, PRODUCTION_CAPACITY = px_asset_fin_rec_new.production_capacity
, UNIT_OF_MEASURE = px_asset_fin_rec_new.unit_of_measure
, REMAINING_LIFE1 = px_asset_fin_rec_new.remaining_life1
, REMAINING_LIFE2 = px_asset_fin_rec_new.remaining_life2
, FORMULA_FACTOR = px_asset_fin_rec_new.formula_factor
, CEILING_NAME = px_asset_fin_rec_new.ceiling_name
, SHORT_FISCAL_YEAR_FLAG = px_asset_fin_rec_new.short_fiscal_year_flag
, SUPER_GROUP_ID = px_asset_fin_rec_new.super_group_id
, OVER_DEPRECIATE_OPTION = px_asset_fin_rec_new.over_depreciate_option
, DEPRN_AMOUNT = DEPRN_AMOUNT + l_expense_amount
, YTD_DEPRN = YTD_DEPRN + l_expense_amount
, DEPRN_RESERVE = DEPRN_RESERVE + l_expense_amount + l_reserve_amount
, YTD_PROCEEDS_OF_SALE = nvl(YTD_PROCEEDS_OF_SALE, 0) + p_proceeds_of_sale
, LTD_PROCEEDS_OF_SALE = nvl(LTD_PROCEEDS_OF_SALE, 0) + p_proceeds_of_sale
, YTD_COST_OF_REMOVAL = nvl(YTD_COST_OF_REMOVAL, 0) + p_cost_of_removal
, LTD_COST_OF_REMOVAL = nvl(LTD_COST_OF_REMOVAL, 0) + p_cost_of_removal
, UNPLANNED_AMOUNT = UNPLANNED_AMOUNT + l_unplanned_amount
, EXPENSE_ADJUSTMENT_AMOUNT = EXPENSE_ADJUSTMENT_AMOUNT + l_expense_amount
, RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reserve_amount
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter;
UPDATE FA_MC_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = 'Y'
, CHANGE_IN_COST = CHANGE_IN_COST + nvl(p_asset_fin_rec_adj.cost, 0)
, CHANGE_IN_CIP_COST = CHANGE_IN_CIP_COST + nvl(p_asset_fin_rec_adj.cip_cost, 0)
, COST = px_asset_fin_rec_new.cost
, CIP_COST = px_asset_fin_rec_new.cip_cost
, SALVAGE_VALUE = px_asset_fin_rec_new.salvage_value
, MEMBER_SALVAGE_VALUE = MEMBER_SALVAGE_VALUE + nvl(l_delta_salvage_value, 0)
, RECOVERABLE_COST = px_asset_fin_rec_new.recoverable_cost
, ALLOWED_DEPRN_LIMIT_AMOUNT = px_asset_fin_rec_new.allowed_deprn_limit_amount
, MEMBER_DEPRN_LIMIT_AMOUNT = MEMBER_DEPRN_LIMIT_AMOUNT +
nvl(l_delta_deprn_limit_amount,
decode(MEMBER_DEPRN_LIMIT_AMOUNT, NULL, NUll, 0))
, ADJUSTED_RECOVERABLE_COST = px_asset_fin_rec_new.adjusted_recoverable_cost
, ADJUSTED_COST = px_asset_fin_rec_new.ADJUSTED_COST
, UNREVALUED_COST = px_asset_fin_rec_new.UNREVALUED_COST
, REVAL_AMORTIZATION_BASIS = px_asset_fin_rec_new.REVAL_AMORTIZATION_BASIS
, DEPRN_AMOUNT = DEPRN_AMOUNT + l_expense_amount
, YTD_DEPRN = YTD_DEPRN + l_expense_amount
, DEPRN_RESERVE = DEPRN_RESERVE + l_expense_amount + l_reserve_amount
, YTD_PROCEEDS_OF_SALE = nvl(YTD_PROCEEDS_OF_SALE, 0) + l_proceeds_of_sale
, LTD_PROCEEDS_OF_SALE = nvl(LTD_PROCEEDS_OF_SALE, 0) + l_proceeds_of_sale
, YTD_COST_OF_REMOVAL = nvl(YTD_COST_OF_REMOVAL, 0) + l_cost_of_removal
, LTD_COST_OF_REMOVAL = nvl(LTD_COST_OF_REMOVAL, 0) + l_cost_of_removal
, UNPLANNED_AMOUNT = UNPLANNED_AMOUNT + l_unplanned_amount
, EXPENSE_ADJUSTMENT_AMOUNT = EXPENSE_ADJUSTMENT_AMOUNT + l_expense_amount
, RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reserve_amount
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = 'Y'
, CHANGE_IN_COST = CHANGE_IN_COST + nvl(p_asset_fin_rec_adj.cost, 0)
, CHANGE_IN_CIP_COST = CHANGE_IN_CIP_COST + nvl(p_asset_fin_rec_adj.cip_cost, 0)
, COST = COST + nvl(p_asset_fin_rec_adj.cost, 0)
, CIP_COST = CIP_COST + nvl(p_asset_fin_rec_adj.cip_cost, 0)
, SALVAGE_VALUE = px_asset_fin_rec_new.salvage_value
, MEMBER_SALVAGE_VALUE = MEMBER_SALVAGE_VALUE + nvl(l_delta_salvage_value, 0)
, RECOVERABLE_COST = px_asset_fin_rec_new.recoverable_cost
, ALLOWED_DEPRN_LIMIT_AMOUNT = px_asset_fin_rec_new.allowed_deprn_limit_amount
, MEMBER_DEPRN_LIMIT_AMOUNT = MEMBER_DEPRN_LIMIT_AMOUNT +
nvl(l_delta_deprn_limit_amount,
decode(MEMBER_DEPRN_LIMIT_AMOUNT, NULL, NUll, 0))
, ADJUSTED_RECOVERABLE_COST = px_asset_fin_rec_new.adjusted_recoverable_cost
, ADJUSTED_COST = px_asset_fin_rec_new.ADJUSTED_COST
, UNREVALUED_COST = px_asset_fin_rec_new.UNREVALUED_COST
, REVAL_AMORTIZATION_BASIS = px_asset_fin_rec_new.REVAL_AMORTIZATION_BASIS
, DEPRN_AMOUNT = DEPRN_AMOUNT + l_expense_amount
, YTD_DEPRN = YTD_DEPRN + l_expense_amount
, DEPRN_RESERVE = DEPRN_RESERVE + l_expense_amount + l_reserve_amount
, YTD_PROCEEDS_OF_SALE = nvl(YTD_PROCEEDS_OF_SALE, 0) + l_proceeds_of_sale
, LTD_PROCEEDS_OF_SALE = nvl(LTD_PROCEEDS_OF_SALE, 0) + l_proceeds_of_sale
, YTD_COST_OF_REMOVAL = nvl(YTD_COST_OF_REMOVAL, 0) + l_cost_of_removal
, LTD_COST_OF_REMOVAL = nvl(LTD_COST_OF_REMOVAL, 0) + l_cost_of_removal
, UNPLANNED_AMOUNT = UNPLANNED_AMOUNT + l_unplanned_amount
, EXPENSE_ADJUSTMENT_AMOUNT = EXPENSE_ADJUSTMENT_AMOUNT + l_expense_amount
, RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + l_reserve_amount
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter;
p_update_books_summary BOOLEAN default FALSE,
p_mrc_sob_type_code VARCHAR2,
p_calling_fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
l_calling_fn VARCHAR2(100) := 'FA_AMORT_PVT.bsRecalculate';
select least(inbk.date_placed_in_service, nvl(outbk.date_placed_in_service, inbk.date_placed_in_service))
from fa_books inbk
, fa_books outbk
where inbk.transaction_header_id_in = c_thid
and outbk.transaction_header_id_out(+) = inbk.transaction_header_id_in
and outbk.asset_id(+) = inbk.asset_id
and outbk.book_type_code(+) = inbk.book_type_code;
select
bs.period_counter
, bs.fiscal_year
, bs.period_num
, bs.calendar_period_open_date
, bs.calendar_period_close_date
, bs.reset_adjusted_cost_flag
, bs.change_in_cost
, bs.change_in_cip_cost
, bs.cost
, bs.cip_cost
, bs.salvage_type
, bs.percent_salvage_value
, bs.salvage_value
, bs.member_salvage_value
, bs.recoverable_cost
, bs.deprn_limit_type
, bs.allowed_deprn_limit
, bs.allowed_deprn_limit_amount
, bs.member_deprn_limit_amount
, bs.adjusted_recoverable_cost
, bs.adjusted_cost
, bs.depreciate_flag
, bs.date_placed_in_service
, bs.deprn_method_code
, bs.life_in_months
, bs.rate_adjustment_factor
, bs.adjusted_rate
, bs.bonus_rule
, bs.adjusted_capacity
, bs.production_capacity
, bs.unit_of_measure
, bs.remaining_life1
, bs.remaining_life2
, bs.formula_factor
, bs.unrevalued_cost
, bs.reval_amortization_basis
, bs.reval_ceiling
, bs.ceiling_name
, bs.eofy_adj_cost
, bs.eofy_formula_factor
, bs.eofy_reserve
, bs.eop_adj_cost
, bs.eop_formula_factor
, bs.short_fiscal_year_flag
, bs.group_asset_id
, bs.super_group_id
, bs.over_depreciate_option
, bs.deprn_amount
, bs.ytd_deprn
, bs.deprn_reserve
, bs.bonus_deprn_amount
, bs.bonus_ytd_deprn
, bs.bonus_deprn_reserve
, bs.bonus_rate
, bs.impairment_amount
, bs.ytd_impairment
, bs.impairment_reserve
, bs.ltd_production
, bs.ytd_production
, bs.production
, bs.reval_amortization
, bs.reval_deprn_expense
, bs.reval_reserve
, bs.ytd_reval_deprn_expense
, bs.deprn_override_flag
, bs.system_deprn_amount
, bs.system_bonus_deprn_amount
, bs.ytd_proceeds_of_sale
, bs.ltd_proceeds_of_sale
, bs.ytd_cost_of_removal
, bs.ltd_cost_of_removal
, bs.deprn_adjustment_amount
, bs.expense_adjustment_amount
, bs.reserve_adjustment_amount
, bs.change_in_eofy_reserve
, 0 impairment_amount
, 0 ytd_impairment
, 0 impairment_reserve
from fa_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter >= c_period_counter
order by bs.period_counter;
select
bs.period_counter
, bs.fiscal_year
, bs.period_num
, bs.calendar_period_open_date
, bs.calendar_period_close_date
, bs.reset_adjusted_cost_flag
, bs.change_in_cost
, bs.change_in_cip_cost
, bs.cost
, bs.cip_cost
, bs.salvage_type
, bs.percent_salvage_value
, bs.salvage_value
, bs.member_salvage_value
, bs.recoverable_cost
, bs.deprn_limit_type
, bs.allowed_deprn_limit
, bs.allowed_deprn_limit_amount
, bs.member_deprn_limit_amount
, bs.adjusted_recoverable_cost
, bs.adjusted_cost
, bs.depreciate_flag
, bs.date_placed_in_service
, bs.deprn_method_code
, bs.life_in_months
, bs.rate_adjustment_factor
, bs.adjusted_rate
, bs.bonus_rule
, bs.adjusted_capacity
, bs.production_capacity
, bs.unit_of_measure
, bs.remaining_life1
, bs.remaining_life2
, bs.formula_factor
, bs.unrevalued_cost
, bs.reval_amortization_basis
, bs.reval_ceiling
, bs.ceiling_name
, bs.eofy_adj_cost
, bs.eofy_formula_factor
, bs.eofy_reserve
, bs.eop_adj_cost
, bs.eop_formula_factor
, bs.short_fiscal_year_flag
, bs.group_asset_id
, bs.super_group_id
, bs.over_depreciate_option
, bs.deprn_amount
, bs.ytd_deprn
, bs.deprn_reserve
, bs.bonus_deprn_amount
, bs.bonus_ytd_deprn
, bs.bonus_deprn_reserve
, bs.bonus_rate
, bs.impairment_amount
, bs.ytd_impairment
, bs.impairment_reserve
, bs.ltd_production
, bs.ytd_production
, bs.production
, bs.reval_amortization
, bs.reval_deprn_expense
, bs.reval_reserve
, bs.ytd_reval_deprn_expense
, bs.deprn_override_flag
, bs.system_deprn_amount
, bs.system_bonus_deprn_amount
, bs.ytd_proceeds_of_sale
, bs.ltd_proceeds_of_sale
, bs.ytd_cost_of_removal
, bs.ltd_cost_of_removal
, deprn_adjustment_amount
, bs.expense_adjustment_amount
, bs.reserve_adjustment_amount
, bs.change_in_eofy_reserve
, 0 impairment_amount
, 0 ytd_impairment
, 0 impairment_reserve
from fa_mc_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter >= c_period_counter
and bs.set_of_books_id = p_asset_hdr_rec.set_of_books_id
order by bs.period_counter;
select recoverable_cost
, salvage_value
, deprn_reserve
from fa_books_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = c_period_counter;
select recoverable_cost
, salvage_value
, deprn_reserve
from fa_mc_books_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = c_period_counter
and set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
select -1 * ret.proceeds_of_sale
, -1 * ret.cost_of_removal
, ret.reserve_retired
, nbv_retired
, ret.recognize_gain_loss -- Added for bug 8425794 / 8244128
, ret.recapture_amount
from fa_retirements ret
, fa_transaction_headers mth
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and mth.asset_id = ret.asset_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out = p_trans_rec.member_transaction_header_id;
select -1 * ret.proceeds_of_sale
, -1 * ret.cost_of_removal
, nvl(ret.reserve_retired, 0)
, nbv_retired
, ret.recognize_gain_loss -- Added for bug 8425794 / 8244128
, ret.recapture_amount
from fa_mc_retirements ret
, fa_transaction_headers mth
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and mth.asset_id = ret.asset_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out = p_trans_rec.member_transaction_header_id
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
select bs.period_counter
from fa_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter = p_period_rec.period_counter;
select bs.period_counter
from fa_mc_books_summary bs
where bs.asset_id = p_asset_hdr_rec.asset_id
and bs.book_type_code = p_asset_hdr_rec.book_type_code
and bs.period_counter = p_period_rec.period_counter
and bs.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select dp.period_counter
from fa_deprn_periods dp
where dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.period_close_date is null;
select dp.period_counter
from fa_mc_deprn_periods dp
where dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.period_close_date is null
and dp.set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
select 'Y'
from fa_books_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_asset_hdr_rec.asset_id
and asset_id <> group_asset_id
and period_counter = l_recalc_start_period_counter - 1;
select ytd_deprn,deprn_reserve,bonus_ytd_deprn,bonus_deprn_reserve,
eofy_reserve,ytd_impairment,impairment_reserve
from fa_books_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_reclassed_asset_id
and group_asset_id = p_asset_hdr_rec.asset_id
and period_counter = l_trx_period_rec.period_counter - 1;
select ytd_deprn,deprn_reserve
from fa_deprn_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_reclassed_asset_id
and period_counter = l_mem_period_counter
and deprn_source_code = 'BOOKS';
select 'Y'
from fa_mc_books_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_asset_hdr_rec.asset_id
and asset_id <> group_asset_id
and period_counter = l_recalc_start_period_counter - 1
and set_of_books_id = p_asset_hdr_rec.set_of_books_id ;
select ytd_deprn,deprn_reserve,bonus_ytd_deprn,bonus_deprn_reserve,
eofy_reserve, ytd_impairment, impairment_reserve
from fa_mc_books_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_reclassed_asset_id
and group_asset_id = p_asset_hdr_rec.asset_id
and period_counter = l_trx_period_rec.period_counter - 1
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select ytd_deprn,deprn_reserve
from fa_mc_deprn_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_reclassed_asset_id
and period_counter = l_mem_period_counter
and deprn_source_code = 'BOOKS'
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
fa_debug_pkg.add(l_calling_fn, 'Insert new row for proceessing','Start', p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Insert row',
l_asset_hdr_rec.book_type_code||':'||p_reclassed_asset_id||':'||p_asset_hdr_rec.asset_id, p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Insert row',
l_mem_period_counter||':'||g_mem_ytd_deprn||':'||g_mem_deprn_reserve, p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Insert row',
g_mem_bonus_ytd_deprn||':'||g_mem_bonus_deprn_reserve||':'||g_mem_eofy_reserve, p_log_level_rec => p_log_level_rec);
update fa_books_summary
set group_Asset_id = p_asset_hdr_rec.asset_id
where asset_id=p_reclassed_asset_id
and period_counter >= l_trx_period_rec.period_counter;
insert into fa_books_summary(book_type_code,
asset_id,
group_asset_id,
period_counter,
cost,
salvage_value,
recoverable_cost,
adjusted_cost,
adjusted_recoverable_cost,
deprn_amount,
ytd_deprn,
deprn_reserve,
bonus_ytd_deprn,
bonus_deprn_reserve,
ytd_impairment,
impairment_reserve,
eofy_reserve,
creation_date,
created_by,
last_update_date,
last_updated_by)
values (l_asset_hdr_rec.book_type_code,
p_reclassed_asset_id,
p_asset_hdr_rec.asset_id,
l_mem_period_counter,
0,0,0,0,0,0,
nvl(g_mem_ytd_deprn,0),
nvl(g_mem_deprn_reserve,0),
nvl(g_mem_bonus_ytd_deprn,0),
nvl(g_mem_bonus_deprn_reserve,0),
nvl(g_mem_ytd_impairment,0),
nvl(g_mem_impairment_reserve,0),
nvl(g_mem_eofy_reserve,0),
p_trans_rec.who_info.creation_date,
p_trans_rec.who_info.created_by,
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by);
update fa_mc_books_summary
set group_Asset_id = p_asset_hdr_rec.asset_id
where asset_id=p_reclassed_asset_id
and period_counter >= l_trx_period_rec.period_counter
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
insert into fa_mc_books_summary(set_of_books_id,
book_type_code,
asset_id,
group_asset_id,
period_counter,
cost,
salvage_value,
recoverable_cost,
adjusted_cost,
adjusted_recoverable_cost,
deprn_amount,
ytd_deprn,
deprn_reserve,
bonus_ytd_deprn,
bonus_deprn_reserve,
ytd_impairment,
impairment_reserve,
eofy_reserve,
creation_date,
created_by,
last_update_date,
last_updated_by)
values (l_asset_hdr_rec.set_of_books_id,
l_asset_hdr_rec.book_type_code,
p_reclassed_asset_id,
p_asset_hdr_rec.asset_id,
l_mem_period_counter,
0,0,0,0,0,0,
nvl(g_mem_ytd_deprn,0),
nvl(g_mem_deprn_reserve,0),
nvl(g_mem_bonus_ytd_deprn,0),
nvl(g_mem_bonus_deprn_reserve,0),
nvl(g_mem_ytd_impairment,0),
nvl(g_mem_impairment_reserve,0),
nvl(g_mem_eofy_reserve,0),
p_trans_rec.who_info.creation_date,
p_trans_rec.who_info.created_by,
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by);
fa_debug_pkg.add(l_calling_fn, 'Inserted rows','Without Error', p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Insert new row for proceessing','Error', p_log_level_rec => p_log_level_rec);
elsif (nvl(fa_cache_pkg.fazcdrd_record.period_update_flag,'N') = 'Y') then -- Added for bug 8425794 / 8244128
fa_amort_pvt.t_reset_adjusted_cost_flag(i) := 'Y';
nvl(fa_cache_pkg.fazcdrd_record.period_update_flag,'N') = 'Y' ) ) then
--tk_util.debug('fa_amort_pvt.t_expense_adjustment_amount('||to_char(i)||'): '||to_char(fa_amort_pvt.t_expense_adjustment_amount(i)));
nvl(fa_cache_pkg.fazcdrd_record.period_update_flag,'N') = 'Y' then
if not fa_cache_pkg.fazccp(fa_cache_pkg.fazcbc_record.prorate_calendar,
fa_cache_pkg.fazcbc_record.fiscal_year_name,
l_dpr_in.prorate_jdate,
g_temp_number,
l_dpr_in.y_begin,
g_temp_integer, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'fa_cache_pkg.fazccp', p_log_level_rec => p_log_level_rec);
l_dpr_in.update_override_status :=
((l_rate_source_rule = fa_std_types.FAD_RSR_FORMULA)
OR (((l_rate_source_rule = fa_std_types.FAD_RSR_CALC)
OR (l_rate_source_rule = fa_std_types.FAD_RSR_TABLE))
AND (l_deprn_basis_rule = fa_std_types.FAD_DBR_COST)));
if l_dpr_in.update_override_status then
p_trans_rec.deprn_override_flag := l_dpr_out.deprn_override_flag;
fa_track_member_pvt.p_track_member_table.delete;
fa_track_member_pvt.p_track_mem_index_table.delete;
'deleted', p_log_level_rec => p_log_level_rec);
nvl(fa_cache_pkg.fazcdrd_record.period_update_flag,'N') = 'Y' or
l_asset_fin_rec_new.adjusted_cost is null
) then
l_asset_fin_rec_new.adjusted_cost := l_asset_fin_rec_new.recoverable_cost;
nvl(fa_cache_pkg.fazcdrd_record.period_update_flag,'N') = 'Y' then
if (not FA_CALC_DEPRN_BASIS1_PKG.CALL_DEPRN_BASIS
(p_event_type => 'AMORT_ADJ',
p_asset_fin_rec_new => l_asset_fin_rec_new,
p_asset_fin_rec_old => l_asset_fin_rec_old,
p_asset_hdr_rec => p_asset_hdr_rec,
p_asset_type_rec => p_asset_type_rec,
p_asset_deprn_rec => l_asset_deprn_rec,
p_trans_rec => p_trans_rec,
p_trans_rec_adj => l_trans_rec,
p_period_rec => l_period_rec,
p_current_total_rsv => l_asset_deprn_rec.deprn_reserve,
p_current_rsv => l_asset_deprn_rec.deprn_reserve -
nvl(l_asset_deprn_rec.bonus_deprn_reserve, 0) - nvl(l_asset_deprn_rec.impairment_reserve, 0),
p_current_total_ytd => l_asset_deprn_rec.ytd_deprn,
p_adj_reserve => p_asset_deprn_rec_adj.deprn_reserve,
p_reserve_retired => l_reserve_retired,
p_hyp_basis => l_asset_fin_rec_new.adjusted_cost,
p_hyp_total_rsv => l_asset_deprn_rec_raf.deprn_reserve,
p_hyp_rsv => l_asset_deprn_rec_raf.deprn_reserve -
nvl(l_asset_deprn_rec_raf.bonus_deprn_reserve, 0) - nvl(l_asset_deprn_rec_raf.impairment_reserve,0),
p_eofy_recoverable_cost => l_eofy_rec_cost,
p_eop_recoverable_cost => l_eop_rec_cost,
p_eofy_salvage_value => l_eofy_sal_val,
p_eop_salvage_value => l_eop_sal_val,
p_mrc_sob_type_code => p_mrc_sob_type_code,
p_used_by_adjustment => 'ADJUSTMENT',
px_new_adjusted_cost => l_asset_fin_rec_new.adjusted_cost,
px_new_raf => l_asset_fin_rec_new.rate_adjustment_factor,
px_new_formula_factor => l_asset_fin_rec_new.formula_factor,
p_log_level_rec => p_log_level_rec)) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_CALC_DEPRN_BASIS1_PKG.CALL_DEPRN_BASIS', p_log_level_rec => p_log_level_rec);
l_dpr_in.update_override_status := TRUE;
if not FA_TRACK_MEMBER_PVT.update_member_books(p_trans_rec=> p_trans_rec,
p_asset_hdr_rec => p_asset_hdr_rec,
p_dpr_in => l_dpr_in,
p_mrc_sob_type_code => p_mrc_sob_type_code, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_TRACK_MEMBER_PVT.update_member_books', p_log_level_rec => p_log_level_rec);
fa_track_member_pvt.p_track_member_eofy_table.delete;
'deleted', p_log_level_rec => p_log_level_rec);
if not FA_TRACK_MEMBER_PVT.create_update_bs_table(p_trans_rec => p_trans_rec,
p_book_type_code => p_asset_hdr_rec.book_type_code,
p_group_asset_id => p_asset_hdr_rec.asset_id,
p_mrc_sob_type_code => p_mrc_sob_type_code, --Bug 8941132
p_sob_id => p_asset_hdr_rec.set_of_books_id, --Bug 8941132
p_calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_TRACK_MEMBER_PVT.create_update_bs_table', p_log_level_rec => p_log_level_rec);
if (p_update_books_summary) then
--
-- Update FA_BOOKS_SUMMARY
--
--tk_util.debug('period# : cost: chcost: msal: exp: ytd: rsv: rsv');
UPDATE FA_MC_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = fa_amort_pvt.t_reset_adjusted_cost_flag(i)
, CHANGE_IN_COST = fa_amort_pvt.t_change_in_cost(i)
, CHANGE_IN_CIP_COST = fa_amort_pvt.t_change_in_cip_cost(i)
, COST = fa_amort_pvt.t_cost(i)
, CIP_COST = fa_amort_pvt.t_cip_cost(i)
, SALVAGE_TYPE = fa_amort_pvt.t_salvage_type(i)
, PERCENT_SALVAGE_VALUE = fa_amort_pvt.t_percent_salvage_value(i)
, SALVAGE_VALUE = fa_amort_pvt.t_salvage_value(i)
, MEMBER_SALVAGE_VALUE = fa_amort_pvt.t_member_salvage_value(i)
, RECOVERABLE_COST = fa_amort_pvt.t_recoverable_cost(i)
, DEPRN_LIMIT_TYPE = fa_amort_pvt.t_deprn_limit_type(i)
, ALLOWED_DEPRN_LIMIT = fa_amort_pvt.t_allowed_deprn_limit(i)
, ALLOWED_DEPRN_LIMIT_AMOUNT = fa_amort_pvt.t_allowed_deprn_limit_amount(i)
, MEMBER_DEPRN_LIMIT_AMOUNT = fa_amort_pvt.t_member_deprn_limit_amount(i)
, ADJUSTED_RECOVERABLE_COST = fa_amort_pvt.t_adjusted_recoverable_cost(i)
, ADJUSTED_COST = fa_amort_pvt.t_adjusted_cost(i)
, DEPRECIATE_FLAG = fa_amort_pvt.t_depreciate_flag(i)
, DEPRN_METHOD_CODE = fa_amort_pvt.t_deprn_method_code(i)
, LIFE_IN_MONTHS = fa_amort_pvt.t_life_in_months(i)
, RATE_ADJUSTMENT_FACTOR = fa_amort_pvt.t_rate_adjustment_factor(i)
, ADJUSTED_RATE = fa_amort_pvt.t_adjusted_rate(i)
, BONUS_RULE = fa_amort_pvt.t_bonus_rule(i)
, ADJUSTED_CAPACITY = fa_amort_pvt.t_adjusted_capacity(i)
, PRODUCTION_CAPACITY = fa_amort_pvt.t_production_capacity(i)
, UNIT_OF_MEASURE = fa_amort_pvt.t_unit_of_measure(i)
, REMAINING_LIFE1 = fa_amort_pvt.t_remaining_life1(i)
, REMAINING_LIFE2 = fa_amort_pvt.t_remaining_life2(i)
, FORMULA_FACTOR = fa_amort_pvt.t_formula_factor(i)
, UNREVALUED_COST = fa_amort_pvt.t_unrevalued_cost(i)
, REVAL_AMORTIZATION_BASIS = fa_amort_pvt.t_reval_amortization_basis(i)
, REVAL_CEILING = fa_amort_pvt.t_reval_ceiling(i)
, CEILING_NAME = fa_amort_pvt.t_ceiling_name(i)
, EOFY_ADJ_COST = fa_amort_pvt.t_eofy_adj_cost(i)
, EOFY_FORMULA_FACTOR = fa_amort_pvt.t_eofy_formula_factor(i)
, EOFY_RESERVE = fa_amort_pvt.t_eofy_reserve(i)
, EOP_ADJ_COST = fa_amort_pvt.t_eop_adj_cost(i)
, EOP_FORMULA_FACTOR = fa_amort_pvt.t_eop_formula_factor(i)
, SHORT_FISCAL_YEAR_FLAG = fa_amort_pvt.t_short_fiscal_year_flag(i)
, GROUP_ASSET_ID = fa_amort_pvt.t_group_asset_id(i)
, SUPER_GROUP_ID = fa_amort_pvt.t_super_group_id(i)
, OVER_DEPRECIATE_OPTION = fa_amort_pvt.t_over_depreciate_option(i)
, DEPRN_AMOUNT = fa_amort_pvt.t_deprn_amount(i)
, YTD_DEPRN = fa_amort_pvt.t_ytd_deprn(i)
, DEPRN_RESERVE = fa_amort_pvt.t_deprn_reserve(i)
, BONUS_DEPRN_AMOUNT = fa_amort_pvt.t_bonus_deprn_amount(i)
, BONUS_YTD_DEPRN = fa_amort_pvt.t_bonus_ytd_deprn(i)
, BONUS_DEPRN_RESERVE = fa_amort_pvt.t_bonus_deprn_reserve(i)
, BONUS_RATE = fa_amort_pvt.t_bonus_rate(i)
, IMPAIRMENT_AMOUNT = fa_amort_pvt.t_impairment_amount(i)
, YTD_IMPAIRMENT = fa_amort_pvt.t_ytd_impairment(i)
, impairment_reserve = fa_amort_pvt.t_impairment_reserve(i)
, LTD_PRODUCTION = fa_amort_pvt.t_ltd_production(i)
, YTD_PRODUCTION = fa_amort_pvt.t_ytd_production(i)
, PRODUCTION = fa_amort_pvt.t_production(i)
, REVAL_AMORTIZATION = fa_amort_pvt.t_reval_amortization(i)
, REVAL_DEPRN_EXPENSE = fa_amort_pvt.t_reval_deprn_expense(i)
, REVAL_RESERVE = fa_amort_pvt.t_reval_reserve(i)
, YTD_REVAL_DEPRN_EXPENSE = fa_amort_pvt.t_ytd_reval_deprn_expense(i)
, DEPRN_OVERRIDE_FLAG = fa_amort_pvt.t_deprn_override_flag(i)
, SYSTEM_DEPRN_AMOUNT = fa_amort_pvt.t_system_deprn_amount(i)
, SYSTEM_BONUS_DEPRN_AMOUNT = fa_amort_pvt.t_system_bonus_deprn_amount(i)
, YTD_PROCEEDS_OF_SALE = fa_amort_pvt.t_ytd_proceeds_of_sale(i)
, LTD_PROCEEDS_OF_SALE = fa_amort_pvt.t_ltd_proceeds_of_sale(i)
, YTD_COST_OF_REMOVAL = fa_amort_pvt.t_ytd_cost_of_removal(i)
, LTD_COST_OF_REMOVAL = fa_amort_pvt.t_ltd_cost_of_removal(i)
, DEPRN_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_deprn_adjustment_amount(i)
, EXPENSE_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_expense_adjustment_amount(i)
, RESERVE_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_reserve_adjustment_amount(i)
, CHANGE_IN_EOFY_RESERVE = fa_amort_pvt.t_change_in_eofy_reserve(i)
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = fa_amort_pvt.t_period_counter(i)
AND SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET RESET_ADJUSTED_COST_FLAG = fa_amort_pvt.t_reset_adjusted_cost_flag(i)
, CHANGE_IN_COST = fa_amort_pvt.t_change_in_cost(i)
, CHANGE_IN_CIP_COST = fa_amort_pvt.t_change_in_cip_cost(i)
, COST = fa_amort_pvt.t_cost(i)
, CIP_COST = fa_amort_pvt.t_cip_cost(i)
, SALVAGE_TYPE = fa_amort_pvt.t_salvage_type(i)
, PERCENT_SALVAGE_VALUE = fa_amort_pvt.t_percent_salvage_value(i)
, SALVAGE_VALUE = fa_amort_pvt.t_salvage_value(i)
, MEMBER_SALVAGE_VALUE = fa_amort_pvt.t_member_salvage_value(i)
, RECOVERABLE_COST = fa_amort_pvt.t_recoverable_cost(i)
, DEPRN_LIMIT_TYPE = fa_amort_pvt.t_deprn_limit_type(i)
, ALLOWED_DEPRN_LIMIT = fa_amort_pvt.t_allowed_deprn_limit(i)
, ALLOWED_DEPRN_LIMIT_AMOUNT = fa_amort_pvt.t_allowed_deprn_limit_amount(i)
, MEMBER_DEPRN_LIMIT_AMOUNT = fa_amort_pvt.t_member_deprn_limit_amount(i)
, ADJUSTED_RECOVERABLE_COST = fa_amort_pvt.t_adjusted_recoverable_cost(i)
, ADJUSTED_COST = fa_amort_pvt.t_adjusted_cost(i)
, DEPRECIATE_FLAG = fa_amort_pvt.t_depreciate_flag(i)
, DEPRN_METHOD_CODE = fa_amort_pvt.t_deprn_method_code(i)
, LIFE_IN_MONTHS = fa_amort_pvt.t_life_in_months(i)
, RATE_ADJUSTMENT_FACTOR = fa_amort_pvt.t_rate_adjustment_factor(i)
, ADJUSTED_RATE = fa_amort_pvt.t_adjusted_rate(i)
, BONUS_RULE = fa_amort_pvt.t_bonus_rule(i)
, ADJUSTED_CAPACITY = fa_amort_pvt.t_adjusted_capacity(i)
, PRODUCTION_CAPACITY = fa_amort_pvt.t_production_capacity(i)
, UNIT_OF_MEASURE = fa_amort_pvt.t_unit_of_measure(i)
, REMAINING_LIFE1 = fa_amort_pvt.t_remaining_life1(i)
, REMAINING_LIFE2 = fa_amort_pvt.t_remaining_life2(i)
, FORMULA_FACTOR = fa_amort_pvt.t_formula_factor(i)
, UNREVALUED_COST = fa_amort_pvt.t_unrevalued_cost(i)
, REVAL_AMORTIZATION_BASIS = fa_amort_pvt.t_reval_amortization_basis(i)
, REVAL_CEILING = fa_amort_pvt.t_reval_ceiling(i)
, CEILING_NAME = fa_amort_pvt.t_ceiling_name(i)
, EOFY_ADJ_COST = fa_amort_pvt.t_eofy_adj_cost(i)
, EOFY_FORMULA_FACTOR = fa_amort_pvt.t_eofy_formula_factor(i)
, EOFY_RESERVE = fa_amort_pvt.t_eofy_reserve(i)
, EOP_ADJ_COST = fa_amort_pvt.t_eop_adj_cost(i)
, EOP_FORMULA_FACTOR = fa_amort_pvt.t_eop_formula_factor(i)
, SHORT_FISCAL_YEAR_FLAG = fa_amort_pvt.t_short_fiscal_year_flag(i)
, GROUP_ASSET_ID = fa_amort_pvt.t_group_asset_id(i)
, SUPER_GROUP_ID = fa_amort_pvt.t_super_group_id(i)
, OVER_DEPRECIATE_OPTION = fa_amort_pvt.t_over_depreciate_option(i)
, DEPRN_AMOUNT = fa_amort_pvt.t_deprn_amount(i)
, YTD_DEPRN = fa_amort_pvt.t_ytd_deprn(i)
, DEPRN_RESERVE = fa_amort_pvt.t_deprn_reserve(i)
, BONUS_DEPRN_AMOUNT = fa_amort_pvt.t_bonus_deprn_amount(i)
, BONUS_YTD_DEPRN = fa_amort_pvt.t_bonus_ytd_deprn(i)
, BONUS_DEPRN_RESERVE = fa_amort_pvt.t_bonus_deprn_reserve(i)
, BONUS_RATE = fa_amort_pvt.t_bonus_rate(i)
, IMPAIRMENT_AMOUNT = fa_amort_pvt.t_impairment_amount(i)
, YTD_IMPAIRMENT = fa_amort_pvt.t_ytd_impairment(i)
, impairment_reserve = fa_amort_pvt.t_impairment_reserve(i)
, LTD_PRODUCTION = fa_amort_pvt.t_ltd_production(i)
, YTD_PRODUCTION = fa_amort_pvt.t_ytd_production(i)
, PRODUCTION = fa_amort_pvt.t_production(i)
, REVAL_AMORTIZATION = fa_amort_pvt.t_reval_amortization(i)
, REVAL_DEPRN_EXPENSE = fa_amort_pvt.t_reval_deprn_expense(i)
, REVAL_RESERVE = fa_amort_pvt.t_reval_reserve(i)
, YTD_REVAL_DEPRN_EXPENSE = fa_amort_pvt.t_ytd_reval_deprn_expense(i)
, DEPRN_OVERRIDE_FLAG = fa_amort_pvt.t_deprn_override_flag(i)
, SYSTEM_DEPRN_AMOUNT = fa_amort_pvt.t_system_deprn_amount(i)
, SYSTEM_BONUS_DEPRN_AMOUNT = fa_amort_pvt.t_system_bonus_deprn_amount(i)
, YTD_PROCEEDS_OF_SALE = fa_amort_pvt.t_ytd_proceeds_of_sale(i)
, LTD_PROCEEDS_OF_SALE = fa_amort_pvt.t_ltd_proceeds_of_sale(i)
, YTD_COST_OF_REMOVAL = fa_amort_pvt.t_ytd_cost_of_removal(i)
, LTD_COST_OF_REMOVAL = fa_amort_pvt.t_ltd_cost_of_removal(i)
, DEPRN_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_deprn_adjustment_amount(i)
, EXPENSE_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_expense_adjustment_amount(i)
, RESERVE_ADJUSTMENT_AMOUNT = fa_amort_pvt.t_reserve_adjustment_amount(i)
, CHANGE_IN_EOFY_RESERVE = fa_amort_pvt.t_change_in_eofy_reserve(i)
, 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 ASSET_ID = p_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = fa_amort_pvt.t_period_counter(i);
end if; -- (p_update_books_summary)
p_update_books_summary BOOLEAN default FALSE,
p_proceeds_of_sale NUMBER default 0,
p_cost_of_removal NUMBER default 0,
x_deprn_exp OUT NOCOPY NUMBER,
x_bonus_deprn_exp OUT NOCOPY NUMBER,
x_impairment_exp OUT NOCOPY NUMBER,
x_deprn_rsv OUT NOCOPY NUMBER
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
l_calling_fn varchar2(50) := 'newFA_AMORT_PVT.faxama';
select inbk.transaction_header_id_in
from fa_books inbk
, fa_books outbk
where inbk.transaction_header_id_in = px_trans_rec.member_transaction_header_id
and outbk.asset_id = inbk.asset_id
and outbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.transaction_header_id_out = px_trans_rec.member_transaction_header_id
and inbk.cost = outbk.cost
and nvl(inbk.salvage_value, 0) = nvl(outbk.salvage_value, 0)
and nvl(inbk.allowed_deprn_limit_amount, 0) = nvl(outbk.allowed_deprn_limit_amount, 0)
and inbk.date_placed_in_service <> outbk.date_placed_in_service;
select runid,
run_date,
run_comment
from plsql_profiler_runs;
p_update_books_summary => p_update_books_summary,
p_mrc_sob_type_code => p_mrc_sob_type_code,
p_calling_fn => l_calling_fn,
p_log_level_rec => p_log_level_rec)) then
raise calc_err;
select
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0)
from fa_adjustments adj
where adj.transaction_header_id = p_trans_rec.transaction_header_id
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.book_type_code = p_asset_hdr_rec.book_type_code;
select
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0)
from fa_mc_adjustments adj
where adj.transaction_header_id = p_trans_rec.transaction_header_id
and adj.asset_id = p_asset_hdr_rec.asset_id
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select 'Y'
from fa_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter <= p_period_rec.period_counter;
select ds.deprn_reserve - ds.ytd_deprn
, ds.bonus_deprn_reserve - ds.bonus_ytd_deprn
, ds.impairment_reserve - ds.ytd_impairment
, ds.ltd_production - ds.ytd_production
, ds.reval_reserve - ds.ytd_reval_deprn_expense
from fa_fiscal_year fy
, fa_deprn_summary ds
, fa_deprn_periods dp
where ds.asset_id = p_asset_hdr_rec.asset_id
and ds.book_type_code = p_asset_hdr_rec.book_type_code
and ds.deprn_source_code = 'BOOKS'
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.period_counter = ds.period_counter + 1 --Bug6987743: add one
and fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
and fy.fiscal_year = (dp.period_counter - dp.period_num)/fa_cache_pkg.fazcct_record.number_per_fiscal_year
and fy.fiscal_year = (p_period_rec.period_counter + 1 - p_period_rec.period_num)/
fa_cache_pkg.fazcct_record.number_per_fiscal_year;
select ds.deprn_reserve
, ds.bonus_deprn_reserve
, ds.impairment_reserve
, ds.ltd_production
, ds.reval_reserve
from fa_deprn_summary ds, fa_deprn_periods dp
,
(select (fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM)
add_amort_period
from fa_calendar_periods cp,
fa_book_controls bc,
fa_fiscal_year fy,
fa_calendar_types ct,
fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and bc.book_type_code = th.book_type_code
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and th.amortization_start_date between cp.start_date and cp.end_date
and th.transaction_type_code = 'ADDITION'
) ilv
where ds.book_type_code = p_asset_hdr_rec.book_type_code
and ds.asset_id = p_asset_hdr_rec.asset_id
and ds.deprn_source_code = 'BOOKS'
and dp.period_counter = ds.period_counter+1
and dp.book_type_code = ds.book_type_code
and nvl(add_amort_period,ds.period_counter) <= p_period_rec.period_counter+1;
SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'BONUS RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'BONUS EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'IMPAIR RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'IMPAIR EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'DEPRN ADJUST',
DECODE(fa_cache_pkg.fazcbc_record.book_class,'TAX',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL AMORT',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.SOURCE_TYPE_CODE,
'REVALUATION',
DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1*ADJ.ADJUSTMENT_AMOUNT)))), 0),
NVL(SUM(DECODE(TH.TRANSACTION_KEY,
'UE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
'UA',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)
)),
0)
FROM FA_ADJUSTMENTS ADJ,
FA_TRANSACTION_HEADERS TH
WHERE TH.ASSET_ID = p_asset_hdr_rec.asset_id
AND TH.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TH.TRANSACTION_HEADER_ID <> p_trans_rec.transaction_header_id
AND TH.TRANSACTION_DATE_ENTERED <= p_trans_rec.transaction_date_entered
AND TH.DATE_EFFECTIVE < p_trans_rec.who_info.creation_date
AND TH.DATE_EFFECTIVE BETWEEN nvl(p_period_rec.period_open_date, TH.DATE_EFFECTIVE)
AND nvl(p_period_rec.period_close_date, TH.DATE_EFFECTIVE)
AND TH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID
AND ADJ.ASSET_ID = p_asset_hdr_rec.asset_id
AND ADJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
--
-- Bug3387996: next condition is nevessary to get adj amount only for this period
-- Bug4741374: period_counter+1 is required to get the rsv from period of addition
AND ADJ.PERIOD_COUNTER_CREATED = p_period_rec.period_counter + 1
AND ADJ.ADJUSTMENT_TYPE in ('RESERVE', 'EXPENSE')
AND not exists ( select 1 from
fa_retirements ret,
fa_conventions con
where th.transaction_type_code in (G_TRX_TYPE_FUL_RET, G_TRX_TYPE_PAR_RET)
and ret.transaction_header_id_in = th.transaction_header_id
and ret.asset_id = p_asset_hdr_rec.asset_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.RETIREMENT_PRORATE_CONVENTION = con.PRORATE_CONVENTION_CODE
and ret.date_retired between con.start_date and con.end_date
and con.prorate_date > p_trans_rec.transaction_date_entered); -- Bug6899375 Added the not exists condition
SELECT NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'BONUS RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'BONUS EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'IMPAIR RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'IMPAIR EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'DEPRN ADJUST',
DECODE(fa_cache_pkg.fazcbc_record.book_class,'TAX',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL AMORT',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.ADJUSTMENT_TYPE,
'REVAL RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT))),
0),
NVL(SUM(DECODE(ADJ.SOURCE_TYPE_CODE,
'REVALUATION',
DECODE(ADJ.ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1*ADJ.ADJUSTMENT_AMOUNT)))), 0),
NVL(SUM(DECODE(TH.TRANSACTION_KEY,
'UE',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT),
'UA',
DECODE(ADJ.DEBIT_CREDIT_FLAG,
'DR', ADJ.ADJUSTMENT_AMOUNT,
'CR', -1 * ADJ.ADJUSTMENT_AMOUNT)
)),
0)
FROM FA_MC_ADJUSTMENTS ADJ,
FA_TRANSACTION_HEADERS TH
WHERE TH.ASSET_ID = p_asset_hdr_rec.asset_id
AND TH.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TH.TRANSACTION_HEADER_ID <> p_trans_rec.transaction_header_id
AND TH.TRANSACTION_DATE_ENTERED <= p_trans_rec.transaction_date_entered
AND TH.DATE_EFFECTIVE < p_trans_rec.who_info.creation_date
AND TH.DATE_EFFECTIVE BETWEEN nvl(p_period_rec.period_open_date, TH.DATE_EFFECTIVE)
AND nvl(p_period_rec.period_close_date, TH.DATE_EFFECTIVE)
AND TH.TRANSACTION_HEADER_ID = ADJ.TRANSACTION_HEADER_ID
AND ADJ.ASSET_ID = p_asset_hdr_rec.asset_id
AND ADJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
--
-- Bug3387996: next condition is nevessary to get adj amount only for this period
-- Bug4741374: period_counter+1 is required to get the rsv from period of addition
AND ADJ.PERIOD_COUNTER_CREATED = p_period_rec.period_counter + 1
AND ADJ.ADJUSTMENT_TYPE in ('RESERVE', 'EXPENSE')
AND ADJ.SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id
AND not exists ( select 1 from
fa_retirements ret,
fa_conventions con
where th.transaction_type_code in (G_TRX_TYPE_FUL_RET, G_TRX_TYPE_PAR_RET)
and ret.transaction_header_id_in = th.transaction_header_id
and ret.asset_id = p_asset_hdr_rec.asset_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.RETIREMENT_PRORATE_CONVENTION = con.PRORATE_CONVENTION_CODE
and ret.date_retired between con.start_date and con.end_date
and con.prorate_date > p_trans_rec.transaction_date_entered); -- Bug6899375 Added the not exists condition
select th.transaction_header_id transaction_header_id,
nvl(th.amortization_start_date, th.transaction_date_entered) transaction_date_entered,
th.date_effective date_effective,
th.transaction_type_code transaction_type_code
from
fa_transaction_headers th,
fa_deprn_periods dp,
fa_book_controls bc,
fa_fiscal_year fy
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.fiscal_year_name = fy.fiscal_year_name
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.fiscal_year = fy.fiscal_year
and dp.calendar_period_open_date = fy.start_date
and p_trans_rec.transaction_date_entered
between fy.start_date and end_date
and th.date_effective >= dp.period_open_date
and th.transaction_date_entered < dp.calendar_period_open_date
and th.transaction_header_id < p_trans_rec.transaction_header_id
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID)
and not exists (select 1
from fa_deprn_summary ds
, fa_deprn_periods dp2
where ds.asset_id = p_asset_hdr_rec.asset_id
and ds.book_type_code = p_asset_hdr_rec.book_type_code
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.fiscal_year = fy.fiscal_year
and ds.period_counter = dp2.period_counter -1
and ds.deprn_source_code = 'BOOKS'
and ds.deprn_reserve <> 0
and th.date_effective between dp2.period_open_date and dp2.period_close_date
and th.transaction_type_code in (G_TRX_TYPE_ADD, G_TRX_TYPE_ADD_VOID))
;
select nvl(sum(ret.reserve_retired), 0),
nvl(sum(ret.eofy_reserve), 0)
from fa_retirements ret,
fa_transaction_headers mth,
fa_transaction_headers gth,
fa_book_controls bc,
fa_fiscal_year fy
where gth.asset_id = p_asset_hdr_rec.asset_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.fiscal_year_name = fy.fiscal_year_name
and ret.date_retired between fy.start_date
and p_trans_rec.transaction_date_entered
and gth.member_transaction_header_id = mth.transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and mth.transaction_header_id = ret.transaction_header_id_in
and mth.asset_id = ret.asset_id
and gth.member_transaction_header_id = ret.transaction_header_id_in
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out is null
and fy.start_date =
(select fy.start_date
from fa_fiscal_year fy,
fa_book_controls bc
where bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.fiscal_year_name = fy.fiscal_year_name
and fy.start_date <= p_trans_rec.transaction_date_entered
and fy.end_date >= p_trans_rec.transaction_date_entered
);
select nvl(sum(ret.reserve_retired), 0),
nvl(sum(ret.eofy_reserve), 0)
from fa_mc_retirements ret,
fa_transaction_headers mth,
fa_transaction_headers gth,
fa_mc_book_controls mbc,
fa_book_controls bc,
fa_fiscal_year fy
where gth.asset_id = p_asset_hdr_rec.asset_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and mbc.book_type_code = p_asset_hdr_rec.book_type_code
and mbc.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and bc.fiscal_year_name = fy.fiscal_year_name
and ret.date_retired between fy.start_date
and p_trans_rec.transaction_date_entered
and gth.member_transaction_header_id = mth.transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and mth.transaction_header_id = ret.transaction_header_id_in
and mth.asset_id = ret.asset_id
and gth.member_transaction_header_id = ret.transaction_header_id_in
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out is null
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and fy.start_date =
(select fy.start_date
from fa_fiscal_year fy,
fa_mc_book_controls bc
where bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and bc.fiscal_year_name = fy.fiscal_year_name
and fy.start_date <= p_trans_rec.transaction_date_entered
and fy.end_date >= p_trans_rec.transaction_date_entered
);
select nvl(sum(ret.reserve_retired), 0),
nvl(sum(ret.eofy_reserve), 0)
from fa_retirements ret,
fa_transaction_headers th,
fa_book_controls bc,
fa_fiscal_year fy
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.fiscal_year_name = fy.fiscal_year_name
and ret.date_retired between fy.start_date
and p_trans_rec.transaction_date_entered
and th.transaction_header_id = ret.transaction_header_id_in
and th.asset_id = ret.asset_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id_out is null;
select period_counter - 1
, period_open_date
, period_close_date
, period_num
from fa_deprn_periods
where book_type_code = p_asset_hdr_rec.book_type_code
and p_trans_rec.transaction_date_entered between
calendar_period_open_date and calendar_period_close_date;
select th.transaction_header_id transaction_header_id,
nvl(th.amortization_start_date, th.transaction_date_entered) transaction_date_entered,
th.date_effective date_effective,
th.transaction_type_code transaction_type_code
from fa_transaction_headers th
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_header_id =
-- Get the latest trasnaction of reclassed member asset on group asset
(select max(th.transaction_header_id)
from fa_transaction_headers th
where th.asset_id =p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID)
and th.transaction_header_id <
-- Get first transaction of reclassed member asset on group asset
(select min(th.transaction_header_id)
from fa_transaction_headers th
where th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.member_transaction_header_id in
-- Get reclassed member's all transaction headers
(select th.transaction_header_id
from fa_transaction_headers th
where th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id in
-- Get reclassed member's asset_id
(select th.asset_id
from fa_transaction_headers th
where th.book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id in
-- Get reclassed transaction after this transaction's fiscal year
(select th.member_transaction_header_id
from fa_transaction_headers th,
fa_deprn_periods dp,
fa_fiscal_year fy,
fa_book_controls bc
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.book_type_code = bc.book_type_code
and bc.fiscal_year_name = fy.fiscal_year_name
and p_trans_rec.transaction_date_entered
between fy.start_date and fy.end_date
and dp.fiscal_year= fy.fiscal_year
and dp.period_num =1
and th.date_effective >= dp.period_open_date
and th.trx_reference_id is not null
and th.member_transaction_header_id is not null
and th.transaction_header_id < p_trans_rec.transaction_header_id
)))));
select nvl(bk.eofy_reserve,0)
from FA_BOOKS bk
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;
select decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.cost - nvl(outbk.cost, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.salvage_value - nvl(outbk.salvage_value, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) -
nvl(outbk.production_capacity, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(nvl(inbk.allowed_deprn_limit_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0))
from fa_transaction_headers th,
fa_books inbk,
fa_books outbk
where inbk.asset_id = p_asset_hdr_rec.asset_id
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and outbk.asset_id(+) = p_asset_hdr_rec.asset_id
and outbk.book_type_code(+) = p_asset_hdr_rec.book_type_code
and inbk.transaction_header_id_in = th.transaction_header_id
and outbk.transaction_header_id_out(+) = th.transaction_header_id
and th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID)
and nvl(th.amortization_start_date,
th.transaction_date_entered) <= p_transaction_date_entered
and not exists(select 'Exclude This Retirement'
from fa_retirements ret,
fa_transaction_headers reith
where ret.transaction_header_id_in = th.transaction_header_id
and ret.transaction_header_id_out = reith.transaction_header_id
and nvl(reith.amortization_start_date,
reith.transaction_date_entered) <= p_transaction_date_entered)
order by transaction_date_entered;
select decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.cost - nvl(outbk.cost, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.salvage_value - nvl(outbk.salvage_value, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) -
nvl(outbk.production_capacity, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0)))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(nvl(inbk.allowed_deprn_limit_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0))
, decode(p_reclass_src_dest, 'SOURCE', -1, 1) * sum(nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0))
from fa_transaction_headers th,
fa_mc_books inbk,
fa_mc_books outbk
where inbk.asset_id = p_asset_hdr_rec.asset_id
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and inbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and outbk.asset_id(+) = p_asset_hdr_rec.asset_id
and outbk.book_type_code(+) = p_asset_hdr_rec.book_type_code
and outbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and inbk.transaction_header_id_in = th.transaction_header_id
and outbk.transaction_header_id_out(+) = th.transaction_header_id
and th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID)
and nvl(th.amortization_start_date,
th.transaction_date_entered) <= p_transaction_date_entered
and not exists(select 'Exclude This Retirement'
from fa_mc_retirements ret,
fa_transaction_headers reith
where ret.transaction_header_id_in = th.transaction_header_id
and ret.transaction_header_id_out = reith.transaction_header_id
and ret.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and nvl(reith.amortization_start_date,
reith.transaction_date_entered) <= p_transaction_date_entered)
order by transaction_date_entered;
select date_placed_in_service
, deprn_start_date
, deprn_method_code
, life_in_months
, rate_adjustment_factor
, adjusted_cost
, cost
, original_cost
, salvage_value
, prorate_convention_code
, prorate_date
, cost_change_flag
, adjustment_required_status
, capitalize_flag
, retirement_pending_flag
, depreciate_flag
, itc_amount_id
, itc_amount
, retirement_id
, tax_request_id
, itc_basis
, basic_rate
, adjusted_rate
, bonus_rule
, ceiling_name
, recoverable_cost
, adjusted_capacity
, fully_rsvd_revals_counter
, idled_flag
, period_counter_capitalized
, period_counter_fully_reserved
, period_counter_fully_retired
, production_capacity
, reval_amortization_basis
, reval_ceiling
, unit_of_measure
, unrevalued_cost
, annual_deprn_rounding_flag
, percent_salvage_value
, allowed_deprn_limit
, allowed_deprn_limit_amount
, period_counter_life_complete
, adjusted_recoverable_cost
, annual_rounding_flag
, eofy_adj_cost
, eofy_formula_factor
, short_fiscal_year_flag
, conversion_date
, ORIGINAL_DEPRN_START_DATE
, remaining_life1
, remaining_life2
, group_asset_id
, old_adjusted_cost
, formula_factor
, salvage_type
, deprn_limit_type
, over_depreciate_option
, super_group_id
, reduction_rate
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, recognize_gain_loss
, recapture_reserve_flag
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, ytd_proceeds
, ltd_proceeds
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, cip_cost
, terminal_gain_loss_amount
, ltd_cost_of_removal
, prior_eofy_reserve
, eofy_reserve
, eop_adj_cost
, eop_formula_factor
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, global_attribute_category
, nbv_at_switch
, prior_deprn_limit_type
, prior_deprn_limit_amount
, prior_deprn_limit
, prior_deprn_method
, prior_life_in_months
, prior_basic_rate
, prior_adjusted_rate
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and (transaction_header_id_out = p_trans_rec.transaction_header_id
or (transaction_header_id_in < p_trans_rec.transaction_header_id and
transaction_header_id_out is null))
order by transaction_header_id_in desc;
select date_placed_in_service
, deprn_start_date
, deprn_method_code
, life_in_months
, rate_adjustment_factor
, adjusted_cost
, cost
, original_cost
, salvage_value
, prorate_convention_code
, prorate_date
, cost_change_flag
, adjustment_required_status
, capitalize_flag
, retirement_pending_flag
, depreciate_flag
, itc_amount_id
, itc_amount
, retirement_id
, tax_request_id
, itc_basis
, basic_rate
, adjusted_rate
, bonus_rule
, ceiling_name
, recoverable_cost
, adjusted_capacity
, fully_rsvd_revals_counter
, idled_flag
, period_counter_capitalized
, period_counter_fully_reserved
, period_counter_fully_retired
, production_capacity
, reval_amortization_basis
, reval_ceiling
, unit_of_measure
, unrevalued_cost
, annual_deprn_rounding_flag
, percent_salvage_value
, allowed_deprn_limit
, allowed_deprn_limit_amount
, period_counter_life_complete
, adjusted_recoverable_cost
, annual_rounding_flag
, eofy_adj_cost
, eofy_formula_factor
, short_fiscal_year_flag
, conversion_date
, ORIGINAL_DEPRN_START_DATE
, remaining_life1
, remaining_life2
, group_asset_id
, old_adjusted_cost
, formula_factor
, salvage_type
, deprn_limit_type
, over_depreciate_option
, super_group_id
, reduction_rate
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, recognize_gain_loss
, recapture_reserve_flag
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, ytd_proceeds
, ltd_proceeds
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, cip_cost
, terminal_gain_loss_amount
, ltd_cost_of_removal
, prior_eofy_reserve
, eofy_reserve
, eop_adj_cost
, eop_formula_factor
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, global_attribute_category
, nbv_at_switch
, prior_deprn_limit_type
, prior_deprn_limit_amount
, prior_deprn_limit
, prior_deprn_method
, prior_life_in_months
, prior_basic_rate
, prior_adjusted_rate
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and (transaction_header_id_out = p_init_transaction_header_id
or (p_init_transaction_header_id > transaction_header_id_in and
transaction_header_id_in < p_trans_rec.transaction_header_id and
transaction_header_id_out is null))
order by transaction_header_id_in desc;
select date_placed_in_service
, deprn_start_date
, deprn_method_code
, life_in_months
, rate_adjustment_factor
, adjusted_cost
, cost
, original_cost
, salvage_value
, prorate_convention_code
, prorate_date
, cost_change_flag
, adjustment_required_status
, capitalize_flag
, retirement_pending_flag
, depreciate_flag
, itc_amount_id
, itc_amount
, retirement_id
, tax_request_id
, itc_basis
, basic_rate
, adjusted_rate
, bonus_rule
, ceiling_name
, recoverable_cost
, adjusted_capacity
, fully_rsvd_revals_counter
, idled_flag
, period_counter_capitalized
, period_counter_fully_reserved
, period_counter_fully_retired
, production_capacity
, reval_amortization_basis
, reval_ceiling
, unit_of_measure
, unrevalued_cost
, annual_deprn_rounding_flag
, percent_salvage_value
, allowed_deprn_limit
, allowed_deprn_limit_amount
, period_counter_life_complete
, adjusted_recoverable_cost
, annual_rounding_flag
, eofy_adj_cost
, eofy_formula_factor
, short_fiscal_year_flag
, conversion_date
, ORIGINAL_DEPRN_START_DATE
, remaining_life1
, remaining_life2
, group_asset_id
, old_adjusted_cost
, formula_factor
, salvage_type
, deprn_limit_type
, over_depreciate_option
, super_group_id
, reduction_rate
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, recognize_gain_loss
, recapture_reserve_flag
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, ytd_proceeds
, ltd_proceeds
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, cip_cost
, terminal_gain_loss_amount
, ltd_cost_of_removal
, prior_eofy_reserve
, eofy_reserve
, eop_adj_cost
, eop_formula_factor
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, global_attribute_category
from fa_mc_books
where asset_id = p_asset_hdr_rec.asset_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id
and book_type_code = p_asset_hdr_rec.book_type_code
and (transaction_header_id_out = p_trans_rec.transaction_header_id
or (transaction_header_id_in < p_trans_rec.transaction_header_id and
transaction_header_id_out is null))
order by transaction_header_id_in desc;
select date_placed_in_service
, deprn_start_date
, deprn_method_code
, life_in_months
, rate_adjustment_factor
, adjusted_cost
, cost
, original_cost
, salvage_value
, prorate_convention_code
, prorate_date
, cost_change_flag
, adjustment_required_status
, capitalize_flag
, retirement_pending_flag
, depreciate_flag
, itc_amount_id
, itc_amount
, retirement_id
, tax_request_id
, itc_basis
, basic_rate
, adjusted_rate
, bonus_rule
, ceiling_name
, recoverable_cost
, adjusted_capacity
, fully_rsvd_revals_counter
, idled_flag
, period_counter_capitalized
, period_counter_fully_reserved
, period_counter_fully_retired
, production_capacity
, reval_amortization_basis
, reval_ceiling
, unit_of_measure
, unrevalued_cost
, annual_deprn_rounding_flag
, percent_salvage_value
, allowed_deprn_limit
, allowed_deprn_limit_amount
, period_counter_life_complete
, adjusted_recoverable_cost
, annual_rounding_flag
, eofy_adj_cost
, eofy_formula_factor
, short_fiscal_year_flag
, conversion_date
, ORIGINAL_DEPRN_START_DATE
, remaining_life1
, remaining_life2
, group_asset_id
, old_adjusted_cost
, formula_factor
, salvage_type
, deprn_limit_type
, over_depreciate_option
, super_group_id
, reduction_rate
, reduce_addition_flag
, reduce_adjustment_flag
, reduce_retirement_flag
, recognize_gain_loss
, recapture_reserve_flag
, limit_proceeds_flag
, terminal_gain_loss
, tracking_method
, exclude_fully_rsv_flag
, excess_allocation_option
, depreciation_option
, member_rollup_flag
, ytd_proceeds
, ltd_proceeds
, allocate_to_fully_rsv_flag
, allocate_to_fully_ret_flag
, cip_cost
, terminal_gain_loss_amount
, ltd_cost_of_removal
, prior_eofy_reserve
, eofy_reserve
, eop_adj_cost
, eop_formula_factor
, global_attribute1
, global_attribute2
, global_attribute3
, global_attribute4
, global_attribute5
, global_attribute6
, global_attribute7
, global_attribute8
, global_attribute9
, global_attribute10
, global_attribute11
, global_attribute12
, global_attribute13
, global_attribute14
, global_attribute15
, global_attribute16
, global_attribute17
, global_attribute18
, global_attribute19
, global_attribute20
, global_attribute_category
from fa_mc_books
where set_of_books_id = p_asset_hdr_rec.set_of_books_id
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and (transaction_header_id_out = p_init_transaction_header_id
or (p_init_transaction_header_id > transaction_header_id_in and
transaction_header_id_in < p_trans_rec.transaction_header_id and
transaction_header_id_out is null))
order by transaction_header_id_in desc;
select inbk.date_placed_in_service
, inbk.deprn_start_date
, decode(inbk.deprn_method_code,
outbk.deprn_method_code,
decode(inbk.life_in_months,
outbk.life_in_months,
decode(inbk.basic_rate,
outbk.basic_rate,
decode(inbk.adjusted_rate,
outbk.adjusted_rate,
decode(inbk.production_capacity,
outbk.production_capacity,
null,
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code)
, decode(inbk.deprn_method_code,
outbk.deprn_method_code,
decode(inbk.life_in_months,
outbk.life_in_months, null,
inbk.life_in_months),
inbk.life_in_months)
, inbk.rate_adjustment_factor
, inbk.adjusted_cost
, inbk.cost - nvl(outbk.cost, 0)
, inbk.original_cost
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'AMT',nvl(inbk.salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'PCT',nvl(inbk.salvage_value, 0)))
, inbk.prorate_convention_code
, inbk.prorate_date
, inbk.cost_change_flag
, inbk.adjustment_required_status
, inbk.capitalize_flag
, inbk.retirement_pending_flag
, inbk.depreciate_flag
, inbk.itc_amount_id
, inbk.itc_amount
, inbk.retirement_id
, inbk.tax_request_id
, inbk.itc_basis
--bug fix 5718524
, decode(inbk.deprn_method_code,
outbk.deprn_method_code,
decode(inbk.basic_rate,
outbk.basic_rate, decode(inbk.adjusted_rate,
outbk.adjusted_rate, null,
inbk.basic_rate),
inbk.basic_rate),
inbk.basic_rate)
, decode(inbk.deprn_method_code,
outbk.deprn_method_code,
decode(inbk.adjusted_rate,
outbk.adjusted_rate, decode(inbk.basic_rate,
outbk.basic_rate, null,
inbk.adjusted_rate),
inbk.adjusted_rate),
inbk.adjusted_rate)
, inbk.bonus_rule
, inbk.ceiling_name
, inbk.recoverable_cost
, inbk.adjusted_capacity
, decode(inbk.fully_rsvd_revals_counter,
null, decode(outbk.fully_rsvd_revals_counter,
null, null,
outbk.fully_rsvd_revals_counter),
nvl(inbk.fully_rsvd_revals_counter, 0) -
nvl(outbk.fully_rsvd_revals_counter, 0))
, inbk.idled_flag
, inbk.period_counter_capitalized
, inbk.period_counter_fully_reserved
, inbk.period_counter_fully_retired
, decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) - nvl(outbk.production_capacity, 0))
, decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0))
, decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0))
, inbk.unit_of_measure
, inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0)
, inbk.annual_deprn_rounding_flag
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'AMT',nvl(inbk.percent_salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'PCT',nvl(inbk.percent_salvage_value, 0)))
,decode(p_trans_rec.calling_interface, 'FAXASSET',decode(inbk.deprn_limit_type,
'PCT', nvl(inbk.allowed_deprn_limit, 0),
'AMT', nvl(inbk.allowed_deprn_limit, 0)),
decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'AMT',nvl(inbk.allowed_deprn_limit, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'PCT',nvl(inbk.allowed_deprn_limit, 0)))) --Bug7196658
, decode(p_trans_rec.calling_interface, 'FAXASSET',decode(inbk.deprn_limit_type,
'PCT', nvl(inbk.allowed_deprn_limit_amount, 0),
'AMT', nvl(inbk.allowed_deprn_limit_amount, 0)),
decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0)))) --Bug7196658
, inbk.period_counter_life_complete
, inbk.adjusted_recoverable_cost
, inbk.annual_rounding_flag
, inbk.eofy_adj_cost
, inbk.eofy_formula_factor
, inbk.short_fiscal_year_flag
, inbk.conversion_date
, inbk.ORIGINAL_DEPRN_START_DATE
, inbk.remaining_life1
, inbk.remaining_life2
, inbk.group_asset_id
, inbk.old_adjusted_cost
, inbk.formula_factor
, inbk.salvage_type
, inbk.deprn_limit_type
, inbk.over_depreciate_option
, decode(inbk.super_group_id, outbk.super_group_id, null, inbk.super_group_id)
, decode(inbk.reduction_rate,
null, decode(outbk.reduction_rate,
null, null,
outbk.reduction_rate),
nvl(inbk.reduction_rate, 0) - nvl(outbk.reduction_rate, 0))
, inbk.reduce_addition_flag
, inbk.reduce_adjustment_flag
, inbk.reduce_retirement_flag
, inbk.recognize_gain_loss
, inbk.recapture_reserve_flag
, inbk.limit_proceeds_flag
, inbk.terminal_gain_loss
, inbk.tracking_method
, inbk.exclude_fully_rsv_flag
, inbk.excess_allocation_option
, inbk.depreciation_option
, inbk.member_rollup_flag
, inbk.ytd_proceeds
, inbk.ltd_proceeds
, inbk.allocate_to_fully_rsv_flag
, inbk.allocate_to_fully_ret_flag
, nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0)
, inbk.terminal_gain_loss_amount
, inbk.ltd_cost_of_removal
, inbk.prior_eofy_reserve
, nvl(inbk.eofy_reserve, 0) - nvl(outbk.eofy_reserve, 0)
, inbk.eop_adj_cost
, inbk.eop_formula_factor
, inbk.global_attribute1
, inbk.global_attribute2
, inbk.global_attribute3
, inbk.global_attribute4
, inbk.global_attribute5
, inbk.global_attribute6
, inbk.global_attribute7
, inbk.global_attribute8
, inbk.global_attribute9
, inbk.global_attribute10
, inbk.global_attribute11
, inbk.global_attribute12
, inbk.global_attribute13
, inbk.global_attribute14
, inbk.global_attribute15
, inbk.global_attribute16
, inbk.global_attribute17
, inbk.global_attribute18
, inbk.global_attribute19
, inbk.global_attribute20
, inbk.global_attribute_category
, inbk.nbv_at_switch
, inbk.prior_deprn_limit_type
, inbk.prior_deprn_limit_amount
, inbk.prior_deprn_limit
, inbk.prior_deprn_method
, inbk.prior_life_in_months
, inbk.prior_basic_rate
, inbk.prior_adjusted_rate
from fa_books inbk,
fa_books outbk
where inbk.asset_id = c_asset_id
and inbk.asset_id = outbk.asset_id(+)
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and inbk.book_type_code = outbk.book_type_code(+)
and inbk.transaction_header_id_in = c_transaction_header_id
and inbk.transaction_header_id_in = outbk.transaction_header_id_out(+);
select inbk.date_placed_in_service
, inbk.deprn_start_date
, decode(inbk.deprn_method_code,
outbk.deprn_method_code,
decode(inbk.life_in_months,
outbk.life_in_months,
decode(inbk.basic_rate,
outbk.basic_rate,
decode(inbk.adjusted_rate,
outbk.adjusted_rate,
decode(inbk.production_capacity,
outbk.production_capacity,
null,
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code),
inbk.deprn_method_code)
, decode(inbk.deprn_method_code,
outbk.deprn_method_code, decode(inbk.life_in_months,
outbk.life_in_months, null,
inbk.life_in_months),
inbk.life_in_months)
, inbk.rate_adjustment_factor
, inbk.adjusted_cost
, inbk.cost - nvl(outbk.cost, 0)
, inbk.original_cost
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'AMT',nvl(inbk.salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'PCT',nvl(inbk.salvage_value, 0)))
, inbk.prorate_convention_code
, inbk.prorate_date
, inbk.cost_change_flag
, inbk.adjustment_required_status
, inbk.capitalize_flag
, inbk.retirement_pending_flag
, inbk.depreciate_flag
, inbk.itc_amount_id
, inbk.itc_amount
, inbk.retirement_id
, inbk.tax_request_id
, inbk.itc_basis
, decode(inbk.deprn_method_code,
outbk.deprn_method_code, decode(inbk.basic_rate,
outbk.basic_rate, decode(inbk.adjusted_rate,
outbk.adjusted_rate, null,
inbk.basic_rate),
inbk.basic_rate),
inbk.basic_rate)
, decode(inbk.deprn_method_code,
outbk.deprn_method_code, decode(inbk.adjusted_rate,
outbk.adjusted_rate, decode(inbk.basic_rate,
outbk.basic_rate, null,
inbk.adjusted_rate),
inbk.adjusted_rate),
inbk.adjusted_rate)
, inbk.bonus_rule
, inbk.ceiling_name
, inbk.recoverable_cost
, inbk.adjusted_capacity
, decode(inbk.fully_rsvd_revals_counter,
null, decode(outbk.fully_rsvd_revals_counter,
null, null,
outbk.fully_rsvd_revals_counter),
nvl(inbk.fully_rsvd_revals_counter, 0) -
nvl(outbk.fully_rsvd_revals_counter, 0))
, inbk.idled_flag
, inbk.period_counter_capitalized
, inbk.period_counter_fully_reserved
, inbk.period_counter_fully_retired
, decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) - nvl(outbk.production_capacity, 0))
, decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0))
, decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0))
, inbk.unit_of_measure
, inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0)
, inbk.annual_deprn_rounding_flag
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'AMT',nvl(inbk.percent_salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'PCT',nvl(inbk.percent_salvage_value, 0)))
,decode(p_trans_rec.calling_interface, 'FAXASSET',decode(inbk.deprn_limit_type,
'PCT', nvl(inbk.allowed_deprn_limit, 0),
'AMT', nvl(inbk.allowed_deprn_limit, 0)),
decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'AMT',nvl(inbk.allowed_deprn_limit, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'PCT',nvl(inbk.allowed_deprn_limit, 0)))) --Bug7196658
, decode(p_trans_rec.calling_interface, 'FAXASSET',decode(inbk.deprn_limit_type,
'PCT', nvl(inbk.allowed_deprn_limit_amount, 0),
'AMT', nvl(inbk.allowed_deprn_limit_amount, 0)),
decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0)))) --Bug7196658
, inbk.period_counter_life_complete
, inbk.adjusted_recoverable_cost
, inbk.annual_rounding_flag
, inbk.eofy_adj_cost
, inbk.eofy_formula_factor
, inbk.short_fiscal_year_flag
, inbk.conversion_date
, inbk.ORIGINAL_DEPRN_START_DATE
, inbk.remaining_life1
, inbk.remaining_life2
, inbk.group_asset_id
, inbk.old_adjusted_cost
, inbk.formula_factor
, inbk.salvage_type
, inbk.deprn_limit_type
, inbk.over_depreciate_option
, decode(inbk.super_group_id, outbk.super_group_id, null, inbk.super_group_id)
, decode(inbk.reduction_rate,
null, decode(outbk.reduction_rate,
null, null,
outbk.reduction_rate),
nvl(inbk.reduction_rate, 0) - nvl(outbk.reduction_rate, 0))
, inbk.reduce_addition_flag
, inbk.reduce_adjustment_flag
, inbk.reduce_retirement_flag
, inbk.recognize_gain_loss
, inbk.recapture_reserve_flag
, inbk.limit_proceeds_flag
, inbk.terminal_gain_loss
, inbk.tracking_method
, inbk.exclude_fully_rsv_flag
, inbk.excess_allocation_option
, inbk.depreciation_option
, inbk.member_rollup_flag
, inbk.ytd_proceeds
, inbk.ltd_proceeds
, inbk.allocate_to_fully_rsv_flag
, inbk.allocate_to_fully_ret_flag
, nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0)
, inbk.terminal_gain_loss_amount
, inbk.ltd_cost_of_removal
, inbk.prior_eofy_reserve
, nvl(inbk.eofy_reserve, 0) - nvl(outbk.eofy_reserve, 0)
, inbk.eop_adj_cost
, inbk.eop_formula_factor
, inbk.global_attribute1
, inbk.global_attribute2
, inbk.global_attribute3
, inbk.global_attribute4
, inbk.global_attribute5
, inbk.global_attribute6
, inbk.global_attribute7
, inbk.global_attribute8
, inbk.global_attribute9
, inbk.global_attribute10
, inbk.global_attribute11
, inbk.global_attribute12
, inbk.global_attribute13
, inbk.global_attribute14
, inbk.global_attribute15
, inbk.global_attribute16
, inbk.global_attribute17
, inbk.global_attribute18
, inbk.global_attribute19
, inbk.global_attribute20
, inbk.global_attribute_category
from fa_mc_books inbk,
fa_mc_books outbk
where inbk.asset_id = c_asset_id
and inbk.asset_id = outbk.asset_id(+)
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and inbk.book_type_code = outbk.book_type_code(+)
and inbk.transaction_header_id_in = c_transaction_header_id
and inbk.transaction_header_id_in = outbk.transaction_header_id_out(+)
and inbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and outbk.set_of_books_id(+) = p_asset_hdr_rec.set_of_books_id;
select inbk.date_placed_in_service
, inbk.deprn_start_date
, inbk.deprn_method_code
, inbk.life_in_months
, inbk.rate_adjustment_factor
, inbk.adjusted_cost
, inbk.cost - nvl(outbk.cost, 0)
, inbk.original_cost
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'AMT',nvl(inbk.salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'PCT',nvl(inbk.salvage_value, 0)))
, inbk.prorate_convention_code
, inbk.prorate_date
, inbk.cost_change_flag
, inbk.adjustment_required_status
, inbk.capitalize_flag
, inbk.retirement_pending_flag
, inbk.depreciate_flag
, inbk.itc_amount_id
, inbk.itc_amount
, inbk.retirement_id
, inbk.tax_request_id
, inbk.itc_basis
, inbk.basic_rate
, inbk.adjusted_rate
, inbk.bonus_rule
, inbk.ceiling_name
, inbk.recoverable_cost
, inbk.adjusted_capacity
, decode(inbk.fully_rsvd_revals_counter,
null, decode(outbk.fully_rsvd_revals_counter,
null, null,
outbk.fully_rsvd_revals_counter),
nvl(inbk.fully_rsvd_revals_counter, 0) -
nvl(outbk.fully_rsvd_revals_counter, 0))
, inbk.idled_flag
, inbk.period_counter_capitalized
, inbk.period_counter_fully_reserved
, inbk.period_counter_fully_retired
, decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) - nvl(outbk.production_capacity, 0))
, decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0))
, decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0))
, inbk.unit_of_measure
, inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0)
, inbk.annual_deprn_rounding_flag
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'AMT',nvl(inbk.percent_salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'PCT',nvl(inbk.percent_salvage_value, 0)))
, decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'AMT',nvl(inbk.allowed_deprn_limit, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'PCT',nvl(inbk.allowed_deprn_limit, 0)))
, decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0)))
, inbk.period_counter_life_complete
, inbk.adjusted_recoverable_cost
, inbk.annual_rounding_flag
, inbk.eofy_adj_cost
, inbk.eofy_formula_factor
, inbk.short_fiscal_year_flag
, inbk.conversion_date
, inbk.ORIGINAL_DEPRN_START_DATE
, inbk.remaining_life1
, inbk.remaining_life2
, inbk.group_asset_id
, inbk.old_adjusted_cost
, inbk.formula_factor
, inbk.salvage_type
, inbk.deprn_limit_type
, inbk.over_depreciate_option
, decode(inbk.super_group_id, outbk.super_group_id, null, inbk.super_group_id)
, decode(inbk.reduction_rate,
null, decode(outbk.reduction_rate,
null, null,
outbk.reduction_rate),
nvl(inbk.reduction_rate, 0) - nvl(outbk.reduction_rate, 0))
, inbk.reduce_addition_flag
, inbk.reduce_adjustment_flag
, inbk.reduce_retirement_flag
, inbk.recognize_gain_loss
, inbk.recapture_reserve_flag
, inbk.limit_proceeds_flag
, inbk.terminal_gain_loss
, inbk.tracking_method
, inbk.exclude_fully_rsv_flag
, inbk.excess_allocation_option
, inbk.depreciation_option
, inbk.member_rollup_flag
, inbk.ytd_proceeds
, inbk.ltd_proceeds
, inbk.allocate_to_fully_rsv_flag
, inbk.allocate_to_fully_ret_flag
, nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0)
, inbk.terminal_gain_loss_amount
, inbk.ltd_cost_of_removal
, inbk.prior_eofy_reserve
, nvl(inbk.eofy_reserve, 0) - nvl(outbk.eofy_reserve, 0)
, inbk.eop_adj_cost
, inbk.eop_formula_factor
, inbk.global_attribute1
, inbk.global_attribute2
, inbk.global_attribute3
, inbk.global_attribute4
, inbk.global_attribute5
, inbk.global_attribute6
, inbk.global_attribute7
, inbk.global_attribute8
, inbk.global_attribute9
, inbk.global_attribute10
, inbk.global_attribute11
, inbk.global_attribute12
, inbk.global_attribute13
, inbk.global_attribute14
, inbk.global_attribute15
, inbk.global_attribute16
, inbk.global_attribute17
, inbk.global_attribute18
, inbk.global_attribute19
, inbk.global_attribute20
, inbk.global_attribute_category
, inbk.nbv_at_switch
, inbk.prior_deprn_limit_type
, inbk.prior_deprn_limit_amount
, inbk.prior_deprn_limit
, inbk.prior_deprn_method
, inbk.prior_life_in_months
, inbk.prior_basic_rate
, inbk.prior_adjusted_rate
from fa_books inbk,
fa_books outbk
where inbk.asset_id = c_asset_id
and inbk.asset_id = outbk.asset_id(+)
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and inbk.book_type_code = outbk.book_type_code(+)
and inbk.transaction_header_id_in = c_transaction_header_id
and inbk.transaction_header_id_in = outbk.transaction_header_id_out(+);
select inbk.date_placed_in_service
, inbk.deprn_start_date
, inbk.deprn_method_code
, inbk.life_in_months
, inbk.rate_adjustment_factor
, inbk.adjusted_cost
, inbk.cost - nvl(outbk.cost, 0)
, inbk.original_cost
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'AMT',nvl(inbk.salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',inbk.salvage_value - nvl(outbk.salvage_value, 0),
'PCT',nvl(inbk.salvage_value, 0)))
, inbk.prorate_convention_code
, inbk.prorate_date
, inbk.cost_change_flag
, inbk.adjustment_required_status
, inbk.capitalize_flag
, inbk.retirement_pending_flag
, inbk.depreciate_flag
, inbk.itc_amount_id
, inbk.itc_amount
, inbk.retirement_id
, inbk.tax_request_id
, inbk.itc_basis
, inbk.basic_rate
, inbk.adjusted_rate
, inbk.bonus_rule
, inbk.ceiling_name
, inbk.recoverable_cost
, inbk.adjusted_capacity
, decode(inbk.fully_rsvd_revals_counter,
null, decode(outbk.fully_rsvd_revals_counter,
null, null,
outbk.fully_rsvd_revals_counter),
nvl(inbk.fully_rsvd_revals_counter, 0) -
nvl(outbk.fully_rsvd_revals_counter, 0))
, inbk.idled_flag
, inbk.period_counter_capitalized
, inbk.period_counter_fully_reserved
, inbk.period_counter_fully_retired
, decode(inbk.production_capacity,
null, decode(outbk.production_capacity,
null, null,
outbk.production_capacity),
nvl(inbk.production_capacity, 0) - nvl(outbk.production_capacity, 0))
, decode(inbk.reval_amortization_basis,
null, decode(outbk.reval_amortization_basis,
null, null,
outbk.reval_amortization_basis),
nvl(inbk.reval_amortization_basis, 0) -
nvl(outbk.reval_amortization_basis, 0))
, decode(inbk.reval_ceiling,
null, decode(outbk.reval_ceiling,
null, null,
outbk.reval_ceiling),
nvl(inbk.reval_ceiling, 0) - nvl(outbk.reval_ceiling, 0))
, inbk.unit_of_measure
, inbk.unrevalued_cost - nvl(outbk.unrevalued_cost, 0)
, inbk.annual_deprn_rounding_flag
, decode(inbk.salvage_type,
'PCT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'PCT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'AMT',nvl(inbk.percent_salvage_value, 0)),
'AMT', decode(nvl(outbk.salvage_type, inbk.salvage_type),
'AMT',nvl(inbk.percent_salvage_value, 0) - nvl(outbk.percent_salvage_value, 0),
'PCT',nvl(inbk.percent_salvage_value, 0)))
, decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'AMT',nvl(inbk.allowed_deprn_limit, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit, 0) - nvl(outbk.allowed_deprn_limit, 0),
'PCT',nvl(inbk.allowed_deprn_limit, 0)))
, decode(inbk.deprn_limit_type,
'PCT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0)),
'AMT', decode(nvl(outbk.deprn_limit_type, inbk.deprn_limit_type),
'AMT',nvl(inbk.allowed_deprn_limit_amount, 0) - nvl(outbk.allowed_deprn_limit_amount, 0),
'PCT',nvl(inbk.allowed_deprn_limit_amount, 0)))
, inbk.period_counter_life_complete
, inbk.adjusted_recoverable_cost
, inbk.annual_rounding_flag
, inbk.eofy_adj_cost
, inbk.eofy_formula_factor
, inbk.short_fiscal_year_flag
, inbk.conversion_date
, inbk.ORIGINAL_DEPRN_START_DATE
, inbk.remaining_life1
, inbk.remaining_life2
, inbk.group_asset_id
, inbk.old_adjusted_cost
, inbk.formula_factor
, inbk.salvage_type
, inbk.deprn_limit_type
, inbk.over_depreciate_option
, decode(inbk.super_group_id, outbk.super_group_id, null, inbk.super_group_id)
, decode(inbk.reduction_rate,
null, decode(outbk.reduction_rate,
null, null,
outbk.reduction_rate),
nvl(inbk.reduction_rate, 0) - nvl(outbk.reduction_rate, 0))
, inbk.reduce_addition_flag
, inbk.reduce_adjustment_flag
, inbk.reduce_retirement_flag
, inbk.recognize_gain_loss
, inbk.recapture_reserve_flag
, inbk.limit_proceeds_flag
, inbk.terminal_gain_loss
, inbk.tracking_method
, inbk.exclude_fully_rsv_flag
, inbk.excess_allocation_option
, inbk.depreciation_option
, inbk.member_rollup_flag
, inbk.ytd_proceeds
, inbk.ltd_proceeds
, inbk.allocate_to_fully_rsv_flag
, inbk.allocate_to_fully_ret_flag
, nvl(inbk.cip_cost, 0) - nvl(outbk.cip_cost, 0)
, inbk.terminal_gain_loss_amount
, inbk.ltd_cost_of_removal
, inbk.prior_eofy_reserve
, nvl(inbk.eofy_reserve, 0) - nvl(outbk.eofy_reserve, 0)
, inbk.eop_adj_cost
, inbk.eop_formula_factor
, inbk.global_attribute1
, inbk.global_attribute2
, inbk.global_attribute3
, inbk.global_attribute4
, inbk.global_attribute5
, inbk.global_attribute6
, inbk.global_attribute7
, inbk.global_attribute8
, inbk.global_attribute9
, inbk.global_attribute10
, inbk.global_attribute11
, inbk.global_attribute12
, inbk.global_attribute13
, inbk.global_attribute14
, inbk.global_attribute15
, inbk.global_attribute16
, inbk.global_attribute17
, inbk.global_attribute18
, inbk.global_attribute19
, inbk.global_attribute20
, inbk.global_attribute_category
from fa_mc_books inbk,
fa_mc_books outbk
where inbk.asset_id = c_asset_id
and inbk.asset_id = outbk.asset_id(+)
and inbk.book_type_code = p_asset_hdr_rec.book_type_code
and inbk.book_type_code = outbk.book_type_code(+)
and inbk.transaction_header_id_in = c_transaction_header_id
and inbk.transaction_header_id_in = outbk.transaction_header_id_out(+)
and inbk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and outbk.set_of_books_id(+) = p_asset_hdr_rec.set_of_books_id;
select asset_id
from fa_transaction_headers
where transaction_header_id = p_trans_rec.member_transaction_header_id;
SELECT bk1.deprn_limit_type, nvl(bk1.allowed_deprn_limit_amount,0), nvl(bk1.allowed_deprn_limit,0)
INTO l_tmp_deprn_limit_type, l_tmp_allowed_deprn_limit_amt, l_tmp_allowed_deprn_limit
FROM fa_books bk1
WHERE bk1.asset_id = p_asset_hdr_rec.asset_id
and bk1.book_type_code = p_asset_hdr_rec.book_type_code
and bk1.transaction_header_id_out is null;
SELECT bk1.deprn_limit_type, nvl(bk1.allowed_deprn_limit_amount,0), nvl(bk1.allowed_deprn_limit,0)
INTO l_tmp_deprn_limit_type, l_tmp_allowed_deprn_limit_amt, l_tmp_allowed_deprn_limit
FROM fa_books bk1
WHERE bk1.asset_id = p_asset_hdr_rec.asset_id
and bk1.book_type_code = p_asset_hdr_rec.book_type_code
and bk1.transaction_header_id_out is null;
select dp.calendar_period_open_date
,dp.fiscal_year
,ds.ytd_deprn
,ds.deprn_reserve
from fa_deprn_summary ds
, fa_deprn_periods dp
where dp.book_type_code = p_asset_hdr_rec.book_type_code
and ds.book_type_code = p_asset_hdr_rec.book_type_code
and ds.asset_id = p_asset_hdr_rec.asset_id
and ds.deprn_source_code = 'BOOKS'
and dp.period_counter = ds.period_counter + 1;
select fy.fiscal_year
from fa_calendar_periods cp,
fa_book_controls bc,
fa_fiscal_year fy,
fa_calendar_types ct,
fa_books bk
WHERE bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_out is null
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and bk.prorate_date between cp.start_date and cp.end_date;
select th.transaction_header_id
, nvl(th.amortization_start_date, th.transaction_date_entered)
, th.date_effective
, th.transaction_type_code
from fa_transaction_headers th
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_header_id = (select min(th2.transaction_header_id)
from fa_transaction_headers th2
where th2.asset_id = p_asset_hdr_rec.asset_id
and th2.book_type_code = p_asset_hdr_rec.book_type_code);
select th.transaction_header_id transaction_header_id,
decode(l_process_addition,
1, decode( th.transaction_subtype,
'EXPENSED', px_asset_fin_rec_new.date_placed_in_service,
decode(th.transaction_type_code,
'ADDITION', th.transaction_date_entered,
'ADDITION/VOID', th.transaction_date_entered,
nvl(th.amortization_start_date,th.transaction_date_entered)),
nvl(th.amortization_start_date,th.transaction_date_entered),
nvl(th.amortization_start_date,th.transaction_date_entered)
),
nvl(th.amortization_start_date,th.transaction_date_entered)
) transaction_date_entered,
th.date_effective date_effective,
th.transaction_type_code transaction_type_code
from fa_transaction_headers th
, fa_deprn_periods dp
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and dp.book_type_code = p_asset_hdr_rec.book_type_code
-- and th.date_effective between dp.period_open_date
-- and nvl(dp.period_close_date, sysdate)
and c_transaction_date_entered between dp.calendar_period_open_date
and dp.calendar_period_close_date
-- and (th.date_effective > nvl(dp.period_close_date, sysdate))
-- Bug 6612507
and ((th.date_effective > nvl(dp.period_close_date, sysdate)) or
((th.date_effective between dp.period_open_date and nvl(dp.period_close_date, sysdate)) and
(nvl(th.transaction_subtype,'EXPENSED') ='AMORTIZED')))
and ( nvl(th.amortization_start_date,
decode(th.transaction_subtype,
'EXPENSED', px_asset_fin_rec_new.date_placed_in_service,
th.transaction_date_entered
)
) <= c_transaction_date_entered
and th.date_effective < c_date_effective)
and c_transaction_date_entered <= dp.calendar_period_close_date
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID)
union all
select ret.transaction_header_id transaction_header_id,
ret.transaction_date_entered transaction_date_entered,
ret.date_effective date_effective,
ret.transaction_type_code transaction_type_code
from fa_transaction_headers ret,
fa_transaction_headers rei,
fa_retirements faret
where ret.asset_id = p_asset_hdr_rec.asset_id
and rei.asset_id = p_asset_hdr_rec.asset_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and rei.book_type_code = p_asset_hdr_rec.book_type_code
and ret.transaction_header_id = faret.transaction_header_id_in
and rei.transaction_header_id = faret.transaction_header_id_out
and c_transaction_date_entered between
ret.transaction_date_entered and rei.transaction_date_entered
and ret.date_effective < c_date_effective
and ret.transaction_type_code in (G_TRX_TYPE_FUL_RET, G_TRX_TYPE_PAR_RET)
and rei.transaction_type_code = G_TRX_TYPE_REI
and ret.transaction_header_id <> c_transaction_header_id
order by transaction_header_id;
select th.transaction_header_id transaction_header_id,
decode(th.transaction_subtype,
'EXPENSED', px_asset_fin_rec_new.date_placed_in_service,
nvl(th.amortization_start_date,th.transaction_date_entered)
) transaction_date_entered,
th.date_effective date_effective,
th.transaction_type_code transaction_type_code
from fa_transaction_headers th
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID, G_TRX_TYPE_ADD_VOID,
G_TRX_TYPE_ADD)
and decode(th.transaction_subtype, null, px_asset_fin_rec_new.date_placed_in_service,
th.amortization_start_date) <
nvl(p_trans_rec.amortization_start_date, p_trans_rec.transaction_date_entered);
select th.transaction_header_id transaction_header_id,
th.transaction_type_code transaction_type_code,
decode(l_process_addition,
1, decode(th.transaction_type_code,
'ADDITION', th.transaction_date_entered,
'ADDITION/VOID', th.transaction_date_entered,
decode(th.transaction_key, 'IM', to_date('01-'||to_char(add_months(th.amortization_start_date, 1), 'MM-YYYY'), 'DD-MM-YYYY'),
/* Japan Tax phase3 */ 'ES', (select calendar_period_open_date
from fa_deprn_periods
where book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = bk.extended_depreciation_period),
nvl(th.amortization_start_date,
decode(th.transaction_subtype,
'EXPENSED', decode(outbk.depreciate_flag,
'NO', decode(bk.depreciate_flag,
'YES', decode(ds.deprn_reserve,
0, bk.date_placed_in_service,
th.transaction_date_entered),
--Bug6190904: Replacing a line above with this could be an option. bk.date_placed_in_service),
-- Bug#4699743 th.transaction_date_entered),
bk.date_placed_in_service),
-- Bug# 4049799 bk.date_placed_in_service),
px_asset_fin_rec_new.date_placed_in_service),
th.transaction_date_entered)))),
decode(th.transaction_key, 'IM', to_date('01-'||to_char(add_months(th.amortization_start_date, 1), 'MM-YYYY'), 'DD-MM-YYYY'),
/* Japan Tax phase3 */ 'ES', (select calendar_period_open_date
from fa_deprn_periods
where book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = bk.extended_depreciation_period),
nvl(th.amortization_start_date,
decode(th.transaction_subtype,
'EXPENSED', decode(outbk.depreciate_flag,
'NO', decode(bk.depreciate_flag,
'YES', decode(ds.deprn_reserve,
0, bk.date_placed_in_service,
th.transaction_date_entered),
th.transaction_date_entered),
-- Bug# 4049799 bk.date_placed_in_service),
px_asset_fin_rec_new.date_placed_in_service),
th.transaction_date_entered)))) transaction_date_entered,
th.date_effective date_effective,
th.transaction_name transaction_name,
th.source_transaction_header_id source_transaction_header_id,
th.mass_reference_id mass_reference_id,
th.transaction_subtype transaction_subtype,
th.transaction_key transaction_key,
th.amortization_start_date amortization_start_date,
th.calling_interface calling_interface,
th.mass_transaction_id mass_transaction_id,
fa_std_types.FA_NO_OVERRIDE deprn_override_flag,
th.member_transaction_header_id member_transaction_header_id,
th.trx_reference_id trx_reference_id,
th.invoice_transaction_id,
'1st SELECT in c_get_ths_adj'
from fa_transaction_headers th,
fa_books bk
, fa_books outbk
, fa_deprn_summary ds
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_in = th.transaction_header_id
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID, G_TRX_TYPE_REI,
G_TRX_TYPE_FUL_RET, G_TRX_TYPE_PAR_RET,
--
--Bug6933891: Fix for bug5074327 included addition void trx as trx NOT to be processed
-- However void trx needs to come in as the subsequent trx in the period of addition
-- may not share the same trx date. As a result, we need to process the some void trx
-- Whether to process the void trx will be determined in the main loop. Not here.
-- Leave CIP ADDITION Void trx for now but it may be removed from this not in condition
-- as well later.
G_TRX_TYPE_CIP_ADJ, -- Bug# 5074327, 5191200
G_TRX_TYPE_CIP_ADD_VOID, G_TRX_TYPE_CIP_ADD) -- Bug: 6019450, 6798953
and (th.transaction_header_id >= c_transaction_header_id
or th.transaction_header_id < c_transaction_header_id
and th.date_effective <= c_date_effective --Bug6617982
-- and th.transaction_date_entered > c_transaction_date_entered)
and decode(th.transaction_subtype, NULL,
decode(th.transaction_type_code,
'ADDITION/VOID', px_asset_fin_rec_new.date_placed_in_service,
'CIP ADDITION/VOID', px_asset_fin_rec_new.date_placed_in_service,
th.transaction_date_entered),
th.transaction_date_entered) > c_transaction_date_entered)
and th.transaction_header_id <> l_incoming_thid
and not exists (select 'Exclude reclass trx'
from fa_trx_references tr
where tr.member_asset_id = th.asset_id
and tr.member_transaction_header_id = th.transaction_header_id
and tr.book_type_code = p_asset_hdr_rec.book_type_code
and tr.transaction_type = G_TRX_TYPE_GRP_CHG)
/*Bug# 8946649 - No need to pick rolled back impairment and transaction got created to reverse impairment*/
and bk.transaction_header_id_out not in (select transaction_header_id
from fa_transaction_headers th2
where th2.asset_id = th.asset_id
and th2.book_type_code = p_asset_hdr_rec.book_type_code
and th2.transaction_key = 'RM')
and nvl(th.transaction_key,'XX') <> 'RM' /*Bug#9355389 - placed nvl around key */
and ds.asset_id(+) = p_asset_hdr_rec.asset_id
and ds.book_type_code(+) = p_asset_hdr_rec.book_type_code
and ds.deprn_source_code(+) = 'BOOKS'
and outbk.asset_id(+) = p_asset_hdr_rec.asset_id
and outbk.book_type_code(+) = p_asset_hdr_rec.book_type_code
and outbk.transaction_header_id_out(+) = bk.transaction_header_id_in
and bk.depreciate_flag = outbk.depreciate_flag(+) /* Bug 7199183 add this condition*//*Bug 7653832 Added folloing*/
and decode(th.transaction_type_code, 'ADDITION/VOID',th.transaction_date_entered,
'CIP ADDITION/VOID',th.transaction_date_entered,
px_asset_fin_rec_new.date_placed_in_service) <= px_asset_fin_rec_new.date_placed_in_service
--
-- Bug3421263: Added following select to takes care retirement
-- prorate date as retirement trx date.
--
union all
select th.transaction_header_id transaction_header_id,
th.transaction_type_code transaction_type_code,
decode(p_asset_fin_rec_old.group_asset_id, null,
decode(sign(con.prorate_date - cptrx.start_date),
1, decode(sign(con.prorate_date - cptrx.end_date),
-1, ret.date_retired,
0, ret.date_retired,
con.prorate_date),
0, decode(sign(con.prorate_date - cptrx.end_date),
-1, ret.date_retired,
0, ret.date_retired,
con.prorate_date),
con.prorate_date)
, ret.date_retired
) transaction_date_entered,
th.date_effective date_effective,
th.transaction_name transaction_name,
th.source_transaction_header_id source_transaction_header_id,
th.mass_reference_id mass_reference_id,
th.transaction_subtype transaction_subtype,
th.transaction_key transaction_key,
th.amortization_start_date amortization_start_date,
th.calling_interface calling_interface,
th.mass_transaction_id mass_transaction_id,
fa_std_types.FA_NO_OVERRIDE deprn_override_flag,
th.member_transaction_header_id member_transaction_header_id,
th.trx_reference_id trx_reference_id,
th.invoice_transaction_id,
'2nd SELECT in c_get_ths_adj'
from fa_transaction_headers th,
fa_retirements ret,
fa_conventions con,
fa_calendar_periods cp,
fa_calendar_periods cptrx
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and ret.asset_id = p_asset_hdr_rec.asset_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and (ret.transaction_header_id_out is null or -- Bug # 7307047
ret.transaction_header_id_out = l_incoming_thid)
and ret.RETIREMENT_PRORATE_CONVENTION = con.PRORATE_CONVENTION_CODE
and ret.date_retired between con.start_date and con.end_date
and cp.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
and con.prorate_date between cp.start_date and cp.end_date
and th.transaction_type_code in (G_TRX_TYPE_FUL_RET, G_TRX_TYPE_PAR_RET)
-- and th.transaction_header_id >= c_transaction_header_id
and th.transaction_header_id <> l_incoming_thid
and cptrx.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
and ret.date_retired between cptrx.start_date and cptrx.end_date
and th.transaction_header_id = ret.transaction_header_id_in
--bug4363712
-- and con.prorate_date >= c_transaction_date_entered
--bug fix 4547599
and ( (con.prorate_date > c_transaction_date_entered)
or
-- Bug 4622110: Modified to handle ret trx date is later than its prorate date.
-- Bug 6740618: changed the decode
-- Bug 6885405: changed the decode
-- Bug 11840704 : Reverted the fix for 6885405
(decode(c_retirement_flag, 'Y', con.prorate_date, th.transaction_date_entered) <= c_transaction_date_entered and
th.date_effective >= c_date_effective)
)
--
--
union all select
l_incoming_thid transaction_header_id,
p_trans_rec.transaction_type_code transaction_type_code,
nvl(p_trans_rec.amortization_start_date,
p_trans_rec.transaction_date_entered) transaction_date_entered,
p_trans_rec.who_info.creation_date date_effective,
p_trans_rec.transaction_name transaction_name,
p_trans_rec.source_transaction_header_id source_transaction_header_id,
p_trans_rec.mass_reference_id mass_reference_id,
p_trans_rec.transaction_subtype transaction_subtype,
p_trans_rec.transaction_key transaction_key,
p_trans_rec.amortization_start_date amortization_start_date,
p_trans_rec.calling_interface calling_interface,
p_trans_rec.mass_transaction_id mass_transaction_id,
p_trans_rec.deprn_override_flag deprn_override_flag,
p_trans_rec.member_transaction_header_id member_transaction_header_id,
p_trans_rec.trx_reference_id trx_reference_id,
to_number(null), -- invoice_transaction_id
'3rd SELECT in c_get_ths_adj'
from fa_books bk
where bk.asset_id = p_asset_hdr_rec.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and p_trans_rec.transaction_type_code
not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID, G_TRX_TYPE_REI)
order by transaction_date_entered, 4,1; -- 4 is date_effective 13372045 - added 1 -- trx_header_id
select th.transaction_date_entered transaction_date_entered,
th.date_effective date_effective
from fa_transaction_headers th
where th.asset_id = p_asset_hdr_rec.asset_id
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.transaction_date_entered >= c_transaction_date_entered
and th.date_effective > c_date_effective
and (p_asset_type_rec.asset_type <> G_ASSET_TYPE_GROUP or
not exists (select 'Y'
from fa_transaction_headers mth,
fa_books bk
where mth.transaction_header_id = th.member_transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = mth.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and ((bk.asset_id <> nvl(p_reclassed_asset_id, 0) and
bk.group_asset_id is null) or
bk.group_asset_id <> p_asset_hdr_rec.asset_id)
)
)
union all select
p_trans_rec.transaction_date_entered transaction_date_entered,
p_trans_rec.who_info.creation_date date_effective
from dual
where p_reclass_src_dest is null
and p_trans_rec.transaction_date_entered >= c_transaction_date_entered
and p_trans_rec.who_info.creation_date = c_date_effective
and (p_asset_type_rec.asset_type <> G_ASSET_TYPE_GROUP or
not exists (select 'Y'
from fa_transaction_headers mth,
fa_books bk
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = mth.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and ((bk.asset_id <> nvl(p_reclassed_asset_id, 0) and
bk.group_asset_id is null) or
bk.group_asset_id <> p_asset_hdr_rec.asset_id)
)
)
order by transaction_date_entered, date_effective;
select retirement_id,
cost_retired,
cost_of_removal,
proceeds_of_sale,
nvl(reserve_retired, 0),
nvl(eofy_reserve, 0),
reval_reserve_retired,
unrevalued_cost_retired,
bonus_reserve_retired,
impair_reserve_retired,
null -- recognize_gain_loss
from fa_retirements
where transaction_header_id_in = c_transaction_header_id
and transaction_header_id_out is null ;
select retirement_id,
cost_retired,
cost_of_removal,
proceeds_of_sale,
nvl(reserve_retired, 0),
nvl(eofy_reserve, 0),
reval_reserve_retired,
unrevalued_cost_retired,
bonus_reserve_retired,
impair_reserve_retired,
null -- recognize_gain_loss
from fa_mc_retirements
where transaction_header_id_in = c_transaction_header_id
and transaction_header_id_out is null
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag, 'CR', -1 * adjustment_amount,
adjustment_amount))
from fa_adjustments
where source_type_code = 'RETIREMENT'
and adjustment_type = 'RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select sum(decode(debit_credit_flag, 'CR', -1 * adjustment_amount,
adjustment_amount))
from fa_mc_adjustments
where source_type_code = 'RETIREMENT'
and adjustment_type = 'RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_mc_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'BONUS RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_mc_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'BONUS RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'IMPAIR RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_mc_adjustments
where source_type_code = 'REVALUATION'
and adjustment_type = 'IMPAIR RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_adjustments
where source_type_code = c_source_type_code
and adjustment_type = 'IMPAIR RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount))
from fa_mc_adjustments
where source_type_code = c_source_type_code
and adjustment_type = 'IMPAIR RESERVE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select nvl(sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount)),0)
from fa_adjustments
where source_type_code = c_source_type_code
and adjustment_type = 'IMPAIR EXPENSE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id;
select nvl(sum(decode(debit_credit_flag, 'DR', -1 * adjustment_amount,
adjustment_amount)),0)
from fa_mc_adjustments
where source_type_code = c_source_type_code
and adjustment_type = 'IMPAIR EXPENSE'
and asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select gth.transaction_header_id
from fa_transaction_headers gth
where gth.asset_id = p_asset_hdr_rec.asset_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and gth.member_transaction_header_id =
(select min(mth.transaction_header_id)
from fa_transaction_headers mth
where mth.asset_id = p_reclassed_asset_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code);
select th.transaction_header_id transaction_header_id,
nvl(th.amortization_start_date,
th.transaction_date_entered) transaction_date_entered,
th.date_effective date_effective,
th.transaction_type_code transaction_type_code
from fa_transaction_headers th
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and not exists (select 'Y'
from fa_transaction_headers mth,
fa_books bk
where mth.transaction_header_id = th.member_transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = mth.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and ((bk.asset_id <> nvl(p_reclassed_asset_id, 0) and
bk.group_asset_id is null) or
bk.group_asset_id <> p_asset_hdr_rec.asset_id)
)
union all
select p_trans_rec.transaction_header_id transaction_header_id,
nvl(p_trans_rec.amortization_start_date,
p_trans_rec.transaction_date_entered) transaction_date_entered,
p_trans_rec.who_info.creation_date date_effective,
p_trans_rec.transaction_type_code transaction_type_code
from dual
where not exists (select 'Y'
from fa_transaction_headers mth,
fa_books bk
where mth.transaction_header_id = p_trans_rec.member_transaction_header_id
and mth.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = mth.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and ((bk.asset_id <> nvl(p_reclassed_asset_id, 0) and
bk.group_asset_id is null) or
bk.group_asset_id <> p_asset_hdr_rec.asset_id)
)
order by transaction_header_id desc;
select outgth.transaction_header_id
from fa_transaction_headers outgth,
fa_transaction_headers ingth,
fa_books gbk
where outgth.asset_id = p_asset_hdr_rec.asset_id
and outgth.book_type_code = p_asset_hdr_rec.book_type_code
and outgth.transaction_header_id <= c_transaction_header_id
and ingth.asset_id = p_asset_hdr_rec.asset_id
and ingth.book_type_code = p_asset_hdr_rec.book_type_code
and gbk.transaction_header_id_out = outgth.transaction_header_id
and gbk.transaction_header_id_in = ingth.transaction_header_id
and not exists (select 1
from fa_transaction_headers mth,
fa_books bk
where mth.transaction_header_id = ingth.member_transaction_header_id
and mth.asset_id = bk.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null
and (bk.asset_id <> p_reclassed_asset_id
and nvl(bk.group_asset_id, 0) <> p_asset_hdr_rec.asset_id))
order by outgth.transaction_header_id desc;
select ytd_deprn, deprn_reserve
from fa_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS';
select ytd_deprn, deprn_reserve
from fa_mc_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS'
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select th.transaction_header_id
, th.transaction_date_entered
, th.date_effective
, th.transaction_type_code
from fa_transaction_headers th
, fa_books bk
where bk.transaction_header_id_out < c_thid
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_in = th.transaction_header_id
and th.transaction_type_code not in (G_TRX_TYPE_TFR_OUT, G_TRX_TYPE_TFR_IN,
G_TRX_TYPE_TFR, G_TRX_TYPE_TFR_VOID,
G_TRX_TYPE_REC, G_TRX_TYPE_UNIT_ADJ,
G_TRX_TYPE_TFR_VOID ,G_TRX_TYPE_REI,
G_TRX_TYPE_ADD_VOID)
order by transaction_header_id desc;
select prorate_date, start_date, end_date
from fa_conventions
where prorate_convention_code = c_prorate_convention_code
and c_date_placed_in_service between start_date and end_date;
select decode(p_asset_fin_rec_old.group_asset_id, null, con.prorate_date, ret.date_retired) prorate_date
from fa_conventions con,
fa_retirements ret
where ret.transaction_header_id_out = p_trans_rec.transaction_header_id
and ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.asset_id = p_asset_hdr_rec.asset_id
and ret.retirement_prorate_convention = con.prorate_convention_code
and ret.date_retired between con.start_date and con.end_date;
t_which_select tab_char30_type;
l_trans_rec.who_info.last_update_date := nvl(p_trans_rec.who_info.last_update_date, sysdate);
select transaction_header_id_in
into l_retirement_thid -- retirement thid
from fa_retirements
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out = l_incoming_thid;
select fy.fiscal_year * ct.NUMBER_PER_FISCAL_YEAR + cp.PERIOD_NUM
into l_ret_prorate_pc
from fa_calendar_periods cp,
fa_book_controls bc,
fa_fiscal_year fy,
fa_calendar_types ct,
fa_retirements ret,
fa_conventions con
WHERE ret.book_type_code = p_asset_hdr_rec.book_type_code
and ret.asset_id = p_asset_hdr_rec.asset_id
and ret.transaction_header_id_in = l_trans_rec.transaction_header_id
and ret.RETIREMENT_PRORATE_CONVENTION = con.PRORATE_CONVENTION_CODE
and ret.date_retired between con.start_date and con.end_date
and bc.book_type_code = p_asset_hdr_rec.book_type_code
and bc.deprn_calendar = ct.calendar_type
and bc.fiscal_year_name = fy.fiscal_year_name
and ct.fiscal_year_name = bc.fiscal_year_name
and ct.calendar_type = cp.calendar_type
and cp.start_date between fy.start_date and fy.end_date
and con.prorate_date between cp.start_date and cp.end_date;
SELECT count(th.transaction_header_id)
INTO l_future_ret_count
FROM fa_deprn_periods dp,
fa_transaction_headers th,
fa_retirements ret,
fa_conventions con
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code like '%RETIREMENT'
and th.date_effective between dp.period_open_date and dp.period_close_Date
and dp.period_counter = l_ret_prorate_pc - 1
and dp.book_type_code = th.book_type_code
and th.transaction_header_id = ret.transaction_header_id_in
and ret.RETIREMENT_PRORATE_CONVENTION = con.PRORATE_CONVENTION_CODE
and ret.date_retired between con.start_date and con.end_date
and con.prorate_date > dp.calendar_period_close_date;
SELECT th.transaction_date_entered,
th.date_effective,
th.transaction_header_id,
'N'
INTO l_trans_rec.transaction_date_entered,
l_trans_rec.who_info.creation_date,
l_trans_rec.transaction_header_id,
l_retirement_flag
FROM fa_transaction_headers th
WHERE th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code = 'ADDITION';
t_which_select
LIMIT l_limit;
Select 1 into l_check_tax_overlap
from fa_transaction_headers hdr,
fa_transaction_headers tax
where hdr.transaction_header_id < t_transaction_header_id(i) --Transaction header id for TAX transaction
and hdr.transaction_date_entered <= t_transaction_date_entered(i) --Transaction date for TAX transaction
and hdr.transaction_type_code like '%RETIREMENT'
and hdr.book_type_code = tax.book_type_code
and hdr.asset_id = tax.asset_id
and tax.transaction_header_id = t_transaction_header_id(i);
fa_debug_pkg.add(l_calling_fn, '++ IN c_get_ths_adj: t_which_select('||to_char(i)||')',t_which_select(i));
l_dpr_in.update_override_status :=
((l_rate_source_rule = fa_std_types.FAD_RSR_FORMULA)
OR (((l_rate_source_rule = fa_std_types.FAD_RSR_CALC)
OR (l_rate_source_rule = fa_std_types.FAD_RSR_TABLE))
AND (l_deprn_basis_rule = fa_std_types.FAD_DBR_COST)));
if l_dpr_in.update_override_status then
p_trans_rec.deprn_override_flag := l_dpr_out.deprn_override_flag;
fa_track_member_pvt.p_track_member_table.delete;
fa_track_member_pvt.p_track_mem_index_table.delete;
'deleted', p_log_level_rec => p_log_level_rec);
select bk.depreciate_flag
from fa_books bk
,fa_deprn_periods dp
where bk.asset_id = p_asset_hdr_rec.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.date_effective <= nvl(dp.period_close_date, sysdate)
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and dp.period_counter = l_period_rec.period_counter
order by bk.date_effective desc;
l_dpr_in.update_override_status := TRUE;
select deprn_reserve
into l_temp_reserve
from fa_deprn_summary ds1
where ds1.asset_id = p_asset_hdr_rec.asset_id
and ds1.book_type_code = p_asset_hdr_rec.book_type_code
and ds1.period_counter =
(select ds2.period_counter + 1
from fa_deprn_summary ds2
where ds2.asset_id = p_asset_hdr_rec.asset_id
and ds2.book_type_code = p_asset_hdr_rec.book_type_code
and ds2.deprn_source_code = 'BOOKS');
select decode(nvl(p_asset_fin_rec_old.extended_deprn_flag,'N'),
'Y', decode(l_start_extended,
'Y', p_asset_fin_rec_old.period_counter_fully_extended,
p_asset_fin_rec_old.period_counter_fully_reserved),
p_asset_fin_rec_old.period_counter_fully_reserved),
decode(nvl(l_asset_fin_rec_new.extended_deprn_flag,'N'),
'Y', decode(l_start_extended,
'Y', l_asset_fin_rec_new.period_counter_fully_extended,
l_asset_fin_rec_new.period_counter_fully_reserved),
l_asset_fin_rec_new.period_counter_fully_reserved)
into l_old_pc_reserved,
l_new_pc_reserved
from dual;
if not FA_TRACK_MEMBER_PVT.update_member_books(p_trans_rec=> p_trans_rec,
p_asset_hdr_rec => p_asset_hdr_rec,
p_dpr_in => l_dpr_in,
p_mrc_sob_type_code => p_mrc_sob_type_code, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_TRACK_MEMBER_PVT.update_member_books', p_log_level_rec => p_log_level_rec);
fa_track_member_pvt.p_track_member_eofy_table.delete;
'deleted', p_log_level_rec => p_log_level_rec);
select runid,
run_date,
run_comment
from plsql_profiler_runs;
select adjusted_cost,
recoverable_cost,
reval_amortization_basis,
adjusted_rate,
production_capacity,
adjusted_capacity,
adjusted_recoverable_cost,
salvage_value,
deprn_method_code,
life_in_months,
ceiling_name,
bonus_rule,
annual_deprn_rounding_flag,
rate_adjustment_factor,
prorate_date,
deprn_start_date,
date_placed_in_service
into l_asset_fin_rec.adjusted_cost,
l_asset_fin_rec.recoverable_cost,
l_asset_fin_rec.reval_amortization_basis,
l_asset_fin_rec.adjusted_rate,
l_asset_fin_rec.production_capacity,
l_asset_fin_rec.adjusted_capacity,
l_asset_fin_rec.adjusted_recoverable_cost,
l_asset_fin_rec.salvage_value,
l_asset_fin_rec.deprn_method_code,
l_asset_fin_rec.life_in_months,
l_asset_fin_rec.ceiling_name,
l_asset_fin_rec.bonus_rule,
l_asset_fin_rec.annual_deprn_rounding_flag,
l_asset_fin_rec.rate_adjustment_factor,
l_asset_fin_rec.prorate_date,
l_asset_fin_rec.deprn_start_date,
l_asset_fin_rec.date_placed_in_service
from fa_mc_books bk
where bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_in = p_add_txn_id
and bk.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select adjusted_cost,
recoverable_cost,
reval_amortization_basis,
adjusted_rate,
production_capacity,
adjusted_capacity,
adjusted_recoverable_cost,
salvage_value,
deprn_method_code,
life_in_months,
ceiling_name,
bonus_rule,
annual_deprn_rounding_flag,
rate_adjustment_factor,
prorate_date,
deprn_start_date,
date_placed_in_service
into l_asset_fin_rec.adjusted_cost,
l_asset_fin_rec.recoverable_cost,
l_asset_fin_rec.reval_amortization_basis,
l_asset_fin_rec.adjusted_rate,
l_asset_fin_rec.production_capacity,
l_asset_fin_rec.adjusted_capacity,
l_asset_fin_rec.adjusted_recoverable_cost,
l_asset_fin_rec.salvage_value,
l_asset_fin_rec.deprn_method_code,
l_asset_fin_rec.life_in_months,
l_asset_fin_rec.ceiling_name,
l_asset_fin_rec.bonus_rule,
l_asset_fin_rec.annual_deprn_rounding_flag,
l_asset_fin_rec.rate_adjustment_factor,
l_asset_fin_rec.prorate_date,
l_asset_fin_rec.deprn_start_date,
l_asset_fin_rec.date_placed_in_service
from fa_books bk
where bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_in = p_add_txn_id;
select th.transaction_header_id
from fa_transaction_headers th,
fa_deprn_periods dp
where th.book_type_code = p_asset_hdr_rec.book_type_code
and th.asset_id = p_asset_hdr_rec.asset_id
and th.transaction_type_code = 'ADDITION'
and th.book_type_code = dp.book_type_code
and th.date_effective between dp.period_open_date and
nvl(dp.period_close_date,sysdate)
and px_trans_rec.amortization_start_date < dp.calendar_period_open_date;
UPDATE FA_DEPRN_OVERRIDE
SET status = 'POST'
WHERE used_by = 'ADJUSTMENT'
AND status = 'SELECTED'
AND transaction_header_id is null;
select deprn_reserve,
bonus_deprn_reserve,
ytd_deprn,
impairment_reserve
into l_deprn_summary.deprn_rsv,
l_deprn_summary.bonus_deprn_rsv,
l_deprn_summary.ytd_deprn,
l_deprn_summary.impairment_rsv
from fa_mc_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS'
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select deprn_reserve,
bonus_deprn_reserve,
ytd_deprn,
impairment_reserve
into l_deprn_summary.deprn_rsv,
l_deprn_summary.bonus_deprn_rsv,
l_deprn_summary.ytd_deprn,
l_deprn_summary.impairment_rsv
from fa_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS';
SELECT NVL(SUM(DECODE(ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(DEBIT_CREDIT_FLAG,
'DR', ADJUSTMENT_AMOUNT,
'CR', -1 * ADJUSTMENT_AMOUNT))),0),
-- backdate amortization enhancement - begin
NVL(SUM(DECODE(ADJUSTMENT_TYPE,
'RESERVE',
DECODE(DEBIT_CREDIT_FLAG,
'DR', ADJUSTMENT_AMOUNT,
'CR', -1 * ADJUSTMENT_AMOUNT))),0)
-- backdate amortization enhancement - end
INTO l_adjustment_amount,
l_rsv_amount
FROM FA_MC_ADJUSTMENTS
WHERE asset_id = p_asset_hdr_rec.asset_id
AND book_type_code = p_asset_hdr_rec.book_type_code
AND period_counter_adjusted = l_amortize_per_ctr
AND SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id;
SELECT NVL(SUM(DECODE(ADJUSTMENT_TYPE,
'EXPENSE',
DECODE(DEBIT_CREDIT_FLAG,
'DR', ADJUSTMENT_AMOUNT,
'CR', -1 * ADJUSTMENT_AMOUNT))),0),
-- backdate amortization enhancement - begin
NVL(SUM(DECODE(ADJUSTMENT_TYPE,
'RESERVE',
DECODE(DEBIT_CREDIT_FLAG,
'DR', ADJUSTMENT_AMOUNT,
'CR', -1 * ADJUSTMENT_AMOUNT))),0)
-- backdate amortization enhancement - end
INTO l_adjustment_amount,
l_rsv_amount
FROM FA_ADJUSTMENTS
WHERE asset_id = p_asset_hdr_rec.asset_id
AND book_type_code = p_asset_hdr_rec.book_type_code
AND period_counter_adjusted = l_amortize_per_ctr;
select 'Y'
from fa_books_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_asset_hdr_rec.asset_id
and asset_id <> group_asset_id
and period_counter = l_recalc_start_period_counter - 1;
select 'N'
from fa_deprn_periods
where book_type_code = p_asset_hdr_rec.book_type_code
and period_counter = p_asset_fin_rec_old.period_counter_fully_reserved
and fiscal_year = p_period_rec.fiscal_year;
Select count (1)
from
fa_deprn_periods fadp,
fa_deprn_periods fadp1,
fa_transaction_headers fatr,
fa_deprn_summary fads
where fadp.book_type_code = p_asset_hdr_rec.book_type_code
and p_trans_rec.amortization_start_date between fadp.calendar_period_open_date and fadp.calendar_period_close_date
and fatr.book_type_code = p_asset_hdr_rec.book_type_code
and fatr.asset_id = p_asset_hdr_rec.asset_id
and fatr.transaction_type_code = 'ADDITION'
and fadp1.book_type_code = p_asset_hdr_rec.book_type_code
and fatr.date_effective between fadp1.period_open_date and fadp1.period_close_date
and fatr.transaction_date_entered < fadp1.calendar_period_open_date
and fadp.fiscal_year = fadp1.fiscal_year
and fads.book_type_code = p_asset_hdr_rec.book_type_code
and fads.asset_id = p_asset_hdr_rec.asset_id
and fads.deprn_source_code = 'BOOKS'
and fads.deprn_reserve = 0;
l_dpr_in.update_override_status :=
((l_rate_source_rule = fa_std_types.FAD_RSR_FORMULA)
OR (((l_rate_source_rule = fa_std_types.FAD_RSR_CALC)
OR (l_rate_source_rule = fa_std_types.FAD_RSR_TABLE))
AND (l_deprn_basis_rule = fa_std_types.FAD_DBR_COST)));
select count(*)
into l_last_trx_count
from fa_books bks,
fa_deprn_periods dp
where bks.asset_id = p_asset_hdr_rec.asset_id
and bks.book_type_code = p_asset_hdr_rec.book_type_code
and bks.date_ineffective is null
and dp.book_type_code = p_asset_hdr_rec.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;
fa_track_member_pvt.p_track_member_table.delete;
fa_track_member_pvt.p_track_mem_index_table.delete;
fa_debug_pkg.add(l_calling_fn, 'Delete p_track_member_table', '+++', p_log_level_rec => p_log_level_rec);
if not FA_TRACK_MEMBER_PVT.update_member_books(p_trans_rec => p_trans_rec,
p_asset_hdr_rec => p_asset_hdr_rec,
p_dpr_in => l_dpr_in,
p_mrc_sob_type_code => p_mrc_sob_type_code, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_TRACK_MEMBER_PVT.update_member_books', p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'fa_track_member_pvt.create_update_bs_table',
'called', p_log_level_rec => p_log_level_rec);
if not FA_TRACK_MEMBER_PVT.create_update_bs_table(p_trans_rec => p_trans_rec,
p_book_type_code => p_asset_hdr_rec.book_type_code,
p_group_asset_id => p_asset_hdr_rec.asset_id,
p_mrc_sob_type_code => p_mrc_sob_type_code, --Bug 8941132
p_sob_id => p_asset_hdr_rec.set_of_books_id, --Bug 8941132
p_calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error calling',
'FA_TRACK_MEMBER_PVT.create_update_bs_table', p_log_level_rec => p_log_level_rec);
fa_track_member_pvt.p_track_member_eofy_table.delete;
fa_track_member_pvt.p_track_member_table.delete;
fa_track_member_pvt.p_track_mem_index_table.delete;
'deleted', p_log_level_rec => p_log_level_rec);
select inbk.cost
, inbk.cip_cost
, inbk.salvage_value
, inbk.allowed_deprn_limit_amount
, bs.period_counter
from fa_books inbk
, fa_books outbk
, fa_transaction_headers inth
, fa_transaction_headers outth
, fa_books_summary bs
where inbk.transaction_header_id_in = p_transaction_header_id
and outbk.asset_id = inbk.asset_id
and outbk.book_type_code = p_book_type_code
and outbk.transaction_header_id_out = p_transaction_header_id
and outbk.transaction_header_id_in = outth.transaction_header_id
and bs.asset_id = p_group_asset_id
and bs.book_type_code = p_book_type_code
and nvl(outth.amortization_start_date,
outth.transaction_date_entered) between bs.calendar_period_open_date
and bs.calendar_period_close_date
and inbk.cost = outbk.cost
and nvl(inbk.salvage_value, 0) = nvl(outbk.salvage_value, 0)
and nvl(inbk.allowed_deprn_limit_amount, 0) = nvl(outbk.allowed_deprn_limit_amount, 0)
and inbk.date_placed_in_service <> outbk.date_placed_in_service
;
select transaction_header_id_in
from fa_books
where group_asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;