[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into l_cnt
from fnd_sessions
where session_id = userenv('sessionid');
hr_utility.set_location('session date not there, inserting',10);
insert into fnd_sessions (session_id,effective_date) values (userenv('sessionid'),trunc(p_effective_date));
select *
into p_crit_rec
from ben_eligy_criteria
where short_code =p_criteria_short_code
and business_group_id = p_business_group_id;
select *
into p_crit_rec
from ben_eligy_criteria
where short_code =p_criteria_short_code
and business_group_id is null;
select copy_entity_result_id
into l_rmn_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias ='RMN'
and information1 = p_rmn_id;
select ph.name
into l_ph_name
from per_position_structures ph, per_pos_structure_versions phv
where ph.position_structure_id = phv.position_structure_id
and phv.pos_structure_version_id = p_pos_hier_ver_id;
select oh.name
into l_oh_name
from per_organization_structures oh, per_org_structure_versions ohv
where oh.organization_structure_id = ohv.organization_structure_id
and ohv.org_structure_version_id = p_org_hier_ver_id;
select to_char(p_date_value1,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
into l_node1_desc
from dual;
select to_char(p_date_value2,fnd_profile.value('ICX_DATE_FORMAT_MASK'))
into l_node2_desc
from dual;
cursor c_rnv is select * from pqh_rt_matrix_node_values
where rate_matrix_node_id = p_rate_matrix_node_id;
select criteria_short_code
into l_crit_short_code
from pqh_rate_matrix_nodes
where rate_matrix_node_id = p_rate_matrix_node_id;
Select upper(flex_value_set_name)
into l_val_set_name
from fnd_flex_value_sets
where flex_value_set_id = l_crt_rec.col1_value_set_id;
select transaction_category_id
into l_rbc_txn_cat
from pqh_transaction_categories
where short_name ='RBC_MATRIX'
and business_group_id is null;
procedure delete_matrix(p_copy_entity_txn_id in number) is
begin
delete from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id ;
end delete_matrix;
cursor c_matx_rates is select distinct rate.criteria_rate_defn_id
from pqh_rate_matrix_rates_f rate, pqh_rate_matrix_nodes node
where node.pl_id = p_rate_matrix_id
and node.rate_matrix_node_id = rate.rate_matrix_node_id
and p_effective_date between effective_start_date and effective_end_date;
select define_min_rate_flag,define_mid_rate_flag,define_max_rate_flag,define_std_rate_flag,rate_calc_cd, name,currency_code,uom
into l_min,l_mid,l_max,l_rate,l_calc_mthd,l_crit_name,l_currency_code,l_uom
from pqh_criteria_rate_defn_vl
where criteria_rate_defn_id = matx_rate.criteria_rate_defn_id;
select *
into l_rate_rec
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and information162 = p_crit_rate_defn_id
and table_alias = 'RMR'
and parent_entity_result_id = p_rmn_cer_id
and dml_operation <> 'DELETE'
and p_effective_date between information2 and nvl(information3,to_date('31/12/4712','dd/mm/yyyy'));
select currency_code,reference_period_cd
into p_currency_cd,p_freq_cd
from pqh_criteria_rate_defn
where criteria_rate_defn_id = p_crit_rate_defn_id;
select *
into l_rate_rec
from pqh_rate_matrix_rates_f
where rate_matrix_node_id = p_rate_matrix_node_id
and criteria_rate_defn_id = p_crit_rate_defn_id
and p_effective_date between effective_start_date and effective_end_date;
select * from pqh_criteria_rate_factors
where criteria_rate_defn_id = p_criteria_rate_defn_id;
select * into l_crit_rate_rec
from pqh_criteria_rate_defn
where criteria_rate_defn_id = p_rate_defn_id;
select conversion_rate
into l_curr_conv
from gl_daily_rates
where from_currency = l_curr_cd
and to_currency = l_crit_rate_rec.currency_code
and conversion_date = (select max(conversion_date)
from gl_daily_rates
where from_currency = l_curr_cd
and to_currency = l_crit_rate_rec.currency_code
and conversion_date <=p_effective_date);
cursor csr_rate_types is select *
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias = 'RCR'
order by information160; -- in rate order
select copy_entity_result_id,information294,information295,information296,information297
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias = 'RMR'
and information162 = p_rate_defn_id
and parent_entity_result_id = p_node_cer_id and dml_operation <> 'DELETE';
ben_copy_entity_results_api.update_copy_entity_results(
p_effective_date => p_effective_date
,p_copy_entity_txn_id => p_cet_id
,p_result_type_cd => 'DISPLAY'
,p_table_name => p_rbr_tr_name
,p_table_route_id => p_rbr_tr_id
,p_table_alias => 'RBR'
,p_Information287 => min1
,p_Information288 => mid1
,p_Information289 => max1
,p_Information290 => rate1
,p_Information160 => rmr1_cer_id
,p_Information36 => min1_c
,p_Information37 => mid1_c
,p_Information38 => max1_c
,p_Information39 => rate1_c
,p_Information11 => min1_flag
,p_Information12 => mid1_flag
,p_Information13 => max1_flag
,p_Information14 => rate1_flag
,p_Information15 => comp1_flag
,p_Information293 => min2
,p_Information294 => mid2
,p_Information295 => max2
,p_Information296 => rate2
,p_Information161 => rmr2_cer_id
,p_Information40 => min2_c
,p_Information41 => mid2_c
,p_Information42 => max2_c
,p_Information43 => rate2_c
,p_Information16 => min2_flag
,p_Information17 => mid2_flag
,p_Information18 => max2_flag
,p_Information19 => rate2_flag
,p_Information20 => comp2_flag
,p_Information297 => min3
,p_Information298 => mid3
,p_Information299 => max3
,p_Information300 => rate3
,p_Information162 => rmr3_cer_id
,p_Information44 => min3_c
,p_Information45 => mid3_c
,p_Information46 => max3_c
,p_Information47 => rate3_c
,p_Information21 => min3_flag
,p_Information22 => mid3_flag
,p_Information23 => max3_flag
,p_Information24 => rate3_flag
,p_Information25 => comp3_flag
,p_Information301 => min4
,p_Information302 => mid4
,p_Information303 => max4
,p_Information304 => rate4
,p_Information169 => rmr4_cer_id
,p_Information48 => min4_c
,p_Information49 => mid4_c
,p_Information50 => max4_c
,p_Information51 => rate4_c
,p_Information26 => min4_flag
,p_Information27 => mid4_flag
,p_Information28 => max4_flag
,p_Information29 => rate4_flag
,p_Information30 => comp4_flag
,p_information1 => p_rmn_cer_id
,p_copy_entity_result_id => p_rbr_cer_id
,p_object_version_number => l_rbr_cer_ovn
,p_information323 => '');
hr_utility.set_location('rbr row updated',20);
cursor c1 is select *
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias = 'RBR'
and nvl(dml_operation,'UPDATE') = 'UPDATE';
cursor csr_nodes is select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias = 'RMN'
and dml_operation <> 'DELETE';
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias = 'RBR'
and information1 = p_rmn_cer_id;
cursor c1 is select * from ben_copy_entity_results
where copy_entity_result_id = p_rmr_cer_id
for update of dml_operation;
select * into l_rcr_rec
from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias ='RCR'
and information160 = p_rate_level;
l_dml_operation := 'UPDATE';
update ben_copy_entity_results
set information294 = p_min,
information295 = p_max,
information296 = p_mid,
information297 = p_value,
dml_operation = l_dml_operation
where current of c1;
cursor c1 is select dml_operation
from ben_copy_entity_results
where copy_entity_result_id = p_rmn_cer_id
for update of dml_operation;
if l_stat not in ('CREATE','UPDATE') then
update ben_copy_entity_results
set dml_operation ='UPDATE'
where current of c1;
cursor c1 is select * from ben_copy_entity_results
where copy_entity_txn_id = p_cet_id
and table_alias ='RBR'
and nvl(dml_operation,'UPDATE') = 'UPDATE'
for update of dml_operation;
update ben_copy_entity_results
set dml_operation = 'COPIED'
where current of c1;
select * into l_rbr_rec
from ben_copy_entity_results
where copy_entity_result_id = p_rbr_cer_id;
hr_utility.set_location('update rmn status '||l_rbr_rec.information1,10);
cursor c1 is select * from ben_pl_f
where pl_id = p_rate_matrix_id
and p_effective_date between effective_start_date and effective_end_date;
cursor c_node_val is select * from pqh_rt_matrix_node_values
where rate_matrix_node_id = p_rate_matrix_node_id;
cursor c_node_rate is select * from pqh_rate_matrix_rates_f
where rate_matrix_node_id = p_rate_matrix_node_id
and p_effective_date between effective_start_date and effective_end_date;
cursor c_crit is select distinct criteria_short_code,level_number
from pqh_rate_matrix_nodes
where pl_id = p_rate_matrix_id
and criteria_short_code is not null
order by level_number;
cursor c_crit is select distinct a.criteria_short_code,a.level_number
from pqh_rate_matrix_nodes a, ben_eligy_prfl_f b
where a.pl_id = p_rate_matrix_id
and a.criteria_short_code is not null
and a.eligy_prfl_id = b.eligy_prfl_id
and p_effective_date between b.effective_start_date and b.effective_end_date
order by a.level_number;
cursor c_node is select * from pqh_rate_matrix_nodes
where pl_id = p_rate_matrix_id
order by level_number;
cursor c_node is select a.* from pqh_rate_matrix_nodes a
where a.pl_id = p_rate_matrix_id
and (a.eligy_prfl_id is null
or exists (Select 1 from ben_eligy_prfl_f b
where a.eligy_prfl_id = b.eligy_prfl_id
and p_effective_date between b.effective_start_date and b.effective_end_date))
order by level_number;
select txn.copy_entity_txn_id
into l_cet_id
from pqh_copy_entity_txns txn , ben_copy_entity_results cer
where cer.copy_entity_txn_id = txn.copy_entity_txn_id
and txn.context ='RBC_MATRIX'
and txn.status = 'UPDATE'
and txn.context_business_group_id = p_business_group_id
and cer.table_alias = 'PLN'
and cer.information1 = p_rate_matrix_id;
create_matrix_txn(p_mode => 'UPDATE',
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_copy_entity_txn_id => l_cet_id);
create_matrix_txn(p_mode => 'UPDATE',
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_copy_entity_txn_id => l_cet_id);
delete_matrix(p_copy_entity_txn_id => l_cet_id);
hr_utility.set_location('cer rows for cet deleted',100);