[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_grrate(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
and table_alias ='GRRATE';
hr_utility.set_location('issues in deleteing gr rates',10);
end delete_grrate;
procedure delete_gsrate(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
and table_alias ='GSRATE';
hr_utility.set_location('issues in deleteing gs rates',10);
end delete_gsrate;
select information293,information287
into p_point_value,p_point_old_value
from ben_copy_entity_results
where table_alias = 'CRRATE'
and copy_entity_txn_id = p_copy_entity_txn_id
and information169 = p_point_cer_id
and information160 = p_crset_id
and p_effective_start_date between information2 and information3
and p_effective_end_date between information2 and information3;
select information297,information287
into p_point_value,p_point_old_value
from ben_copy_entity_results
where table_alias = 'HRRATE'
and copy_entity_txn_id = p_copy_entity_txn_id
and information278 = p_point_cer_id
and p_effective_start_date between information2 and information3
and p_effective_end_date between information2 and information3;
procedure update_date_ranges(p_start_date in date,
p_dt_matx in out nocopy t_pt_matx) is
l_exists boolean := false;
select information2 start_date
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and table_alias = 'HRRATE' -- check oipl row
and information278 = p_point_cer_id -- check rate of point row
order by 1 ;
select count(*),min(information2),max(information3)
into l_num_pt_rates,l_min_st_dt,l_max_end_dt
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and table_alias = 'HRRATE' -- check hrr row
and information278 = p_point_cer_id;
update_date_ranges(p_start_date => rate.start_date,
p_dt_matx => p_dt_matx);
select information2 start_date
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and table_alias = 'CRRATE' -- check oipl row
and information169 = p_point_cer_id -- check rate of point row
and information160 = p_crset_id
order by 1 ;
select count(*),min(information2),max(information3)
into l_num_pt_rates,l_min_st_dt,l_max_end_dt
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and table_alias = 'CRRATE' -- check crr row
and information160 = p_crset_id
and information278 = p_point_cer_id;
update_date_ranges(p_start_date => rate.start_date,
p_dt_matx => p_dt_matx);
select copy_entity_result_id,information252,information253
-- into p_plip_cer_id,p_grade_cer_id,p_grade_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and information104 = 'LINK' -- checked linked rows
and table_alias = 'CPP' -- check plip row
order by information263 ; -- for getting the order of grades correct
select information262
-- into p_point_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and information104 = 'LINK' -- checked linked rows
and table_alias = 'COP' -- check oipl row
and gs_parent_entity_result_id = p_plip_cer_id -- check child of plip row
order by information263 ; -- step sequence
select count(*)
into l_grd_num_steps
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY' -- which are displayed
and information104 = 'LINK' -- checked linked rows
and table_alias = 'COP' -- check oipl row
and gs_parent_entity_result_id = grd.copy_entity_result_id; -- check child of plip row
delete_gsrate(p_copy_entity_txn_id => p_copy_entity_txn_id);
delete_grrate(p_copy_entity_txn_id => p_copy_entity_txn_id);
update ben_copy_entity_results
set INFORMATION297 = p_new_value,
dml_operation = nvl(dml_operation,'UPDATE')
where information278 = p_point_cer_id
and table_alias ='HRRATE'
and p_effective_date between information2 and information3
and copy_entity_txn_id = p_copy_entity_txn_id;
procedure update_hrrate(p_copy_entity_txn_id in number,
p_point_cer_id in number,
p_datetrack_mode in varchar2,
p_effective_date in date,
p_new_value in number) is
l_old_hrr_cer_id number;
select copy_entity_result_id
into l_old_hrr_cer_id
from ben_copy_entity_results
where information278 = p_point_cer_id
and table_alias ='HRRATE'
and p_effective_date between information2 and information3
and copy_entity_txn_id = p_copy_entity_txn_id;
update_hrrate(p_old_hrrate_cer_id => l_old_hrr_cer_id,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_new_hrrate_cer_id => l_new_hrr_cer_id,
p_value => p_new_value);
end update_hrrate;
procedure update_hrrate(p_old_hrrate_cer_id in number,
p_effective_date in date,
p_value in number,
p_datetrack_mode in varchar2,
p_grd_min_value in number default null,
p_grd_mid_value in number default null,
p_grd_max_value in number default null,
p_new_hrrate_cer_id out nocopy number) is
l_eot date := to_date('31/12/4712','dd/mm/RRRR');
hr_utility.set_location('inside update_hrrate '||p_old_hrrate_cer_id,10);
select copy_entity_txn_id,table_alias,table_name,table_route_id,dml_operation,datetrack_mode,
information1,information2,information3,information4,information277,information278,
information293,information300,information298
into l_cet_id,l_table_alias,l_table_name,l_table_route_id,l_dml_oper,l_dt_mode,
l_pk,l_esd,l_eed,l_bg,l_grade_cer_id,l_point_cer_id,
l_payrate_id,l_abr_cer_id,l_hrr_ovn
from ben_copy_entity_results
where copy_entity_result_id = p_old_hrrate_cer_id;
l_new_dml_oper := 'UPDATE';
update ben_copy_entity_results
set information294 = p_grd_min_value,
information295 = p_grd_max_value,
information296 = p_grd_mid_value,
information297 = p_value,
dml_operation = l_new_dml_oper,
datetrack_mode = l_new_dt_mode,
information3 = l_hrr_eed
where copy_entity_result_id = p_old_hrrate_cer_id;
update ben_copy_entity_results
set information287 = p_value
where copy_entity_result_id = p_old_hrrate_cer_id
and nvl(information287,0) =0 ;
hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
delete from ben_copy_entity_results
where copy_entity_txn_id = l_cet_id
and table_alias = 'HRRATE'
and (information277 is null or information277 = l_grade_cer_id)
and (information278 is null or information278 = l_point_cer_id)
and information2 > p_effective_date;
update ben_copy_entity_results
set information3 = l_hrr_eed
where copy_entity_result_id = p_old_hrrate_cer_id;
hr_utility.set_location('old hrrate updated '||p_old_hrrate_cer_id,10);
hr_utility.set_location('inserting new row ',10);
,p_dml_operation => 'INSERT'
,p_datetrack_mode => 'UPDATE_REPLACE'
,p_information1 => l_pk
,p_information2 => p_effective_date
,p_information3 => l_eot
,p_information4 => l_bg
,p_information277 => l_grade_cer_id
,p_information278 => l_point_cer_id
,p_information293 => l_payrate_id
,p_information294 => p_grd_min_value
,p_information288 => p_grd_min_value
,p_information295 => p_grd_max_value
,p_information289 => p_grd_max_value
,p_information296 => p_grd_mid_value
,p_information290 => p_grd_mid_value
,p_information297 => p_value
,p_information287 => p_value
,p_information298 => l_hrr_ovn
,p_information300 => l_abr_cer_id
,p_copy_entity_result_id => p_new_hrrate_cer_id
,p_object_version_number => l_hrr_cer_ovn);
end update_hrrate;
procedure update_hgrid_data(p_copy_entity_txn_id in number,
p_pl_cer_id in number default null,
p_point_cer_id in number default null,
p_value in number) is
begin
hr_utility.set_location('applying data to hgrid',10);
update ben_copy_entity_results
set information298 = p_value
where table_alias ='CPP'
and information252 = p_pl_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set information298 = p_value
where table_alias ='COP'
and information262 = p_point_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
end update_hgrid_data;
procedure update_grade_hrrate(p_copy_entity_txn_id in number,
p_rt_effective_date in date,
p_gl_effective_date in date,
p_business_group_id in number,
p_hrrate_cer_id in out nocopy number,
p_grade_cer_id in number,
p_grd_value in number,
p_grd_min_value in number,
p_grd_mid_value in number,
p_grd_max_value in number,
p_datetrack_mode in varchar2) is
l_abr_cer_id number;
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => p_hrrate_cer_id);
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_pl_cer_id => p_grade_cer_id,
p_value => p_grd_value);
update_hrrate(p_old_hrrate_cer_id => p_hrrate_cer_id,
p_effective_date => p_rt_effective_date,
p_new_hrrate_cer_id => l_new_hrrate_cer_id,
p_value => p_grd_value,
p_datetrack_mode => p_datetrack_mode,
p_grd_min_value => p_grd_min_value,
p_grd_mid_value => p_grd_mid_value,
p_grd_max_value => p_grd_max_value);
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_pl_cer_id => p_grade_cer_id,
p_value => p_grd_value);
elsif p_datetrack_mode = 'UPDATE_REPLACE' and p_gl_effective_date >= p_rt_effective_date then
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_pl_cer_id => p_grade_cer_id,
p_value => p_grd_value);
end update_grade_hrrate;
procedure update_point_hrrate(p_copy_entity_txn_id in number,
p_rt_effective_date in date,
p_gl_effective_date in date,
p_business_group_id in number,
p_hrrate_cer_id in out nocopy number,
p_point_cer_id in number,
p_point_value in number,
p_datetrack_mode in varchar2) is
l_abr_cer_id number;
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => p_hrrate_cer_id);
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => p_point_cer_id,
p_value => p_point_value);
update_hrrate(p_old_hrrate_cer_id => p_hrrate_cer_id,
p_effective_date => p_rt_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_new_hrrate_cer_id => l_new_hrrate_cer_id,
p_value => p_point_value);
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => p_point_cer_id,
p_value => p_point_value);
elsif p_datetrack_mode = 'UPDATE_REPLACE' and p_gl_effective_date >= p_rt_effective_date then
update_hgrid_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => p_point_cer_id,
p_value => p_point_value);
end update_point_hrrate;
select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
RATE_ID, MAXIMUM, MID_VALUE, MINIMUM,VALUE, OBJECT_VERSION_NUMBER
from pay_grade_rules_f
where grade_rule_id = p_pay_rule_id
and rate_type ='G'
and GRADE_OR_SPINAL_POINT_ID = p_grade_id
order by effective_start_date;
select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
RATE_ID,VALUE, OBJECT_VERSION_NUMBER
from pay_grade_rules_f
where grade_rule_id = p_pay_rule_id
and rate_type ='SP'
and GRADE_OR_SPINAL_POINT_ID = p_point_id
order by effective_start_date;
procedure update_crrate(p_crset_id in number,
p_effective_date in date,
p_copy_entity_txn_id in number,
p_datetrack_mode in varchar2,
p_grade_cer_id in number default null,
p_point_cer_id in number default null,
p_new_value in number) is
cursor csr_crrate is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRRATE'
and (information230 is null or information230 = p_grade_cer_id)
and (information169 is null or information169 = p_point_cer_id)
and information160 = p_crset_id
and p_effective_date between information2 and information3;
if p_datetrack_mode not in ('CORRECTION','UPDATE_REPLACE') then
hr_utility.set_location('invalid dt mode '||p_datetrack_mode,1);
l_dml_operation := 'UPDATE';
hr_utility.set_location('same row is to be updated',6);
hr_utility.set_location('fut rows being deleted',11);
delete from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRRATE'
and information160 = p_crset_id
and (information230 is null or information230 = p_grade_cer_id)
and (information169 is null or information169 = p_point_cer_id)
and information2 > p_effective_date;
update ben_copy_entity_results
set INFORMATION3 = l_crrate_eed
where copy_entity_result_id = crrate_rec.copy_entity_result_id;
update ben_copy_entity_results
set dml_operation = l_dml_operation,
INFORMATION293 = p_new_value,
information3 = l_crrate_eed
where copy_entity_result_id = crrate_rec.copy_entity_result_id;
update ben_copy_entity_results
set INFORMATION287 = p_new_value
where copy_entity_result_id = crrate_rec.copy_entity_result_id
and nvl(information287,0) =0 ;
p_datetrack_mode => 'UPDATE_REPLACE',
p_vpf_cer_id => crrate_rec.information162,
p_vpf_name => crrate_rec.information170,
p_vpf_id => crrate_rec.information278,
p_crset_id => crrate_rec.information160,
p_elp_id => crrate_rec.information279,
p_crr_cer_id => l_crr_cer_id);
end update_crrate;
procedure update_gsrate(p_copy_entity_txn_id in number,
p_gsr_cer_id in number,
p_effective_date in date,
p_business_group_id in number,
p_value1 in number,
p_value2 in number,
p_value3 in number,
p_value4 in number,
p_value5 in number,
p_datetrack_mode in varchar2) is
l_grade_cer_id number;
select information160, information229, information231, information174,
information178, information222, information287, information288, information289,
information290, information291, information228, information2, information3
into l_grade_cer_id, l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
from ben_copy_entity_results
where copy_entity_result_id = p_gsr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set information287 = p_value1,
information288 = p_value2,
information289 = p_value3,
information290 = p_value4,
information291 = p_value5,
information3 = l_crrate_eed
where copy_entity_result_id = p_gsr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set INFORMATION3 = p_effective_date -1
where copy_entity_result_id = p_gsr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
delete from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'GSRATE'
and information160 = l_grade_cer_id
and information2 > p_effective_date;
hr_utility.set_location('new row inserted',14);
update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point1_cer_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value1);
update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point2_cer_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value2);
update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point3_cer_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value3);
update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point4_cer_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value4);
update_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point5_cer_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value5);
end update_gsrate;
procedure update_grrate(p_copy_entity_txn_id in number,
p_grr_cer_id in number,
p_effective_date in date,
p_business_group_id in number,
p_value1 in number,
p_value2 in number,
p_value3 in number,
p_value4 in number,
p_value5 in number,
p_datetrack_mode in varchar2) is
l_grade_cer_id number;
select information160, information161,information162,information229, information231, information174,
information178, information222, information287, information288, information289,
information290, information291, information228, information2, information3
into l_grade_cer_id, l_crset_id, l_plip_cer_id,l_point1_cer_id, l_point2_cer_id, l_point3_cer_id,
l_point4_cer_id, l_point5_cer_id, l_point1_value, l_point2_value, l_point3_value,
l_point4_value, l_point5_value, l_step_range, l_esd, l_eed
from ben_copy_entity_results
where copy_entity_result_id = p_grr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set information287 = p_value1,
information288 = p_value2,
information289 = p_value3,
information290 = p_value4,
information291 = p_value5,
information3 = l_crrate_eed
where copy_entity_result_id = p_grr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set INFORMATION3 = p_effective_date -1
where copy_entity_result_id = p_grr_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
delete from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'GRRATE'
and information160 = l_grade_cer_id
and information2 > p_effective_date;
hr_utility.set_location('new row inserted',14);
update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point1_cer_id,
p_crset_id => l_crset_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value1);
update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point2_cer_id,
p_crset_id => l_crset_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value2);
update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point3_cer_id,
p_crset_id => l_crset_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value3);
update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point4_cer_id,
p_crset_id => l_crset_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value4);
update_crrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_point_cer_id => l_point5_cer_id,
p_crset_id => l_crset_id,
p_datetrack_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_new_value => p_value5);
end update_grrate;
select * from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and information160 is null
and table_alias = 'CRRATE'
and information169 = p_point_cer_id;
l_dml_operation := 'INSERT';
l_dml_operation := 'UPDATE';
update ben_copy_entity_results
set information293 = p_value,
information160 = p_crset_id,
dml_operation = l_dml_operation
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = crr_rec.copy_entity_result_id
and information160 is null
and table_alias = 'CRRATE'
and information169 = p_point_cer_id;
update ben_copy_entity_results
set INFORMATION287 = p_value
where copy_entity_result_id = crr_rec.copy_entity_result_id
and nvl(information287,0) =0 ;
select * from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and information161 is null
and table_alias = 'GRRATE';
select copy_entity_result_id
from ben_copy_entity_results
where table_alias = 'PLN'
and copy_entity_txn_id = p_copy_entity_txn_id;
select copy_entity_result_id,information1,information2,information3,information294,information295,information296,information297,dml_operation
from ben_copy_entity_results
where table_alias = 'HRRATE'
and information277= p_grade_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
select copy_entity_result_id,information1,information2,information3,information293,dml_operation
from ben_copy_entity_results
where table_alias = 'CRRATE'
and information230= p_grade_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
select value,minimum,maximum,mid_value
from pay_grade_rules_f
where grade_rule_id = p_grade_rule_id
and effective_start_date = p_effective_start_date;
select copy_entity_result_id
from ben_copy_entity_results
where table_alias = 'OPT'
and copy_entity_txn_id = p_copy_entity_txn_id;
select copy_entity_result_id,information1,information2,information3,information297,dml_operation
from ben_copy_entity_results
where table_alias = 'HRRATE'
and information278= p_point_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
select copy_entity_result_id,information1,information2,information3,information293,dml_operation
from ben_copy_entity_results
where table_alias = 'CRRATE'
and information169= p_point_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id;
select value
from pay_grade_rules_f
where grade_rule_id = p_grade_rule_id
and effective_start_date = p_effective_start_date;
select copy_entity_result_id
from ben_copy_entity_results
where information109 is null --nvl(information109,0) = 0
and table_alias = 'PQH_GSP_TASK_LIST'
and copy_entity_txn_id = p_copy_entity_txn_id
and rownum < 2 ;
update ben_copy_entity_results
set information109 = 'Y'
where copy_entity_result_id = rec_gsp_task_list.copy_entity_result_id;
if grade_std_rates_rec.dml_operation = 'INSERT' then
update ben_copy_entity_results
set information287 = information297
,information288 = information294
,information289 = information295
,information290 = information296
where copy_entity_result_id = grade_std_rates_rec.copy_entity_result_id;
update ben_copy_entity_results
set information287 = l_value
,information288 = l_grd_min_value
,information289 = l_grd_max_value
,information290 = l_grd_mid_value
where copy_entity_result_id = grade_std_rates_rec.copy_entity_result_id;
update ben_copy_entity_results
set information287 = information293
where copy_entity_result_id = grade_cri_rates_rec.copy_entity_result_id;
if pnt_rate.dml_operation = 'INSERT' then
update ben_copy_entity_results
set information287 = information297
where copy_entity_result_id = pnt_rate.copy_entity_result_id;
update ben_copy_entity_results
set information287 = l_value
where copy_entity_result_id = pnt_rate.copy_entity_result_id;
update ben_copy_entity_results
set information287 = information293
where copy_entity_result_id = pnt_cri_rate.copy_entity_result_id;