The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT allow_indexed_reval_flag
FROM igi_iac_category_books
WHERE book_type_code = p_book_type_code
AND category_id = p_category_id;
SELECT period_num_for_catchup
FROM igi_iac_book_controls
WHERE book_type_code = p_book_type_code;
SELECT deprn_reserve
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND deprn_source_code = 'BOOKS';
/* bug 2450796 sekhar need to update the reval rates ..only one record should have staus = 'Y' for an asset */
Cursor C_Reval_Rates is
SELECT max(adjustment_id)
FROM igi_iac_transaction_headers ith
WHERE ith.book_type_code =p_book_type_code
AND ith.asset_id = p_asset_id
AND (ith.transaction_type_code = 'ADDITION' AND ith.Transaction_sub_type ='REVALUATION');
SELECT salvage_value,cost
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 'X'
FROM IGI_IAC_CATEGORY_BOOKS
WHERE book_type_code = l_book_type_code
AND category_id = l_category_id
AND rownum = 1;
/* Last revaluation - Insert records into revaluation tables*/
IF (p_calling_function <> 'UPGRADE') THEN
igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
p_full_path => l_path_name,
p_string => ' Last Revaluation - Inserting into igi_iac_revaluations');
igi_iac_revaluations_pkg.insert_row
(l_rowid,
l_revaluation_id,
p_book_type_code,
sysdate,
l_reval_asset_params(l_idx1).period_counter,
'NEW',
NULL,
NULL,
'ADDITION',
X_event_id => p_event_id);
p_string => ' Inserting into igi_iac_reval_asset_rules');
igi_iac_reval_asset_rules_pkg.insert_row
(l_rowid,
l_revaluation_id,
l_reval_asset_params(l_idx1).book_type_code,
l_reval_asset_params(l_idx1).category_id,
l_reval_asset_params(l_idx1).asset_id,
l_reval_asset_rules(l_idx1).revaluation_factor,
l_reval_asset_rules(l_idx1).revaluation_type,
l_reval_asset_rules(l_idx1).new_cost,
l_reval_input_asset(l_idx2).adjusted_cost,
'Y',
'N',
NULL
);
UPDATE igi_iac_revaluation_rates
SET period_counter = l_last_reval_period
WHERE revaluation_id = l_revaluation_id
AND asset_id = p_asset_id
AND book_type_code = p_book_type_code;
p_string => ' Records in reval rates updated for correct period');
p_string => '*** No record found in reval rates table to update');
/* bug 2502128 need to update the reval rates ..only one record should have staus = 'Y' for an asset */
IF (p_calling_function <> 'UPGRADE') THEN
l_get_latest_adjustment_id :=0;
IF NOT IGI_IAC_REVAL_CRUD.update_reval_rates (fp_adjustment_id => l_get_latest_adjustment_id) THEN
igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
p_full_path => l_path_name,
p_string => '*** Failed to Update REVAL RATES');
UPDATE igi_iac_transaction_headers
SET event_id = p_event_id
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id;
UPDATE igi_iac_adjustments
SET event_id = p_event_id
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id;
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;