[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cost,
salvage_value,
date_placed_in_service,
depreciate_flag,
period_counter_fully_reserved,
period_counter_fully_retired,
transaction_header_id_in,
life_in_months,
add_months(date_placed_in_service,life_in_months) last_date
FROM fa_books
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND transaction_header_id_out IS NULL;
SELECT period_counter,deprn_reserve,YTD_deprn
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND deprn_source_code = 'BOOKS';
SELECT period_counter, deprn_reserve
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter = (SELECT max(period_counter)
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id);
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code like p_book_type_code;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = p_calendar_type;
SELECT *
FROM igi_iac_transaction_headers
WHERE period_counter = p_period_counter
AND book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND transaction_type_code = p_calling_function
AND transaction_sub_type = 'REVALUATION'
AND adjustment_id_out is NULL;
SELECT *
FROM igi_iac_det_balances
WHERE book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND adjustment_id = p_adjustment_id
AND distribution_id = p_distribution_id;
SELECT *
FROM igi_iac_det_balances
WHERE adjustment_id = p_adjustment_id
AND book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND nvl(active_flag,'Y') = 'N';
SELECT *
FROM igi_imp_iac_interface_py_add
WHERE book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND period_counter = p_period_counter;
SELECT *
FROM igi_iac_fa_deprn
WHERE book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND adjustment_id = cp_adjustment_id
AND distribution_id = cp_distribution_id;
SELECT *
FROM igi_iac_fa_deprn
WHERE adjustment_id = cp_adjustment_id
AND book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND nvl(active_flag,'Y') = 'N';
SELECT life_in_months,salvage_value,rate_adjustment_factor
FROM fa_books
WHERE book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND date_ineffective is NULL ;
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code = p_asset_balance.book_type_code;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = p_calendar_type;
SELECT period_counter,deprn_reserve,YTD_deprn
FROM fa_deprn_summary
WHERE book_type_code = p_asset_balance.book_type_code
AND asset_id = p_asset_balance.asset_id
AND deprn_source_code = 'BOOKS';
p_string => ' Inserting records into IAC tables');
p_string => ' Inserting record into igi_iac_transaction_headers');
igi_iac_trans_headers_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_transaction_header_id => NULL ,
X_adjustment_id_out => NULL ,
X_transaction_type_code => p_calling_function ,
X_transaction_date_entered => sysdate ,
X_mass_refrence_id => NULL ,
X_transaction_sub_type => 'CATCHUP' ,
X_book_type_code => l_transaction.book_type_code ,
X_asset_id => l_transaction.asset_id ,
X_category_id => l_transaction.category_id ,
X_adj_deprn_start_date => l_transaction.adj_deprn_start_date ,
X_revaluation_type_flag => NULL,
X_adjustment_status => 'COMPLETE' ,
X_period_counter => l_transaction.period_counter,
X_event_id => p_event_id) ;
igi_iac_det_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_asset_id => l_inactive_dist.asset_id ,
X_distribution_id => l_inactive_dist.distribution_id ,
X_book_type_code => l_inactive_dist.book_type_code ,
X_period_counter => p_period_counter ,
X_adjustment_cost => l_inactive_dist.adjustment_cost ,
X_net_book_value => l_inactive_dist.net_book_value ,
X_reval_reserve_cost => l_inactive_dist.reval_reserve_cost ,
X_reval_reserve_backlog => l_inactive_dist.reval_reserve_backlog ,
X_reval_reserve_gen_fund => l_inactive_dist.reval_reserve_gen_fund ,
X_reval_reserve_net => l_inactive_dist.reval_reserve_net,
X_operating_acct_cost => l_inactive_dist.operating_acct_cost ,
X_operating_acct_backlog => l_inactive_dist.operating_acct_backlog ,
X_operating_acct_net => l_inactive_dist.operating_acct_net ,
X_operating_acct_ytd => l_inactive_dist.operating_acct_ytd ,
X_deprn_period => l_inactive_dist.deprn_period ,
X_deprn_ytd => l_inactive_dist.deprn_ytd ,
X_deprn_reserve => l_inactive_dist.deprn_reserve ,
X_deprn_reserve_backlog => l_inactive_dist.deprn_reserve_backlog ,
X_general_fund_per => l_inactive_dist.general_fund_per ,
X_general_fund_acc => l_inactive_dist.general_fund_acc ,
X_last_reval_date => l_inactive_dist.last_reval_date ,
X_current_reval_factor => l_inactive_dist.current_reval_factor ,
X_cumulative_reval_factor => l_inactive_dist.cumulative_reval_factor ,
X_active_flag => l_inactive_dist.active_flag ) ;
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => l_iac_fa_inactive_dist.book_type_code,
x_asset_id => l_iac_fa_inactive_dist.asset_id,
x_distribution_id => l_iac_fa_inactive_dist.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_adjustment_id,
x_deprn_period => l_iac_fa_inactive_dist.deprn_period,
x_deprn_ytd => l_iac_fa_inactive_dist.deprn_ytd ,
x_deprn_reserve => l_iac_fa_inactive_dist.deprn_reserve ,
x_active_flag => l_iac_fa_inactive_dist.active_flag,
x_mode => 'R');
p_string => ' Inserting records into igi_iac_adjustments');
igi_iac_adjustments_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_book_type_code => l_transaction.book_type_code ,
X_code_combination_id => l_account_ccid,
X_set_of_books_id => l_set_of_books_id ,
X_dr_cr_flag => 'DR' ,
X_amount => l_distribution_amount ,
X_adjustment_type => 'EXPENSE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distributions_tab(l_idx).units ,
X_asset_id => l_transaction.asset_id ,
X_distribution_id => l_distributions_tab(l_idx).distribution_id ,
X_period_counter => l_transaction.period_counter,
X_adjustment_offset_type => 'RESERVE' ,
X_report_ccid => Null,
x_mode => 'R',
X_event_id => p_event_id ) ;
igi_iac_adjustments_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_book_type_code => l_transaction.book_type_code ,
X_code_combination_id => l_account_ccid,
X_set_of_books_id => l_set_of_books_id ,
X_dr_cr_flag => 'CR' ,
X_amount => l_distribution_amount ,
X_adjustment_type => 'RESERVE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distributions_tab(l_idx).units ,
X_asset_id => l_transaction.asset_id ,
X_distribution_id => l_distributions_tab(l_idx).distribution_id ,
X_period_counter => l_transaction.period_counter,
X_adjustment_offset_type => 'EXPENSE' ,
X_report_ccid => Null,
x_mode => 'R',
X_event_id => p_event_id ) ;
igi_iac_adjustments_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_book_type_code => l_transaction.book_type_code ,
X_code_combination_id => l_account_ccid,
X_set_of_books_id => l_set_of_books_id ,
X_dr_cr_flag => 'DR' ,
X_amount => l_distribution_amount ,
X_adjustment_type => 'REVAL RESERVE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distributions_tab(l_idx).units ,
X_asset_id => l_transaction.asset_id ,
X_distribution_id => l_distributions_tab(l_idx).distribution_id ,
X_period_counter => l_transaction.period_counter,
X_adjustment_offset_type => 'GENERAL FUND' ,
X_report_ccid => Null ,
x_mode => 'R',
X_event_id => p_event_id ) ;
igi_iac_adjustments_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_book_type_code => l_transaction.book_type_code ,
X_code_combination_id => l_account_ccid,
X_set_of_books_id => l_set_of_books_id ,
X_dr_cr_flag => 'CR' ,
X_amount => l_distribution_amount ,
X_adjustment_type => 'GENERAL FUND' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distributions_tab(l_idx).units ,
X_asset_id => l_transaction.asset_id ,
X_distribution_id => l_distributions_tab(l_idx).distribution_id ,
X_period_counter => l_transaction.period_counter,
X_adjustment_offset_type => 'REVAL RESERVE' ,
X_report_ccid => l_reval_rsv_ccid ,
x_mode => 'R',
X_event_id => p_event_id ) ;
p_string => ' Inserting into igi_iac_det_balances ');
igi_iac_det_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_asset_id => l_detail_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_book_type_code => l_detail_balance.book_type_code ,
X_period_counter => l_detail_balance.period_counter ,
X_adjustment_cost => l_detail_balance.adjustment_cost ,
X_net_book_value => l_detail_balance.net_book_value - l_distribution_amount ,
X_reval_reserve_cost => l_detail_balance.reval_reserve_cost ,
X_reval_reserve_backlog => l_detail_balance.reval_reserve_backlog ,
X_reval_reserve_gen_fund => l_reval_general_fund ,
X_reval_reserve_net => l_reval_reserve_net,
X_operating_acct_cost => l_detail_balance.operating_acct_cost ,
X_operating_acct_backlog => l_detail_balance.operating_acct_backlog ,
X_operating_acct_net => l_detail_balance.operating_acct_net ,
X_operating_acct_ytd => l_detail_balance.operating_acct_ytd ,
X_deprn_period => l_iac_deprn_period_amount ,
X_deprn_ytd => l_deprn_ytd ,
X_deprn_reserve => l_detail_balance.deprn_reserve + l_distribution_amount ,
X_deprn_reserve_backlog => l_detail_balance.deprn_reserve_backlog ,
X_general_fund_per => l_distribution_amount ,
X_general_fund_acc => l_general_fund_acc ,
X_last_reval_date => l_detail_balance.last_reval_date ,
X_current_reval_factor => l_detail_balance.current_reval_factor ,
X_cumulative_reval_factor =>l_detail_balance.cumulative_reval_factor ,
X_active_flag => l_detail_balance.active_flag ) ;
igi_iac_asset_balances_pkg.update_row(
X_asset_id => p_asset_balance.asset_id ,
X_book_type_code => p_asset_balance.book_type_code ,
X_period_counter => p_period_counter ,
X_net_book_value => p_asset_balance.net_book_value ,
X_adjusted_cost => p_asset_balance.adjusted_cost ,
X_operating_acct => p_asset_balance.operating_acct ,
X_reval_reserve => p_asset_balance.reval_reserve ,
X_deprn_amount => l_iac_deprn_period_amount ,
X_deprn_reserve => p_asset_balance.deprn_reserve ,
X_backlog_deprn_reserve => p_asset_balance.backlog_deprn_reserve ,
X_general_fund => p_asset_balance.general_fund ,
X_last_reval_date => p_asset_balance.last_reval_date ,
X_current_reval_factor => p_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => p_asset_balance.cumulative_reval_factor ) ;
igi_iac_asset_balances_pkg.update_row(
X_asset_id => p_asset_balance.asset_id ,
X_book_type_code => p_asset_balance.book_type_code ,
X_period_counter => p_period_counter + 1 ,
X_net_book_value => p_asset_balance.net_book_value ,
X_adjusted_cost => p_asset_balance.adjusted_cost ,
X_operating_acct => p_asset_balance.operating_acct ,
X_reval_reserve => p_asset_balance.reval_reserve ,
X_deprn_amount => l_iac_deprn_period_amount ,
X_deprn_reserve => p_asset_balance.deprn_reserve ,
X_backlog_deprn_reserve => p_asset_balance.backlog_deprn_reserve ,
X_general_fund => p_asset_balance.general_fund ,
X_last_reval_date => p_asset_balance.last_reval_date ,
X_current_reval_factor => p_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => p_asset_balance.cumulative_reval_factor ) ;
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => l_iac_fa_deprn_rec.book_type_code,
x_asset_id => l_iac_fa_deprn_rec.asset_id,
x_distribution_id => l_iac_fa_deprn_rec.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_adjustment_id,
x_deprn_period => l_fa_deprn_period_amount,
x_deprn_ytd => l_deprn_ytd ,
x_deprn_reserve => l_iac_fa_deprn_rec.deprn_reserve + l_fa_dist_catchup_tab(l_idx).amount,
x_active_flag => l_iac_fa_deprn_rec.active_flag,
x_mode => 'R');
igi_iac_trans_headers_pkg.update_row(
X_prev_adjustment_id => l_transaction.adjustment_id ,
X_adjustment_id => l_adjustment_id ) ;
UPDATE igi_imp_iac_interface_py_add
SET net_book_value = p_asset_balance.net_book_value,
adjusted_cost = p_asset_balance.adjusted_cost,
operating_acct = p_asset_balance.operating_acct,
reval_reserve = p_asset_balance.reval_reserve,
deprn_amount = l_iac_deprn_period_amount,
deprn_reserve = p_asset_balance.deprn_reserve,
backlog_deprn_reserve = p_asset_balance.backlog_deprn_reserve,
general_fund = p_asset_balance.general_fund,
hist_deprn_expense = l_fa_deprn_period_amount,
hist_accum_deprn = l_fa_deprn_acc,
hist_ytd = l_deprn_ytd,
hist_nbv = l_upgrade_rec.hist_cost - l_fa_deprn_acc,
general_fund_periodic = p_asset_balance.deprn_amount
WHERE asset_id = l_upgrade_rec.asset_id
AND book_type_code = l_upgrade_rec.book_type_code
AND period_counter = l_upgrade_rec.period_counter;
SELECT cost,
adjusted_cost,
original_cost,
salvage_value,
life_in_months,
rate_adjustment_factor,
period_counter_fully_reserved,
adjusted_recoverable_cost,
recoverable_cost,
date_placed_in_service,
0 deprn_periods_elapsed,
0 deprn_periods_current_year,
0 deprn_periods_prior_year,
last_period_counter,
gl_posting_allowed_flag,
0 ytd_deprn,
0 deprn_reserve,
0 pys_deprn_reserve,
0 deprn_amount,
deprn_start_date,
depreciate_flag
FROM fa_books b, fa_book_controls c
WHERE b.book_type_code = p_reval_asset_params(1).book_type_code
AND c.book_type_code = p_reval_asset_params(1).book_type_code
AND b.asset_id = p_reval_asset_params(1).asset_id
AND b.transaction_header_id_out IS NULL;
SELECT period_counter,deprn_reserve,YTD_deprn
FROM fa_deprn_summary
WHERE book_type_code = p_reval_asset_params(1).book_type_code
AND asset_id = p_reval_asset_params(1).asset_id
AND deprn_source_code = 'BOOKS';
p_reval_input_asset(idx).last_update_login := l_login_id;
p_reval_input_asset(idx).last_update_date := sysdate;
p_reval_input_asset(idx).last_updated_by := l_user_id;
p_prev_rate_info(idx).last_update_login := l_login_id;
p_prev_rate_info(idx).last_update_date := sysdate;
p_prev_rate_info(idx).last_updated_by := l_user_id;
p_curr_rate_info(idx).last_update_login := l_login_id;
p_curr_rate_info(idx).last_update_date := sysdate;
p_curr_rate_info(idx).last_updated_by := l_user_id;