[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT reduction_rate,
reduce_retirement_flag
FROM FA_BOOKS
WHERE ASSET_ID = l_asset_fin_rec.group_asset_id
AND BOOK_TYPE_CODE = px_asset_hdr_rec.book_type_code
AND transaction_header_id_out is null;
SELECT retirement_prorate_convention,
use_stl_retirements_flag,
stl_method_code,
stl_life_in_months
INTO
l_ret_prorate_convention,
l_use_stl_retirements_flag,
l_stl_method_code,
l_stl_life_in_months
FROM fa_category_book_defaults, fa_additions_b a
WHERE book_type_code = l_asset_hdr_rec.book_type_code
and category_id = a.asset_category_id
and a.asset_id = l_asset_hdr_rec.asset_id
and l_asset_fin_rec.Date_Placed_In_Service between start_dpis and
nvl(end_dpis,l_asset_fin_rec.Date_Placed_In_Service);
Select hist.units_assigned into l_unit_assigned
from fa_distribution_history hist
where hist.distribution_id = p_asset_dist_tbl(ctr).distribution_id;
fa_debug_pkg.add(l_calling_fn, 'selected tax book: ', l_tax_book_tbl(l_tax_index));
SELECT set_of_books_id AS sob_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_sob_id
AND enabled_flag = 'Y';
SELECT retirement_prorate_convention,
use_stl_retirements_flag,
stl_method_code,
stl_life_in_months
INTO
l_ret_prorate_convention,
l_use_stl_retirements_flag,
l_stl_method_code,
l_stl_life_in_months
FROM fa_category_book_defaults, fa_additions_b a
WHERE book_type_code = l_asset_hdr_rec.book_type_code
and category_id = a.asset_category_id
and a.asset_id = l_asset_hdr_rec.asset_id
and l_asset_fin_rec.Date_Placed_In_Service between start_dpis and
nvl(end_dpis,l_asset_fin_rec.Date_Placed_In_Service);
select prorate_date
into l_ret_prorate_date
from fa_calendar_periods cp,
fa_conventions conv
where conv.prorate_convention_code = l_asset_retire_rec.retirement_prorate_convention
and conv.start_date <= l_asset_retire_rec.date_retired
and conv.end_date >= l_asset_retire_rec.date_retired
and cp.calendar_type = l_prorate_calendar
and conv.prorate_date >= cp.start_date
and conv.prorate_date <= cp.end_date;
select MAX(transaction_date_entered)
into l_latest_reval_date
from fa_transaction_headers
where asset_id = l_asset_hdr_rec.asset_id
and book_type_code = l_asset_hdr_rec.book_type_code
and transaction_type_code in ('REVALUATION');
select fa_transaction_headers_s.nextval
into l_trans_rec.transaction_header_id
from dual;
select fa_retirements_s.nextval
into l_asset_retire_rec.retirement_id
from dual;
l_trans_rec.who_info.last_update_date := sysdate;
select br1.avg_exchange_rate
into l_rate
from fa_mc_books_rates br1
where br1.asset_id = l_asset_hdr_rec.asset_id
and br1.book_type_code = l_asset_hdr_rec.book_type_code
and br1.set_of_books_id = l_asset_hdr_rec.set_of_books_id
and br1.transaction_header_id =
(select max(br2.transaction_header_id)
from fa_mc_books_rates br2
where br2.asset_id = l_asset_hdr_rec.asset_id
and br2.book_type_code = l_asset_hdr_rec.book_type_code
and br2.set_of_books_id = l_asset_hdr_rec.set_of_books_id);
SELECT fa_transaction_headers_s.nextval
into l_dist_trans_rec.transaction_header_id /* 3513319 */
FROM dual; /* Do we need this ???? dist th_id is initialized again below */
l_dist_trans_rec.who_info.last_update_date := sysdate;
SELECT fa_transaction_headers_s.nextval
into l_dist_trans_rec.transaction_header_id
FROM dual;
l_dist_trans_rec.who_info.last_update_date := sysdate;
select bk.transaction_header_id_in
,bk.Allowed_Deprn_Limit_Amount
,bk.date_effective
,itc.basis_reduction_rate
,ce.limit
,lk.lookup_type
from fa_books bk
,fa_itc_rates itc
,fa_ceilings ce
,fa_lookups_b lk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book_type_code
and bk.date_ineffective is null
and bk.itc_amount_id = itc.itc_amount_id(+)
and bk.ceiling_name = ce.ceiling_name(+)
and bk.date_placed_in_service between
nvl(ce.start_date, bk.date_placed_in_service)
and nvl(ce.end_date, bk.date_placed_in_service)
and bk.ceiling_name = lk.lookup_code(+)
and p_mrc_sob_type_code = 'P'
UNION
select /*+ INDEX (bk fa_mc_books_n1)*/
bk.transaction_header_id_in
,bk.Allowed_Deprn_Limit_Amount
,bk.date_effective
,itc.basis_reduction_rate
,ce.limit
,lk.lookup_type
from fa_mc_books bk
,fa_itc_rates itc
,fa_ceilings ce
,fa_lookups_b lk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book_type_code
and bk.date_ineffective is null
and bk.itc_amount_id = itc.itc_amount_id(+)
and bk.ceiling_name = ce.ceiling_name(+)
and bk.date_placed_in_service between
nvl(ce.start_date, bk.date_placed_in_service)
and nvl(ce.end_date, bk.date_placed_in_service)
and bk.ceiling_name = lk.lookup_code(+)
and p_mrc_sob_type_code <> 'P'
and bk.set_of_books_id = p_sob_id;
select cbd.percent_salvage_value
,cbd.use_deprn_limits_flag
,cbd.allowed_deprn_limit
,cbd.special_deprn_limit_amount
from fa_additions_b fad
,fa_category_book_defaults cbd
where fad.asset_id = p_asset_id
and cbd.category_id = fad.asset_category_id
and cbd.book_type_code = p_book_type_code
and p_dpis
between cbd.start_dpis
and nvl(cbd.end_dpis,to_date('31-12-4712','DD-MM-YYYY'));
select 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,
date_ineffective,
assigned_to,
transaction_header_id_out,
transaction_units,
retirement_id,
last_update_login
from fa_distribution_history
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and date_ineffective is null;
select deprn_reserve -- ENERGY
from fa_books_summary -- ENERGY
where asset_id = p_asset_id -- ENERGY
and book_type_code = p_book_type_code -- ENERGY
and period_counter = p_period_rec.period_counter -- ENERGY
and transaction_header_id_out is null; -- ENERGY
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_trans_rec.who_info.last_update_date := sysdate;
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'do fa_transaction_headers_pkg.insert_row', '', p_log_level_rec => p_log_level_rec); end if;
fa_transaction_headers_pkg.insert_row
(x_rowid => l_th_rowid,
x_transaction_header_id => l_trans_rec.transaction_header_id,
x_book_type_code => l_asset_hdr_rec.book_type_code,
x_asset_id => l_asset_hdr_rec.asset_id,
x_transaction_type_code => l_trans_rec.transaction_type_code,
x_transaction_date_entered => l_trans_rec.transaction_date_entered,
x_date_effective => l_trans_rec.who_info.creation_date,
x_last_update_date => l_trans_rec.who_info.last_update_date,
x_last_updated_by => l_trans_rec.who_info.last_updated_by,
x_transaction_name => l_trans_rec.transaction_name,
x_invoice_transaction_id => g_inv_trans_rec.invoice_transaction_id,
x_source_transaction_Header_id => l_trans_rec.source_transaction_header_id,
x_mass_reference_id => l_trans_rec.mass_reference_id,
x_last_Update_login => l_trans_rec.who_info.last_update_login,
x_transaction_subtype => null, -- l_trans_rec.transaction_subtype
x_Attribute1 => l_trans_rec.desc_flex.attribute1,
x_Attribute2 => l_trans_rec.desc_flex.attribute2,
x_Attribute3 => l_trans_rec.desc_flex.attribute3,
x_Attribute4 => l_trans_rec.desc_flex.attribute4,
x_Attribute5 => l_trans_rec.desc_flex.attribute5,
x_Attribute6 => l_trans_rec.desc_flex.attribute6,
x_Attribute7 => l_trans_rec.desc_flex.attribute7,
x_Attribute8 => l_trans_rec.desc_flex.attribute8,
x_Attribute9 => l_trans_rec.desc_flex.attribute9,
x_Attribute10 => l_trans_rec.desc_flex.attribute10,
x_Attribute11 => l_trans_rec.desc_flex.attribute11,
x_Attribute12 => l_trans_rec.desc_flex.attribute12,
x_Attribute13 => l_trans_rec.desc_flex.attribute13,
x_Attribute14 => l_trans_rec.desc_flex.attribute14,
x_Attribute15 => l_trans_rec.desc_flex.attribute15,
x_attribute_category_code => l_trans_rec.desc_flex.attribute_category_code,
x_transaction_key => 'R', -- l_trans_rec.transaction_key
x_mass_transaction_id => l_trans_rec.mass_transaction_id,
x_event_id => l_trans_rec.event_id,
x_calling_interface => l_trans_rec.calling_interface,
x_return_status => l_status,
x_calling_fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'do fa_retirements_pkg.insert_row', '', p_log_level_rec => p_log_level_rec);
fa_retirements_pkg.insert_row
(x_rowid => l_asset_retire_rec.detail_info.row_id,
X_Retirement_Id => l_asset_retire_rec.retirement_id,
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_Transaction_Header_Id_In => l_trans_rec.transaction_header_id,
X_Date_Retired => l_asset_retire_rec.date_retired,
X_Date_Effective => l_trans_rec.who_info.creation_date,
X_Cost_Retired => l_asset_retire_rec.cost_retired,
X_Status => l_asset_retire_rec.status,-- ? need to check
X_Last_Update_Date => l_trans_rec.who_info.last_update_date,
X_Last_Updated_By => l_trans_rec.who_info.last_updated_by,
X_Ret_Prorate_Convention => l_asset_retire_rec.retirement_prorate_convention,
X_Transaction_Header_Id_Out => NULL,
X_Units => l_asset_retire_rec.units_retired,
X_Cost_Of_Removal => l_asset_retire_rec.cost_of_removal,
X_Nbv_Retired => l_asset_retire_rec.detail_info.nbv_retired,
X_Gain_Loss_Amount => l_asset_retire_rec.detail_info.gain_loss_amount,
X_Proceeds_Of_Sale => l_asset_retire_rec.proceeds_of_sale,
X_Gain_Loss_Type_Code => l_asset_retire_rec.detail_info.gain_loss_type_code,
X_Retirement_Type_Code => l_asset_retire_rec.retirement_type_code,
X_Itc_Recaptured => l_asset_retire_rec.detail_info.itc_recaptured,
X_Itc_Recapture_Id => l_asset_retire_rec.detail_info.itc_recapture_id,
X_Reference_Num => l_asset_retire_rec.reference_num,
X_Sold_To => l_asset_retire_rec.sold_to,
X_Trade_In_Asset_Id => l_asset_retire_rec.trade_in_asset_id,
X_Stl_Method_Code => l_asset_retire_rec.detail_info.stl_method_code,
X_Stl_Life_In_Months => l_asset_retire_rec.detail_info.stl_life_in_months,
X_Stl_Deprn_Amount => l_asset_retire_rec.detail_info.stl_deprn_amount,
X_Created_By => l_trans_rec.who_info.created_by,
X_Creation_Date => l_trans_rec.who_info.creation_date,
X_Last_Update_Login => l_trans_rec.who_info.last_update_login,
X_Attribute1 => l_asset_retire_rec.desc_flex.attribute1,
X_Attribute2 => l_asset_retire_rec.desc_flex.attribute2,
X_Attribute3 => l_asset_retire_rec.desc_flex.attribute3,
X_Attribute4 => l_asset_retire_rec.desc_flex.attribute4,
X_Attribute5 => l_asset_retire_rec.desc_flex.attribute5,
X_Attribute6 => l_asset_retire_rec.desc_flex.attribute6,
X_Attribute7 => l_asset_retire_rec.desc_flex.attribute7,
X_Attribute8 => l_asset_retire_rec.desc_flex.attribute8,
X_Attribute9 => l_asset_retire_rec.desc_flex.attribute9,
X_Attribute10 => l_asset_retire_rec.desc_flex.attribute10,
X_Attribute11 => l_asset_retire_rec.desc_flex.attribute11,
X_Attribute12 => l_asset_retire_rec.desc_flex.attribute12,
X_Attribute13 => l_asset_retire_rec.desc_flex.attribute13,
X_Attribute14 => l_asset_retire_rec.desc_flex.attribute14,
X_Attribute15 => l_asset_retire_rec.desc_flex.attribute15,
X_Attribute_Category_Code => l_asset_retire_rec.desc_flex.attribute_category_code,
X_Reval_Reserve_Retired => l_asset_retire_rec.detail_info.reval_reserve_retired,
X_Unrevalued_Cost_Retired => l_asset_retire_rec.detail_info.unrevalued_cost_retired,
X_Recognize_Gain_Loss => l_asset_retire_rec.recognize_gain_loss,
X_Recapture_Reserve_Flag => l_asset_retire_rec.recapture_reserve_flag,
X_Limit_Proceeds_Flag => l_asset_retire_rec.limit_proceeds_flag,
X_Terminal_Gain_Loss => l_asset_retire_rec.terminal_gain_loss,
X_Reserve_Retired => l_asset_retire_rec.reserve_retired,
X_Eofy_Reserve => l_asset_retire_rec.eofy_reserve,
X_Reduction_Rate => l_asset_retire_rec.reduction_rate,
X_Recapture_Amount => l_asset_retire_rec.detail_info.recapture_amount,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
x_calling_fn => l_calling_fn,
p_log_level_rec => p_log_level_rec);
SELECT nvl(rate_in_use,0)
INTO l_rate_in_use
FROM fa_books
WHERE asset_id = l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.Book_Type_Code
AND transaction_header_id_out is null;
select prorate_date
into l_ret_prorate_date
from fa_calendar_periods cp,
fa_conventions conv
where conv.prorate_convention_code = l_asset_retire_rec.retirement_prorate_convention
and conv.start_date <= l_asset_retire_rec.date_retired
and conv.end_date >= l_asset_retire_rec.date_retired
and cp.calendar_type = fa_cache_pkg.fazcbc_record.prorate_calendar
and conv.prorate_date >= cp.start_date
and conv.prorate_date <= cp.end_date;
Select decode (count(*),0,l_asset_fin_rec.period_counter_fully_extended,null)
INTO l_period_fully_extend
from FA_DEPRN_PERIODS DP1
where DP1.book_type_code = l_asset_hdr_rec.Book_Type_Code and
DP1.period_counter = l_asset_fin_rec.period_counter_fully_extended and
DP1.CALENDAR_PERIOD_CLOSE_DATE > l_ret_prorate_date;
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'do fa_books_pkg.update_row', '', p_log_level_rec => p_log_level_rec); end if;
fa_books_pkg.update_row (
X_Rowid => NULL,
X_Book_Type_Code => l_asset_hdr_rec.Book_Type_Code,
X_Asset_Id => l_asset_hdr_rec.Asset_Id,
X_Date_Placed_In_Service => l_asset_fin_rec.Date_Placed_In_Service,
X_Date_Effective => bk_rec.Date_Effective,
X_Deprn_Start_Date => l_asset_fin_rec.Deprn_Start_Date,
X_Deprn_Method_Code => l_asset_fin_rec.Deprn_Method_Code,
X_Life_In_Months => l_asset_fin_rec.Life_In_Months,
X_Rate_Adjustment_Factor => l_asset_fin_rec.Rate_Adjustment_Factor,
X_Adjusted_Cost => l_asset_fin_rec.Adjusted_Cost,
X_Cost => l_asset_fin_rec.Cost,
X_Original_Cost => l_asset_fin_rec.Original_Cost,
X_Salvage_Value => l_asset_fin_rec.Salvage_Value,
X_Prorate_Convention_Code => l_asset_fin_rec.Prorate_Convention_Code,
X_Prorate_Date => l_asset_fin_rec.Prorate_Date,
X_Cost_Change_Flag => l_asset_fin_rec.Cost_Change_Flag,
X_Adjustment_Required_Status => l_asset_fin_rec.Adjustment_Required_Status,
X_Capitalize_Flag => l_asset_fin_rec.Capitalize_Flag,
X_Retirement_Pending_Flag => l_retirement_pending_flag,
X_Depreciate_Flag => l_asset_fin_rec.Depreciate_Flag,
X_Disabled_Flag => l_asset_fin_rec.Disabled_Flag, --HH
X_Last_Update_Date => l_trans_rec.who_info.Last_Update_Date,
X_Last_Updated_By => l_trans_rec.who_info.Last_Updated_By,
X_Date_Ineffective => l_trans_rec.who_info.creation_date,
X_Transaction_Header_Id_In => bk_rec.Transaction_Header_Id_In,
X_Transaction_Header_Id_Out => l_trans_rec.Transaction_Header_Id,
X_Itc_Amount_Id => l_asset_fin_rec.Itc_Amount_Id,
X_Itc_Amount => l_asset_fin_rec.Itc_Amount,
X_Retirement_Id => l_asset_retire_rec.Retirement_Id,
X_Tax_Request_Id => l_asset_fin_rec.Tax_Request_Id,
X_Itc_Basis => l_asset_fin_rec.Itc_Basis,
X_Basic_Rate => l_asset_fin_rec.Basic_Rate,
X_Adjusted_Rate => l_asset_fin_rec.Adjusted_Rate,
X_Bonus_Rule => l_asset_fin_rec.Bonus_Rule,
X_Ceiling_Name => l_asset_fin_rec.Ceiling_Name,
X_Recoverable_Cost => l_asset_fin_rec.Recoverable_Cost,
X_Last_Update_Login => l_trans_rec.who_info.Last_Update_Login,
X_Adjusted_Capacity => l_asset_fin_rec.Adjusted_Capacity,
X_Fully_Rsvd_Revals_Counter => l_asset_fin_rec.Fully_Rsvd_Revals_Counter,
X_Idled_Flag => l_asset_fin_rec.Idled_Flag,
X_Period_Counter_Capitalized => l_asset_fin_rec.Period_Counter_Capitalized,
X_PC_Fully_Reserved => l_asset_fin_rec.Period_Counter_Fully_Reserved,
X_Period_Counter_Fully_Retired => l_Period_Counter_Fully_Ret,
X_Production_Capacity => l_asset_fin_rec.Production_Capacity,
X_Reval_Amortization_Basis => l_asset_fin_rec.Reval_Amortization_Basis,
X_Reval_Ceiling => l_asset_fin_rec.Reval_Ceiling,
X_Unit_Of_Measure => l_asset_fin_rec.Unit_Of_Measure,
X_Unrevalued_Cost => l_asset_fin_rec.Unrevalued_Cost,
X_Annual_Deprn_Rounding_Flag => 'RET',
-- X_Percent_Salvage_Value => l_asset_fin_rec.Percent_Salvage_Value,
-- X_Allowed_Deprn_Limit => l_asset_fin_rec.allowed_deprn_limit,
-- X_Allowed_Deprn_Limit_Amount => l_asset_fin_rec.allowed_deprn_limit_amount,
X_Period_Counter_Life_Complete => l_asset_fin_rec.Period_Counter_Life_Complete,
-- X_Adjusted_Recoverable_Cost => l_asset_fin_rec.Adjusted_Recoverable_Cost,
X_Group_Asset_Id => l_asset_fin_rec.Group_Asset_ID,
X_salvage_type => l_asset_fin_rec.salvage_type,
X_deprn_limit_type => l_asset_fin_rec.deprn_limit_type,
X_over_depreciate_option => l_asset_fin_rec.over_depreciate_option,
X_super_group_id => l_asset_fin_rec.super_group_id,
X_reduction_rate => l_asset_retire_rec.reduction_rate,
X_reduce_addition_flag => l_asset_fin_rec.reduce_addition_flag,
X_reduce_adjustment_flag => l_asset_fin_rec.reduce_adjustment_flag,
X_reduce_retirement_flag => l_asset_fin_rec.reduce_retirement_flag,
X_recognize_gain_loss => l_asset_fin_rec.recognize_gain_loss,
X_recapture_reserve_flag => l_asset_fin_rec.recapture_reserve_flag,
X_limit_proceeds_flag => l_asset_fin_rec.limit_proceeds_flag,
X_terminal_gain_loss => l_asset_fin_rec.terminal_gain_loss,
X_tracking_method => l_asset_fin_rec.tracking_method,
X_allocate_to_fully_rsv_flag => l_asset_fin_rec.allocate_to_fully_rsv_flag,
X_allocate_to_fully_ret_flag => l_asset_fin_rec.allocate_to_fully_ret_flag,
X_exclude_fully_rsv_flag => l_asset_fin_rec.exclude_fully_rsv_flag,
X_excess_allocation_option => l_asset_fin_rec.excess_allocation_option,
X_depreciation_option => l_asset_fin_rec.depreciation_option,
X_member_rollup_flag => l_asset_fin_rec.member_rollup_flag,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn,
X_nbv_at_switch => l_asset_fin_rec.nbv_at_switch , -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy Start
X_prior_deprn_limit_type => l_asset_fin_rec.prior_deprn_limit_type ,
X_prior_deprn_limit_amount => l_asset_fin_rec.prior_deprn_limit_amount ,
X_prior_deprn_limit => l_asset_fin_rec.prior_deprn_limit ,
X_period_counter_fully_rsrved => l_asset_fin_rec.period_counter_fully_reserved ,
X_extended_depreciation_period => l_asset_fin_rec.extended_depreciation_period ,
X_prior_deprn_method => l_asset_fin_rec.prior_deprn_method ,
X_prior_life_in_months => l_asset_fin_rec.prior_life_in_months ,
X_prior_basic_rate => l_asset_fin_rec.prior_basic_rate ,
X_prior_adjusted_rate => l_asset_fin_rec.prior_adjusted_rate -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy End
, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'do fa_books_pkg.insert_row', '', p_log_level_rec => p_log_level_rec); end if;
fa_books_pkg.insert_row
(X_Rowid => l_bk_rowid,
X_Book_Type_Code => l_asset_hdr_rec.book_type_code,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_Date_Placed_In_Service => l_asset_fin_rec.date_placed_in_service,
X_Date_Effective => l_trans_rec.who_info.last_update_date,
X_Deprn_Start_Date => l_asset_fin_rec.deprn_start_date,
X_Deprn_Method_Code => l_asset_fin_rec.deprn_method_code,
X_Life_In_Months => l_asset_fin_rec.life_in_months,
X_Rate_Adjustment_Factor => l_asset_fin_rec.rate_adjustment_factor,
X_Adjusted_Cost => l_adjusted_cost_new,
X_Cost => l_cost_new,
X_Original_Cost => l_asset_fin_rec.original_cost,
X_Salvage_Value => l_salvage_value_new,
X_Prorate_Convention_Code => l_asset_fin_rec.prorate_convention_code,
X_Prorate_Date => l_asset_fin_rec.prorate_date,
X_Cost_Change_Flag => l_asset_fin_rec.cost_change_flag,
X_Adjustment_Required_Status => l_asset_fin_rec.adjustment_required_status,
X_Capitalize_Flag => l_asset_fin_rec.capitalize_flag,
X_Retirement_Pending_Flag => l_retirement_pending_flag,
X_Depreciate_Flag => l_asset_fin_rec.depreciate_flag,
X_Disabled_Flag => l_asset_fin_rec.Disabled_Flag, --HH
X_Last_Update_Date => l_trans_rec.who_info.last_update_date,
X_Last_Updated_By => l_trans_rec.who_info.last_updated_by,
X_Date_Ineffective => NULL,
X_Transaction_Header_Id_In => l_trans_rec.transaction_header_id,
X_Transaction_Header_Id_Out => NULL,
X_Itc_Amount_Id => l_asset_fin_rec.itc_amount_id,
X_Itc_Amount => l_asset_fin_rec.itc_amount,
X_Retirement_Id => NULL,
X_Tax_Request_Id => l_asset_fin_rec.tax_request_id,
X_Itc_Basis => l_asset_fin_rec.itc_basis,
X_Basic_Rate => l_asset_fin_rec.basic_rate,
X_Adjusted_Rate => l_asset_fin_rec.adjusted_rate,
X_Bonus_Rule => l_asset_fin_rec.bonus_rule,
X_Ceiling_Name => l_asset_fin_rec.ceiling_name,
X_Recoverable_Cost => l_recoverable_cost_new,
X_Last_Update_Login => l_trans_rec.who_info.last_update_login,
X_Adjusted_Capacity => l_asset_fin_rec.adjusted_capacity,
X_Fully_Rsvd_Revals_Counter => l_asset_fin_rec.fully_rsvd_revals_counter,
X_Idled_Flag => l_asset_fin_rec.idled_flag,
X_Period_Counter_Capitalized => l_asset_fin_rec.period_counter_capitalized,
X_PC_Fully_Reserved => l_asset_fin_rec.period_counter_fully_reserved,
X_Period_Counter_Fully_Retired => l_period_counter_fully_ret,
X_Production_Capacity => l_asset_fin_rec.production_capacity,
X_Reval_Amortization_Basis => l_reval_amort_basis_new,
X_Reval_Ceiling => l_asset_fin_rec.reval_ceiling,
X_Unit_Of_Measure => l_asset_fin_rec.unit_of_measure,
X_Unrevalued_Cost => l_unrevalued_cost_new,
--X_Annual_Deprn_Rounding_Flag => l_deprn_rounding_flag,
X_Annual_Deprn_Rounding_Flag => 'RET',
X_Percent_Salvage_Value => l_asset_fin_rec.percent_salvage_value,
X_Allowed_Deprn_Limit => l_asset_fin_rec.allowed_deprn_limit,
X_Allowed_Deprn_Limit_Amount => l_asset_fin_rec.allowed_deprn_limit_amount,
X_Period_Counter_Life_Complete => l_asset_fin_rec.period_counter_life_complete,
X_Adjusted_Recoverable_Cost => l_asset_fin_rec.adjusted_recoverable_cost,
X_Short_Fiscal_Year_Flag => l_asset_fin_rec.short_fiscal_year_flag,
X_Conversion_Date => l_asset_fin_rec.conversion_date,
X_Orig_Deprn_Start_Date => l_asset_fin_rec.orig_deprn_start_date,
X_Remaining_Life1 => l_asset_fin_rec.remaining_life1,
X_Remaining_Life2 => l_asset_fin_rec.remaining_life2,
X_Old_Adj_Cost => l_asset_fin_rec.old_adjusted_cost,
X_Formula_Factor => l_asset_fin_rec.formula_factor,
X_gf_Attribute1 => l_asset_fin_rec.global_attribute1,
X_gf_Attribute2 => l_asset_fin_rec.global_attribute2,
X_gf_Attribute3 => l_asset_fin_rec.global_attribute3,
X_gf_Attribute4 => l_asset_fin_rec.global_attribute4,
X_gf_Attribute5 => l_asset_fin_rec.global_attribute5,
X_gf_Attribute6 => l_asset_fin_rec.global_attribute6,
X_gf_Attribute7 => l_asset_fin_rec.global_attribute7,
X_gf_Attribute8 => l_asset_fin_rec.global_attribute8,
X_gf_Attribute9 => l_asset_fin_rec.global_attribute9,
X_gf_Attribute10 => l_asset_fin_rec.global_attribute10,
X_gf_Attribute11 => l_asset_fin_rec.global_attribute11,
X_gf_Attribute12 => l_asset_fin_rec.global_attribute12,
X_gf_Attribute13 => l_asset_fin_rec.global_attribute13,
X_gf_Attribute14 => l_asset_fin_rec.global_attribute14,
X_gf_Attribute15 => l_asset_fin_rec.global_attribute15,
X_gf_Attribute16 => l_asset_fin_rec.global_attribute16,
X_gf_Attribute17 => l_asset_fin_rec.global_attribute17,
X_gf_Attribute18 => l_asset_fin_rec.global_attribute18,
X_gf_Attribute19 => l_asset_fin_rec.global_attribute19,
X_gf_Attribute20 => l_asset_fin_rec.global_attribute20,
X_global_attribute_category => l_asset_fin_rec.global_attribute_category,
X_group_asset_id => l_asset_fin_rec.group_asset_id,
X_salvage_type => l_asset_fin_rec.salvage_type,
X_deprn_limit_type => l_asset_fin_rec.deprn_limit_type,
X_over_depreciate_option => l_asset_fin_rec.over_depreciate_option,
X_super_group_id => l_asset_fin_rec.super_group_id,
X_reduction_rate => l_asset_fin_rec.reduction_rate,
X_reduce_addition_flag => l_asset_fin_rec.reduce_addition_flag,
X_reduce_adjustment_flag => l_asset_fin_rec.reduce_adjustment_flag,
X_reduce_retirement_flag => l_asset_fin_rec.reduce_retirement_flag,
X_recognize_gain_loss => l_asset_fin_rec.recognize_gain_loss,
X_recapture_reserve_flag => l_asset_fin_rec.recapture_reserve_flag,
X_limit_proceeds_flag => l_asset_fin_rec.limit_proceeds_flag,
X_terminal_gain_loss => l_asset_fin_rec.terminal_gain_loss,
X_exclude_proceeds_from_basis => l_asset_fin_rec.exclude_proceeds_from_basis,
X_retirement_deprn_option => l_asset_fin_rec.retirement_deprn_option,
X_tracking_method => l_asset_fin_rec.tracking_method,
X_allocate_to_fully_rsv_flag => l_asset_fin_rec.allocate_to_fully_rsv_flag,
X_allocate_to_fully_ret_flag => l_asset_fin_rec.allocate_to_fully_ret_flag,
X_exclude_fully_rsv_flag => l_asset_fin_rec.exclude_fully_rsv_flag,
X_excess_allocation_option => l_asset_fin_rec.excess_allocation_option,
X_depreciation_option => l_asset_fin_rec.depreciation_option,
X_member_rollup_flag => l_asset_fin_rec.member_rollup_flag,
X_ytd_proceeds => nvl(l_asset_fin_rec.ytd_proceeds, 0) +
nvl(l_asset_retire_rec.proceeds_of_sale, 0),
X_ltd_proceeds => nvl(l_asset_fin_rec.ltd_proceeds, 0) +
nvl(l_asset_retire_rec.proceeds_of_sale, 0),
X_eofy_reserve => l_asset_fin_rec.eofy_reserve,
X_terminal_gain_loss_amount => l_asset_fin_rec.terminal_gain_loss_amount,
X_ltd_cost_of_removal => nvl(l_asset_fin_rec.ltd_cost_of_removal, 0) +
nvl(l_asset_retire_rec.cost_of_removal, 0),
X_cash_generating_unit_id =>
l_asset_fin_rec.cash_generating_unit_id,
X_extended_deprn_flag => l_asset_fin_rec.extended_deprn_flag, -- Japan Tax phase3
X_extended_depreciation_period => l_asset_fin_rec.extended_depreciation_period, -- Japan Tax phase3
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Return_Status => l_status,
X_Calling_Fn => l_calling_fn,
X_nbv_at_switch => l_asset_fin_rec.nbv_at_switch , -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy Start
X_prior_deprn_limit_type => l_asset_fin_rec.prior_deprn_limit_type ,
X_prior_deprn_limit_amount => l_asset_fin_rec.prior_deprn_limit_amount ,
X_prior_deprn_limit => l_asset_fin_rec.prior_deprn_limit ,
X_period_counter_fully_rsrved => l_asset_fin_rec.period_counter_fully_reserved ,
X_prior_deprn_method => l_asset_fin_rec.prior_deprn_method ,
X_prior_life_in_months => l_asset_fin_rec.prior_life_in_months ,
X_prior_basic_rate => l_asset_fin_rec.prior_basic_rate ,
X_prior_adjusted_rate => l_asset_fin_rec.prior_adjusted_rate , -- Changes made as per the ER No.s 6606548 and 6606552 by Sbyreddy End
X_period_counter_fully_extend => l_period_fully_extend -- Bug 7576755 --bug 14286465
, p_log_level_rec => p_log_level_rec
);
UPDATE fa_books
SET rate_in_use = l_rate_in_use
WHERE asset_id = l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.Book_Type_Code
AND transaction_header_id_out is null;
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'do fa_distribution_history_pkg.update_row', '', p_log_level_rec => p_log_level_rec); end if;
fa_distribution_history_pkg.update_row
(null,
dh_rec.distribution_id,
dh_rec.book_type_code,
dh_rec.asset_id,
dh_rec.units_assigned,
dh_rec.date_effective,
dh_rec.code_combination_id,
dh_rec.location_id,
dh_rec.transaction_header_id_in,
l_trans_rec.who_info.last_update_date,
l_trans_rec.who_info.last_updated_by,
dh_rec.date_ineffective,
dh_rec.assigned_to,
dh_rec.transaction_header_id_out,
dh_rec.transaction_units,
l_asset_retire_rec.retirement_id,
l_trans_rec.who_info.last_update_login,
l_calling_fn, p_log_level_rec => p_log_level_rec);
select retirement_id
into lv_asset_retire_rec.retirement_id
from fa_retirements
where asset_id=lv_asset_hdr_rec.asset_id
and book_type_code=l_tax_book_tbl(l_tax_index)
and status = 'PENDING';
SELECT set_of_books_id AS sob_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_sob_id
AND enabled_flag = 'Y';
if not fa_xla_events_pvt.delete_transaction_event
(p_ledger_id => l_asset_hdr_rec.set_of_books_id,
p_transaction_header_id => l_asset_retire_rec.detail_info.transaction_header_id_in,
p_book_type_code => l_asset_hdr_rec.book_type_code,
p_asset_type => l_asset_type_rec.asset_type , -- bug 8630242
p_calling_fn => 'fa_retirement_pub.undo_all_books_retirement'
,p_log_level_rec => p_log_level_rec) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;
delete event for secondary ledger*/
l_secondary_sob_id := FA_XLA_EVENTS_PVT.get_secondary_sob_id(l_asset_hdr_rec.book_type_code);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'before delete', l_asset_retire_rec.detail_info.transaction_header_id_in, p_log_level_rec => p_log_level_rec); end if;
if not fa_xla_events_pvt.delete_transaction_event
(p_ledger_id => sob_rec.sob_id,
p_transaction_header_id => l_asset_retire_rec.detail_info.transaction_header_id_in,
p_book_type_code => l_asset_hdr_rec.book_type_code,
p_asset_type => l_asset_type_rec.asset_type , -- bug 8630242,
p_secondary_sob_id => l_secondary_sob_id,
p_calling_fn => 'fa_ret_pub.undo_all_books_retirement.secondary'
,p_log_level_rec => p_log_level_rec) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;
select transaction_header_id_out
,transaction_units
,units_assigned
from fa_distribution_history
where retirement_id = l_asset_retire_rec.retirement_id;
select rowid
from fa_adjustments
where asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id = transfer_id
and source_type_code = 'TRANSFER'
and adjustment_type in ('COST', 'RESERVE', 'REVAL RESERVE');
select invoice_transaction_id
from fa_transaction_headers
where transaction_header_id = c_thid;
select cost
from fa_books
where asset_id = c_asset_id
and book_type_code = c_book
and retirement_id = c_ret_id
and c_reporting_flag <> 'R'
union
select cost
from fa_mc_books
where asset_id = c_asset_id
and book_type_code = c_book
and retirement_id = c_ret_id
and c_reporting_flag = 'R';
DELETE FROM FA_MC_ASSET_INVOICES
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND INVOICE_TRANSACTION_ID_IN = l_invoice_transaction_id;
UPDATE FA_MC_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = '',
DATE_INEFFECTIVE = ''
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id;
DELETE FROM FA_ASSET_INVOICES
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND INVOICE_TRANSACTION_ID_IN = l_invoice_transaction_id;
UPDATE FA_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = '',
DATE_INEFFECTIVE = ''
WHERE ASSET_ID = l_asset_hdr_rec.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id;
DELETE FROM FA_INVOICE_TRANSACTIONS
WHERE INVOICE_TRANSACTION_ID = l_invoice_transaction_id;
update fa_books
set adjustment_required_status = 'NONE'
where asset_id = l_asset_fin_rec.group_asset_id
and book_type_code = l_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;
SELECT prev.units
INTO l_old_units
FROM fa_asset_history prev,
fa_retirements ret
WHERE prev.asset_id = l_asset_hdr_rec.asset_id
AND prev.transaction_header_id_out = ret.transaction_header_id_in
AND ret.retirement_id = l_asset_retire_rec.retirement_id;
fa_debug_pkg.add(l_calling_fn, 'FA_BOOKS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec);
FA_BOOKS_PKG.DELETE_ROW
(X_Transaction_Header_Id_In =>
l_asset_retire_rec.detail_info.transaction_header_id_in,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_BOOKS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_RETIREMENTS_PKG.DELETE_ROW
(X_Rowid => l_asset_retire_rec.detail_info.row_id,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_DISTRIBUTION_HISTORY_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_DISTRIBUTION_HISTORY_PKG.DELETE_ROW
(X_Asset_Id => l_asset_hdr_rec.Asset_Id,
X_Book_Type_Code=> l_asset_hdr_rec.Book_Type_Code,
X_Transaction_Header_Id => transfer_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_ASSET_HISTORY_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_ASSET_HISTORY_PKG.DELETE_ROW
(X_Transaction_Header_Id_In =>
l_asset_retire_rec.detail_info.transaction_header_id_in,
X_Asset_Id => l_asset_hdr_rec.Asset_Id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_ADDITIONS_PKG.UPDATE_UNITS', '', p_log_level_rec => p_log_level_rec); end if;
FA_ADDITIONS_PKG.UPDATE_UNITS
(X_Asset_Id => l_asset_hdr_rec.Asset_Id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_TRANSFER_DETAILS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_TRANSFER_DETAILS_PKG.DELETE_ROW
(X_Transfer_Header_Id => transfer_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_TRANSACTION_HEADERS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_TRANSACTION_HEADERS_PKG.DELETE_ROW -- delete PARTIAL RETIREMENT row
(X_Transaction_Header_Id =>
l_asset_retire_rec.detail_info.transaction_header_id_in,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_TRANSACTION_HEADERS_PKG.DELETE_ROW -- delete TRANSFER OUT row', '', p_log_level_rec => p_log_level_rec); end if;
FA_TRANSACTION_HEADERS_PKG.DELETE_ROW -- delete TRANSFER OUT row
(X_Transaction_Header_Id =>
transfer_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_ADJUSTMENTS_PKG.delete_row', '', p_log_level_rec => p_log_level_rec); end if;
FA_ADJUSTMENTS_PKG.delete_row(
X_Rowid => l_adj_rowid,
X_Asset_Id => l_asset_hdr_rec.asset_id,
X_Calling_Fn => l_calling_fn,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_BOOKS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_BOOKS_PKG.DELETE_ROW
(X_Transaction_Header_Id_In =>
l_asset_retire_rec.detail_info.transaction_header_id_in,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_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, 'FA_RETIREMENTS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec);
FA_RETIREMENTS_PKG.DELETE_ROW
(X_Rowid => l_asset_retire_rec.detail_info.row_id,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'FA_TRANSACTION_HEADERS_PKG.DELETE_ROW', '', p_log_level_rec => p_log_level_rec); end if;
FA_TRANSACTION_HEADERS_PKG.DELETE_ROW
(X_Transaction_Header_Id =>
l_asset_retire_rec.detail_info.transaction_header_id_in,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select max(th.transaction_date_entered)
from fa_deprn_periods dp
,fa_transaction_headers th
,fa_books bk
where dp.book_type_code = l_asset_hdr_rec.book_type_code
and dp.period_close_date is null
and bk.book_type_code = dp.book_type_code
and bk.asset_id = l_asset_hdr_rec.asset_id
and th.date_effective
between bk.date_effective
and nvl(bk.date_ineffective,sysdate)
and th.date_effective
between dp.period_open_date
and nvl(dp.period_close_date,sysdate)
and th.book_type_code = dp.book_type_code
and th.asset_id = bk.asset_id
and th.transaction_type_code like '%RETIREMENT';
fa_debug_pkg.add(l_calling_fn, 'selected tax book: ', l_tax_book_tbl(l_tax_index));
select max(retirement_id)
into lv_asset_retire_rec.retirement_id
from fa_retirements
where asset_id=l_asset_hdr_rec.asset_id
and book_type_code=lv_asset_hdr_rec.book_type_code;
SELECT set_of_books_id AS sob_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_sob_id
AND enabled_flag = 'Y';
select fa_transaction_headers_s.nextval
into l_trans_rec.transaction_header_id
from dual;
l_trans_rec.who_info.last_update_date := sysdate;
select invoice_transaction_id
from fa_transaction_headers
where transaction_header_id = c_thid;
l_asset_retire_rec.status := 'DELETED';
l_trans_rec.who_info.last_update_date := sysdate;
if p_log_level_rec.statement_level then fa_debug_pkg.add(l_calling_fn, 'do fa_transaction_headers_pkg.insert_row', '', p_log_level_rec => p_log_level_rec); end if;
fa_transaction_headers_pkg.insert_row
(x_rowid => l_rowid,
x_transaction_header_id => l_trans_rec.transaction_header_id,
x_book_type_code => l_asset_hdr_rec.book_type_code,
x_asset_id => l_asset_hdr_rec.asset_id,
x_transaction_type_code => l_trans_rec.transaction_type_code,
x_transaction_date_entered => l_trans_rec.transaction_date_entered,
x_date_effective => l_trans_rec.who_info.creation_date,
x_last_update_date => l_trans_rec.who_info.last_update_date,
x_last_updated_by => l_trans_rec.who_info.last_updated_by,
x_transaction_name => null, --l_trans_rec.transaction_name,
x_invoice_transaction_id => null,
x_source_transaction_Header_id => l_trans_rec.source_transaction_header_id,
x_mass_reference_id => l_trans_rec.mass_reference_id,
x_last_Update_login => l_trans_rec.who_info.last_update_login,
x_transaction_subtype => null, --l_trans_rec.transaction_subtype
x_Attribute1 => l_trans_rec.desc_flex.attribute1,
x_Attribute2 => l_trans_rec.desc_flex.attribute2,
x_Attribute3 => l_trans_rec.desc_flex.attribute3,
x_Attribute4 => l_trans_rec.desc_flex.attribute4,
x_Attribute5 => l_trans_rec.desc_flex.attribute5,
x_Attribute6 => l_trans_rec.desc_flex.attribute6,
x_Attribute7 => l_trans_rec.desc_flex.attribute7,
x_Attribute8 => l_trans_rec.desc_flex.attribute8,
x_Attribute9 => l_trans_rec.desc_flex.attribute9,
x_Attribute10 => l_trans_rec.desc_flex.attribute10,
x_Attribute11 => l_trans_rec.desc_flex.attribute11,
x_Attribute12 => l_trans_rec.desc_flex.attribute12,
x_Attribute13 => l_trans_rec.desc_flex.attribute13,
x_Attribute14 => l_trans_rec.desc_flex.attribute14,
x_Attribute15 => l_trans_rec.desc_flex.attribute15,
x_attribute_category_code => l_trans_rec.desc_flex.attribute_category_code,
x_transaction_key => 'R', -- l_trans_rec.transaction_key
x_mass_transaction_id => l_trans_rec.mass_transaction_id,
x_event_id => l_trans_rec.event_id,
x_calling_interface => l_trans_rec.calling_interface,
x_return_status => l_status,
x_calling_fn => l_calling_fn
, p_log_level_rec => p_log_level_rec);
fa_debug_pkg.add(l_calling_fn, 'do fa_retirements_pkg.update_row', '', p_log_level_rec => p_log_level_rec);
FA_RETIREMENTS_PKG.UPDATE_ROW (
X_Rowid => l_asset_retire_rec.detail_info.row_id,
X_Retirement_Id => l_asset_retire_rec.Retirement_Id,
X_Book_Type_Code => l_asset_hdr_rec.Book_Type_Code,
X_Asset_Id => l_asset_hdr_rec.Asset_Id,
X_Transaction_Header_Id_In => l_asset_retire_rec.detail_info.Transaction_Header_Id_in,
X_Date_Retired => l_asset_retire_rec.Date_Retired,
X_Date_Effective => null,
X_Cost_Retired => l_asset_retire_rec.Cost_Retired,
X_Status => l_asset_retire_rec.status,
X_Last_Update_Date => l_trans_rec.who_info.last_update_date,
X_Last_Updated_By => l_trans_rec.who_info.last_updated_by,
X_Ret_Prorate_Convention => l_asset_retire_rec.retirement_prorate_convention,
X_Transaction_Header_Id_Out => l_trans_rec.transaction_header_id,
X_Units => l_asset_retire_rec.units_retired,
X_Cost_Of_Removal => l_asset_retire_rec.cost_of_removal,
X_Nbv_Retired => l_asset_retire_rec.detail_info.nbv_retired,
X_Gain_Loss_Amount => l_asset_retire_rec.detail_info.gain_loss_amount,
X_Proceeds_Of_Sale => l_asset_retire_rec.proceeds_of_sale,
X_Gain_Loss_Type_Code => l_asset_retire_rec.detail_info.gain_loss_type_code,
X_Retirement_Type_Code => l_asset_retire_rec.retirement_type_code,
X_Itc_Recaptured => l_asset_retire_rec.detail_info.itc_recaptured,
X_Itc_Recapture_Id => l_asset_retire_rec.detail_info.itc_recapture_id,
X_Reference_Num => l_asset_retire_rec.reference_num,
X_Sold_To => l_asset_retire_rec.sold_to,
X_Trade_In_Asset_Id => l_asset_retire_rec.trade_in_asset_id,
X_Stl_Method_Code => l_asset_retire_rec.detail_info.stl_method_code,
X_Stl_Life_In_Months => l_asset_retire_rec.detail_info.stl_life_in_months,
X_Stl_Deprn_Amount => l_asset_retire_rec.detail_info.stl_deprn_amount,
X_Last_Update_Login => l_trans_rec.who_info.last_update_login,
X_Attribute1 => l_asset_retire_rec.desc_flex.attribute1,
X_Attribute2 => l_asset_retire_rec.desc_flex.attribute2,
X_Attribute3 => l_asset_retire_rec.desc_flex.attribute3,
X_Attribute4 => l_asset_retire_rec.desc_flex.attribute4,
X_Attribute5 => l_asset_retire_rec.desc_flex.attribute5,
X_Attribute6 => l_asset_retire_rec.desc_flex.attribute6,
X_Attribute7 => l_asset_retire_rec.desc_flex.attribute7,
X_Attribute8 => l_asset_retire_rec.desc_flex.attribute8,
X_Attribute9 => l_asset_retire_rec.desc_flex.attribute9,
X_Attribute10 => l_asset_retire_rec.desc_flex.attribute10,
X_Attribute11 => l_asset_retire_rec.desc_flex.attribute11,
X_Attribute12 => l_asset_retire_rec.desc_flex.attribute12,
X_Attribute13 => l_asset_retire_rec.desc_flex.attribute13,
X_Attribute14 => l_asset_retire_rec.desc_flex.attribute14,
X_Attribute15 => l_asset_retire_rec.desc_flex.attribute15,
X_Attribute_Category_Code => l_asset_retire_rec.desc_flex.attribute_category_code,
X_Reval_Reserve_Retired => l_asset_retire_rec.detail_info.reval_reserve_retired,
X_Unrevalued_Cost_Retired => l_asset_retire_rec.detail_info.unrevalued_cost_retired,
X_Recognize_Gain_Loss => l_asset_retire_rec.recognize_gain_loss,
X_Recapture_Reserve_Flag => l_asset_retire_rec.recapture_reserve_flag,
X_Limit_Proceeds_Flag => l_asset_retire_rec.limit_proceeds_flag,
X_Terminal_Gain_Loss => l_asset_retire_rec.terminal_gain_loss,
X_Reserve_Retired => l_asset_retire_rec.reserve_retired,
X_Eofy_Reserve => l_asset_retire_rec.eofy_reserve,
X_Reduction_Rate => l_asset_retire_rec.reduction_rate,
X_Recapture_Amount => l_asset_retire_rec.detail_info.recapture_amount,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_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, 'selected tax book: ', l_tax_book_tbl(l_tax_index));
select retirement_id
into lv_asset_retire_rec.retirement_id
from fa_retirements
where asset_id=lv_asset_hdr_rec.asset_id
and book_type_code=l_tax_book_tbl(l_tax_index)
and status = 'REINSTATE';
SELECT set_of_books_id AS sob_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_sob_id
AND enabled_flag = 'Y';
if not fa_xla_events_pvt.delete_transaction_event
(p_ledger_id => l_asset_hdr_rec.set_of_books_id,
p_transaction_header_id => l_asset_retire_rec.detail_info.transaction_header_id_out,
p_book_type_code => l_asset_hdr_rec.book_type_code,
p_asset_type => l_asset_type_rec.asset_type, --bug 8643362
p_calling_fn => 'fa_retirement_pub.undo_all_books_reinstatement'
,p_log_level_rec => p_log_level_rec) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;
if not fa_xla_events_pvt.delete_transaction_event
(p_ledger_id => sob_rec.sob_id,
p_transaction_header_id => l_asset_retire_rec.detail_info.transaction_header_id_out,
p_book_type_code => l_asset_hdr_rec.book_type_code,
p_asset_type => l_asset_type_rec.asset_type, --bug 8643362
p_secondary_sob_id => l_secondary_sob_id,
p_calling_fn => 'fa_ret_pub.undo_all_books_reinstatement.secondary'
,p_log_level_rec => p_log_level_rec) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;
select invoice_transaction_id
from fa_transaction_headers
where transaction_header_id = c_thid;
DELETE FROM FA_MC_ASSET_INVOICES
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_IN = l_invoice_transaction_id
AND INVOICE_TRANSACTION_ID_OUT is null
AND SET_OF_BOOKS_ID = l_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = '',
DATE_INEFFECTIVE = ''
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id
AND FIXED_ASSETS_COST <> 0
AND SET_OF_BOOKS_ID = l_asset_hdr_rec.set_of_books_id;
UPDATE FA_MC_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = INVOICE_TRANSACTION_ID_IN,
DATE_INEFFECTIVE = DATE_EFFECTIVE
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id
AND FIXED_ASSETS_COST = 0
AND SET_OF_BOOKS_ID = l_asset_hdr_rec.set_of_books_id;
DELETE FROM FA_ASSET_INVOICES
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_IN = l_invoice_transaction_id
AND INVOICE_TRANSACTION_ID_OUT is null;
UPDATE FA_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = '',
DATE_INEFFECTIVE = ''
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id
AND FIXED_ASSETS_COST <> 0;
UPDATE FA_ASSET_INVOICES
SET INVOICE_TRANSACTION_ID_OUT = INVOICE_TRANSACTION_ID_IN,
DATE_INEFFECTIVE = DATE_EFFECTIVE
WHERE ASSET_ID = l_asset_retire_rec.detail_info.asset_id
AND INVOICE_TRANSACTION_ID_OUT = l_invoice_transaction_id
AND FIXED_ASSETS_COST = 0;
DELETE FROM FA_INVOICE_TRANSACTIONS
WHERE INVOICE_TRANSACTION_ID = l_invoice_transaction_id;
FA_TRANSACTION_HEADERS_PKG.DELETE_ROW
(X_Transaction_Header_Id => l_asset_retire_rec.detail_info.transaction_header_id_out,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
FA_RETIREMENTS_PKG.UPDATE_ROW(
X_Rowid => l_asset_retire_rec.detail_info.row_id,
X_Retirement_Id => l_asset_retire_rec.Retirement_Id,
X_Book_Type_Code => l_asset_retire_rec.detail_info.Book_Type_Code,
X_Asset_Id => l_asset_retire_rec.detail_info.Asset_Id,
X_Transaction_Header_Id_In => l_asset_retire_rec.detail_info.Transaction_Header_Id_in,
X_Date_Retired => l_asset_retire_rec.Date_Retired,
X_Date_Effective => l_trans_rec.who_info.creation_date,
X_Cost_Retired => l_asset_retire_rec.Cost_Retired,
X_Status => l_asset_retire_rec.status,
X_Last_Update_Date => l_trans_rec.who_info.last_update_date,
X_Last_Updated_By => l_trans_rec.who_info.last_updated_by,
X_Ret_Prorate_Convention => l_asset_retire_rec.retirement_prorate_convention,
X_Transaction_Header_Id_Out => FND_API.G_MISS_NUM,--bug fix 4088953
X_Units => l_asset_retire_rec.units_retired,
X_Cost_Of_Removal => l_asset_retire_rec.cost_of_removal,
X_Nbv_Retired => l_asset_retire_rec.detail_info.nbv_retired,
X_Gain_Loss_Amount => l_asset_retire_rec.detail_info.gain_loss_amount,
X_Proceeds_Of_Sale => l_asset_retire_rec.proceeds_of_sale,
X_Gain_Loss_Type_Code => l_asset_retire_rec.detail_info.gain_loss_type_code,
X_Retirement_Type_Code => l_asset_retire_rec.retirement_type_code,
X_Itc_Recaptured => l_asset_retire_rec.detail_info.itc_recaptured,
X_Itc_Recapture_Id => l_asset_retire_rec.detail_info.itc_recapture_id,
X_Reference_Num => l_asset_retire_rec.reference_num,
X_Sold_To => l_asset_retire_rec.sold_to,
X_Trade_In_Asset_Id => l_asset_retire_rec.trade_in_asset_id,
X_Stl_Method_Code => l_asset_retire_rec.detail_info.stl_method_code,
X_Stl_Life_In_Months => l_asset_retire_rec.detail_info.stl_life_in_months,
X_Stl_Deprn_Amount => l_asset_retire_rec.detail_info.stl_deprn_amount,
X_Last_Update_Login => l_trans_rec.who_info.last_update_login,
X_Attribute1 => l_asset_retire_rec.desc_flex.attribute1,
X_Attribute2 => l_asset_retire_rec.desc_flex.attribute2,
X_Attribute3 => l_asset_retire_rec.desc_flex.attribute3,
X_Attribute4 => l_asset_retire_rec.desc_flex.attribute4,
X_Attribute5 => l_asset_retire_rec.desc_flex.attribute5,
X_Attribute6 => l_asset_retire_rec.desc_flex.attribute6,
X_Attribute7 => l_asset_retire_rec.desc_flex.attribute7,
X_Attribute8 => l_asset_retire_rec.desc_flex.attribute8,
X_Attribute9 => l_asset_retire_rec.desc_flex.attribute9,
X_Attribute10 => l_asset_retire_rec.desc_flex.attribute10,
X_Attribute11 => l_asset_retire_rec.desc_flex.attribute11,
X_Attribute12 => l_asset_retire_rec.desc_flex.attribute12,
X_Attribute13 => l_asset_retire_rec.desc_flex.attribute13,
X_Attribute14 => l_asset_retire_rec.desc_flex.attribute14,
X_Attribute15 => l_asset_retire_rec.desc_flex.attribute15,
X_Attribute_Category_Code => l_asset_retire_rec.desc_flex.attribute_category_code,
X_Reval_Reserve_Retired => l_asset_retire_rec.detail_info.reval_reserve_retired,
X_Unrevalued_Cost_Retired => l_asset_retire_rec.detail_info.unrevalued_cost_retired,
X_Recognize_Gain_Loss => l_asset_retire_rec.recognize_gain_loss,
X_Recapture_Reserve_Flag => l_asset_retire_rec.recapture_reserve_flag,
X_Limit_Proceeds_Flag => l_asset_retire_rec.limit_proceeds_flag,
X_Terminal_Gain_Loss => l_asset_retire_rec.terminal_gain_loss,
X_Reserve_Retired => l_asset_retire_rec.reserve_retired,
X_Eofy_Reserve => l_asset_retire_rec.eofy_reserve,
X_Reduction_Rate => l_asset_retire_rec.reduction_rate,
X_Recapture_Amount => l_asset_retire_rec.detail_info.recapture_amount,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);
select th.transaction_date_entered
from fa_transaction_headers th
, fa_trx_references tr
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 = tr.member_transaction_header_id
and th.trx_reference_id = tr.trx_reference_id
and tr.member_asset_id = p_asset_hdr_rec.asset_id
and tr.book_type_code = p_asset_hdr_rec.book_type_code
and tr.transaction_type = 'GROUP CHANGE'
and nvl(th.amortization_start_date, th.transaction_date_entered) > l_asset_retire_rec.date_retired;
select distinct 'Y'
from fa_transaction_headers trx,fa_Deprn_periods prd
where trx.book_type_code = p_asset_hdr_rec.book_type_code
and trx.asset_id = p_asset_hdr_rec.asset_id
and trx.date_effective > prd.period_open_date
and prd.book_type_code = trx.book_type_code
and prd.period_close_date is null
and trx.transaction_key = 'ES'
and p_period_rec.calendar_period_open_date > l_asset_retire_rec.date_retired;
select distinct 'Y'
into l_trans_flag
from
fa_transaction_headers th,
fa_calendar_periods cp,
fa_book_controls bc,
fa_conventions con,
fa_additions_b ad,
fa_category_book_defaults cbd
where th.book_type_code = l_asset_hdr_rec.book_type_code
and th.asset_id = l_asset_hdr_rec.asset_id
and ad.asset_id = l_asset_hdr_rec.asset_id
and cbd.book_type_code = l_asset_hdr_rec.book_type_code
and cbd.category_id = ad.asset_category_id
and p_asset_fin_rec.Date_Placed_In_Service between cbd.start_dpis and
nvl(cbd.end_dpis,p_asset_fin_rec.Date_Placed_In_Service)
and con.prorate_convention_code = nvl(l_asset_retire_rec.retirement_prorate_convention, cbd.retirement_prorate_convention)
and l_asset_retire_rec.date_retired between con.start_date and con.end_date
and bc.book_type_code = th.book_type_code
and cp.calendar_type = bc.deprn_calendar
and con.prorate_date between cp.start_date and cp.end_date
and th.transaction_date_entered > cp.end_date
and th.transaction_type_code NOT IN ('ADDITION','TRANSFER IN')
and th.TRANSACTION_SUBTYPE = 'AMORTIZED';
select distinct 'Y'
into l_trans_flag
from
fa_calendar_periods cp,
fa_book_controls bc,
fa_conventions con,
fa_deprn_override ovr,
fa_deprn_periods fadp,
fa_additions_b ad,
fa_category_book_defaults cbd
where ovr.book_type_code = l_asset_hdr_rec.book_type_code
and ovr.asset_id = l_asset_hdr_rec.asset_id
and ovr.status = 'POSTED'
and fadp.book_type_code = l_asset_hdr_rec.book_type_code
and fadp.period_name = ovr.period_name
and ad.asset_id = l_asset_hdr_rec.asset_id
and cbd.book_type_code = l_asset_hdr_rec.book_type_code
and cbd.category_id = ad.asset_category_id
and p_asset_fin_rec.Date_Placed_In_Service between cbd.start_dpis and
nvl(cbd.end_dpis,p_asset_fin_rec.Date_Placed_In_Service)
and con.prorate_convention_code = nvl(l_asset_retire_rec.retirement_prorate_convention, cbd.retirement_prorate_convention)
and l_asset_retire_rec.date_retired between con.start_date and con.end_date
and bc.book_type_code = ovr.book_type_code
and cp.calendar_type = bc.deprn_calendar
and con.prorate_date between cp.start_date and cp.end_date
and fadp.calendar_period_close_date >= cp.end_date;
SELECT nvl(max(transaction_header_id),0)
INTO l_latest_ret_thid
FROM fa_transaction_headers
WHERE asset_id = l_asset_hdr_rec.asset_id
AND book_type_code = l_asset_hdr_rec.book_type_code
AND transaction_key = 'R'
AND transaction_type_code||'' like '%RETIREMENT';
SELECT SOURCE_LINE_ID
, FIXED_ASSETS_COST
FROM FA_ASSET_INVOICES
WHERE INVOICE_TRANSACTION_ID_IN = p_invoice_transaction_id
AND ASSET_INVOICE_ID = c_asset_invoice_id;
SELECT ASSET_INVOICE_ID
, FIXED_ASSETS_COST
, SOURCE_LINE_ID
FROM FA_ASSET_INVOICES
WHERE INVOICE_TRANSACTION_ID_OUT = p_invoice_transaction_id
AND ASSET_ID = c_asset_id;
l_inv_tbl.delete;
FA_TRANSACTION_HEADERS_PKG.UPDATE_ROW(
X_Rowid => p_rowid,
X_Invoice_Transaction_Id => l_inv_trans_rec.invoice_transaction_id,
X_Calling_Fn => l_calling_fn, p_log_level_rec => p_log_level_rec);