[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asset_id,adjustment_id,transaction_sub_type
FROM igi_iac_transaction_headers
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND transaction_type_code = 'ADDITION';
SELECT distribution_id
FROM igi_iac_det_balances
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id;
SELECT a.revaluation_id
FROM igi_iac_revaluations r,igi_iac_reval_asset_rules a
WHERE a.revaluation_id = r.revaluation_id
AND a.book_type_code = p_book_type_code
AND a.asset_id = p_asset_id
AND r.calling_program = 'ADDITION';
SELECT 'X'
FROM igi_iac_adjustments
WHERE adjustment_id = p_adjustment_id
AND book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND rownum = 1;
SELECT 'X'
FROM igi_iac_asset_balances
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND period_counter = cp_period_counter;
SELECT 'X'
FROM igi_iac_revaluation_rates
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND revaluation_id = p_revaluation_id;
SELECT distribution_id
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;
/* Delete records from igi_iac_adjustments */
l_dummy := NULL;
igi_iac_adjustments_pkg.delete_row(
x_adjustment_id => l_asset_info.adjustment_id);
p_string => ' No records found in igi_iac_adjustments for delete');
/* Delete records from igi_iac_det_balances */
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Deleting records from igi_iac_det_balances');
igi_iac_det_balances_pkg.delete_row(
x_adjustment_id => l_asset_info.adjustment_id,
x_asset_id => l_asset_info.asset_id,
x_distribution_id => l_det_balance.distribution_id,
x_book_type_code => p_book_type_code,
x_period_counter => p_period_counter);
/* Delete records from igi_iac_fa_deprn */
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Deleting records from igi_iac_fa_deprn');
igi_iac_fa_deprn_pkg.delete_row(
x_book_type_code => p_book_type_code,
x_asset_id => l_asset_info.asset_id,
x_period_counter => p_period_counter,
x_adjustment_id => l_asset_info.adjustment_id,
x_distribution_id => l_iac_fa_det_balance.distribution_id);
/* Delete records from igi_iac_asset_balances */
IF l_asset_info.transaction_sub_type <> 'CATCHUP' THEN
OPEN c_get_asset_balances(l_asset_info.asset_id,p_period_counter);
igi_iac_asset_balances_pkg.delete_row(
x_asset_id => l_asset_info.asset_id,
x_book_type_code => p_book_type_code,
x_period_counter => p_period_counter);
p_string => ' No records found in igi_iac_asset_balances to delete');
igi_iac_asset_balances_pkg.delete_row(
x_asset_id => l_asset_info.asset_id,
x_book_type_code => p_book_type_code,
x_period_counter => p_period_counter+1);
p_string => ' No records found in igi_iac_asset_balances to delete');
/* Delete records from igi_iac_transaction_headers */
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Deleting records from igi_iac_transaction_headers');
igi_iac_trans_headers_pkg.delete_row(
x_adjustment_id => l_asset_info.adjustment_id);
/* Delete records from igi_iac_reval_asset_rules */
IF (l_revaluation_id IS NOT NULL) THEN
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Deleting records from igi_iac_reval_asset_rules');
igi_iac_reval_asset_rules_pkg.delete_row(
x_asset_id => l_asset_info.asset_id,
x_book_type_code => p_book_type_code,
x_revaluation_id => l_revaluation_id);
/* Delete records from igi_iac_revaluations */
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Deleting records from igi_iac_revaluations');
igi_iac_revaluations_pkg.delete_row(
x_revaluation_id => l_revaluation_id);
/* Delete records from igi_iac_revaluation_rates */
OPEN c_get_revaluation_rates(l_asset_info.asset_id,l_revaluation_id);
DELETE FROM igi_iac_revaluation_rates
WHERE asset_id = l_asset_info.asset_id
AND book_type_code = p_book_type_code
AND revaluation_id = l_revaluation_id;
/* Cursor to select data from transaction headers which need to be rolled back */
CURSOR c_trans_headers IS
SELECT *
FROM igi_iac_transaction_headers
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND transaction_type_code='TRANSFER';
SELECT sum(deprn_period)
FROM igi_iac_det_balances
WHERE book_type_code = p_book_type_code
AND asset_id = cp_asset_id
AND adjustment_id = cp_adjustment_id;
/* Cursor to select the previous data */
CURSOR c_prev_data(c_adjustment_id igi_iac_transaction_headers.adjustment_id%type) IS
SELECT *
FROM igi_iac_transaction_headers
WHERE adjustment_id_out=c_adjustment_id;
SELECT *
FROM igi_iac_asset_balances
WHERE asset_id = cp_asset_id
AND book_type_code = p_book_type_code
AND period_counter = p_period_counter;
/* Cursor to select impacted distributions for roll back */
CURSOR c_dist(c_adjustment_id igi_iac_adjustments.adjustment_id%type) IS
SELECT distribution_id
FROM igi_iac_det_balances
WHERE adjustment_id=c_adjustment_id;
SELECT book_type_code,asset_id,period_counter,distribution_id,adjustment_id
FROM igi_iac_fa_deprn
WHERE asset_id = cp_asset_id
AND book_type_code = p_book_type_code
AND adjustment_id = cp_adjustment_id;
/*DELETE from igi_iac_det_balances
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND adjustment_id=l_trans_headers.adjustment_id;*/
IGI_IAC_DET_BALANCES_PKG.delete_row(
x_adjustment_id =>l_trans_headers.adjustment_id,
x_asset_id =>l_trans_headers.asset_id,
x_distribution_id =>l_dist.distribution_id,
x_book_type_code =>l_trans_headers.book_type_code,
x_period_counter =>p_period_counter
);
IGI_IAC_FA_DEPRN_PKG.delete_row(
x_adjustment_id =>l_fa_dist.adjustment_id,
x_asset_id =>l_fa_dist.asset_id,
x_distribution_id =>l_fa_dist.distribution_id,
x_book_type_code =>l_fa_dist.book_type_code,
x_period_counter =>l_fa_dist.period_counter
);
/* DELETE from igi_iac_adjustments
WHERE book_type_code=p_book_type_code
AND period_counter=p_period_counter
AND adjustment_id=l_trans_headers.adjustment_id; */
IGI_IAC_ADJUSTMENTS_PKG.delete_row(
x_adjustment_id =>l_trans_headers.adjustment_id
);
IGI_IAC_ASSET_BALANCES_PKG.update_row (
x_asset_id =>l_trans_headers.asset_id,
x_book_type_code =>p_book_type_code,
x_period_counter =>p_period_counter,
x_net_book_value =>l_amounts.net_book_value,
x_adjusted_cost =>l_amounts.adjusted_cost,
x_operating_acct =>l_amounts.operating_acct,
x_reval_reserve =>l_amounts.reval_reserve,
x_deprn_amount =>l_deprn_expense,
x_deprn_reserve =>l_amounts.deprn_reserve,
x_backlog_deprn_reserve =>l_amounts.backlog_deprn_reserve,
x_general_fund =>l_amounts.general_fund,
x_last_reval_date =>l_amounts.last_reval_date,
x_current_reval_factor =>l_amounts.current_reval_factor,
x_cumulative_reval_factor =>l_amounts.cumulative_reval_factor,
x_mode =>'R'
);
IGI_IAC_TRANS_HEADERS_PKG.update_row (
x_prev_adjustment_id =>l_prev_data.adjustment_id,
x_adjustment_id =>null,
x_mode =>'R'
);
IGI_IAC_TRANS_HEADERS_PKG.delete_row (
x_adjustment_id =>l_trans_headers.adjustment_id
);