[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select copy_entity_result_id
into l_vpf_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY'
and table_alias = 'VPF'
and information1 = p_vpf_id;
procedure update_crset_type(p_copy_entity_txn_id in number,
p_crset_id in number,
p_crset_type in varchar2) is
begin
update ben_copy_entity_results
set information100 = p_crset_type
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CRSET'
and information161 = p_crset_id;
select information262
into l_point_cer_id
from ben_copy_entity_results
where copy_entity_result_id = p_oipl_cer_id;
select information252
into l_grade_cer_id
from ben_copy_entity_results
where copy_entity_result_id = p_plip_cer_id;
select count(*)
into l_count
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CRRATE'
and p_effective_date between information2 and information3
and information169 = l_point_cer_id
and information160 is not null;
select count(*)
into l_count
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CRRATE'
and p_effective_date between information2 and information3
and information230 = l_grade_cer_id
and information160 is not null;
select information1
into l_elp_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='ELP'
and parent_entity_result_id = p_vpf_cer_id
and p_effective_date between information2 and information3;
select information261,information247,information277,information278
into p_pl_id,p_opt_id,p_grade_cer_id,p_point_cer_id
from ben_copy_entity_results
where copy_entity_result_id = p_abr_cer_id;
select copy_entity_result_id
into l_abr_cer_id
from ben_copy_entity_results
where INFORMATION277 = p_pl_cer_id
and table_alias = 'ABR'
and copy_entity_txn_id = p_copy_entity_txn_id
and p_effective_date between information2 and information3;
select copy_entity_result_id
into l_abr_cer_id
from ben_copy_entity_results
where INFORMATION278 = p_opt_cer_id
and table_alias = 'ABR'
and copy_entity_txn_id = p_copy_entity_txn_id
and p_effective_date between information2 and information3;
select copy_entity_result_id
into l_hrr_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'HRRATE'
and (information277 is null or information277 = p_grade_cer_id)
and (information278 is null or information278 = p_point_cer_id)
and p_hrr_esd between information2 and information3;
select count(*) into l_count
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 p_crr_esd between information2 and information3
and information160 = p_crset_id;
p_dml_operation in varchar2 default 'INSERT',
p_datetrack_mode in varchar2 default 'INSERT',
p_elp_id in number default null,
p_crr_cer_id out nocopy number) is
l_crr_tr_id number;
if p_dml_operation not in ('INSERT','REUSE','UPDATE') then
hr_utility.set_location('wrong dml_oper passed ',55);
if p_datetrack_mode not in ('INSERT','UPDATE_REPLACE') then
hr_utility.set_location('wrong dt_mode passed ',55);
hr_utility.set_location('crr exists, no insert',55);
hr_utility.set_location('cer insert api called',55);
p_dml_oper => 'INSERT');
l_dml_oper := 'INSERT';
procedure update_crset(p_crset_id in number,
p_effective_date in date,
p_crset_name in varchar2,
p_copy_entity_txn_id in number,
p_datetrack_mode in varchar2,
p_bu_cd in varchar2 default null,
p_bu_name in varchar2 default null,
p_fp_cd in varchar2 default null,
p_fp_name in varchar2 default null,
p_job_id in number default null,
p_job_name in varchar2 default null,
p_org_id in number default null,
p_org_name in varchar2 default null,
p_pt_id in number default null,
p_pt_name in varchar2 default null,
p_loc_id in number default null,
p_loc_name in varchar2 default null,
p_perf_rtng_cd in varchar2 default null,
p_perf_rtng_name in varchar2 default null,
p_event_type in varchar2 default null,
p_event_name in varchar2 default null,
p_sa_id in number default null,
p_sa_name in varchar2 default null,
p_ff_id in number default null,
p_ff_name in varchar2 default null) is
cursor csr_crset is
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and information161 = p_crset_id
and p_effective_date between information2 and information3;
hr_utility.set_location('inside update_crset ',10);
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',10);
delete from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and information161 = p_crset_id
and information2 > p_effective_date;
update ben_copy_entity_results
set INFORMATION3 = l_crset_eed
where copy_entity_result_id = crset_rec.copy_entity_result_id;
update ben_copy_entity_results
set dml_operation = l_dml_operation,
INFORMATION232 = p_loc_id,
INFORMATION233 = p_job_id,
INFORMATION234 = p_org_id,
INFORMATION235 = p_ff_id,
INFORMATION236 = p_pt_id,
INFORMATION237 = p_sa_id,
INFORMATION101 = p_bu_cd,
INFORMATION102 = p_fp_cd,
INFORMATION103 = p_perf_rtng_cd,
INFORMATION104 = p_event_type,
INFORMATION179 = p_loc_name,
INFORMATION181 = p_job_name,
INFORMATION182 = p_org_name,
INFORMATION170 = p_ff_name,
INFORMATION173 = p_pt_name,
INFORMATION185 = p_sa_name,
INFORMATION186 = p_bu_name,
INFORMATION187 = p_fp_name,
INFORMATION188 = p_perf_rtng_name,
INFORMATION175 = p_event_name,
information151 = p_crset_name,
information3 = l_crset_eed
where copy_entity_result_id = crset_rec.copy_entity_result_id;
hr_utility.set_location('end of update_crset ',6);
hr_utility.set_location('out of update_crset',8);
end update_crset;
select pqh_gsp_criteria_set_id_s.nextval into l_crset_id from dual;
select information151,copy_entity_result_id,information161,information2,information3,information100
from ben_copy_entity_results
where table_alias ='CRSET'
and copy_entity_txn_id = p_copy_entity_txn_id
and information277 = p_elp_id;
select *
from ben_copy_entity_results
where information1 = p_elp_id
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='ELP'
order by result_type_cd;
select *
from ben_copy_entity_results
where parent_entity_result_id = l_elp_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id
and l_elp_esd between information2 and information3;
update_crset_type(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_crset_id => l_crset_id,
p_crset_type => l_crset_type);
hr_utility.set_location('crset type updated to be '||l_crset_type,30);
select pqh_gsp_criteria_set_id_s.nextval into l_crset_id from dual;
cursor crset_rec is select information151 crset_name,
INFORMATION232 loc_id,
INFORMATION233 job_id,
INFORMATION234 org_id,
INFORMATION235 rule_id,
INFORMATION236 pt_id,
INFORMATION237 sa_id,
INFORMATION101 bu_cd,
INFORMATION102 fp_cd,
INFORMATION103 pr_cd,
INFORMATION104 event_type
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CRSET'
and information161 = p_crset_id
and p_effective_date between information2 and information3;
select PARENT_ENTITY_RESULT_ID , -- abr_cer_id
INFORMATION170 NAME , -- name of Variable Rate
INFORMATION303 VAL , -- value
INFORMATION2 esd , -- st dt
INFORMATION3 eed , -- end dt
INFORMATION4 bg , -- end dt
INFORMATION1 VRBL_RT_PRFL_ID , -- pk of database row
INFORMATION265 ovn
into l_abr_cer_id,l_vpf_name, l_vpf_value, l_vpf_esd, l_vpf_eed, l_bg, l_vpf_id, l_vpf_ovn
from ben_copy_entity_results
where copy_entity_result_id = p_vpf_cer_id
and information72 ='GSPSA'
and INFORMATION77 = 'A';
update ben_copy_entity_results
set information255 = p_scale_id,
information258 = p_scale_cer_id
where table_alias = 'CPP'
and copy_entity_txn_id = p_copy_entity_txn_id
and (information258 = p_scale_cer_id or information255 = p_scale_id);
update ben_copy_entity_results
set parent_entity_result_id = p_oipl_cer_id,
gs_parent_entity_result_id = p_oipl_cer_id
where table_alias = 'ELP'
and copy_entity_txn_id = p_copy_entity_txn_id
and parent_entity_result_id = p_opt_cer_id;
hr_utility.set_location('table routes updated ',25);
update ben_copy_entity_results
set parent_entity_result_id = p_plip_cer_id,
gs_parent_entity_result_id = p_plip_cer_id
where table_alias = 'COP'
and copy_entity_txn_id = p_copy_entity_txn_id
and parent_entity_result_id = p_pl_cer_id;
update ben_copy_entity_results
set parent_entity_result_id = p_plip_cer_id,
gs_parent_entity_result_id = p_plip_cer_id
where table_alias = 'ELP'
and copy_entity_txn_id = p_copy_entity_txn_id
and parent_entity_result_id = p_pl_cer_id;
select information102,information307,information308
into l_grd_short_name,l_grd_date_from,l_grd_date_to
from ben_copy_entity_results
where copy_entity_result_id = p_pl_cer_id;
select max(information263) into l_ordr_num
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP';
,p_dml_operation => 'INSERT'
-- ,p_information1 => p_plip_id -- new ben object
,p_information2 => p_effective_date
,p_information4 => p_business_group_id
,p_information5 => p_grade_name
,p_information12 => l_grd_short_name
,p_information104 => 'LINK'
,p_information252 => p_pl_cer_id
,p_information263 => l_ordr_num
,p_information306 => l_grd_date_from
,p_information307 => l_grd_date_to
,p_copy_entity_result_id => p_plip_cer_id
,p_object_version_number => p_plip_cer_ovn);
select information307
into l_start_date
from ben_copy_entity_results
where copy_entity_result_id = p_grade_cer_id;
select copy_entity_result_id,information1
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='SCALE';
update ben_copy_entity_results
set gs_parent_entity_result_id = parent_entity_result_id,
gs_mirror_src_entity_result_id = mirror_src_entity_result_id
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = p_cep_cer_id
and gs_parent_entity_result_id is null;
update ben_copy_entity_results
set gs_parent_entity_result_id = parent_entity_result_id,
gs_mirror_src_entity_result_id = mirror_src_entity_result_id
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = p_epa_cer_id
and gs_parent_entity_result_id is null;
Select Cep.Result_Type_cd
From Ben_Copy_Entity_results ELP,
Ben_Copy_Entity_Results CEP
Where Elp.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
and Elp.Copy_Entity_Result_id = p_Elp_Cer_Id
and Cep.Copy_Entity_Txn_Id = Elp.Copy_Entity_Txn_id
and Cep.Copy_Entity_Result_id = Elp.Mirror_Src_Entity_result_id
and Elp.information1 is not null;
update ben_copy_entity_results
set gs_parent_entity_result_id = parent_entity_result_id,
gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
information101 = information1,
information1 = null
where copy_entity_txn_id = p_copy_entity_txn_id
and parent_entity_result_id = p_elp_cer_id;
update ben_copy_entity_results
set gs_parent_entity_result_id = parent_entity_result_id,
gs_mirror_src_entity_result_id = mirror_src_entity_result_id,
Result_type_cd = Nvl(l_Result_type_Cd, result_type_Cd)
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = p_elp_cer_id;
update ben_copy_entity_results
set result_type_cd = 'NO DISPLAY'
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = p_elp_cer_id
and table_alias = 'ELP'
and p_Effective_Date not between information2 and information3;
select eligy_prfl_id
from ben_prtn_elig_prfl_f
where p_effective_date between effective_start_date and effective_end_date
and prtn_elig_prfl_id = p_prtn_elig_prfl_id;
select acty_base_rt_id,pay_rate_grade_rule_id
into l_abr_id,p_pay_rule_id
from ben_acty_base_rt_f
where p_effective_date between effective_start_date and effective_end_date
and pl_id = p_plan_id
and acty_typ_cd ='GSPSA'; -- we are interested only in this type
select acty_base_rt_id,pay_rate_grade_rule_id
into l_abr_id,p_pay_rule_id
from ben_acty_base_rt_f
where p_effective_date between effective_start_date and effective_end_date
and opt_id = p_opt_id
and acty_typ_cd ='GSPSA'; -- we are interested only in this type
select rate.rate_id
into l_pay_rate_id
from pay_rates rate, per_parent_spines scale
where rate.name = scale.name
and rate.rate_type ='SP'
and rate.parent_spine_id = p_scale_id
and scale.parent_spine_id = p_scale_id;
select step_id,sequence,object_version_number
into p_step_id, p_step_name,p_step_ovn
from per_spinal_point_steps_f
where grade_spine_id = p_grade_spine_id
and spinal_point_id = p_point_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selected step',40);
select ceiling_step_id,grade_spine_id,starting_step
into p_ceiling_step_id,p_grade_spine_id,p_starting_step
from per_grade_spines_f
where grade_id = p_grade_id
and parent_spine_id = p_scale_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selected grade_spine',30);
select parent_spine_id,sequence,spinal_point,object_version_number
into p_scale_id, p_point_seq, p_spinal_point,p_point_ovn
from per_spinal_points
where spinal_point_id = p_point_id;
select spinal_point_id
into l_spinal_point_id
from per_spinal_point_steps_f
where step_id = p_step_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selecting step',20);
select mapping_table_pk_id
into l_spinal_point_id
from ben_opt_f
where opt_id = p_option_id
and p_effective_date between effective_start_date and effective_end_date
and mapping_table_name ='PER_SPINAL_POINTS';
hr_utility.set_location('selected option is not linked to SP'||p_option_id,15);
hr_utility.set_location('issues in selecting point',20);
select opt_id
into l_opt_id
from ben_opt_f
where mapping_table_pk_id = p_point_id
and p_effective_date between effective_start_date and effective_end_date
and mapping_table_name ='PER_SPINAL_POINTS';
hr_utility.set_location('issues in selecting option',20);
select mapping_table_pk_id
into l_grade_id
from ben_pl_f
where pl_id = p_plan_id
and p_effective_date between effective_start_date and effective_end_date
and mapping_table_name ='PER_GRADES';
hr_utility.set_location('selected plan is not linked to grade'||p_plan_id,15);
hr_utility.set_location('issues in selecting grade',20);
select pl_id
into l_pl_id
from ben_pl_f
where mapping_table_pk_id = p_grade_id
and p_effective_date between effective_start_date and effective_end_date
and mapping_table_name ='PER_GRADES';
hr_utility.set_location('issues in selecting plan',20);
select gs.grade_id,sps.spinal_point_id
into l_grade_id, l_point_id
from per_spinal_point_steps_f sps, per_grade_spines_f gs
where sps.grade_spine_id = gs.grade_spine_id
and p_effective_date between sps.effective_start_date and sps.effective_end_date
and p_effective_date between gs.effective_start_date and gs.effective_end_date
and sps.step_id = p_step_id;
hr_utility.set_location('issues in selecting step detail'||p_step_id,30);
select oipl_id
into l_oipl_id
from ben_oipl_f
where pl_id = l_pl_id
and opt_id = l_opt_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selecting oipl detail',120);
select pl_id,opt_id
into l_pl_id,l_opt_id
from ben_oipl_f
where oipl_id = p_oipl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selecting oipl detail'||p_oipl_id,120);
select step_id
into l_step_id
from per_spinal_point_steps_f sps, per_grade_spines_f gs
where sps.grade_spine_id = gs.grade_spine_id
and p_effective_date between sps.effective_start_date and sps.effective_end_date
and p_effective_date between gs.effective_start_date and gs.effective_end_date
and gs.grade_id = l_grade_id
and sps.spinal_point_id = l_point_id;
hr_utility.set_location('issues in selecting step ',80);
select pl_id
into p_plan_id
from ben_plip_f
where plip_id = p_plip_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selected plip',20);
select table_route_id, substrb(display_name,1,30) -- fix for the bug#12797263
into p_table_route_id, p_table_name
from pqh_table_route_vl
where table_alias = p_table_alias;
procedure update_gsp_control_rec(p_copy_entity_txn_id in number,
p_effective_date in date,
p_business_area in varchar2 default 'PQH_GSP_TASK_LIST') is
l_grd_exists varchar2(30) := 'N';
select count(*) into l_plip_row
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CPP'
and Result_type_Cd = 'DISPLAY'
and information104 ='LINK'; -- plip record is linked as well
select count(*) into l_oipl_row
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='COP'
and Result_Type_Cd = 'DISPLAY';
select count(*) into l_rate_row
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'ABR'
and Result_Type_Cd = 'DISPLAY';
select count(*) into l_rule_row
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CEP'
and Result_Type_Cd = 'DISPLAY';
select copy_entity_result_id
into l_ctrl_rec_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = p_business_area;
update ben_copy_entity_results
set information100 = 'Y'
,information101 = 'Y'
,information102 = l_grd_exists
,information103 = l_step_exists
,information104 = l_rate_exists
,information105 = l_rule_exists
,information106 = l_grd_exists -- review is enabled only when grade is enabled
,information107 = l_cpd_exists
,information108 = l_cdd_exists
where copy_entity_result_id = l_ctrl_rec_cer_id;
end update_gsp_control_rec;
update_gsp_control_rec(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date,
p_business_area => p_business_area);
select rate_id
into l_pay_rate_id
from pay_rates
where name = l_pay_rate_name
and rate_type ='G'
and business_group_id = p_business_group_id;
select date_from, date_to, short_name, name,
grade_definition_id, object_version_number
into p_date_from,p_date_to,p_short_name,p_grade_name,
p_grade_definition_id,p_grd_ovn
from per_grades
where grade_id = p_grade_id;
select value,minimum,mid_value,maximum
into p_grd_value,p_grd_min,p_grd_mid,p_grd_max
from pay_grade_rules_f
where grade_rule_id = l_pay_rule_id
and grade_or_spinal_point_id = p_grade_id
and p_effective_date between effective_start_date and effective_end_date
and rate_type ='G';
select value,minimum,mid_value,maximum
into p_grd_value,p_grd_min,p_grd_mid,p_grd_max
from pay_grade_rules_f
where rate_id = l_grd_pay_rate_id
and grade_or_spinal_point_id = p_grade_id
and p_effective_date between effective_start_date and effective_end_date
and rate_type ='G';
select value
into p_point_value
from pay_grade_rules_f
where grade_rule_id = l_pay_rule_id
and grade_or_spinal_point_id = p_point_id
and p_effective_date between effective_start_date and effective_end_date
and rate_type ='SP';
select value
into p_point_value
from pay_grade_rules_f
where rate_id = l_scl_pay_rate_id
and grade_or_spinal_point_id = p_point_id
and p_effective_date between effective_start_date and effective_end_date
and rate_type ='SP';
select opt_id,pl_id
into l_option_id,l_plan_id
from ben_oipl_f
where oipl_id = p_oipl_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selected oipl',20);
select sps.spinal_point_id,gs.grade_id, gs.grade_spine_id
into p_point_id,p_grade_id,p_grade_spine_id
from per_spinal_point_steps_f sps, per_grade_spines_f gs
where sps.step_id = p_step_id
and sps.grade_spine_id = gs.grade_spine_id
and p_effective_date between sps.effective_start_date and sps.effective_end_date
and p_effective_date between gs.effective_start_date and gs.effective_end_date;
hr_utility.set_location('issues in selected step',20);
select pl_id, opt_id, pay_rate_grade_rule_id
into p_plan_id, p_option_id,p_pay_rule_id
from ben_acty_base_rt_f
where acty_base_rt_id = p_abr_id
and p_effective_date between effective_start_date and effective_end_date;
select maximum, mid_value,minimum,value
into p_grd_max,p_grd_mid, p_grd_min,p_grd_value
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
and p_effective_date between effective_start_date and effective_end_date;
select value
into p_point_value
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
and p_effective_date between effective_start_date and effective_end_date;
l_sal_update_cd varchar2(30);
select ler_id
from ben_ler_f
where business_Group_id = p_business_group_id
and typ_cd = 'GSP'
and lf_evt_oper_cd ='SYNC';
select information17
from ben_copy_entity_results
where table_alias = 'LEN'
and information257 = p_ler_id
and copy_entity_txn_id = p_copy_entity_txn_id;
hr_utility.set_location('inside pgm update ',10);
select information16,information14,information4
into l_sal_update_cd,l_dflt_step_cd,l_business_group_id
from ben_copy_entity_results
where copy_entity_result_id = p_pgm_cer_id;
if nvl(l_sal_update_cd,'NO_UPDATE') = 'NO_UPDATE' then
l_sal_upd_flag := 'N';
l_sal_update_cd := '';
elsif l_sal_update_cd in ('SALARY_ELEMENT','SALARY_BASIS') then
l_sal_upd_flag := 'Y';
update ben_copy_entity_results
set information_category = 'GRADE_LADDER',
information16 = l_sal_upd_flag,
information51 = l_enrt_cd,
information14 = l_dflt_step_cd,
information71 = l_sal_update_cd,
information53 = l_rate_st_dt,
dml_operation = 'UPDATE'
where copy_entity_result_id = p_pgm_cer_id;
hr_utility.set_location('done pgm update ',50);
hr_utility.set_location('comp object selected ',20);
update ben_copy_entity_results
set information277 = l_pl_cer_id,
information278 = l_opt_cer_id,
gs_mirror_src_entity_result_id = mirror_src_entity_result_id
where copy_entity_result_id = p_abr_cer_id;
hr_utility.set_location('issue in update abr with co cer',120);
select gs.parent_spine_id scale_id,gs.grade_id grade_id
from ben_copy_entity_results cer, per_grade_spines_f gs
where copy_entity_txn_id = p_copy_entity_txn_id
and cer.information294 = gs.grade_id
and table_alias = 'PLN'
and p_effective_date between gs.effective_start_date and gs.effective_end_date
and copy_entity_result_id > nvl(p_start_cer_id,0);
SELECT pgm_extra_info_id,pgi_information1,pgi_information2,pgi_information3,pgi_information4
FROM ben_pgm_extra_info
where information_type ='PQH_GSP_EXTRA_INFO'
and pgm_id = p_pgm_id;
,p_dml_operation => 'UPDATE'
);
,p_dml_operation => 'UPDATE'
);
hr_utility.set_location('issues is selecting pgm extra info',10);
select copy_entity_result_id,table_alias,information1,information5,information253,
result_type_cd,parent_entity_result_id,information261,mirror_src_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and ((table_alias in ('PGM','PLN','OPT','CPP','COP','EPA','CEP') and result_type_cd ='DISPLAY')
or table_alias in ('ABR','AVR','ELP','VPF','VEP'))
and copy_entity_result_id > nvl(p_start_cer_id,0)
order by copy_entity_result_id;
hr_utility.set_location('plan row updated',22);
hr_utility.set_location('PGM row updated',28);
hr_utility.set_location('PGI row updated',28);
hr_utility.set_location('plip rec updated ',38);
hr_utility.set_location('option row updated',42);
hr_utility.set_location('oipl row updated',52);
hr_utility.set_location('ABR row updated',62);
hr_utility.set_location('ELP row updated',85);
hr_utility.set_location('Epa row updated',100);
hr_utility.set_location('cep row updated',115);
update ben_copy_entity_results set
information98 = nvl(l_spinal_point,information98),
information253 = nvl(l_point_seq,information253),
information254 = nvl(l_point_ovn,information254),
information255 = nvl(l_scale_id,information255),
information256 = nvl(l_scale_cer_id,information256),
information173 = nvl(l_information1,information173),
information175 = nvl(l_information2,information175),
information179 = nvl(l_information3,information179),
information181 = nvl(l_information4,information181),
information182 = nvl(l_information5,information182),
information101 = nvl(l_information_category,information101)
where copy_entity_result_id = p_opt_cer_id;
update ben_copy_entity_results set
information5 = nvl(l_grade_name,information5), -- plan name is overridden by Grade Name
information102 = nvl(l_short_name,information102),
information221 = nvl(l_grade_definition_id,information221),
information222 = nvl(l_grd_ovn,information222),
information307 = nvl(l_date_from,information307),
information308 = nvl(l_date_to,information308),
information223 = nvl(p_grade_id,information223),
gs_mirror_src_entity_result_id = nvl(gs_mirror_src_entity_result_id,
mirror_src_entity_result_id)
where copy_entity_result_id = p_pl_cer_id;
select psp.parent_spine_id,psp.name,gsp.ceiling_step_id,gsp.grade_spine_id,
gsp.object_version_number,psp.object_version_number,gsp.starting_step
into p_scale_id,p_scale_name, p_ceiling_step_id,p_grade_spine_id,p_grade_spine_ovn,p_scale_ovn,p_starting_step
from per_grade_spines_f gsp, per_parent_spines psp
where gsp.grade_id = p_grade_id
and psp.parent_spine_id = gsp.parent_spine_id
and p_effective_date between gsp.effective_start_date and gsp.effective_end_date;
hr_utility.set_location('going for update ',40);
update ben_copy_entity_results set
information5 = nvl(l_grade_name,information5), -- plip name is same as plan name and overridden
information306 = nvl(l_date_from,information306),
information307 = nvl(l_date_to,information307),
information253 = nvl(p_grade_id,information253),
information252 = nvl(p_pl_cer_id,information252),
information255 = nvl(l_scale_id,information255),
information280 = nvl(l_grade_spine_id,information280),
information98 = nvl(l_scale_name,information98),
information12 = nvl(l_short_name,information12),
information259 = nvl(l_ceiling_step_id,information259),
information298 = nvl(l_grd_value,information298),
information287 = l_perc_quota,
information99 = l_population_cd,
information219 = l_comb_grades,
information288 = l_max_speed_quota,
information289 = l_avg_speed_quota,
information290 = l_corps_extra_info_id,
information291 = l_corps_definition_id,
information228 = l_starting_step,
information104 = 'LINK',
gs_parent_entity_result_id = nvl(gs_parent_entity_result_id,p_parent_cer_id),
gs_mirror_src_entity_result_id = nvl(gs_mirror_src_entity_result_id,p_mirror_ser_id),
information281 = l_grade_spine_ovn
where copy_entity_result_id = p_plip_cer_id;
hr_utility.set_location('going for update '||l_proc,20);
update ben_copy_entity_results set
information253 = nvl(l_step_id, information253),
information255 = nvl(l_grade_spine_id,information255),
information256 = nvl(l_point_id,information256),
information262 = nvl(l_point_cer_id,information262),
information98 = nvl(l_ceiling_flag, information98),
information99 = nvl(l_point_name, information99),
information5 = nvl(l_point_name, information5),
information263 = nvl(l_step_name, information263),
information298 = nvl(l_point_value, information298),
information260 = nvl(l_scale_id,information260),
information259 = nvl(l_scale_cer_id,information259),
information104 = 'LINK',
gs_parent_entity_result_id = nvl(gs_parent_entity_result_id,p_parent_cer_id)
where copy_entity_result_id = p_oipl_cer_id;
select 'x'
into l_check
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='HRRATE'
and information299 = p_abr_id;
,p_dml_operation => 'INSERT'
,p_datetrack_mode => 'CORRECTION'
,p_information2 => p_start_date
,p_information3 => l_eot
,p_information4 => p_business_group_id
,p_information277 => p_grade_cer_id
,p_information293 => l_pay_rate_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_grd_value
,p_information287 => p_grd_value
,p_information300 => p_abr_cer_id
,p_copy_entity_result_id => p_hrrate_cer_id
,p_object_version_number => l_hrr_cer_ovn);
,p_dml_operation => 'INSERT'
,p_datetrack_mode => 'CORRECTION'
,p_information2 => p_start_date
,p_information3 => l_eot
,p_information4 => p_business_group_id
,p_information278 => p_point_cer_id
,p_information293 => l_pay_rate_id
,p_information297 => p_point_value
,p_information287 => p_point_value
,p_information300 => p_abr_cer_id
,p_copy_entity_result_id => p_hrrate_cer_id
,p_object_version_number => l_hrr_cer_ovn);
,p_dml_operation => 'INSERT'
,p_information2 => p_start_date
,p_information3 => l_eot
,p_information4 => p_business_group_id
,p_information277 => p_pl_cer_id
,p_information278 => p_opt_cer_id
,p_copy_entity_result_id => p_abr_cer_id
,p_object_version_number => l_abr_cer_ovn);
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
select name, increment_frequency,business_group_id, increment_period,object_version_number
into l_scale_name,l_increment_frequency,l_bg,l_increment_period,l_scale_ovn
from per_parent_spines
where parent_spine_id = p_scale_id
and business_group_id = p_business_group_id;
select max(copy_entity_result_id)
into l_max_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id;
procedure update_txn_table_route(p_copy_entity_txn_id in number) is
cursor tr is select table_route_id,display_name,table_alias
from pqh_table_route
where from_clause ='OAB';
update ben_copy_entity_results
set table_alias = nvl(table_alias,i.table_alias)
, table_name = nvl(table_name,substr(i.display_name,1,30))
where copy_entity_txn_id = p_copy_entity_txn_id
and table_route_id = i.table_route_id;
end update_txn_table_route;
procedure update_or_view_GL(p_calling_mode in varchar2,
p_action_type in varchar2 default 'REFRESH',
p_pgm_id in number,
p_pgm_name in varchar2,
p_effective_date in date,
p_business_group_id in number,
p_user_id in number,
p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
p_copy_entity_txn_id out nocopy number ) is
cursor csr_txns is select txn.copy_entity_txn_id
from pqh_copy_entity_txns txn, ben_copy_entity_results cer
where txn.context_business_group_id = p_business_group_id
and txn.status ='VIEW'
and txn.context ='GSP'
and cer.copy_entity_txn_id = txn.copy_entity_txn_id
and cer.information1 = p_pgm_id
and cer.table_alias = 'PGM';
hr_utility.set_location('calling for update with'||p_pgm_name,10);
update_GL(p_pgm_id => p_pgm_id,
p_pgm_name => p_pgm_name,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_action_type => p_action_type,
p_user_id => p_user_id,
p_business_area => p_business_area,
p_copy_entity_txn_id => p_copy_entity_txn_id);
pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id =>l_cet_id);
end update_or_view_gl;
procedure update_GL(p_pgm_id in number,
p_action_type in varchar2 default 'REFRESH',
p_pgm_name in varchar2,
p_effective_date in date,
p_business_group_id in number,
p_user_id in number,
p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
p_copy_entity_txn_id out nocopy number ) is
cursor csr_txns is select txn.copy_entity_txn_id
from pqh_copy_entity_txns txn, ben_copy_entity_results cer,
pqh_copy_entity_attribs attr
where txn.context_business_group_id = p_business_group_id
and attr.copy_entity_txn_id = txn.copy_entity_txn_id
--changes for bug no 6030246 starts here
-- and attr.information7 = p_user_id
and attr.information7 = to_char(p_user_id)
--changes for bug no 6030246 ends here
and txn.status ='SFL'
and txn.context ='GSP'
and cer.copy_entity_txn_id = txn.copy_entity_txn_id
and cer.information1 = p_pgm_id
and cer.table_alias = 'PGM';
hr_utility.set_location('inside update '||p_pgm_id,10);
pqh_gsp_utility.delete_transaction(p_pqh_copy_entity_txn_id => l_copy_entity_txn_id);
end update_GL;
select transaction_category_id
into l_gsp_txn_cat
from pqh_transaction_categories
where short_name ='PQHGSP'
and business_group_id is null;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='SCALE'
and information1 = p_scale_id;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='PLN'
and information223 = p_grd_id;
select copy_entity_result_id
into l_plip_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CPP'
and information253 = p_grade_id;
select copy_entity_result_id
into l_plip_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CPP'
and information252 = p_pl_cer_id;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='PLN'
and information1 = p_pl_id;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='ELP'
and copy_entity_result_id > p_start_cer
and (information1 = p_ep_id OR information101 = p_ep_id) ;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='OPT'
and information1 = p_opt_id;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='OPT'
and information257 = p_point_id;
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='COP'
and information253 = p_step_id;
select copy_entity_result_id
into l_oipl_cer_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='COP'
and information247 = p_option_id
and information261 = p_pl_id;
select parent_spine_id
from per_grade_spines_f
where p_effective_date between effective_start_date and effective_end_date
and grade_id = p_grade_id;
select sps.step_id,sps.spinal_point_id,sps.object_version_number,gs.grade_spine_id
from per_spinal_point_steps_f sps, per_grade_spines_f gs
where p_effective_date between sps.effective_start_date and sps.effective_end_date
and gs.grade_spine_id = sps.grade_spine_id
and p_effective_date between gs.effective_start_date and gs.effective_end_date
and gs.grade_id = p_grade_id;
update ben_copy_entity_results
set gs_mirror_src_entity_result_id = p_plip_cer_id,
mirror_src_entity_result_id = p_plip_cer_id,
information104 = ''
where copy_entity_result_id = p_in_pl_cer_id;
hr_utility.set_location('going for grade details update on plip ',49);
hr_utility.set_location('plip updated with Grade ',50);
p_dml_operation in varchar2 default 'INSERT',
p_oipl_cer_id out nocopy number) is
l_cop_tr_id number;
update ben_copy_entity_results
set information262 = p_oipl_cer_id,
information259 = l_step_id
where copy_entity_result_id = p_plip_cer_id;
update ben_copy_entity_results
set gs_mirror_src_entity_result_id = p_plip_cer_id,
mirror_src_entity_result_id = p_plip_cer_id
where copy_entity_result_id = p_pl_cer_id;
hr_utility.set_location('pl update had issues ',18);
update ben_copy_entity_results a
set future_data_exists ='Y'
where a.copy_entity_txn_id = p_copy_entity_txn_id
and a.future_data_exists is null
and a.information3 < to_date('4712/12/31','YYYY/MM/DD')
and exists
( select 'Y' from ben_copy_entity_results b
where b.copy_entity_txn_id = a.copy_entity_txn_id
and b.table_alias = a.table_alias
and b.information1 = a.information1
and b.information2 = a.information3+1);
hr_utility.set_location('Updated bcer records for future_data_exists flag',25);
p_dml_operation in varchar2 default 'INSERT',
p_pl_cer_id out nocopy number,
p_pl_cer_ovn out nocopy number) is
l_pln_tr_id number;
p_dml_operation in varchar2 default 'INSERT',
p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
p_opt_cer_id out nocopy number,
p_opt_cer_ovn out nocopy number) is
l_opt_tr_id number;
pqh_gsp_utility.update_frps_point_rate(p_point_cer_id => p_opt_cer_id,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_business_group_id => p_business_group_id,
p_salary_rate => l_information2,
p_gross_index => l_information1,
p_effective_date => p_effective_date);
select spinal_point_id
from per_spinal_points
where parent_spine_id = p_scale_id
and not exists (select 1 from
ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='OPT'
and information257 = spinal_point_id) ;
select * from pay_grade_rules_f
where rate_id = p_payrate_id
and p_effective_date between effective_start_date and effective_end_date;
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
procedure update_frps_point_rate(p_point_cer_id in number,
p_copy_entity_txn_id in number,
p_business_group_id in number,
p_point_value in number,
p_effective_date in date) is
l_hrr_cer_id number;
pqh_gsp_rates.update_point_hrrate(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_rt_effective_date => p_effective_date,
p_gl_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_hrrate_cer_id => l_hrr_cer_id,
p_point_cer_id => p_point_cer_id,
p_point_value => p_point_value,
p_datetrack_mode => 'UPDATE_REPLACE');
end update_frps_point_rate;
select information70 calc_method,information51 prog_style
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM'
and result_type_cd = 'DISPLAY';
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'PLN'
AND result_type_cd ='DISPLAY';
select copy_entity_result_id
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
AND table_alias = 'OPT';
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
p_dml_oper => 'INSERT');
p_dml_oper => 'INSERT',
p_hrrate_cer_id => l_hrr_cer_id);
select plip_id
into l_plip_id
from ben_plip_f
where pgm_id = p_pgm_id
and pl_id = p_plan_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selected plip',20);