[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select revaluation_id, book_type_code, revaluation_period
from igi_iac_revaluations
where revaluation_id = p_revaluation_id
;
select iir.revaluation_id, iir.book_type_code
from igi_iac_revaluations iir
where iir.revaluation_id = fp_revaluation_id
and iir.book_type_code = fp_book_type_code
;
select category_id
from igi_iac_reval_categories
where revaluation_id = cp_revaluation_id
and book_type_code = cp_book_type_code
and nvl(select_category,'X') = 'Y';
select ac.asset_id, ac.revaluation_type, ac.revaluation_factor, fadd.asset_number
from igi_iac_reval_asset_rules ac, fa_additions fadd
where ac.revaluation_id = cp_revaluation_id
and ac.book_type_code = cp_book_type_code
and ac.category_id = cp_category_id
and ac.asset_id = fadd.asset_id
and nvl(ac.selected_for_reval_flag,'X') = 'Y'
and exists ( select 1
from igi_iac_transaction_headers
where asset_id = ac.asset_id
and book_type_code = ac.book_type_code
and mass_reference_id = ac.revaluation_id
and adjustment_status = 'PREVIEW'
)
;
if IGI_IAC_REVAL_CRUD.update_balances
( fp_reval_id => l_reval.revaluation_id
, fp_asset_id => l_asset_id(l_loop_count)
, fp_period_counter => fp_period_counter
, fp_book_type_code => l_reval.book_type_code
)
then
l_success_ct := l_success_ct + 1;
select adjustment_id
into l_adjustment_id
from igi_iac_transaction_headers
where book_type_code = l_reval.book_type_code
and asset_id = l_asset_id(l_loop_count)
and adjustment_id_out is null
;
if not igi_iac_reval_crud.update_reval_rates
( fp_adjustment_id => l_adjustment_id )
then
l_failure_ct := l_failure_ct + 1;
Following code will delete SLA event in case of failure*/
if l_failure_ct = 0 and l_success_ct > 0 then
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Revaluation is complete...');
If IGI_IAC_XLA_EVENTS_PKG.delete_revaluation_event(l_reval.revaluation_id) then
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Event deleted successfully');
select iir.revaluation_id, iir.book_type_code
from igi_iac_revaluations iir
where iir.revaluation_id = fp_revaluation_id
and iir.book_type_code = fp_book_type_code
-- and upper(iir.status) = IGI_IAC_TYPES.gc_previewed_status
;
select category_id
from igi_iac_reval_categories
where revaluation_id = cp_revaluation_id
and book_type_code = cp_book_type_code
and nvl(select_category,'X') = 'Y'
;
select r.asset_id, r.revaluation_type, r.revaluation_factor, fadd.asset_number
from igi_iac_reval_asset_rules r, fa_additions fadd
where r.revaluation_id = cp_revaluation_id
and r.book_type_code = cp_book_type_code
and r.category_id = cp_category_id
and nvl(r.selected_for_reval_flag,'X') = 'Y'
and r.asset_id = fadd.asset_id
and not exists ( select 'x'
from igi_iac_transaction_headers
where asset_id = r.asset_id
and book_type_code = r.book_type_code
and mass_reference_id = r.revaluation_id
and transaction_type_code = 'REVALUATION'
)
;
function preview_mode_hist_delete ( fp_revaluation_id in number)
return boolean is
cursor c_reval_rates is
select distinct asset_id, book_type_code, period_counter
from igi_iac_transaction_headers
where mass_reference_id = fp_revaluation_id
and transaction_type_code = 'REVALUATION'
;
select adjustment_id
from igi_iac_transaction_headers t
where book_type_code = cp_book_type_code
and asset_id = cp_asset_id
and transaction_type_code = 'REVALUATION'
and period_counter = cp_period_counter
and mass_reference_id = fp_revaluation_id
and exists ( select 'x'
from igi_iac_reval_asset_rules
where asset_id = t.asset_id
and book_type_code = t.book_type_code
and revaluation_id = t.mass_reference_id
and nvl(allow_prof_update,'X') = 'Y'
)
;
l_delete_flag boolean;
l_path := g_path||'preview_mode_hist_delete';
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Begin preview_mode_hist_delete');
l_delete_flag := false;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from exceptions table');
l_delete_flag := true;
delete from igi_iac_exceptions
where asset_id = l_rates.asset_id
and revaluation_id = fp_revaluation_id
and book_type_code = l_rates.book_type_code
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the transaction headers table');
delete igi_iac_transaction_headers
where adjustment_id = l_headers.adjustment_id
and adjustment_id_out is null
/* and not exists ( select 'x'
from igi_iac_transaction_headers
where adjustment_id_out = l_headers.adjustment_id
)*/
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+update previous transaction row if one exists');
select 'x'
from igi_iac_transaction_headers
where adjustment_id_out = l_headers.adjustment_id
;
update igi_iac_transaction_headers
set adjustment_id_out = null
where adjustment_id_out = l_headers.adjustment_id
and book_type_code = l_rates.book_type_code
and asset_id = l_rates.asset_id
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the adjustments table');
delete igi_iac_adjustments
where adjustment_id = l_headers.adjustment_id
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the iac adjustments');
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the det balances table');
delete igi_iac_det_balances
where adjustment_id = l_headers.adjustment_id
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the det balances info');
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the revaluation rates table');
delete from igi_iac_revaluation_rates
where adjustment_id = l_headers.adjustment_id
;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the revaluation rates info');
if not l_delete_flag then
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'no preview_mode_hist_delete');
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'yes preview_mode_hist_delete');
select distinct asset_id, book_type_code, period_counter
from igi_iac_transaction_headers
where mass_reference_id = fp_revaluation_id
and transaction_type_code = 'REVALUATION'
and adjustment_status = 'PREVIEW'
;
select distinct asset_id, book_type_code, period_counter
from igi_iac_transaction_headers
where mass_reference_id = fp_revaluation_id
and transaction_type_code = 'REVALUATION'
and adjustment_status = 'RUN'
;
preview_mode_hist_delete ( fp_revaluation_id => revaluation_id ) then
if not preview_mode_hist_generate ( fp_revaluation_id => revaluation_id ,
fp_book_type_code => book_type_code ,
fp_period_counter => period_counter ,
fp_wait_request_id => create_request_id
) then
rollback;