[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select adjustment_type,
debit_credit_flag,
adjustment_amount,
period_counter_adjusted,
period_counter_created
from fa_adjustments
where asset_id = l_asset_hdr_rec.asset_id
and book_type_code = l_asset_hdr_rec.book_type_code
and transaction_header_id = l_trans_rec.transaction_header_id
and source_type_code = 'DEPRECIATION';
select 'Y'
from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_asset_fin_rec.group_asset_id
and period_counter_fully_retired is null
and transaction_header_id_out is null
/*bug#16426081*/
and not(
tracking_method = 'CALCULATE'
and member_rollup_flag='Y'
and cost=0
and adjusted_cost <> 0
);
select fa_transaction_headers_s.nextval
from dual;
select transaction_header_id
from fa_transaction_headers
where asset_id = p_asset_fin_rec.group_asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and member_transaction_header_id = p_trans_rec.transaction_header_id;
l_trans_rec.who_info.last_update_date := p_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.last_updated_by := p_trans_rec.who_info.last_updated_by;
l_trans_rec.who_info.last_update_login := p_trans_rec.who_info.last_update_login;
l_adj.selection_retid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise ret_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise ret_err;
fa_debug_pkg.add(l_calling_fn, 'Call FA_RETIREMENTS_PKG.Update_Row', 'Begin', p_log_level_rec => p_log_level_rec);
FA_RETIREMENTS_PKG.Update_Row(
X_Rowid => l_rowid,
X_Retirement_Id => p_asset_retire_rec.retirement_id,
X_Status => 'PROCESSED',
X_Recapture_Amount => l_recapture_amount,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'Call FA_RETIREMENTS_PKG.Update_Row', 'End', p_log_level_rec => p_log_level_rec);
element => 'calling fa_xla_events_pvt.update_transaction_event with thid: ',
value => p_trans_rec.transaction_header_id);
if not fa_xla_events_pvt.update_transaction_event
(p_ledger_id => p_asset_hdr_rec.set_of_books_id,
p_transaction_header_id => p_trans_rec.transaction_header_id,
p_book_type_code => p_asset_hdr_rec.book_type_code,
p_event_type_code => 'RETIREMENTS',
p_event_date => p_asset_retire_rec.date_retired, --?
p_event_status_code => FA_XLA_EVENTS_PVT.C_EVENT_UNPROCESSED,
p_calling_fn => l_calling_fn,
p_log_level_rec => p_log_level_rec) then
raise ret_err;
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_terminal_gain_loss_flag => 'Y',
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
UPDATE FA_MC_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
WHERE GROUP_ASSET_ID = l_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
WHERE GROUP_ASSET_ID = l_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null;
select transaction_header_id
from fa_transaction_headers
where asset_id = p_group_asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and member_transaction_header_id = p_transaction_header_id;
select rowid
from fa_adjustments
where asset_id = p_group_asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = l_transaction_header_id;
select rowid
from fa_mc_adjustments
where transaction_header_id = l_transaction_header_id
and set_of_books_id = p_set_of_books_id;
FA_BOOKS_PKG.Delete_Row(
X_Transaction_Header_Id_in => l_transaction_header_id,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_ADJUSTMENTS_PKG.DELETE_ROW(
X_Rowid => l_adj.rowid,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_BOOKS_PKG.Delete_Row(
X_Transaction_Header_Id_in => l_transaction_header_id,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_ADJUSTMENTS_PKG.DELETE_ROW(
X_Rowid => l_adj.rowid,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select FA_DISTRIBUTION_HISTORY_S.NEXTVAL
, -1 * transaction_units
, assigned_to
, code_combination_id
, location_id
, units_assigned
from fa_distribution_history
where retirement_id = p_asset_retire_rec.retirement_id;
select distribution_id
, units_assigned + nvl(transaction_units, 0)
from fa_distribution_history
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
and (assigned_to = c_assigned_to
or (assigned_to is null and
c_assigned_to is null))
and code_combination_id = c_expense_ccid
and location_id = c_location_ccid;
select transaction_type_code
from fa_transaction_headers
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id = p_asset_retire_rec.detail_info.transaction_header_id_in;
select fa_transaction_headers_s.nextval
from dual;
select transaction_header_id
from fa_transaction_headers
where asset_id = p_asset_fin_rec.group_asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and member_transaction_header_id = p_trans_rec.transaction_header_id;
select distribution_id
, transaction_header_id_in
, transaction_header_id_out
, units_assigned
, transaction_units
from fa_distribution_history
where asset_id = p_asset_hdr_rec.asset_id
order by distribution_id;
update FA_DISTRIBUTION_HISTORY
set transaction_units = nvl(transaction_units, t_units_assigned(i)),
transaction_header_id_out = p_trans_rec.transaction_header_id,
date_ineffective = p_trans_rec.who_info.last_update_date,
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 distribution_id = l_distribution_id;
insert into FA_DISTRIBUTION_HISTORY(
DISTRIBUTION_ID,
BOOK_TYPE_CODE,
ASSET_ID,
UNITS_ASSIGNED,
DATE_EFFECTIVE,
CODE_COMBINATION_ID,
LOCATION_ID,
TRANSACTION_HEADER_ID_IN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ASSIGNED_TO)
VALUES(t_distribution_id(i),
p_asset_hdr_rec.book_type_code,
p_asset_hdr_rec.asset_id,
t_units_assigned(i),
p_trans_rec.who_info.creation_date,
t_expense_ccid(i),
t_location_ccid(i),
p_trans_rec.transaction_header_id,
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
t_assigned_to(i));
FA_ASSET_HISTORY_PKG.Update_Row
(X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
X_Transaction_Header_Id_Out => p_trans_rec.transaction_header_id,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Return_Status => l_status,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_ASSET_HISTORY_PKG.Insert_Row
(X_Rowid => l_rowid,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Category_Id => p_asset_cat_rec.category_id,
X_Asset_Type => p_asset_type_rec.asset_type,
X_Units => l_asset_desc_rec_m.current_units + p_asset_retire_rec.units_retired,
X_Date_Effective => p_trans_rec.who_info.last_update_date,
X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Return_Status => l_status,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history', p_log_level_rec => p_log_level_rec);
update fa_additions_b
set current_units = current_units + p_asset_retire_rec.units_retired
where asset_id = p_asset_hdr_rec.asset_id;
l_trans_rec.who_info.last_update_date := p_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.last_updated_by := p_trans_rec.who_info.last_updated_by;
l_trans_rec.who_info.last_update_login := p_trans_rec.who_info.last_update_login;
X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_BOOKS_PKG.INSERT_ROW(
X_Rowid => l_rowid,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Date_Placed_In_Service => p_asset_fin_rec.date_placed_in_service,
X_Date_Effective => p_trans_rec.who_info.creation_date,
X_Deprn_Start_Date => p_asset_fin_rec.deprn_start_date,
X_Deprn_Method_Code => p_asset_fin_rec.deprn_method_code,
X_Life_In_Months => p_asset_fin_rec.life_in_months,
X_Rate_Adjustment_Factor => l_asset_fin_rec_mn.rate_adjustment_factor,
X_Adjusted_Cost => l_asset_fin_rec_mn.adjusted_cost,
X_Cost => l_asset_fin_rec_mn.cost,
X_Original_Cost => p_asset_fin_rec.original_cost,
X_Salvage_Value => l_asset_fin_rec_mn.salvage_value,
X_Prorate_Convention_Code => p_asset_fin_rec.prorate_convention_code,
X_Prorate_Date => p_asset_fin_rec.prorate_date,
X_Cost_Change_Flag => p_asset_fin_rec.cost_change_flag,
X_Adjustment_Required_Status => p_asset_fin_rec.adjustment_required_status,
X_Capitalize_Flag => p_asset_fin_rec.capitalize_flag,
X_Retirement_Pending_Flag => 'NO',
X_Depreciate_Flag => p_asset_fin_rec.depreciate_flag,
X_Disabled_Flag => p_asset_fin_rec.disabled_flag, --HH
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
X_Itc_Amount_Id => p_asset_fin_rec.itc_amount_id,
X_Itc_Amount => p_asset_fin_rec.itc_amount,
-- X_Retirement_Id => ,
-- X_Tax_Request_Id => ,
X_Itc_Basis => p_asset_fin_rec.itc_basis,
X_Basic_Rate => p_asset_fin_rec.basic_rate,
X_Adjusted_Rate => p_asset_fin_rec.adjusted_rate,
X_Bonus_Rule => p_asset_fin_rec.bonus_rule,
X_Ceiling_Name => p_asset_fin_rec.ceiling_name,
X_Recoverable_Cost => l_asset_fin_rec_mn.recoverable_cost,
X_Adjusted_Capacity => l_asset_fin_rec_mn.adjusted_capacity,
X_Fully_Rsvd_Revals_Counter => p_asset_fin_rec.fully_rsvd_revals_counter,
X_Idled_Flag => p_asset_fin_rec.idled_flag,
X_Period_Counter_Capitalized => p_asset_fin_rec.period_counter_capitalized,
X_PC_Fully_Reserved => null,
X_Production_Capacity => p_asset_fin_rec.production_capacity,
X_Reval_Amortization_Basis => l_asset_fin_rec_mn.reval_amortization_basis,
X_Reval_Ceiling => l_asset_fin_rec_mn.reval_ceiling,
X_Unit_Of_Measure => p_asset_fin_rec.unit_of_measure,
X_Unrevalued_Cost => l_asset_fin_rec_mn.unrevalued_cost,
X_Annual_Deprn_Rounding_Flag => l_asset_fin_rec_mn.annual_deprn_rounding_flag,
X_Percent_Salvage_Value => p_asset_fin_rec.percent_salvage_value,
X_Allowed_Deprn_Limit => p_asset_fin_rec.allowed_deprn_limit,
X_Allowed_Deprn_Limit_Amount => l_asset_fin_rec_mn.allowed_deprn_limit_amount,
X_Period_Counter_Life_Complete => p_asset_fin_rec.period_counter_life_complete,
X_Adjusted_Recoverable_Cost => l_asset_fin_rec_mn.adjusted_recoverable_cost,
X_Short_Fiscal_Year_Flag => p_asset_fin_rec.short_fiscal_year_flag,
X_Conversion_Date => p_asset_fin_rec.conversion_date,
X_Orig_Deprn_Start_Date => p_asset_fin_rec.orig_deprn_start_date,
X_Remaining_Life1 => p_asset_fin_rec.remaining_life1,
X_Remaining_Life2 => p_asset_fin_rec.remaining_life2,
X_Old_Adj_Cost => p_asset_fin_rec.adjusted_cost, ---???
X_Formula_Factor => l_asset_fin_rec_mn.formula_factor,
X_gf_Attribute1 => p_asset_fin_rec.global_attribute1,
X_gf_Attribute2 => p_asset_fin_rec.global_attribute2,
X_gf_Attribute3 => p_asset_fin_rec.global_attribute3,
X_gf_Attribute4 => p_asset_fin_rec.global_attribute4,
X_gf_Attribute5 => p_asset_fin_rec.global_attribute5,
X_gf_Attribute6 => p_asset_fin_rec.global_attribute6,
X_gf_Attribute7 => p_asset_fin_rec.global_attribute7,
X_gf_Attribute8 => p_asset_fin_rec.global_attribute8,
X_gf_Attribute9 => p_asset_fin_rec.global_attribute9,
X_gf_Attribute10 => p_asset_fin_rec.global_attribute10,
X_gf_Attribute11 => p_asset_fin_rec.global_attribute11,
X_gf_Attribute12 => p_asset_fin_rec.global_attribute12,
X_gf_Attribute13 => p_asset_fin_rec.global_attribute13,
X_gf_Attribute14 => p_asset_fin_rec.global_attribute14,
X_gf_Attribute15 => p_asset_fin_rec.global_attribute15,
X_gf_Attribute16 => p_asset_fin_rec.global_attribute16,
X_gf_Attribute17 => p_asset_fin_rec.global_attribute17,
X_gf_Attribute18 => p_asset_fin_rec.global_attribute18,
X_gf_Attribute19 => p_asset_fin_rec.global_attribute19,
X_gf_Attribute20 => p_asset_fin_rec.global_attribute20,
X_global_attribute_category => p_asset_fin_rec.global_attribute_category,
X_group_asset_id => p_asset_fin_rec.group_asset_id,
X_salvage_type => p_asset_fin_rec.salvage_type,
X_deprn_limit_type => p_asset_fin_rec.deprn_limit_type,
X_over_depreciate_option => p_asset_fin_rec.over_depreciate_option,
X_super_group_id => p_asset_fin_rec.super_group_id,
X_reduction_rate => l_asset_fin_rec_mn.reduction_rate,
X_reduce_addition_flag => p_asset_fin_rec.reduce_addition_flag,
X_reduce_adjustment_flag => p_asset_fin_rec.reduce_adjustment_flag,
X_reduce_retirement_flag => p_asset_fin_rec.reduce_retirement_flag,
X_recognize_gain_loss => p_asset_fin_rec.recognize_gain_loss,
X_recapture_reserve_flag => p_asset_fin_rec.recapture_reserve_flag,
X_limit_proceeds_flag => p_asset_fin_rec.limit_proceeds_flag,
X_terminal_gain_loss => p_asset_fin_rec.terminal_gain_loss,
X_tracking_method => p_asset_fin_rec.tracking_method,
X_allocate_to_fully_rsv_flag => p_asset_fin_rec.allocate_to_fully_rsv_flag,
X_allocate_to_fully_ret_flag => p_asset_fin_rec.allocate_to_fully_ret_flag,
X_exclude_fully_rsv_flag => p_asset_fin_rec.exclude_fully_rsv_flag,
X_excess_allocation_option => p_asset_fin_rec.excess_allocation_option,
X_depreciation_option => p_asset_fin_rec.depreciation_option,
X_member_rollup_flag => p_asset_fin_rec.member_rollup_flag,
X_ytd_proceeds => l_asset_fin_rec_mn.ytd_proceeds,
X_ltd_proceeds => l_asset_fin_rec_mn.ltd_proceeds,
X_eofy_reserve => l_asset_fin_rec_mn.eofy_reserve,
X_cip_cost => l_asset_fin_rec_mn.cip_cost,
X_terminal_gain_loss_amount => l_asset_fin_rec_mn.terminal_gain_loss_amount,
X_ltd_cost_of_removal => l_asset_fin_rec_mn.ltd_cost_of_removal,
X_cash_generating_unit_id =>
p_asset_fin_rec.cash_generating_unit_id,
X_Return_Status => l_status,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn,
p_log_level_rec => p_log_level_rec);
DELETE FROM FA_ADJUSTMENTS
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
AND SOURCE_TYPE_CODE = 'DEPRECIATION'
AND ADJUSTMENT_TYPE in ('EXPENSE', 'BONUS EXPENSE',
'IMPAIR EXPENSE');
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_terminal_gain_loss_amount => FND_API.G_MISS_NUM,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select distinct nvl(transaction_header_id_out,0)
from fa_distribution_history
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and retirement_id = p_asset_retire_rec.retirement_id; */
select db.rule_name -- ENERGY
from fa_deprn_basis_rules db -- ENERGY
, fa_methods mt -- ENERGY
, fa_books bk -- ENERGY
where bk.asset_id = p_asset_fin_rec.group_asset_id -- ENERGY
and bk.book_type_code = p_asset_hdr_rec.book_type_code -- ENERGY
and bk.transaction_header_id_out is null -- ENERGY
and bk.deprn_method_code = mt.method_code -- ENERGY
and nvl(bk.life_in_months, -99) = nvl(mt.life_in_months, -99) -- ENERGY
and mt.deprn_basis_rule_id = db.deprn_basis_rule_id; -- ENERGY
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
FETCH c_get_thid INTO l_adj.selection_thid;
if (nvl(l_adj.selection_thid, 0) = 0) then
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Error', 'Getting selection_thid', p_log_level_rec => p_log_level_rec);
l_adj.selection_thid := p_trans_rec.transaction_header_id;
l_adj.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_thid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_thid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_trans_rec.who_info.last_update_date,
l_trans_rec.who_info.last_updated_by,
l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
--p_mode <> 'CR' p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
/* select transaction_header_id_out
from fa_distribution_history
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and retirement_id = p_asset_retire_rec.retirement_id;
select transaction_header_id_out
from fa_retirements
where asset_id = p_asset_hdr_rec.asset_id
and retirement_id = p_asset_retire_rec.retirement_id
and book_type_code = p_asset_hdr_rec.book_type_code;
select outbk.salvage_value - inbk.salvage_value
from fa_books inbk , fa_books outbk
where inbk.transaction_header_id_in = p_asset_retire_rec.detail_info.transaction_header_id_in
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 = p_asset_retire_rec.detail_info.transaction_header_id_in;
select outbk.salvage_value - inbk.salvage_value
from fa_mc_books inbk , fa_mc_books outbk
where inbk.transaction_header_id_in = p_asset_retire_rec.detail_info.transaction_header_id_in
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 = p_asset_retire_rec.detail_info.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 transaction_header_id,
asset_id,
date_effective
FROM fa_transaction_headers
WHERE member_transaction_header_id =
p_asset_retire_rec.detail_info.transaction_header_id_in
AND asset_id = p_asset_fin_rec.group_asset_id
AND book_type_code = p_asset_hdr_rec.book_type_code;
SELECT TRANSACTION_HEADER_ID,
SOURCE_TYPE_CODE,
ADJUSTMENT_TYPE,
DEBIT_CREDIT_FLAG,
CODE_COMBINATION_ID,
BOOK_TYPE_CODE,
ASSET_ID,
ADJUSTMENT_AMOUNT,
DISTRIBUTION_ID,
ANNUALIZED_ADJUSTMENT,
PERIOD_COUNTER_ADJUSTED,
PERIOD_COUNTER_CREATED,
ASSET_INVOICE_ID
FROM FA_MC_ADJUSTMENTS
WHERE TRANSACTION_HEADER_ID = l_group_thid
AND ASSET_ID = l_group_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER_CREATED = l_period_rec.period_counter
AND ADJUSTMENT_TYPE in ('PROCEEDS CLR', 'REMOVALCOST CLR', 'NBV RETIRED')
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT TRANSACTION_HEADER_ID,
SOURCE_TYPE_CODE,
ADJUSTMENT_TYPE,
DEBIT_CREDIT_FLAG,
CODE_COMBINATION_ID,
BOOK_TYPE_CODE,
ASSET_ID,
ADJUSTMENT_AMOUNT,
DISTRIBUTION_ID,
ANNUALIZED_ADJUSTMENT,
PERIOD_COUNTER_ADJUSTED,
PERIOD_COUNTER_CREATED,
ASSET_INVOICE_ID
FROM FA_ADJUSTMENTS
WHERE TRANSACTION_HEADER_ID = l_group_thid
AND ASSET_ID = l_group_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER_CREATED = l_period_rec.period_counter
AND ADJUSTMENT_TYPE in ('PROCEEDS CLR', 'REMOVALCOST CLR', 'NBV RETIRED');
l_adj.selection_thid := 0;
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
FETCH c_get_thid_for_dist INTO l_adj.selection_thid;
if (nvl(l_adj.selection_thid, 0) = 0) then
raise calc_err;
l_adj.selection_mode := FA_STD_TYPES.FA_AJ_CLEAR;
l_adj.selection_thid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_thid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.adjustment_amount := l_adj.amount_inserted +
p_asset_retire_rec.cost_retired;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
select fa_transaction_headers_s.nextval
from dual;
select trx.transaction_date_entered
, trx.last_update_date
, trx.last_updated_by
, trx.last_update_login
, trx.mass_transaction_id
, trx.calling_interface
, trx.mass_reference_id
, nvl(trx.amortization_start_date, trx.transaction_date_entered)
, trx.event_id
from fa_transaction_headers trx
where trx.transaction_header_id = p_ret.th_id_in;
select 'Y'
from fa_books
where book_type_code = p_ret.book
and group_asset_id = p_bk.group_asset_id
and ((period_counter_fully_retired is null)
or (period_counter_fully_retired is not null
and retirement_pending_flag = 'YES'))
and transaction_header_id_out is null;
select -1 * eofy_reserve
from fa_retirements
where retirement_id = p_ret.retirement_id;
select -1 * eofy_reserve
from fa_mc_retirements
where retirement_id = p_ret.retirement_id
and set_of_books_id = p_ret.set_of_books_id;
, l_trans_rec.who_info.last_update_date
, l_trans_rec.who_info.last_updated_by
, l_trans_rec.who_info.last_update_login
, l_trans_rec.mass_transaction_id
, l_trans_rec.calling_interface
, l_trans_rec.mass_reference_id
, l_trans_rec.amortization_start_date
, l_trans_rec.event_id;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_terminal_gain_loss_flag => 'Y',
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_ret.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
l_adj.selection_retid := 0;
l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.last_update_date,
l_trans_rec.who_info.last_updated_by,
l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_trans_rec.who_info.last_update_date,
l_trans_rec.who_info.last_updated_by,
l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_terminal_gain_loss_amount => l_deprn_reserve,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_ret.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select fa_transaction_headers_s.nextval
from dual;
select trx.transaction_header_id
, trx.transaction_date_entered
, trx.last_update_date
, trx.last_updated_by
, trx.last_update_login
, trx.mass_transaction_id
, trx.calling_interface
, trx.mass_reference_id
, nvl(trx.amortization_start_date, trx.transaction_date_entered)
, event_id
from fa_retirements ret
, fa_transaction_headers trx
where ret.retirement_id = p_ret.retirement_id
and ret.transaction_header_id_out = trx.transaction_header_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_ret.asset_id
and book_type_code = p_ret.book
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_ret.asset_id
and book_type_code = p_ret.book
and transaction_header_id = c_transaction_header_id
and set_of_books_id = p_ret.set_of_books_id;
select sum(decode(debit_credit_flag, 'DR', -1, 1) * adjustment_amount)
from fa_adjustments aj, fa_transaction_headers th
where aj.source_type_code = 'RETIREMENT'
and aj.adjustment_type = 'EXPENSE'
and aj.asset_id = p_bk.group_asset_id
and aj.book_type_code = p_ret.book
and aj.transaction_header_id = th.transaction_header_id
and th.asset_id = p_bk.group_asset_id
and th.book_type_code = p_ret.book
and th.member_transaction_header_id = p_ret.th_id_in;
select sum(decode(debit_credit_flag, 'DR', -1, 1) * adjustment_amount)
from fa_mc_adjustments aj, fa_transaction_headers th
where aj.source_type_code = 'RETIREMENT'
and aj.adjustment_type = 'EXPENSE'
and aj.asset_id = p_bk.group_asset_id
and aj.book_type_code = p_ret.book
and aj.transaction_header_id = th.transaction_header_id
and th.asset_id = p_bk.group_asset_id
and th.book_type_code = p_ret.book
and th.member_transaction_header_id = p_ret.th_id_in
and aj.set_of_books_id = p_ret.set_of_books_id;
, l_trans_rec.who_info.last_update_date
, l_trans_rec.who_info.last_updated_by
, l_trans_rec.who_info.last_update_login
, l_trans_rec.mass_transaction_id
, l_trans_rec.calling_interface
, l_trans_rec.mass_reference_id
, l_trans_rec.amortization_start_date
, l_trans_rec.event_id;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
l_adj.selection_retid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.last_update_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.last_update_date,
l_trans_rec.who_info.last_updated_by,
l_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
UPDATE FA_ADJUSTMENTS
SET TRANSACTION_HEADER_ID = l_trans_rec.transaction_header_id
WHERE TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
AND ASSET_ID = l_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code;
DELETE FROM FA_ADJUSTMENTS
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND BOOK_TYPE_CODE = l_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_trans_rec.member_transaction_header_id
AND SOURCE_TYPE_CODE = 'DEPRECIATION'
AND ADJUSTMENT_TYPE in ('EXPENSE', 'BONUS EXPENSE', 'IMPAIR EXPENSE');
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_terminal_gain_loss_amount => FND_API.G_MISS_NUM,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => p_ret.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select 'Y'
from fa_books
where book_type_code = p_book_type_code
and group_asset_id = p_asset_id
and period_counter_fully_retired is null
and transaction_header_id_out is null;
select max(gth.transaction_header_id)
from fa_transaction_headers gth,
fa_transaction_headers mth,
fa_retirements ret,
fa_books bk
where bk.group_asset_id = p_asset_id
and bk.book_type_code = p_book_type_code
and ret.asset_id = bk.asset_id
and ret.book_type_code = p_book_type_code
and mth.asset_id = bk.asset_id
and mth.book_type_code = p_book_type_code
and gth.asset_id = p_asset_id
and gth.book_type_code = p_book_type_code
and mth.transaction_type_code = 'FULL RETIREMENT'
and mth.transaction_header_id = gth.member_transaction_header_id;
select 'Y'
from fa_mc_books
where book_type_code = p_book_type_code
and group_asset_id = p_asset_id
and period_counter_fully_retired is null
and transaction_header_id_out is null
and set_of_books_id = p_set_of_books_id;
select max(gth.transaction_header_id)
from fa_transaction_headers gth,
fa_transaction_headers mth,
fa_mc_retirements ret,
fa_mc_books bk
where bk.group_asset_id = p_asset_id
and bk.book_type_code = p_book_type_code
and bk.set_of_books_id = p_set_of_books_id
and ret.asset_id = bk.asset_id
and ret.book_type_code = p_book_type_code
and ret.set_of_books_id = p_set_of_books_id
and mth.asset_id = bk.asset_id
and mth.book_type_code = p_book_type_code
and gth.asset_id = p_asset_id
and gth.book_type_code = p_book_type_code
and mth.transaction_type_code = 'FULL RETIREMENT'
and mth.transaction_header_id = gth.member_transaction_header_id;
select units,
category_id
from fa_asset_history
where asset_id = p_asset_id
and transaction_header_id_out is null;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER(15) := fnd_global.user_id;
l_last_update_login NUMBER(15) := fnd_global.user_id;
l_adj.selection_retid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.last_update_date := sysdate;
l_last_update_date,
l_last_updated_by,
l_last_update_login, p_log_level_rec => p_log_level_rec) then
raise gl_err;
l_last_update_date,
l_last_updated_by,
l_last_update_login, p_log_level_rec => p_log_level_rec) then
raise gl_err;
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => p_book_type_code,
X_Asset_Id => p_asset_id,
X_terminal_gain_loss_amount => l_deprn_reserve,
X_terminal_gain_loss_flag => 'N',
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => fa_cache_pkg.fazcbc_record.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_BOOKS_PKG.Update_Row(
X_Book_Type_Code => p_book_type_code,
X_Asset_Id => p_asset_id,
X_terminal_gain_loss_flag => FND_API.G_MISS_CHAR,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => fa_cache_pkg.fazcbc_record.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select bk.asset_id asset_id
from fa_books bk,
fa_additions ad
where bk.book_type_code = p_book_type_code
and bk.transaction_header_id_out is null
and bk.asset_id = ad.asset_id
and ad.asset_type = 'GROUP'
and bk.terminal_gain_loss = 'YES'
and bk.terminal_gain_loss_flag = 'Y'
and MOD(bk.asset_id, p_total_requests) = (p_request_number - 1);
select bk.asset_id asset_id
from fa_books bk,
fa_additions ad
where bk.book_type_code = p_book_type_code
and bk.transaction_header_id_out is null
and bk.asset_id = ad.asset_id
and ad.asset_type = 'GROUP'
and bk.terminal_gain_loss_flag = 'Y'
and MOD(bk.asset_id, p_total_requests) = (p_request_number - 1);
select asset_id
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 period_counter_fully_retired is null
and transaction_header_id_out is null;
select asset_id
from fa_mc_books
where group_asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and period_counter_fully_retired is null
and transaction_header_id_out is null
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_BOOKS_SUMMARY
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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_MC_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND tracking_method = 'CALCULATE'
AND recognize_gain_loss = 'NO'
and member_rollup_flag = 'Y';
UPDATE FA_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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;
UPDATE FA_BOOKS_SUMMARY
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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_BOOKS
SET TERMINAL_GAIN_LOSS_FLAG = 'Y'
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
AND tracking_method = 'CALCULATE'
AND recognize_gain_loss = 'NO'
and member_rollup_flag = 'Y';
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.RECOVERABLE_COST RECOVERABLE_COST
FROM FA_BOOKS BK
, FA_ADDITIONS_B AD
, fa_transaction_headers th
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.ASSET_ID = th.asset_id
AND th.transaction_header_id = p_mem_ret_thid
AND AD.ASSET_TYPE = 'CAPITALIZED';
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.RECOVERABLE_COST RECOVERABLE_COST
FROM FA_MC_BOOKS BK
, FA_ADDITIONS_B AD
, fa_transaction_headers th
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.ASSET_ID = th.asset_id
AND th.transaction_header_id = p_mem_ret_thid
AND AD.ASSET_TYPE = 'CAPITALIZED'
AND BK.SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id;
SELECT SUM(RECOVERABLE_COST)
FROM FA_BOOKS
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND PERIOD_COUNTER_FULLY_RESERVED is null
AND PERIOD_COUNTER_FULLY_RETIRED is null
AND nvl(CIP_COST, 0) = 0
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id;
SELECT SUM(RECOVERABLE_COST)
FROM FA_MC_BOOKS
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND PERIOD_COUNTER_FULLY_RESERVED is null
AND PERIOD_COUNTER_FULLY_RETIRED is null
AND nvl(CIP_COST, 0) = 0
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT SUM(ADJUSTED_COST)
FROM FA_BOOKS_SUMMARY
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND nvl(FULLY_RESERVED_FLAG, 'N') = 'N'
AND nvl(FULLY_RETIRED_FLAG, 'N') = 'N'
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(CIP_COST, 0) = 0
AND PERIOD_COUNTER = p_period_rec.period_counter;
SELECT SUM(ADJUSTED_COST)
FROM FA_MC_BOOKS_SUMMARY
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND nvl(FULLY_RESERVED_FLAG, 'N') = 'N'
AND nvl(FULLY_RETIRED_FLAG, 'N') = 'N'
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(CIP_COST, 0) = 0
AND PERIOD_COUNTER = p_period_rec.period_counter
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
SELECT sum(decode(AJ.DEBIT_CREDIT_FLAG, 'CR', -1, 1)*AJ.ADJUSTMENT_AMOUNT)
FROM FA_ADJUSTMENTS AJ
WHERE AJ.ASSET_ID = p_asset_hdr_rec.asset_id
AND AJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND AJ.TRANSACTION_HEADER_ID = p_trans_rec.transaction_header_id;
SELECT sum(decode(AJ.DEBIT_CREDIT_FLAG, 'CR', -1, 1)*AJ.ADJUSTMENT_AMOUNT)
FROM FA_ADJUSTMENTS AJ
WHERE AJ.ASSET_ID = p_asset_hdr_rec.asset_id
AND AJ.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND AJ.TRANSACTION_HEADER_ID = p_trans_rec.transaction_header_id;
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
, BK.RECOVERABLE_COST RECOVERABLE_COST
, BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
, AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, AD.CURRENT_UNITS CURRENT_UNITS
, 'NO' REINS_ASSET_FLAG
FROM FA_BOOKS BK
, FA_ADDITIONS_B AD
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.PERIOD_COUNTER_FULLY_RESERVED is null
AND BK.PERIOD_COUNTER_FULLY_RETIRED is null
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.ASSET_ID <> decode(p_mode,'RECURR',nvl(h_rein_mem_asset_id,-1),-1)
AND AD.ASSET_TYPE = 'CAPITALIZED'
UNION ALL
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
, BK.RECOVERABLE_COST RECOVERABLE_COST
, BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
, AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, AD.CURRENT_UNITS CURRENT_UNITS
, 'YES' REINS_ASSET_FLAG
FROM FA_BOOKS BK
, FA_ADDITIONS_B AD
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.ASSET_ID = decode(p_mode,'RECURR',nvl(h_rein_mem_asset_id,-1),-1)
AND AD.ASSET_TYPE = 'CAPITALIZED'
order by 10,1; --Please never change this Order By clause. If changed will cause huge corruption.
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
, BK.RECOVERABLE_COST RECOVERABLE_COST
, BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
, AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, AD.CURRENT_UNITS CURRENT_UNITS
, 'NO' REINS_ASSET_FLAG
FROM FA_MC_BOOKS BK
, FA_ADDITIONS_B AD
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.PERIOD_COUNTER_FULLY_RESERVED is null
AND BK.PERIOD_COUNTER_FULLY_RETIRED is null
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.ASSET_ID <> decode(p_mode,'RECURR',nvl(h_rein_mem_asset_id,-1),-1)
AND AD.ASSET_TYPE = 'CAPITALIZED'
AND BK.SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id
UNION ALL
SELECT BK.ASSET_ID ASSET_ID
, BK.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN
, BK.RATE_ADJUSTMENT_FACTOR RATE_ADJUSTMENT_FACTOR
, BK.ADJUSTED_COST ADJUSTED_COST
, BK.CAPITALIZE_FLAG CAPITALIZE_FLAG
, BK.RECOVERABLE_COST RECOVERABLE_COST
, BK.ADJUSTED_RECOVERABLE_COST ADJUSTED_RECOVERABLE_COST
, AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
, AD.CURRENT_UNITS CURRENT_UNITS
, 'YES' REINS_ASSET_FLAG
FROM FA_MC_BOOKS BK
, FA_ADDITIONS_B AD
WHERE BK.BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT is null
AND BK.GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND nvl(BK.CIP_COST, 0) = 0
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.ASSET_ID = decode(p_mode,'RECURR',nvl(h_rein_mem_asset_id,-1),-1)
AND AD.ASSET_TYPE = 'CAPITALIZED'
AND BK.SET_OF_BOOKS_ID = p_asset_hdr_rec.set_of_books_id
order by 10,1; --Please never change this Order By clause. If changed will cause huge corruption.
SELECT min(transaction_header_id)
FROM fa_transaction_headers fath
WHERE fath.asset_id = p_asset_id
and fath.source_transaction_header_id = p_source_trans_id;
SELECT max(transaction_header_id)
FROM fa_transaction_headers fath
WHERE fath.asset_id = p_asset_id
and fath.source_transaction_header_id = p_source_trans_id;
SELECT ASSET_ID
, TRANSACTION_HEADER_ID_IN
, BK.RECOVERABLE_COST -
(FA_UTIL_PVT.get_asset_deprn_resv(p_asset_hdr_rec.book_type_code,BK.ASSET_ID, p_asset_hdr_rec.set_of_books_id
, null, 'P' ) )
ADJUSTED_COST
FROM FA_BOOKS BK
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND PERIOD_COUNTER_FULLY_RESERVED is null
AND PERIOD_COUNTER_FULLY_RETIRED is null
AND nvl(CIP_COST, 0) = 0
AND (BK.RECOVERABLE_COST -
(FA_UTIL_PVT.get_asset_deprn_resv(p_asset_hdr_rec.book_type_code,BK.ASSET_ID, p_asset_hdr_rec.set_of_books_id
, null, 'P' ) )
) <> 0;
SELECT ASSET_ID
, TRANSACTION_HEADER_ID_IN
, BK.RECOVERABLE_COST -
(FA_UTIL_PVT.get_asset_deprn_resv(p_asset_hdr_rec.book_type_code,BK.ASSET_ID, p_asset_hdr_rec.set_of_books_id
, null, 'R' ) )
ADJUSTED_COST
FROM FA_MC_BOOKS BK
WHERE BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID_OUT is null
AND GROUP_ASSET_ID = p_asset_hdr_rec.asset_id
AND PERIOD_COUNTER_FULLY_RESERVED is null
AND PERIOD_COUNTER_FULLY_RETIRED is null
AND nvl(CIP_COST, 0) = 0
AND (BK.RECOVERABLE_COST -
(FA_UTIL_PVT.get_asset_deprn_resv(p_asset_hdr_rec.book_type_code,BK.ASSET_ID, p_asset_hdr_rec.set_of_books_id
, null, 'R' ) )
) <> 0
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select th.asset_id
, th.transaction_header_id
, aj.debit_credit_flag
, ad.current_units
, ad.asset_category_id
, bk.adjusted_recoverable_cost
, sum(aj.adjustment_amount)
from fa_transaction_headers th -- member
, fa_transaction_headers gth -- group
, fa_adjustments aj
, fa_deprn_periods dp
, fa_additions_b ad
, fa_books bk
where th.book_type_code = p_asset_hdr_rec.book_type_code
-- and th.date_effective = gth.date_effective
and th.source_transaction_header_id = gth.transaction_header_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and gth.member_transaction_header_id = p_mem_ret_thid
and aj.asset_id = th.asset_id
and aj.book_type_code = p_asset_hdr_rec.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and gth.date_effective between dp.period_open_date and nvl(dp.period_close_date, sysdate)
and dp.period_counter = aj.period_counter_created
and th.asset_id = ad.asset_id
and bk.asset_id = th.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.group_asset_id = gth.asset_id
and bk.transaction_header_id_out is null
group by th.asset_id
, th.transaction_header_id
, aj.debit_credit_flag
, ad.current_units
, ad.asset_category_id
, bk.adjusted_recoverable_cost;
select th.asset_id
, th.transaction_header_id
, aj.debit_credit_flag
, ad.current_units
, ad.asset_category_id
, bk.adjusted_recoverable_cost
, sum(aj.adjustment_amount)
from fa_transaction_headers th -- member
, fa_transaction_headers gth -- group
, fa_mc_adjustments aj
, fa_mc_deprn_periods dp
, fa_additions_b ad
, fa_mc_books bk
where th.book_type_code = p_asset_hdr_rec.book_type_code
-- and th.date_effective = gth.date_effective
and th.source_transaction_header_id = gth.transaction_header_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and gth.member_transaction_header_id = p_mem_ret_thid
and aj.asset_id = th.asset_id
and aj.book_type_code = p_asset_hdr_rec.book_type_code
and aj.transaction_header_id = th.transaction_header_id
and dp.book_type_code = p_asset_hdr_rec.book_type_code
and gth.date_effective between dp.period_open_date and nvl(dp.period_close_date, sysdate)
and dp.period_counter = aj.period_counter_created
and th.asset_id = ad.asset_id
and bk.asset_id = th.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.group_asset_id = gth.asset_id
and bk.transaction_header_id_out is null
and bk.set_of_books_id = p_asset_hdr_rec.set_of_books_id
and aj.set_of_books_id = bk.set_of_books_id
and dp.set_of_books_id = aj.set_of_books_id
group by th.asset_id
, th.transaction_header_id
, aj.debit_credit_flag
, ad.current_units
, ad.asset_category_id
, bk.adjusted_recoverable_cost;
select transaction_header_id_in
from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = c_asset_id
and transaction_header_id_out is null;
select transaction_header_id_in
from fa_mc_books
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = c_asset_id
and transaction_header_id_out is null
and set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select count(1)
from fa_transaction_headers th -- member
, fa_transaction_headers gth -- group
, fa_adjustments aj
where th.book_type_code = p_asset_hdr_rec.book_type_code
and th.source_transaction_header_id = gth.transaction_header_id
and gth.book_type_code = p_asset_hdr_rec.book_type_code
and gth.member_transaction_header_id = p_mem_ret_thid
and aj.asset_id = th.asset_id
and aj.book_type_code = p_asset_hdr_rec.book_type_code
and aj.transaction_header_id = th.transaction_header_id;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
t_thid.delete;
t_asset_id.delete;
t_transaction_header_id_in.delete;
t_rate_adjustment_factor.delete;
t_adjusted_cost.delete;
t_capitalize_flag.delete;
t_recoverable_cost.delete;
t_adjusted_recoverable_cost.delete;
t_asset_category_id.delete;
t_current_units.delete;
t_new_adj_cost.delete;
t_adjustment_amount.delete;
t_reins_asset_flag.delete;
l_loc := 'Before Insert TH';
/* Do not insert last record in RECURR mode and reinstated asset flag is YES */
if(p_mode = 'RECURR' AND t_reins_asset_flag(l_th_count) = 'YES') THEN
l_th_count := l_th_count-1;
IF (p_mrc_sob_type_code <> 'R') THEN --Bug 9076882 No need insert transaction headers for MRC
FORALL i in t_asset_id.first..l_th_count
INSERT INTO FA_TRANSACTION_HEADERS(
TRANSACTION_HEADER_ID
, BOOK_TYPE_CODE
, ASSET_ID
, TRANSACTION_TYPE_CODE
, TRANSACTION_DATE_ENTERED
, DATE_EFFECTIVE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, SOURCE_TRANSACTION_HEADER_ID
, MASS_REFERENCE_ID
, LAST_UPDATE_LOGIN
, TRANSACTION_SUBTYPE
, TRANSACTION_KEY
, AMORTIZATION_START_DATE
, CALLING_INTERFACE
, MASS_TRANSACTION_ID
) VALUES (
FA_TRANSACTION_HEADERS_S.NEXTVAL
, p_asset_hdr_rec.book_type_code
, t_asset_id(i)
, 'ADJUSTMENT'
, p_trans_rec.transaction_date_entered
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
, p_trans_rec.transaction_header_id
, p_trans_rec.mass_reference_id
, p_trans_rec.who_info.last_update_login
, 'AMORTIZED'
, 'RA'
, p_trans_rec.amortization_start_date
, p_trans_rec.calling_interface
, p_trans_rec.mass_transaction_id
) RETURNING transaction_header_id BULK COLLECT INTO t_thid;
select FA_TRANSACTION_HEADERS_S.NEXTVAL
into t_thid(i)
from dual;
/* Insert record into fa_transaction_headers */
INSERT INTO FA_TRANSACTION_HEADERS(
TRANSACTION_HEADER_ID
, BOOK_TYPE_CODE
, ASSET_ID
, TRANSACTION_TYPE_CODE
, TRANSACTION_DATE_ENTERED
, DATE_EFFECTIVE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, SOURCE_TRANSACTION_HEADER_ID
, MASS_REFERENCE_ID
, LAST_UPDATE_LOGIN
, TRANSACTION_SUBTYPE
, TRANSACTION_KEY
, AMORTIZATION_START_DATE
, CALLING_INTERFACE
, MASS_TRANSACTION_ID
) VALUES (
t_thid(i)
, p_asset_hdr_rec.book_type_code
, t_asset_id(i)
, 'ADJUSTMENT'
, p_trans_rec.transaction_date_entered
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_update_date
, p_trans_rec.who_info.last_updated_by
, p_trans_rec.transaction_header_id
, p_trans_rec.mass_reference_id
, p_trans_rec.who_info.last_update_login
, 'AMORTIZED'
, 'RA'
, p_trans_rec.amortization_start_date
, p_trans_rec.calling_interface
, p_trans_rec.mass_transaction_id
);
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise allocate_err;
/* Bug 8633654 starts (delete last record when it is not required)*/
IF (p_mode = 'RECURR' and (NOT bln_rein_mem_reqd)) THEN
if (p_log_level_rec.statement_level) then
fa_debug_pkg.add(l_calling_fn, 'Delete Reinstated member from PL-SQL','CHECK',p_log_level_rec);
l_loc := 'Before PL-SQL Delete';
t_asset_id.delete(t_asset_id.last);
t_transaction_header_id_in.delete(t_transaction_header_id_in.last);
t_rate_adjustment_factor.delete(t_rate_adjustment_factor.last);
t_adjusted_cost.delete(t_adjusted_cost.last);
t_capitalize_flag.delete(t_capitalize_flag.last);
t_recoverable_cost.delete(t_recoverable_cost.last);
t_adjusted_recoverable_cost.delete(t_adjusted_recoverable_cost.last);
t_asset_category_id.delete(t_asset_category_id.last);
t_current_units.delete(t_current_units.last);
t_reins_asset_flag.delete(t_reins_asset_flag.last);
fa_debug_pkg.add(l_calling_fn, 'Delete Reinstated member from PL-SQL','Done',p_log_level_rec);
UPDATE FA_MC_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjustment_amount(i)
, DEPRN_RESERVE = DEPRN_RESERVE + t_adjustment_amount(i)
, ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE - t_adjustment_amount(i)
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND TRANSACTION_HEADER_ID_OUT IS NULL
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_BOOKS
SET DATE_INEFFECTIVE = p_trans_rec.who_info.last_update_date
, TRANSACTION_HEADER_ID_OUT = t_thid(i)
WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
l_loc := 'Before MRC Insert Books';
INSERT INTO FA_MC_BOOKS( BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, DATE_EFFECTIVE
, 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
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, LAST_UPDATE_LOGIN
, 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
, 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
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, 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
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, SET_OF_BOOKS_ID
) SELECT BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, p_trans_rec.who_info.last_update_date -- DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, t_adjusted_cost(i) - t_adjustment_amount(i)
, COST
, ORIGINAL_COST
, SALVAGE_VALUE
, PRORATE_CONVENTION_CODE
, PRORATE_DATE
, COST_CHANGE_FLAG
, ADJUSTMENT_REQUIRED_STATUS
, CAPITALIZE_FLAG
, RETIREMENT_PENDING_FLAG
, DEPRECIATE_FLAG
, p_trans_rec.who_info.last_update_date -- LAST_UPDATE_DATE
, p_trans_rec.who_info.last_updated_by -- LAST_UPDATED_BY
, t_thid(i) -- TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, p_trans_rec.who_info.last_update_login -- LAST_UPDATE_LOGIN
, 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
, 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
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, 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
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
, SET_OF_BOOKS_ID
FROM FA_MC_BOOKS
WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjustment_amount(i)
, DEPRN_RESERVE = DEPRN_RESERVE + t_adjustment_amount(i)
, ADJUSTED_COST = RECOVERABLE_COST - DEPRN_RESERVE - t_adjustment_amount(i)
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter;
UPDATE FA_BOOKS
SET DATE_INEFFECTIVE = p_trans_rec.who_info.last_update_date
, TRANSACTION_HEADER_ID_OUT = t_thid(i)
WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
l_loc := 'Before primary Insert Books';
INSERT INTO FA_BOOKS( BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, DATE_EFFECTIVE
, 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
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, LAST_UPDATE_LOGIN
, 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
, 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
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, 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
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
) SELECT BOOK_TYPE_CODE
, ASSET_ID
, DATE_PLACED_IN_SERVICE
, p_trans_rec.who_info.last_update_date -- DATE_EFFECTIVE
, DEPRN_START_DATE
, DEPRN_METHOD_CODE
, LIFE_IN_MONTHS
, RATE_ADJUSTMENT_FACTOR
, t_adjusted_cost(i) - t_adjustment_amount(i) -- 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
, p_trans_rec.who_info.last_update_date -- LAST_UPDATE_DATE
, p_trans_rec.who_info.last_updated_by -- LAST_UPDATED_BY
, t_thid(i) -- TRANSACTION_HEADER_ID_IN
, ITC_AMOUNT_ID
, ITC_AMOUNT
, RETIREMENT_ID
, TAX_REQUEST_ID
, ITC_BASIS
, BASIC_RATE
, ADJUSTED_RATE
, BONUS_RULE
, CEILING_NAME
, RECOVERABLE_COST
, p_trans_rec.who_info.last_update_login -- LAST_UPDATE_LOGIN
, 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
, 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
, EOFY_ADJ_COST
, EOFY_FORMULA_FACTOR
, SHORT_FISCAL_YEAR_FLAG
, CONVERSION_DATE
, ORIGINAL_DEPRN_START_DATE
, REMAINING_LIFE1
, REMAINING_LIFE2
, OLD_ADJUSTED_COST
, FORMULA_FACTOR
, GROUP_ASSET_ID
, SALVAGE_TYPE
, DEPRN_LIMIT_TYPE
, 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
, ALLOCATE_TO_FULLY_RSV_FLAG
, ALLOCATE_TO_FULLY_RET_FLAG
, TERMINAL_GAIN_LOSS_AMOUNT
, CIP_COST
, YTD_PROCEEDS
, LTD_PROCEEDS
, LTD_COST_OF_REMOVAL
, EOFY_RESERVE
, PRIOR_EOFY_RESERVE
, EOP_ADJ_COST
, EOP_FORMULA_FACTOR
, EXCLUDE_PROCEEDS_FROM_BASIS
, RETIREMENT_DEPRN_OPTION
, TERMINAL_GAIN_LOSS_FLAG
, SUPER_GROUP_ID
, OVER_DEPRECIATE_OPTION
, DISABLED_FLAG
FROM FA_BOOKS
WHERE TRANSACTION_HEADER_ID_IN = t_transaction_header_id_in(i);
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise allocate_err;
UPDATE FA_MC_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_last_thid
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_MC_ADJUSTMENTS
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_last_thid
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
, DEPRN_RESERVE = DEPRN_RESERVE + ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
, ADJUSTED_COST = ADJUSTED_COST - ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND TRANSACTION_HEADER_ID_OUT IS NULL
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_BOOKS
SET ADJUSTED_COST = ADJUSTED_COST - ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
WHERE TRANSACTION_HEADER_ID_IN = l_last_thid
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_last_thid
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_ADJUSTMENTS
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = l_last_thid);
UPDATE FA_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
, DEPRN_RESERVE = DEPRN_RESERVE + ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
, ADJUSTED_COST = ADJUSTED_COST - ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
WHERE ASSET_ID = l_last_asset_id
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND TRANSACTION_HEADER_ID_OUT IS NULL;
UPDATE FA_BOOKS
SET ADJUSTED_COST = ADJUSTED_COST - ( l_reallocate_amount * sign(p_reserve_amount) * -1 )
WHERE TRANSACTION_HEADER_ID_IN = l_last_thid;
t_thid.delete;
t_asset_id.delete;
t_adjusted_cost.delete;
UPDATE FA_MC_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_MC_ADJUSTMENTS
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND TRANSACTION_HEADER_ID = t_thid(i));
UPDATE FA_MC_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + (l_reallocate_amount * sign(p_reserve_amount) * -1)
, DEPRN_RESERVE = DEPRN_RESERVE + (l_reallocate_amount * sign(p_reserve_amount) * -1)
, ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
WHERE ASSET_ID = t_asset_id(i)
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
AND TRANSACTION_HEADER_ID_OUT is null;
UPDATE FA_MC_BOOKS
SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
WHERE TRANSACTION_HEADER_ID_IN = t_thid(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + l_reallocate_amount
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i)
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_ADJUSTMENTS
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i));
UPDATE FA_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + (l_reallocate_amount * sign(p_reserve_amount) * -1)
, DEPRN_RESERVE = DEPRN_RESERVE + (l_reallocate_amount * sign(p_reserve_amount) * -1)
, ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter
AND TRANSACTION_HEADER_ID_OUT is null;
UPDATE FA_BOOKS
SET ADJUSTED_COST = ADJUSTED_COST + l_reallocate_amount
WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
UPDATE FA_MC_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_MC_ADJUSTMENTS
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND TRANSACTION_HEADER_ID = t_thid(i));
UPDATE FA_MC_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
, DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id
AND PERIOD_COUNTER = p_period_rec.period_counter;
UPDATE FA_MC_BOOKS
SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
WHERE TRANSACTION_HEADER_ID_IN = t_thid(i)
AND set_of_books_id = p_asset_hdr_rec.set_of_books_id;
UPDATE FA_ADJUSTMENTS
SET ADJUSTMENT_AMOUNT = ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i)
AND DISTRIBUTION_ID = (SELECT MIN(DISTRIBUTION_ID)
FROM FA_ADJUSTMENTS
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND TRANSACTION_HEADER_ID = t_thid(i));
UPDATE FA_BOOKS_SUMMARY
SET RESERVE_ADJUSTMENT_AMOUNT = RESERVE_ADJUSTMENT_AMOUNT + t_adjusted_cost(i)
, DEPRN_RESERVE = ADJUSTED_RECOVERABLE_COST
, ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
WHERE ASSET_ID = t_asset_id(i)
AND BOOK_TYPE_CODE = p_asset_hdr_rec.book_type_code
AND PERIOD_COUNTER = p_period_rec.period_counter;
UPDATE FA_BOOKS
SET ADJUSTED_COST = RECOVERABLE_COST - ADJUSTED_RECOVERABLE_COST
WHERE TRANSACTION_HEADER_ID_IN = t_thid(i);
select distribution_id
from FA_DISTRIBUTION_HISTORY
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id_out = p_trans_rec.transaction_header_id;
l_adj.selection_thid := 0;
l_adj.selection_retid := 0;
l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_SINGLE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;
l_adj.selection_thid := 0;
l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
p_trans_rec.who_info.last_update_date,
p_trans_rec.who_info.last_updated_by,
p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
raise calc_err;