[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(period_counter_fully_reserved,0)
FROM fa_books
WHERE book_type_code = n_book_type_code
AND asset_id = n_asset_id
AND date_ineffective IS NULL;
SELECT units_assigned
FROM fa_distribution_history
WHERE distribution_id = n_dist_id;
SELECT adjustment_id
FROM igi_iac_transaction_headers
WHERE book_type_code = cp_book_type_code
AND asset_id = cp_asset_id
AND adjustment_id_out = (select min(adjustment_id)
from igi_iac_transaction_headers
where transaction_header_id = cp_trxhdr_id_retire);
SELECT adjustment_id,
period_counter
FROM igi_iac_transaction_headers
WHERE adjustment_id_out = (SELECT adjustment_id
FROM igi_iac_transaction_headers
WHERE transaction_header_id = n_trx_id); */
SELECT iiab.period_counter,
iiab.net_book_value,
iiab.adjusted_cost,
iiab.operating_acct,
iiab.reval_reserve,
iiab.deprn_amount,
iiab.deprn_reserve,
iiab.backlog_deprn_reserve,
iiab.general_fund,
iiab.last_reval_date,
iiab.current_reval_factor,
iiab.cumulative_reval_factor
FROM igi_iac_asset_balances iiab
WHERE iiab.asset_id = n_asset_id
AND iiab.book_type_code = n_book_type_code
AND iiab.period_counter = n_period_counter;
SELECT iidb.adjustment_id,
iidb.distribution_id,
iidb.period_counter,
iidb.adjustment_cost,
iidb.net_book_value,
iidb.reval_reserve_cost,
iidb.reval_reserve_backlog,
iidb.reval_reserve_gen_fund,
iidb.reval_reserve_net,
iidb.operating_acct_cost,
iidb.operating_acct_backlog,
iidb.operating_acct_ytd,
iidb.operating_acct_net,
iidb.deprn_period,
iidb.deprn_ytd,
iidb.deprn_reserve,
iidb.deprn_reserve_backlog,
iidb.general_fund_per,
iidb.general_fund_acc,
iidb.last_reval_date,
iidb.current_reval_factor,
iidb.cumulative_reval_factor,
iidb.active_flag
FROM igi_iac_det_balances iidb
WHERE iidb.adjustment_id = n_adjust_id
AND iidb.active_flag IS NULL
ORDER BY iidb.distribution_id;
SELECT iifd.deprn_period,
iifd.deprn_ytd,
iifd.deprn_reserve
FROM igi_iac_fa_deprn iifd
WHERE iifd.adjustment_id = cp_adjustment_id
AND iifd.distribution_id = cp_distribution_id;
SELECT nvl(deprn_reserve,0) deprn_reserve,
(nvl(deprn_amount,0) - nvl(deprn_adjustment_amount,0)) deprn_amount
FROM fa_deprn_detail
WHERE book_type_code = cp_book_type_code
AND asset_id = cp_asset_id
AND distribution_id = cp_distribution_id
AND period_counter = (SELECT max(period_counter)
FROM fa_deprn_detail
WHERE book_type_code = cp_book_type_code
AND asset_id = cp_asset_id
AND distribution_id = cp_distribution_id
AND period_counter <= cp_period_counter);
SELECT sum(nvl(fdd.deprn_amount,0)-nvl(fdd.deprn_adjustment_amount,0)) deprn_YTD
FROM fa_deprn_detail fdd
WHERE fdd.distribution_id = cp_distribution_id
AND fdd.book_type_code = cp_book_type_code
AND fdd.asset_id = cp_asset_id
AND fdd.period_counter IN (SELECT period_counter
FROM fa_deprn_periods
WHERE book_type_code = cp_book_type_code
AND fiscal_year = (SELECT fiscal_year
FROM fa_deprn_periods
WHERE period_close_date IS NULL
AND book_type_code = cp_book_type_code))
GROUP BY fdd.distribution_id;
SELECT iidb.adjustment_id,
iidb.distribution_id,
iidb.adjustment_cost,
iidb.net_book_value,
iidb.reval_reserve_cost,
iidb.reval_reserve_backlog,
iidb.reval_reserve_gen_fund,
iidb.reval_reserve_net,
iidb.operating_acct_cost,
iidb.operating_acct_backlog,
iidb.operating_acct_net,
iidb.operating_acct_ytd,
iidb.deprn_period,
iidb.deprn_ytd,
iidb.deprn_reserve,
iidb.deprn_reserve_backlog,
iidb.general_fund_per,
iidb.general_fund_acc,
iidb.active_flag,
iidb.last_reval_date,
iidb.current_reval_factor,
iidb.cumulative_reval_factor
FROM igi_iac_det_balances iidb,
fa_distribution_history fdh
WHERE iidb.adjustment_id = cp_adjustment_id
AND iidb.book_type_code = cp_book_type_code
AND iidb.asset_id = cp_asset_id
AND fdh.book_type_code = cp_book_type_code
AND fdh.asset_id = cp_asset_id
AND iidb.distribution_id = fdh.distribution_id
AND fdh.transaction_header_id_out IS NOT NULL;
SELECT iidb.deprn_period,
iidb.deprn_reserve,
iidb.general_fund_acc,
iidb.reval_reserve_gen_fund
FROM igi_iac_det_balances iidb
WHERE iidb.adjustment_id = n_adjust_id
AND iidb.distribution_id = n_dist_id;
SELECT ifd.book_type_code,
ifd.asset_id,
ifd.period_counter,
ifd.adjustment_id,
ifd.distribution_id,
ifd.deprn_period,
ifd.deprn_ytd,
ifd.deprn_reserve,
ifd.active_flag
FROM igi_iac_fa_deprn ifd
WHERE ifd.adjustment_id = n_adjust_id
AND ifd.distribution_id = n_dist_id
AND ifd.period_counter = n_prd_cnt;
SELECT fdd.ytd_deprn
FROM fa_deprn_detail fdd
WHERE fdd.distribution_id = cp_distribution_id
AND fdd.book_type_code = cp_book_type_code
AND fdd.asset_id = cp_asset_id
AND fdd.period_counter = (SELECT period_counter
FROM fa_deprn_periods
WHERE book_type_code = cp_book_type_code
AND period_counter = cp_period_counter
AND fiscal_year = (SELECT fiscal_year
FROM fa_deprn_periods
WHERE period_close_date IS NULL
AND book_type_code = cp_book_type_code)) ;
SELECT count(*)
INTO l_cnt
FROM igi_iac_transaction_headers a1
WHERE a1.asset_id = x_asset_id
AND a1.book_type_code = x_book_type_code
AND a1.transaction_type_code = 'REVALUATION'
AND a1.adjustment_status NOT IN ('PREVIEW', 'OBSOLETE')
AND a1.period_counter BETWEEN x_eff_ret_period_cnt AND x_curr_period_cnt;
/* AND a1.adjustment_id > (SELECT a2.adjustment_id
FROM igi_iac_transaction_headers a2
WHERE a2.transaction_header_id = x_ret_trx_hdr_id
AND a2.transaction_type_code = 'PARTIAL RETIRE'
AND a2.period_counter <= a1.period_counter
AND a2.asset_id = x_asset_id
AND a2.book_type_code = x_book_type_code); */
SELECT period_counter
INTO l_ret_prd_counter
FROM igi_iac_transaction_headers
WHERE adjustment_id = x_ret_adj_id;
SELECT period_counter
INTO l_ren_prd_counter
FROM igi_iac_transaction_headers
WHERE adjustment_id = x_ren_adj_id;
SELECT COUNT(*)
INTO l_cnt
FROM fa_distribution_history old,
fa_distribution_history new
WHERE old.location_id = new.location_id
AND old.code_combination_id = new.code_combination_id
AND NVL(old.assigned_to, -999) = NVL(new.assigned_to, -999)
AND old.retirement_id = g_retirement_id
AND new.transaction_header_id_in = n_trx_hdr_id_in
AND old.distribution_id = n_ret_dist_id;
SELECT new.distribution_id
INTO l_ren_dist_id
FROM fa_distribution_history old,
fa_distribution_history new
WHERE old.location_id = new.location_id
AND old.code_combination_id = new.code_combination_id
AND NVL(old.assigned_to, -999) = NVL(new.assigned_to, -999)
AND old.retirement_id = g_retirement_id
AND new.transaction_header_id_in = n_trx_hdr_id_in
AND old.distribution_id = n_ret_dist_id;
SELECT units_assigned
INTO l_units_assigned
FROM fa_distribution_history
WHERE distribution_id = l_ren_dist_id;
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_adjustment_cost,
x_net_book_value => l_net_book_value,
x_reval_reserve_cost => l_reval_reserve_cost,
x_reval_reserve_backlog => l_reval_reserve_backlog,
x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
x_reval_reserve_net => l_reval_reserve_net,
x_operating_acct_cost => l_operating_acct_cost,
x_operating_acct_backlog => l_operating_acct_backlog,
x_operating_acct_net => l_operating_acct_net,
x_operating_acct_ytd => l_operating_acct_ytd,
x_deprn_period => l_deprn_period,
x_deprn_ytd => l_deprn_ytd,
x_deprn_reserve => l_deprn_reserve,
x_deprn_reserve_backlog => l_deprn_reserve_backlog,
x_general_fund_per => l_general_fund_per,
x_general_fund_acc => l_general_fund_acc,
x_last_reval_date => l_det_bal.last_reval_date,
x_current_reval_factor => l_det_bal.current_reval_factor,
x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
x_active_flag => null,
x_mode => 'R'
);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_ren_dist_id,
x_deprn_period => l_fa_deprn_period,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => l_fa_deprn_reserve,
x_active_flag => null,
x_mode => 'R'
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup_Amt, -- l_deprn_period,
x_adjustment_type => 'EXPENSE',
x_adjustment_offset_type => 'RESERVE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup_amt,
x_adjustment_type => 'RESERVE',
x_adjustment_offset_type => 'EXPENSE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup_Amt, --l_gf_catchup_amt,
x_adjustment_type => 'REVAL RESERVE',
x_adjustment_offset_type => 'GENERAL FUND',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup_Amt, --l_gf_catchup_amt,
x_adjustment_type => 'GENERAL FUND',
x_adjustment_offset_type => 'REVAL RESERVE',
x_report_ccid => l_reval_rsv_ccid,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_get_ytd.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_get_ytd.adjustment_cost,
x_net_book_value => l_get_ytd.net_book_value,
x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
x_reval_reserve_net => l_get_ytd.reval_reserve_net,
x_operating_acct_cost => l_get_ytd.operating_acct_cost,
x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
x_operating_acct_net => l_get_ytd.operating_acct_net,
x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
x_deprn_period => l_get_ytd.deprn_period,
x_deprn_ytd => l_get_ytd.deprn_ytd,
x_deprn_reserve => l_get_ytd.deprn_reserve,
x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
x_general_fund_per => l_get_ytd.general_fund_per,
x_general_fund_acc => l_get_ytd.general_fund_acc,
x_last_reval_date => l_get_ytd.last_reval_date,
x_current_reval_factor => l_get_ytd.current_reval_factor,
x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
x_active_flag => l_active_flag
);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_get_ytd.distribution_id,
x_deprn_period => 0,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode => 'R'
);
SELECT count(*)
INTO l_exists
FROM igi_iac_asset_balances
WHERE asset_id = g_asset_id
AND book_type_code = g_book_type_code
AND period_counter = p_period_counter;
IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserting into asset balances');
IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
X_rowid => l_rowid,
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_det_bal.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_adjustment_cost,
x_net_book_value => l_net_book_value,
x_reval_reserve_cost => l_reval_reserve_cost,
x_reval_reserve_backlog => l_reval_reserve_backlog,
x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
x_reval_reserve_net => l_reval_reserve_net,
x_operating_acct_cost => l_operating_acct_cost,
x_operating_acct_backlog => l_operating_acct_backlog,
x_operating_acct_net => l_operating_acct_net,
x_operating_acct_ytd => l_operating_acct_ytd,
x_deprn_period => l_deprn_period,
x_deprn_ytd => l_deprn_ytd,
x_deprn_reserve => l_deprn_reserve,
x_deprn_reserve_backlog => l_deprn_reserve_backlog,
x_general_fund_per => l_general_fund_per,
x_general_fund_acc => l_general_fund_acc,
x_last_reval_date => l_det_bal.last_reval_date,
x_current_reval_factor => l_det_bal.current_reval_factor,
x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
x_active_flag => null,
x_mode => 'R'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into det balances dist id '||l_det_bal.distribution_id);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_det_bal.distribution_id,
x_deprn_period => l_fa_deprn_period,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => l_fa_deprn_reserve,
x_active_flag => null,
x_mode => 'R'
);
SELECT units_assigned
INTO l_units_assigned
FROM fa_distribution_history
WHERE distribution_id = l_det_bal.distribution_id;
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'EXPENSE',
x_adjustment_offset_type => 'RESERVE', --Null,
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_det_bal.distribution_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup, --l_rsv_catchup_amt,
x_adjustment_type => 'RESERVE',
x_adjustment_offset_type => 'EXPENSE', -- Null,
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_det_bal.distribution_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'REVAL RESERVE',
x_adjustment_offset_type => 'GENERAL FUND', --Null,
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_det_bal.distribution_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'GENERAL FUND',
x_adjustment_offset_type => 'REVAL RESERVE', -- Null,
x_report_ccid => l_reval_rsv_ccid, -- Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_det_bal.distribution_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_get_ytd.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_get_ytd.adjustment_cost,
x_net_book_value => l_get_ytd.net_book_value,
x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
x_reval_reserve_net => l_get_ytd.reval_reserve_net,
x_operating_acct_cost => l_get_ytd.operating_acct_cost,
x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
x_operating_acct_net => l_get_ytd.operating_acct_net,
x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
x_deprn_period => l_get_ytd.deprn_period,
x_deprn_ytd => l_get_ytd.deprn_ytd,
x_deprn_reserve => l_get_ytd.deprn_reserve,
x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
x_general_fund_per => l_get_ytd.general_fund_per,
x_general_fund_acc => l_get_ytd.general_fund_acc,
x_last_reval_date => l_get_ytd.last_reval_date,
x_current_reval_factor => l_get_ytd.current_reval_factor,
x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
x_active_flag => l_get_ytd.active_flag
);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_get_ytd.distribution_id,
x_deprn_period => 0,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode => 'R'
);
SELECT count(*)
INTO l_exists
FROM igi_iac_asset_balances
WHERE asset_id = g_asset_id
AND book_type_code = g_book_type_code
AND period_counter = p_period_counter;
IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
X_rowid => l_rowid,
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'finish asset balance updates');
SELECT iidb.adjustment_id,
iidb.distribution_id,
iidb.adjustment_cost,
iidb.net_book_value,
iidb.reval_reserve_cost,
iidb.reval_reserve_backlog,
iidb.reval_reserve_gen_fund,
iidb.reval_reserve_net,
iidb.operating_acct_cost,
iidb.operating_acct_backlog,
iidb.operating_acct_net,
iidb.operating_acct_ytd,
iidb.deprn_period,
iidb.deprn_ytd,
iidb.deprn_reserve,
iidb.deprn_reserve_backlog,
iidb.general_fund_per,
iidb.general_fund_acc,
iidb.last_reval_date,
iidb.current_reval_factor,
iidb.cumulative_reval_factor,
iidb.active_flag
FROM igi_iac_det_balances iidb,
fa_distribution_history fdh
WHERE iidb.adjustment_id = n_adjust_id
AND iidb.distribution_id = n_dist_id
AND fdh.distribution_id = iidb.distribution_id
AND fdh.retirement_id = n_retirement_id;
SELECT iidb.distribution_id,
iidb.adjustment_id,
iidb.operating_acct_ytd,
iidb.deprn_ytd,
iidb.last_reval_date,
iidb.current_reval_factor,
iidb.cumulative_reval_factor
FROM igi_iac_det_balances iidb
WHERE iidb.distribution_id = n_dist_id
AND iidb.period_counter = (SELECT max(idb1.period_counter)
FROM igi_iac_det_balances idb1
WHERE idb1.distribution_id = n_dist_id
AND active_flag IS NULL);
SELECT COUNT(*)
INTO l_retire_effect
FROM fa_distribution_history
WHERE distribution_id = l_det_bal.distribution_id
AND retirement_id = g_retirement_id;
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_adjustment_cost,
x_net_book_value => l_net_book_value,
x_reval_reserve_cost => l_reval_reserve_cost,
x_reval_reserve_backlog => l_reval_reserve_backlog,
x_reval_reserve_gen_fund => l_reval_reserve_gen_fund,
x_reval_reserve_net => l_reval_reserve_net,
x_operating_acct_cost => l_operating_acct_cost,
x_operating_acct_backlog => l_operating_acct_backlog,
x_operating_acct_net => l_operating_acct_net,
x_operating_acct_ytd => l_operating_acct_ytd,
x_deprn_period => l_deprn_period,
x_deprn_ytd => l_deprn_ytd,
x_deprn_reserve => l_deprn_reserve,
x_deprn_reserve_backlog => l_deprn_reserve_backlog,
x_general_fund_per => l_general_fund_per,
x_general_fund_acc => l_general_fund_acc,
x_last_reval_date => l_det_bal.last_reval_date,
x_current_reval_factor => l_det_bal.current_reval_factor,
x_cumulative_reval_factor => l_det_bal.cumulative_reval_factor,
x_active_flag => null,
x_mode => 'R'
);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_ren_dist_id,
x_deprn_period => l_fa_deprn_period,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => l_fa_deprn_reserve,
x_active_flag => null,
x_mode => 'R'
);
SELECT units_assigned,
transaction_units
INTO l_units_assigned,
l_dist_units
FROM fa_distribution_history
WHERE distribution_id = l_det_bal.distribution_id;
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup_amt, -- l_deprn_period,
x_adjustment_type => 'EXPENSE',
x_adjustment_offset_type => 'RESERVE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup_amt,
x_adjustment_type => 'RESERVE',
x_adjustment_offset_type => 'EXPENSE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup_amt, --l_gf_catchup_amt,
x_adjustment_type => 'REVAL RESERVE',
x_adjustment_offset_type => 'GENERAL FUND', -- Null,
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup_amt, --l_gf_catchup_amt,
x_adjustment_type => 'GENERAL FUND',
x_adjustment_offset_type => 'REVAL RESERVE',
x_report_ccid => l_reval_rsv_ccid,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
SELECT new.distribution_id
INTO l_ret_dist_id
FROM fa_distribution_history new, fa_distribution_history old
WHERE old.retirement_id = g_retirement_id
AND old.location_id = new.location_id
AND old.code_combination_id = new.code_combination_id
AND NVL(old.assigned_to,-99) = NVL(new.assigned_to,-99)
AND old.transaction_header_id_out = new.transaction_header_id_in
AND old.distribution_id = l_det_bal.distribution_id ;
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_ret_dist_id,
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 => l_ret_ytd.operating_acct_ytd,
x_deprn_period => 0,
x_deprn_ytd => 0, --l_ret_ytd.deprn_ytd,
x_deprn_reserve => 0,
x_deprn_reserve_backlog => 0,
x_general_fund_per => 0,
x_general_fund_acc => 0,
x_last_reval_date => l_ret_ytd.last_reval_date,
x_current_reval_factor => l_ret_ytd.current_reval_factor,
x_cumulative_reval_factor => l_ret_ytd.cumulative_reval_factor,
x_active_flag => 'N',
x_mode => 'R'
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted YTD for partially retired distribution: '||l_ret_dist_id);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_ret_dist_id,
x_deprn_period => 0,
x_deprn_ytd => 0, --l_fa_deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode => 'R'
);
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'EXPENSE',
x_adjustment_offset_type => 'RESERVE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup, --l_rsv_catchup_amt,
x_adjustment_type => 'RESERVE',
x_adjustment_offset_type => 'EXPENSE',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'DR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'REVAL RESERVE',
x_adjustment_offset_type => 'GENERAL FUND',
x_report_ccid => Null,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_book_type_code => g_book_type_code,
x_code_combination_id => l_ccid,
x_set_of_books_id => p_sob_id,
x_dr_cr_flag => 'CR',
x_amount => l_rsv_catchup,
x_adjustment_type => 'GENERAL FUND',
x_adjustment_offset_type => 'REVAL RESERVE',
x_report_ccid => l_reval_rsv_ccid,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => g_asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_period_counter,
x_mode => 'R',
x_event_id => p_event_id );
IGI_IAC_DET_BALANCES_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => p_adjust_id_reinstate,
x_asset_id => g_asset_id,
x_book_type_code => g_book_type_code,
x_distribution_id => l_get_ytd.distribution_id,
x_period_counter => p_period_counter,
x_adjustment_cost => l_get_ytd.adjustment_cost,
x_net_book_value => l_get_ytd.net_book_value,
x_reval_reserve_cost => l_get_ytd.reval_reserve_cost,
x_reval_reserve_backlog => l_get_ytd.reval_reserve_backlog,
x_reval_reserve_gen_fund => l_get_ytd.reval_reserve_gen_fund,
x_reval_reserve_net => l_get_ytd.reval_reserve_net,
x_operating_acct_cost => l_get_ytd.operating_acct_cost,
x_operating_acct_backlog => l_get_ytd.operating_acct_backlog,
x_operating_acct_net => l_get_ytd.operating_acct_net,
x_operating_acct_ytd => l_get_ytd.operating_acct_ytd,
x_deprn_period => l_get_ytd.deprn_period,
x_deprn_ytd => l_get_ytd.deprn_ytd,
x_deprn_reserve => l_get_ytd.deprn_reserve,
x_deprn_reserve_backlog => l_get_ytd.deprn_reserve_backlog,
x_general_fund_per => l_get_ytd.general_fund_per,
x_general_fund_acc => l_get_ytd.general_fund_acc,
x_last_reval_date => l_get_ytd.last_reval_date,
x_current_reval_factor => l_get_ytd.current_reval_factor,
x_cumulative_reval_factor => l_get_ytd.cumulative_reval_factor,
x_active_flag => l_active_flag
);
IGI_IAC_FA_DEPRN_PKG.Insert_Row(
x_rowid => l_rowid,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => p_adjust_id_reinstate,
x_distribution_id => l_get_ytd.distribution_id,
x_deprn_period => 0,
x_deprn_ytd => l_fa_deprn_ytd,
x_deprn_reserve => 0,
x_active_flag => 'N',
x_mode => 'R'
);
SELECT count(*)
INTO l_exists
FROM igi_iac_asset_balances
WHERE asset_id = g_asset_id
AND book_type_code = g_book_type_code
AND period_counter = p_period_counter;
IGI_IAC_ASSET_BALANCES_PKG.Update_Row(
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
IGI_IAC_ASSET_BALANCES_PKG.Insert_Row(
X_rowid => l_rowid,
X_asset_id => g_asset_id,
X_book_type_code => g_book_type_code,
X_period_counter => p_period_counter,
X_net_book_value => l_ab_net_book_value,
X_adjusted_cost => l_ab_adjusted_cost,
X_operating_acct => l_ab_operating_acct,
X_reval_reserve => l_ab_reval_reserve,
X_deprn_amount => l_ab_deprn_amount,
X_deprn_reserve => l_ab_deprn_reserve,
X_backlog_deprn_reserve => l_ab_backlog_deprn_reserve,
X_general_fund => l_ab_general_fund,
X_last_reval_date => l_ret_ass_bal.last_reval_date,
X_current_reval_factor => l_ret_ass_bal.current_reval_factor,
X_cumulative_reval_factor => l_ret_ass_bal.cumulative_reval_factor
) ;
SELECT iaa.adjustment_id,
iaa.book_type_code,
iaa.code_combination_id,
iaa.set_of_books_id,
iaa.dr_cr_flag,
iaa.amount,
iaa.adjustment_type,
iaa.transfer_to_gl_flag,
iaa.units_assigned,
iaa.asset_id,
iaa.distribution_id,
iaa.period_counter,
iaa.adjustment_offset_type,
iaa.report_ccid
FROM igi_iac_adjustments iaa,
igi_iac_transaction_headers iath
WHERE iaa.adjustment_id = iath.adjustment_id
AND iath.transaction_header_id = p_trx_header_id;
IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => l_adjust_id_reinstate, -- out parameter
x_transaction_header_id => p_fa_reins_rec_info.transaction_header_id,
x_adjustment_id_out => null,
x_transaction_type_code => p_fa_reins_rec_info.transaction_type_code,
x_transaction_date_entered => p_fa_reins_rec_info.transaction_date_entered,
x_mass_refrence_id => p_fa_reins_rec_info.mass_reference_id,
x_transaction_sub_type => p_fa_reins_rec_info.transaction_subtype,
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_category_id => p_fa_reins_rec_info.asset_category_id,
x_adj_deprn_start_date => null,
x_revaluation_type_flag => null,
x_adjustment_status => 'COMPLETE',
x_period_counter => p_fa_reins_rec_info.curr_period_counter,
x_event_id => p_event_id );
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into headers table');
IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
x_prev_adjustment_id => l_latest_adj_id,
x_adjustment_id => l_adjust_id_reinstate
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updated Headers table');
SELECT units_assigned
INTO l_units_assigned
FROM fa_distribution_history
WHERE distribution_id = l_reverse_je.distribution_id;
IGI_IAC_ADJUSTMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => l_adjust_id_reinstate,
x_book_type_code => l_reverse_je.book_type_code,
x_code_combination_id => l_reverse_je.code_combination_id,
x_set_of_books_id => l_reverse_je.set_of_books_id,
x_dr_cr_flag => l_dr_cr_flag,
x_amount => l_reverse_je.amount,
x_adjustment_type => l_reverse_je.adjustment_type,
x_adjustment_offset_type => l_reverse_je.adjustment_offset_type,
x_report_ccid => l_reverse_je.report_ccid,
x_transfer_to_gl_flag => 'Y',
x_units_assigned => l_units_assigned,
x_asset_id => l_reverse_je.asset_id,
x_distribution_id => l_ren_dist_id,
x_period_counter => p_fa_reins_rec_info.curr_period_counter,
x_mode => 'R',
x_event_id => p_event_id
);
IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
x_rowid => l_rowid,
x_adjustment_id => l_adjust_id_reinstate, -- out parameter
x_transaction_header_id => null,
x_adjustment_id_out => null,
x_transaction_type_code => 'DEPRECIATION',
x_transaction_date_entered => p_fa_reins_rec_info.transaction_date_entered,
x_mass_refrence_id => null,
x_transaction_sub_type => 'REINSTATEMENT',
x_book_type_code => g_book_type_code,
x_asset_id => g_asset_id,
x_category_id => p_fa_reins_rec_info.asset_category_id,
x_adj_deprn_start_date => null,
x_revaluation_type_flag => null,
x_adjustment_status => 'COMPLETE',
x_period_counter => p_fa_reins_rec_info.curr_period_counter,
x_event_id => p_event_id
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Inserted into headers table');
IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
x_prev_adjustment_id => l_latest_adj_id,
x_adjustment_id => l_adjust_id_reinstate
);
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updated Headers table');
SELECT fth.transaction_header_id,
fth.book_type_code,
fth.asset_id,
fth.transaction_type_code,
fth.transaction_date_entered,
fth.date_effective,
fth.mass_reference_id,
fth.transaction_subtype,
fab.asset_category_id
FROM fa_transaction_headers fth,
fa_additions_b fab
WHERE fth.asset_id = fab.asset_id
AND fth.transaction_header_id = p_trx_hdr_id;
SELECT *
FROM IGI_IAC_TRANSACTION_HEADERS
WHERE book_type_code = p_book_type_code
AND period_counter >= p_period_counter
AND asset_id = p_asset_id
AND transaction_type_code='REVALUATION' and transaction_sub_type in ('OCCASSIONAL','PRFOESSIONAL');
SELECT asset_number
INTO l_asset_num
FROM fa_additions_b
WHERE asset_id = p_asset_id;