[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT cal_price_index_link_id
FROM igi_iac_category_books
WHERE book_type_code = p_book_type_code;
SELECT max(end_date)
FROM fa_calendar_periods
WHERE calendar_type = (
SELECT calendar_type
FROM igi_iac_cal_price_indexes
WHERE cal_price_index_link_id = p_cal_price_index_link_id );
SELECT max(date_to)
FROM igi_iac_cal_idx_values
WHERE cal_price_index_link_id = p_cal_price_index_link_id;
SELECT current_price_index_value
FROM igi_iac_cal_idx_values
WHERE trunc(date_to) = trunc(p_max_date_to)
AND cal_price_index_link_id = p_cal_price_index_link_id ;
SELECT start_date, end_date
FROM fa_calendar_periods
WHERE trunc(end_date) > trunc(p_max_date_to)
AND calendar_type = (
SELECT calendar_type
FROM igi_iac_cal_price_indexes
WHERE cal_price_index_link_id = p_cal_price_index_link_id );
/* Insert a record into IAC for each period that is defined in FA and not yet defined in IAC */
FOR l_get_fa_period IN c_get_fa_periods(l_iac_max_date ,
l_get_cal_price_indexes.cal_price_index_link_id) LOOP
l_rowid := NULL;
igi_iac_cal_idx_values_pkg.insert_row(
X_rowid => l_rowid ,
X_cal_price_index_link_id => l_get_cal_price_indexes.cal_price_index_link_id,
X_date_from => l_get_fa_period.start_date ,
X_date_to => l_get_fa_period.end_date ,
X_original_price_index_value => NULL ,
X_current_price_index_value => 9999.99 );
SELECT bk.asset_id, ad.asset_category_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.depreciate_flag = 'NO'
AND bk.adjustment_required_status = 'ADD'
AND bk.asset_id = ad.asset_id
AND NOT EXISTS (SELECT 'X'
FROM igi_iac_asset_balances ab
WHERE book_type_code = p_book_type_code
AND ab.asset_id = bk.asset_id);
SELECT asset_id, deprn_amount
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter ;
SELECT sum(nvl(deprn_amount,0)), sum(nvl(deprn_adjustment_amount,0))
FROM fa_deprn_detail
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND asset_id = p_asset_id;
SELECT nvl(deprn_amount,0) deprn_amount, nvl(deprn_adjustment_amount,0) deprn_adjustment_amount, nvl(deprn_reserve,0) deprn_reserve
FROM fa_deprn_detail
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND asset_id = p_asset_id
AND distribution_id = cp_distribution_id;
SELECT period_counter_fully_reserved, period_counter_fully_retired,
life_in_months, transaction_header_id_in, depreciate_flag,salvage_value,rate_adjustment_factor,cost
FROM fa_books
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND date_ineffective is NULL ;
SELECT life_in_months
FROM fa_books
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND transaction_header_id_out = p_transaction_id
AND adjustment_required_status <> 'ADD';
SELECT transaction_date_entered
FROM fa_transaction_headers
WHERE transaction_header_id = p_transaction_id;
SELECT adjustment_id
FROM igi_iac_transaction_headers
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND adjustment_id_out is NULL ;
SELECT max(period_counter)
FROM igi_iac_asset_balances
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND period_counter <= p_period_counter;
SELECT *
FROM igi_iac_asset_balances
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND period_counter = p_period_counter ;
SELECT asset_category_id,current_units
FROM fa_additions
WHERE asset_id = p_asset_id;
SELECT *
FROM igi_iac_det_balances
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND adjustment_id = p_prev_adjustment_id ;
SELECT units_assigned
FROM fa_distribution_history
WHERE distribution_id = p_distribution_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 'X'
FROM igi_iac_det_balances
WHERE book_type_code = p_book_type_code
AND asset_id = cp_asset_id
AND distribution_id = cp_distribution_id
AND period_counter = p_period_counter - 1
AND nvl(active_flag,'Y') = 'N';
SELECT *
FROM igi_iac_fa_deprn
WHERE book_type_code = p_book_type_code
AND asset_id = cp_asset_id
AND adjustment_id = cp_adjustment_id;
SELECT 'X'
FROM igi_iac_fa_deprn
WHERE book_type_code = p_book_type_code
AND asset_id = cp_asset_id
AND distribution_id = cp_distribution_id
AND period_counter = p_period_counter - 1
AND nvl(active_flag,'Y') = 'N';
SELECT count(*) from igi_iac_asset_balances
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting into igi_iac_transaction_headers');
select event_id into p_event_id from fa_deprn_summary
where asset_id=l_fa_asset_id(l_loop_count)
and book_type_code=p_book_type_code
and period_counter=p_period_counter;
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 => 'DEPRECIATION' ,
X_transaction_date_entered => sysdate ,
X_mass_refrence_id => NULL ,
X_transaction_sub_type => NULL ,
X_book_type_code => p_book_type_code ,
X_asset_id => l_fa_asset_id(l_loop_count) ,
X_category_id => l_category_id ,
X_adj_deprn_start_date => l_prd_rec.period_end_date ,
X_revaluation_type_flag => NULL,
X_adjustment_status => 'COMPLETE' ,
X_period_counter => p_period_counter,
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 => p_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_amount ,
X_adjustment_type => 'EXPENSE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distribution_units ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_period_counter => p_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 => p_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_amount ,
X_adjustment_type => 'RESERVE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distribution_units ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_period_counter => p_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 => p_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_amount ,
X_adjustment_type => 'REVAL RESERVE' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distribution_units ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_period_counter => p_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 => p_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_amount ,
X_adjustment_type => 'GENERAL FUND' ,
X_transfer_to_gl_flag => 'Y' ,
X_units_assigned => l_distribution_units ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_period_counter => p_period_counter,
X_adjustment_offset_type => 'REVAL RESERVE',
X_report_ccid => l_reval_reserve_ccid,
x_mode => 'R',
X_event_id => p_event_id ) ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' 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_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter ,
X_adjustment_cost => l_detail_balance.adjustment_cost ,
X_net_book_value => l_detail_balance.net_book_value - l_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_rsv_gen_fund ,
X_reval_reserve_net => l_reval_rsv_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_amount ,
X_deprn_ytd => l_deprn_ytd + l_amount ,
X_deprn_reserve => l_detail_balance.deprn_reserve + l_amount ,
X_deprn_reserve_backlog => l_detail_balance.deprn_reserve_backlog ,
X_general_fund_per => l_gen_fund_per ,
X_general_fund_acc => l_gen_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_det_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter ,
X_adjustment_cost => l_detail_balance.adjustment_cost ,
X_net_book_value => l_detail_balance.net_book_value ,
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_detail_balance.reval_reserve_gen_fund ,
X_reval_reserve_net => l_detail_balance.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_detail_balance.deprn_period ,
X_deprn_ytd => l_detail_balance.deprn_ytd ,
X_deprn_reserve => l_detail_balance.deprn_reserve ,
X_deprn_reserve_backlog => l_detail_balance.deprn_reserve_backlog ,
X_general_fund_per => l_detail_balance.general_fund_per ,
X_general_fund_acc => l_detail_balance.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_det_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_adjustment_id => l_adjustment_id ,
X_asset_id => l_asset_balance.asset_id ,
X_distribution_id => l_detail_balance.distribution_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter ,
X_adjustment_cost => 0 ,
X_net_book_value => 0 ,
X_reval_reserve_cost => 0 ,
X_reval_reserve_backlog => 0 ,
X_reval_reserve_gen_fund => 0 ,
X_reval_reserve_net => 0 ,
X_operating_acct_cost => 0 ,
X_operating_acct_backlog => 0 ,
X_operating_acct_net => 0 ,
X_operating_acct_ytd => 0 ,
X_deprn_period => 0 ,
X_deprn_ytd => 0 ,
X_deprn_reserve => 0 ,
X_deprn_reserve_backlog => 0 ,
X_general_fund_per => 0 ,
X_general_fund_acc => 0 ,
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_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => p_book_type_code,
x_asset_id => l_asset_balance.asset_id,
x_distribution_id => l_iac_fa_deprn_dist.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_adjustment_id,
x_deprn_period => l_fa_dist_amounts.deprn_amount - l_fa_dist_amounts.deprn_adjustment_amount,
x_deprn_ytd => l_deprn_ytd +
(l_fa_dist_amounts.deprn_amount - l_fa_dist_amounts.deprn_adjustment_amount),
x_deprn_reserve => l_fa_dist_amounts.deprn_reserve,
x_active_flag => l_iac_fa_deprn_dist.active_flag,
x_mode => 'R');
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => l_iac_fa_deprn_dist.book_type_code,
x_asset_id => l_iac_fa_deprn_dist.asset_id,
x_distribution_id => l_iac_fa_deprn_dist.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_adjustment_id,
x_deprn_period => l_iac_fa_deprn_dist.deprn_period,
x_deprn_ytd => l_iac_fa_deprn_dist.deprn_ytd,
x_deprn_reserve => l_iac_fa_deprn_dist.deprn_reserve,
x_active_flag => l_iac_fa_deprn_dist.active_flag,
x_mode => 'R');
igi_iac_fa_deprn_pkg.insert_row(
x_rowid => l_rowid,
x_book_type_code => l_iac_fa_deprn_dist.book_type_code,
x_asset_id => l_iac_fa_deprn_dist.asset_id,
x_distribution_id => l_iac_fa_deprn_dist.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_adjustment_id,
x_deprn_period => 0,
x_deprn_ytd => 0,
x_deprn_reserve => 0,
x_active_flag => l_iac_fa_deprn_dist.active_flag,
x_mode => 'R');
igi_iac_asset_balances_pkg.update_row(
X_asset_id => l_asset_balance.asset_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter ,
X_net_book_value => l_asset_balance.net_book_value - l_deprn_expense ,
X_adjusted_cost => l_asset_balance.adjusted_cost ,
X_operating_acct => l_asset_balance.operating_acct ,
X_reval_reserve => l_reval_reserve ,
X_deprn_amount => l_deprn_expense ,
X_deprn_reserve => l_asset_balance.deprn_reserve + l_deprn_expense ,
X_backlog_deprn_reserve => l_asset_balance.backlog_deprn_reserve ,
X_general_fund => l_general_fund ,
X_last_reval_date => l_asset_balance.last_reval_date ,
X_current_reval_factor => l_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => l_asset_balance.cumulative_reval_factor ) ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting asset balances for the current period');
igi_iac_asset_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_asset_id => l_asset_balance.asset_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter ,
X_net_book_value => l_asset_balance.net_book_value - l_deprn_expense ,
X_adjusted_cost => l_asset_balance.adjusted_cost ,
X_operating_acct => l_asset_balance.operating_acct ,
X_reval_reserve => l_reval_reserve ,
X_deprn_amount => l_deprn_expense ,
X_deprn_reserve => l_asset_balance.deprn_reserve + l_deprn_expense ,
X_backlog_deprn_reserve => l_asset_balance.backlog_deprn_reserve ,
X_general_fund => l_general_fund ,
X_last_reval_date => l_asset_balance.last_reval_date ,
X_current_reval_factor => l_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => l_asset_balance.cumulative_reval_factor ) ;
igi_iac_asset_balances_pkg.update_row(
X_asset_id => l_asset_balance.asset_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter + 1 ,
X_net_book_value => l_asset_balance.net_book_value - l_deprn_expense ,
X_adjusted_cost => l_asset_balance.adjusted_cost ,
X_operating_acct => l_asset_balance.operating_acct ,
X_reval_reserve => l_reval_reserve ,
X_deprn_amount => l_deprn_expense ,
X_deprn_reserve => l_asset_balance.deprn_reserve + l_deprn_expense ,
X_backlog_deprn_reserve => l_asset_balance.backlog_deprn_reserve ,
X_general_fund => l_general_fund ,
X_last_reval_date => l_asset_balance.last_reval_date ,
X_current_reval_factor => l_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => l_asset_balance.cumulative_reval_factor ) ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Inserting asset balances for the next period');
igi_iac_asset_balances_pkg.insert_row(
X_rowid => l_rowid ,
X_asset_id => l_asset_balance.asset_id ,
X_book_type_code => p_book_type_code ,
X_period_counter => p_period_counter + 1 ,
X_net_book_value => l_asset_balance.net_book_value - l_deprn_expense ,
X_adjusted_cost => l_asset_balance.adjusted_cost ,
X_operating_acct => l_asset_balance.operating_acct ,
X_reval_reserve => l_reval_reserve ,
X_deprn_amount => l_deprn_expense ,
X_deprn_reserve => l_asset_balance.deprn_reserve + l_deprn_expense ,
X_backlog_deprn_reserve => l_asset_balance.backlog_deprn_reserve ,
X_general_fund => l_general_fund ,
X_last_reval_date => l_asset_balance.last_reval_date ,
X_current_reval_factor => l_asset_balance.current_reval_factor ,
X_cumulative_reval_factor => l_asset_balance.cumulative_reval_factor ) ;
igi_iac_trans_headers_pkg.update_row(
X_prev_adjustment_id => l_adjustment_id_out ,
X_adjustment_id => l_adjustment_id ) ;
SELECT rowid,
adjustment_id,
book_type_code,
code_combination_id,
adjustment_type,
asset_id,
distribution_id,
period_counter
FROM igi_iac_adjustments
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND adjustment_type IN ('COST','RESERVE','EXPENSE')
AND adjustment_id = c_adjustment_id for update;
SELECT adjustment_id,
transaction_header_id,
transaction_type_code
FROM igi_iac_transaction_headers
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND transaction_type_code in ('RECLASS','ADDITION','DEPRECIATION');
SELECT code_combination_id
FROM fa_adjustments
WHERE book_type_code = p_book_type_code
AND asset_id = c_asset_id
AND distribution_id = c_distribution_id
AND period_counter_created = p_period_counter
AND source_type_code = c_adjustment_source_type_code
AND adjustment_type = c_adjustment_type;
SELECT code_combination_id
FROM fa_distribution_history
WHERE book_type_code = p_book_type_code
AND asset_id = c_asset_id
AND distribution_id = c_distribution_id
AND transaction_header_id_in = nvl(c_transaction_header_id,transaction_header_id_in);
SELECT nvl(ASSET_COST_ACCOUNT_CCID, -1),
nvl(DEPRN_EXPENSE_ACCOUNT_CCID, -1),
nvl(DEPRN_RESERVE_ACCOUNT_CCID, -1),
bc.accounting_flex_structure
FROM FA_DISTRIBUTION_ACCOUNTS da,
FA_BOOK_CONTROLS bc
WHERE bc.book_type_code = p_book_type_code
AND da.book_type_code = bc.book_type_code
AND da.distribution_id = c_distribution_id;
SELECT code_combination_id
FROM fa_adjustments
WHERE book_type_code = p_book_type_code
AND asset_id = c_asset_id
AND distribution_id = c_distribution_id
AND adjustment_type = c_adjustment_type;
SELECT a.category_id
INTO l_category_id
FROM fa_asset_history a
,fa_distribution_history d
WHERE d.distribution_id = l_dist_id(l_loop_count)
AND a.asset_id = d.asset_id
AND d.date_effective >= a.date_effective
AND d.date_effective < nvl(a.date_ineffective,sysdate);
SELECT asset_cost_acct, deprn_expense_acct, deprn_reserve_acct,
asset_cost_account_ccid, reserve_account_ccid
INTO l_asset_cost_acct, l_dep_exp_acct, l_dep_res_acct,
l_asset_cost_account_ccid ,l_reserve_account_ccid
FROM fa_category_books
WHERE book_type_code = p_book_type_code
AND category_id = l_category_id;
SELECT accounting_flex_structure, flexbuilder_defaults_ccid
into l_flex_num, l_default_ccid
FROM fa_book_controls
WHERE book_type_code = p_book_type_code ;
Select calendar_period_close_date
into l_validation_date
From fa_deprn_periods
where book_type_code = p_book_type_code
and period_counter = p_period_counter;
UPDATE igi_iac_adjustments
SET code_combination_id= l_account_ccid
WHERE rowid=l_rowid;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Updated the adjusment with correct ccid' );