[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_delete_clause in varchar2
,l_query out nocopy varchar2) is
l_static varchar2(200):='select copy_entity_result_id,table_alias, information1 pk_id,dml_operation from ben_copy_entity_results where copy_entity_txn_id = ' || p_copy_entity_txn_id || ' and ' ||
'table_alias in (';
l_query := l_static || l_dynamic || ' = ' ||p_pk_id || p_delete_clause ;
procedure delete_dpnts(p_table_alias in varchar2
, p_copy_entity_txn_id in Number
,p_pk_id in Number
,p_top_level_result_id in varchar2) is
type c_dpnts is REF CURSOR;
cursor l_template is select copy_entity_result_id,table_alias,information1 pk_id, dml_operation from ben_copy_entity_results;
l_proc varchar2(72) := g_package||'delete_dpnts';
l_delete_clause varchar2(100) := ' AND( DML_OPERATION <> ''DELETE'' OR pd_parent_entity_result_id IS NULL )';
get_query(p_table_alias,p_copy_entity_txn_id,p_pk_id,l_delete_clause,l_query);
delete_dpnts(l_dpnt_rows.table_alias,p_copy_entity_txn_id,l_dpnt_rows.pk_id,p_top_level_result_id);
IF(l_dpnt_rows.dml_operation = 'INSERT') THEN
delete from ben_copy_entity_results where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode ='DELETE',pd_parent_entity_result_id= p_top_level_result_id where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
IF(l_dpnt_rows.dml_operation = 'REUSE' OR l_dpnt_rows.dml_operation = 'UPDATE') THEN
null;
delete from ben_copy_entity_results where copy_entity_result_id = l_dpnt_rows.copy_entity_result_id ;
end delete_dpnts;
UPDATE ben_copy_entity_results cer
set (table_route_id, order_in_hierarchy) =
(select table_route_id,display_order
from pqh_table_route
where from_clause ='OAB'
and table_alias = cer.table_alias)
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and( table_route_id is null
or order_in_hierarchy is null);
UPDATE ben_copy_entity_results cer
set (table_route_id, order_in_hierarchy) =
(select table_route_id,display_order
from pqh_table_route
where from_clause ='OAB'
and table_alias = cer.table_alias)
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and (pd_parent_entity_result_id = p_parent_entity_result_id
or copy_entity_result_id = p_parent_entity_result_id)
and( table_route_id is null
or order_in_hierarchy is null);
procedure update_result_rows(p_copy_entity_txn_id in number)
is
l_proc varchar2(72) := g_package||'update_result_rows';
select src_effective_date into l_effectve_date
from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
UPDATE ben_copy_entity_results cer
set number_of_copies = 0
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and l_effectve_date not between information2 and information3;
end update_result_rows;
procedure validate_delete_api_calls
( p_copy_entity_txn_id in Number
,p_parent_entity_result_id in varchar2
,p_delete_failed out nocopy varchar2
)is
l_validate number :=0;
savepoint VALIDATE_DELETE_API_CALLS;
update ben_copy_entity_results set dml_operation = 'DELETE',datetrack_mode ='DELETE' where copy_entity_result_id = p_parent_entity_result_id ;
ben_plan_design_delete_api.call_delete_apis_for_hierarchy
( p_process_validate => l_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_parent_entity_result_id => p_parent_entity_result_id
,p_delete_failed => p_delete_failed
);
rollback to VALIDATE_DELETE_API_CALLS;
ROLLBACK TO VALIDATE_DELETE_API_CALLS;
end validate_delete_api_calls;
procedure delete_Entity
(p_copy_entity_txn_id in Number
,p_copy_entity_result_id in Number
,p_table_alias in Varchar2
) is
l_pk_id Number;
l_proc varchar2(72) := g_package||'delete_entity';
select information1 from ben_copy_entity_results where
copy_entity_result_id = p_copy_entity_result_id;
delete_dpnts(p_table_alias,p_copy_entity_txn_id,l_pk_id,p_copy_entity_result_id);
end delete_Entity ;
procedure delete_entity
(p_copy_entity_txn_id in Number
,p_copy_entity_result_id in Number
,p_table_alias in Varchar2
,p_top_level_entity in varchar2
) is
l_pk_id Number;
p_delete_failed varchar2(1) := 'N';
l_proc varchar2(72) := g_package||'delete_entity';
select information1 from ben_copy_entity_results where
copy_entity_result_id = p_copy_entity_result_id;
delete_dpnts(p_table_alias,p_copy_entity_txn_id,l_pk_id,p_copy_entity_result_id);
validate_delete_api_calls
(p_copy_entity_txn_id =>p_copy_entity_txn_id
,p_parent_entity_result_id =>p_copy_entity_result_id
,p_delete_failed => p_delete_failed
);
end delete_Entity ;
,p_delete_failed out nocopy varchar2
)
is
cursor c_effective_date is
select src_effective_date
from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
update_result_rows(p_copy_entity_txn_id);
savepoint DELETE_REQUEST;
ben_plan_design_delete_api.call_delete_apis
( p_process_validate => p_process_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_delete_failed => p_delete_failed
);
UPDATE ben_copy_entity_results cer
set number_of_copies = 0
where cer.copy_entity_txn_id = p_copy_entity_txn_id
and l_effective_date between information2 and information3
and cer.dml_operation = 'DELETE';
rollback to DELETE_REQUEST;
p_delete_failed :='Y';
/*-- call delete again if the delete failed previously
if(p_delete_failed ='Y') then
p_delete_failed:='N';
ben_plan_design_delete_api.call_delete_apis
( p_process_validate => p_process_validate
,p_copy_entity_txn_id => p_copy_entity_txn_id
,p_delete_failed => p_delete_failed
);
procedure reuse_deleted_hierarchy
(p_copy_entity_txn_id in number
,p_copy_entity_result_id in number)is
begin
update ben_copy_entity_results
set dml_operation = 'REUSE'
,datetrack_mode= 'INSERT'
,pd_parent_entity_result_id = null
where
copy_entity_txn_id = p_copy_entity_txn_id
and (pd_parent_entity_result_id = p_copy_entity_result_id
or copy_entity_result_id = p_copy_entity_result_id);