[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distribution_id,
units_assigned,
code_combination_id,
location_id,
assigned_to
from fa_distribution_history
where asset_id = p_asset_id
and transaction_header_id_out is null;
select nvl(reserve_transfer_amount, 0)
from fa_trx_references
where dest_asset_id = l_src_asset_hdr_rec.asset_id
and member_asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
order by trx_reference_id desc;
select fa_transaction_headers_s.nextval
into px_src_trans_rec.transaction_header_id
from dual;
delete from fa_adjustments_mrc_v
where asset_id = px_src_asset_hdr_rec.asset_id
and book_type_code = px_src_asset_hdr_rec.book_type_code
and transaction_header_id = px_src_trans_rec.transaction_header_id
and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and source_type_code = 'DEPRECIATION';
delete from fa_adjustments
where asset_id = px_src_asset_hdr_rec.asset_id
and book_type_code = px_src_asset_hdr_rec.book_type_code
and transaction_header_id = px_src_trans_rec.transaction_header_id
and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and source_type_code = 'DEPRECIATION';
l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_rsv_adj.selection_thid := 0;
l_rsv_adj.selection_retid := 0;
l_rsv_adj.last_update_date := px_src_trans_rec.transaction_date_entered;
px_src_trans_rec.who_info.last_update_date,
px_src_trans_rec.who_info.last_updated_by,
px_src_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
px_src_trans_rec.who_info.last_update_date,
px_src_trans_rec.who_info.last_updated_by,
px_src_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
update fa_adjustments
set track_member_flag = null
where transaction_header_id = l_trans_rec.transaction_header_id
and book_type_code = p_asset_hdr_rec.book_type_code;
select transaction_header_id
into l_trans_rec.transaction_header_id
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_type_code = 'TRANSFER'
and transaction_header_id > p_trans_rec.transaction_header_id;
update fa_adjustments_mrc_v
set track_member_flag = null
where transaction_header_id = l_trans_rec.transaction_header_id
and book_type_code = p_asset_hdr_rec.book_type_code;
select fa_transaction_headers_s.nextval
into px_dest_trans_rec.transaction_header_id
from dual;
delete from fa_adjustments_mrc_v
where asset_id = px_dest_asset_hdr_rec.asset_id
and book_type_code = px_dest_asset_hdr_rec.book_type_code
and transaction_header_id = px_dest_trans_rec.transaction_header_id
and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and source_type_code = 'DEPRECIATION';
delete from fa_adjustments
where asset_id = px_dest_asset_hdr_rec.asset_id
and book_type_code = px_dest_asset_hdr_rec.book_type_code
and transaction_header_id = px_dest_trans_rec.transaction_header_id
and adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
and source_type_code = 'DEPRECIATION';
l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_rsv_adj.selection_thid := 0;
l_rsv_adj.selection_retid := 0;
l_rsv_adj.last_update_date := px_dest_trans_rec.transaction_date_entered;
px_dest_trans_rec.who_info.last_update_date,
px_dest_trans_rec.who_info.last_updated_by,
px_dest_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
px_dest_trans_rec.who_info.last_update_date,
px_dest_trans_rec.who_info.last_updated_by,
px_dest_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
fa_trx_references_pkg.insert_row
(X_Rowid => l_rowid,
X_Trx_Reference_Id => l_trx_reference_id,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Src_Asset_Id => l_src_asset_hdr_rec.asset_id,
X_Src_Transaction_Header_Id => px_src_trans_rec.transaction_header_id,
X_Dest_Asset_Id => l_dest_asset_hdr_rec.asset_id,
X_Dest_Transaction_Header_Id => px_dest_trans_rec.transaction_header_id,
X_Member_Asset_Id => p_asset_hdr_rec.asset_id,
X_Member_Transaction_Header_Id => p_trans_rec.transaction_header_id,
X_Transaction_Type => 'GROUP CHANGE',
X_Src_Transaction_Subtype => px_src_trans_rec.transaction_subtype || ' ' || px_group_reclass_options_rec.group_reclass_type,
X_Dest_Transaction_Subtype => px_dest_trans_rec.transaction_subtype || ' ' || px_group_reclass_options_rec.group_reclass_type,
X_Src_Amortization_Start_Date => px_src_trans_rec.amortization_start_date,
X_Dest_Amortization_Start_Date => px_dest_trans_rec.amortization_start_date,
X_Reserve_Transfer_Amount => px_group_reclass_options_rec.reserve_amount,
X_Src_Expense_Amount => px_group_reclass_options_rec.source_exp_amount,
X_Dest_Expense_Amount => px_group_reclass_options_rec.destination_exp_amount,
X_Src_Eofy_Reserve => px_group_reclass_options_rec.source_eofy_reserve,
X_Dest_Eofy_Reserve => px_group_reclass_options_rec.destination_eofy_reserve,
X_Creation_Date => p_trans_rec.who_info.creation_date,
X_Created_By => p_trans_rec.who_info.created_by,
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_return_status,
X_Calling_Fn => l_calling_fn
);
select NVL(SUM(DECODE(adj.debit_credit_flag,
'DR', adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)), 0)
from fa_adjustments adj
where adj.asset_id = p_asset_hdr_rec.asset_id
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter
and adj.track_member_flag = 'Y'
and adj.transaction_header_id = px_trans_rec.transaction_header_id;
we need to consider the expense just inserted into fa_adjustments by this transaction*/
--bug6983091: MRC: getting expense just backed out
CURSOR c_get_mc_exp_amount IS
select NVL(SUM(DECODE(adj.debit_credit_flag,
'DR', adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount)), 0)
from fa_adjustments_mrc_v adj
where adj.asset_id = p_asset_hdr_rec.asset_id
and adj.book_type_code = p_asset_hdr_rec.book_type_code
and adj.adjustment_type = 'EXPENSE'
and adj.period_counter_created = p_period_rec.period_counter
and adj.track_member_flag = 'Y'
and adj.transaction_header_id = px_trans_rec.transaction_header_id;
we need to consider the expense just inserted into fa_adjustments by this transaction*/
l_calling_fn VARCHAR2(35) := 'fa_group_reclass_pvt.do_adjustment';
l_rsv_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_rsv_adj.selection_thid := 0;
l_rsv_adj.selection_retid := 0;
l_rsv_adj.last_update_date := px_trans_rec.transaction_date_entered;
l_exp_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
l_exp_adj.selection_thid := 0;
l_exp_adj.selection_retid := 0;
l_exp_adj.last_update_date := px_trans_rec.transaction_date_entered;
px_trans_rec.who_info.last_update_date,
px_trans_rec.who_info.last_updated_by,
px_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
px_trans_rec.who_info.last_update_date,
px_trans_rec.who_info.last_updated_by,
px_trans_rec.who_info.last_update_login) then
raise grp_rec_err;
fa_books_pkg.update_row
(X_asset_id => p_asset_hdr_rec.asset_id,
X_book_type_code => p_asset_hdr_rec.book_type_code,
X_rate_adjustment_factor => l_asset_fin_rec_new.rate_adjustment_factor,
X_reval_amortization_basis => l_asset_fin_rec_new.reval_amortization_basis,
X_adjusted_cost => l_asset_fin_rec_new.adjusted_cost,
X_adjusted_capacity => l_asset_fin_rec_new.adjusted_capacity,
X_eofy_reserve => l_asset_fin_rec_new.eofy_reserve,
X_mrc_sob_type_code => p_mrc_sob_type_code,
X_calling_fn => l_calling_fn);
select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
from fa_deprn_summary ds1,
fa_deprn_periods dp1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = h_member_asset_id
and dp1.book_type_code = ds1.book_type_Code
and dp1.period_counter = ds1.period_counter
and ds1.period_counter =
(select max(period_counter)
from fa_deprn_summary ds2
where ds2.book_type_code = h_book_type_code
and ds2.asset_id = h_member_asset_id
and period_counter <= h_period_counter);
select ds1.deprn_reserve,ds1.ytd_deprn,dp1.fiscal_year
from fa_mc_deprn_summary ds1,
fa_mc_deprn_periods dp1
where ds1.book_type_code = h_book_type_code
and ds1.asset_id = h_member_asset_id
and dp1.book_type_code = ds1.book_type_Code
and dp1.period_counter = ds1.period_counter
and dp1.set_of_books_id = h_set_of_books_id
and ds1.period_counter =
(select max(period_counter)
from fa_deprn_summary ds2
where ds2.book_type_code = h_book_type_code
and ds2.asset_id = h_member_asset_id
and period_counter <= h_period_counter)
and ds1.set_of_books_id = h_set_of_books_id;
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_retirements ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and ret.transaction_header_id_in in
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods dp1,
fa_deprn_periods dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and th1.transaction_date_entered >= dp1.calendar_period_open_date
and th1.transaction_date_entered <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT'));
select sum(nvl(ret.reserve_retired,0) - nvl(ret.eofy_reserve,0))
from fa_mc_retirements ret
where ret.book_type_code = h_book_type_code
and ret.asset_id = h_member_asset_id
and ret.set_of_books_id = h_set_of_books_id
and ret.transaction_header_id_in in
(select th1.transaction_header_id
from fa_transaction_headers th1,
fa_deprn_periods dp1,
fa_deprn_periods dp3
where th1.asset_id = ret.asset_id
and dp1.book_type_code = h_book_type_code
and dp1.fiscal_year =
(select dp2.fiscal_year
from fa_deprn_periods dp2
where dp2.book_type_code = dp1.book_type_code
and dp2.period_Counter = h_period_counter - 1)
and dp1.period_num = 1
and dp3.book_type_code = dp1.book_type_code
and dp3.period_counter = h_period_counter - 1
and th1.transaction_date_entered >= dp1.calendar_period_open_date
and th1.transaction_date_entered <= dp3.calendar_period_close_date
and th1.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT'));
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and adj.period_counter_adjusted in
(select dp2.period_counter
from fa_deprn_periods dp1,
fa_deprn_periods dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.period_counter <= dp1.period_counter);
select sum(decode(adj.debit_credit_flag,
'DR',adj.adjustment_amount,
'CR', -1 * adj.adjustment_amount))
from fa_mc_adjustments adj
where adj.book_type_code = h_book_type_code
and adj.asset_id = h_member_asset_id
and adj.adjustment_type = 'RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and adj.set_of_books_id = h_set_of_books_id
and adj.period_counter_adjusted in
(select dp2.period_counter
from fa_mc_deprn_periods dp1,
fa_mc_deprn_periods dp2
where dp1.book_type_code = adj.book_type_code
and dp1.period_counter = h_period_counter - 1
and dp1.set_of_books_id = h_set_of_books_id
and dp2.book_type_code = dp1.book_type_code
and dp2.fiscal_year = dp1.fiscal_year
and dp2.set_of_books_id = h_set_of_books_id
and dp2.period_counter <= dp1.period_counter);
select fiscal_year into h_cur_fiscal_year
from fa_deprn_periods
where book_type_code = h_book_type_code
and period_counter = h_period_counter;
select fiscal_year into h_cur_fiscal_year
from fa_mc_deprn_periods
where book_type_code = h_book_type_code
and period_counter = h_period_counter
and set_of_books_id = h_set_of_books_id;
select period_Counter,fiscal_year
into h_period_counter,h_amort_fiscal_year
from fa_deprn_periods
where book_type_Code= h_book_type_code
and nvl(calendar_period_open_date,sysdate) <= h_amort_start_date
and nvl(calendar_period_close_date,sysdate) >= h_amort_start_date;
select period_Counter,fiscal_year
into h_period_counter,h_amort_fiscal_year
from fa_mc_deprn_periods
where book_type_Code= h_book_type_code
and nvl(calendar_period_open_date,sysdate) <= h_amort_start_date
and nvl(calendar_period_close_date,sysdate) >= h_amort_start_date
and set_of_books_id = h_set_of_books_id;