[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_lept_data(p_copy_entity_txn_id in number,
p_le_id in number,
p_pt_id in number) is
begin
-- make sure plan type row exists so that plan copy can read it
-- if no row doesnot exist, then we have to create it
-- make sure ptip row also exists else, we have to create it
-- do we need row in ben_opt_typ_pl_typ for plan copy
begin
update ben_copy_entity_results
set information248 = p_pt_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN';
end update_lept_data;
select meaning into l_name
from hr_lookups
where lookup_type = 'PQH_GSP_LE_PT_NAME'
and lookup_code = decode(p_cd,'PROG','PROG_LE'
,'SYNC','SYNC_LE'
,'PLAN','GSP_PT');
select ler_id,name,effective_start_date
into l_ler_id,l_ler_name,l_effective_start_date
from ben_ler_f
where effective_end_date = hr_general.end_of_time
and business_group_id = p_business_group_id
and typ_cd ='GSP'
and lf_evt_oper_cd ='PROG';
select ler_id,name,effective_start_date
into l_ler_id,l_ler_name,l_effective_start_date
from ben_ler_f
where effective_end_date = hr_general.end_of_time
and business_group_id = p_business_group_id
and typ_cd ='GSP'
and lf_evt_oper_cd ='SYNC';
select pl_typ_id,name,effective_start_date
into l_pt_id,l_pt_name,l_effective_start_date
from ben_pl_typ_f
where effective_end_date = hr_general.end_of_time
and business_group_id = p_business_group_id
and opt_typ_cd ='GSP'
and pl_typ_stat_cd ='A';
hr_utility.set_location('setup is fine, update staging area',10);
update_lept_data(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_le_id => l_ler_id,
p_pt_id => l_pt_id);
procedure delete_steps(p_grade_spine_id in number,
p_effective_date in date) is
cursor csr_steps is
select step_id,object_version_number,effective_start_date,effective_end_date
from per_spinal_point_steps_f
where grade_spine_id = p_grade_spine_id
and p_effective_date between effective_start_date and effective_end_date;
hr_grade_step_api.delete_grade_step
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE'
,p_step_id => l_step_id
,p_object_version_number => l_step_ovn
,p_effective_start_date => l_step_esd
,p_effective_end_date => l_step_eed
);
hr_utility.set_location('delete step complete'||l_step_id,20);
hr_utility.set_location('steps could not be deleted',40);
end delete_steps;
procedure delete_grade_spine(p_grade_spine_id in number,
p_effective_date in date,
P_Date_track_mode In Varchar2 Default 'DELETE') is
l_gs_ovn number;
select object_version_number,effective_start_date,effective_end_date
into l_gs_ovn,l_gs_esd,l_gs_eed
from per_grade_spines_f
where grade_spine_id = p_grade_spine_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('issues in selecting grade spine',20);
hr_grade_scale_api.delete_grade_scale
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_datetrack_mode => P_Date_track_mode
,p_grade_spine_id => p_grade_spine_id
,p_object_version_number => l_gs_ovn
,p_effective_start_date => l_gs_esd
,p_effective_end_date => l_gs_eed
);
end delete_grade_spine;
Procedure Delete_Step (p_copy_entity_txn_id in number,
p_effective_date in date,
p_date_track_mode in varchar2 default null) Is
--
l_Step_Id Per_Spinal_Point_Steps_F.Step_Id%TYPE;
l_Scale_Delete Varchar2(1) := 'N';
L_DELETE BOOLEAN;
L_DELETE_NEXT_CHANGE BOOLEAN;
Select information253 Step_id,
information254 Step_Ovn
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And Gs_parent_entity_result_id = P_Plip_Cer_Id
And table_alias = 'COP'
And dml_operation = 'DELETE';
Select Effective_Start_Date,
Effective_End_Date,
Object_Version_Number
From Per_Spinal_Point_Steps_F
Where Step_Id = P_Step_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select Copy_Entity_Result_id,
Information253 Grade_Id,
Information255 Scale_Id,
Information258 Scale_Cer_Id
from Ben_Copy_Entity_Results Cpp
Where Copy_Entity_Txn_id = P_Copy_Entity_Txn_Id
and Table_Alias = 'CPP'
and Result_type_Cd = 'DISPLAY'
and Exists
(Select 1
from Ben_Copy_Entity_Results
Where Copy_Entity_txn_id = P_Copy_Entity_Txn_id
and Gs_parent_Entity_Result_id = CPP.Copy_Entity_Result_id
and Table_Alias = 'COP'
and Dml_Operation = 'DELETE');
Select Parent_Spine_Id , Grade_Spine_Id,
Effective_Start_Date, Effective_End_Date
From Per_Grade_Spines_F
Where Grade_Id = P_grade_Id
and P_Effective_Date
Between Effective_Start_Date
and Effective_End_Date;
Select 'N'
From Ben_Copy_Entity_Results Opt
Where Opt.Copy_Entity_txn_id = P_Copy_Entity_txn_id
and Opt.Table_Alias = 'OPT'
and Information256 = P_Scale_Cer_Id
and Nvl(Dml_Operation,'XX') <> 'DELETE';
l_Scale_Delete := 'N';
Fetch Csr_Del_Scle into l_Scale_Delete;
l_Scale_Delete := 'N';
l_Scale_Delete := 'Y';
L_DELETE := FALSE;
L_DELETE_NEXT_CHANGE := FALSE;
l_Scale_Delete := 'Y';
,P_DELETE => L_DELETE
,P_FUTURE_CHANGE => L_FUTURE_CHANGE
,P_DELETE_NEXT_CHANGE => L_DELETE_NEXT_CHANGE);
If l_Datetrack_Mode = 'DELETE' then
IF L_DELETE THEN
l_Datetrack_Mode := 'DELETE';
ElsIf L_DELETE_NEXT_CHANGE Then
l_Datetrack_Mode := 'DELETE_NEXT_CHANGE';
Elsif L_DELETE Then
l_Datetrack_Mode := 'DELETE';
ElsIf L_DELETE_NEXT_CHANGE Then
l_Datetrack_Mode := 'DELETE_NEXT_CHANGE';
Select Count(Placement_Id)
into l_Plcmt_Cnt
from Per_Spinal_POint_Placements_f
Where Step_Id in
(Select Step_Id
from Per_Spinal_Point_Steps_f
Where Grade_Spine_id = L_hr_Grade_Scale_id);
l_Datetrack_Mode := 'DELETE';
hr_grade_step_api.delete_grade_step
(p_validate => FALSE
,p_effective_date => L_effective_date
,p_datetrack_mode => L_DateTrack_Mode
,p_step_id => Oipl_Rec.Step_id
,p_object_version_number => l_step_ovn
,p_effective_start_date => l_Effective_Start_Date
,p_effective_end_date => l_Effective_End_Date);
If L_Scale_Delete = 'Y' then
delete_grade_spine(L_hr_Grade_Scale_id,
L_effective_date,
L_Datetrack_Mode);
End Delete_Step;
Function Delete_Rate (p_copy_entity_txn_id in number,
p_effective_date in date)
Return Varchar2 Is
Cursor Csr_Pay_Rts is
Select Distinct Information293 Rt_Id
From Ben_Copy_Entity_results
Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
and Table_Alias = 'HRRATE'
and Dml_operation = 'DELETE';
Select Information1,
Information2,
Information3,
Information298
From Ben_Copy_Entity_results
Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
and Table_Alias = 'HRRATE'
and Dml_operation = 'DELETE'
and Information293 = P_Rat_Name_Id;
Select Rate_Id, Rate_type, Object_version_Number
From Pay_rates
Where Rate_Id = P_Rate_Id
and Not Exists
(Select 1
from Ben_Copy_Entity_results
Where Copy_Entity_Txn_Id = P_Copy_Entity_Txn_Id
and Table_Alias = 'HRRATE'
and Nvl(Dml_operation,'XX') <> 'DELETE'
and Information293 = P_Rate_Id);
hr_utility.set_location('Delete HR Rate' || Hr_Rate_Rec.Information1 ,20);
Hr_Rate_Values_Api.DELETE_RATE_VALUE
(P_GRADE_RULE_ID => Hr_Rate_Rec.Information1
,P_DATETRACK_MODE => 'ZAP'
,P_EFFECTIVE_DATE => P_Effective_Date
,P_OBJECT_VERSION_NUMBER => l_Hr_Rt_Ovn
,P_EFFECTIVE_START_DATE => l_Hr_Rt_Esd
,P_EFFECTIVE_END_DATE => l_Hr_Rt_Eed);
hr_utility.set_location('Delete HR Rate Name ' || l_Rate_Id ,10);
hr_rate_api.DELETE_RATE
(P_EFFECTIVE_DATE => P_Effective_Date
,P_RATE_ID => l_Rate_Id
,P_RATE_TYPE => l_rate_type
,P_OBJECT_VERSION_NUMBER => L_Rate_Ovn);
End Delete_Rate;
Function delete_option (p_copy_entity_txn_id in number,
p_effective_date in date)
RETURN varchar2 is
--
Cursor csr_delete_opt is
Select information1 opt_id,
information2 effective_start_date,
information3 effective_end_date,
information4 business_group_id,
information265 opt_ovn
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
And table_alias = 'OPT'
And dml_operation ='DELETE';
Select Mapping_Table_Pk_Id
From Ben_opt_F
Where opt_id = p_opt_id
and p_effective_date
between Effective_Start_Date and Effective_End_Date
and Mapping_table_name = 'PER_SPINAL_POINTS';
Select parent_spine_Id, Object_version_Number
from Per_Spinal_Points
Where Spinal_Point_Id = P_Point_id;
Select Step_id
From Per_Spinal_Point_Steps_F
Where Spinal_Point_Id = P_Point_id;
Select Object_Version_Number
from per_Parent_spines
Where Parent_spine_Id = P_Parent_Spine_Id;
hr_utility.set_location('Entering: delete_option',5);
For del_opt_rec in csr_delete_opt loop
--
-- When a point that is not used as step is brought to staging area, an option
-- is created for it anyway, if the option does not already exist.
-- When the point is deleted the option record will be marked
-- delete but theere will be not opt_id as there is no record in BEN.
--
--
If del_opt_rec.opt_id is not null then
--
-- Determine the date-tracked mode to use when deleting the row. If no date-tracked
-- mode is passed, the system will determine date-tracked mode to use when deleting
-- by reading actual BEN table rows.
--
--
Open csr_ben_opt(del_opt_rec.opt_id);
hr_utility.set_location('Going to Delete Progression Points' || l_Point_Id ,10);
hr_progression_point_api.Delete_Progression_point
(P_SPINAL_POINT_ID => l_Point_Id,
P_OBJECT_VERSION_NUMBER => l_Point_Ovn);
hr_utility.set_location('Progression Points deleted' || l_Point_Id ,20);
Select Count(Spinal_Point_Id) into L_Spinal_Cnt
From Per_Spinal_Points
where Parent_Spine_Id = l_Parent_Spine_Id;
hr_utility.set_location('PARENT SPINE TO delete' || l_Parent_Spine_Id ,30);
hr_pay_scale_api.DELETE_PAY_SCALE
(P_PARENT_SPINE_ID => l_Parent_Spine_Id
,P_OBJECT_VERSION_NUMBER => l_Prnt_Ovn);
hr_utility.set_location('PARENT SPINE deleted' || l_Parent_Spine_Id ,30);
hr_utility.set_location('Leaving: delete_option',10);
hr_utility.set_location('Exception raised: delete_option',99);
select rt.rate_id
into l_rate_id
from hr_lookups lkp, pay_rates rt
where lkp.lookup_code = p_frequency
and rt.rate_type ='G'
and rt.business_group_id = p_business_group_id
and lkp.lookup_type = 'PQH_GSP_GEN_PAY_RATE_NAME'
and rt.name = lkp.meaning;
hr_utility.set_location('issues in selecting freq payrate ',30);
select rt.rate_id
into l_rate_id
from per_parent_spines scl, pay_rates rt
where rt.parent_spine_id = scl.parent_spine_id
and scl.parent_spine_id = p_scale_id
and rt.rate_type ='SP'
and rt.name = scl.name;
hr_utility.set_location('issues in selecting scl payrate ',30);
select copy_entity_result_id,information1,information98
from ben_copy_entity_results Scl
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'SCALE'
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 (information256 = Scl.Copy_Entity_Result_Id or information255 = scl.Information1)
and Dml_Operation = 'DELETE')
and Exists
(Select 1
from Ben_Copy_Entity_Results
Where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'HRRATE'
and dml_operation in ('INSERT','UPDATE'));
select copy_entity_result_id,information1
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'OPT'
and (information256 = p_scl_cer_id or information255 = p_scale_id);
update ben_copy_entity_results
set information293 = l_grd_payrate_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'HRRATE'
and information277 is not null
and information293 is null;
hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
hr_utility.set_location('grd hrrate rows updated',30);
update ben_copy_entity_results
set information293 = l_scl_payrate_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'HRRATE'
and information278 = j.copy_entity_result_id
and information293 is null;
hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
hr_utility.set_location('pt hrrate rows updated',30);
update ben_copy_entity_results
set information257 = p_point_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='OPT'
and copy_entity_result_id = p_point_cer_id;
hr_utility.set_location('num of opt updated'||sql%rowcount,20);
update ben_copy_entity_results
set information256 = p_point_id
where table_alias = 'COP'
and copy_entity_txn_id = p_copy_entity_txn_id
and information262 = p_point_cer_id;
hr_utility.set_location('num of oipl updated'||sql%rowcount,20);
update ben_copy_entity_results
set information276 = p_point_id
where table_alias = 'HRRATE'
and copy_entity_txn_id = p_copy_entity_txn_id
and information278 = p_point_cer_id;
hr_utility.set_location('num of hrrs updated'||sql%rowcount,20);
update ben_copy_entity_results
set information253 = p_step_id,
information1 = nvl(information1,l_oipl_id)
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='COP'
and copy_entity_result_id = p_step_cer_id;
hr_utility.set_location('num of oipl updated'||sql%rowcount,20);
update ben_copy_entity_results
set information266 = p_hrrate_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='ABR'
and (information277 is null or information277 = p_grade_cer_id)
and (information278 is null or information278 = p_point_cer_id);
hr_utility.set_location('num of abrs updated'||sql%rowcount,20);
update ben_copy_entity_results
set information280 = p_grade_spine_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CPP'
and copy_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information255 = p_grade_spine_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='COP'
and gs_parent_entity_result_id = p_plip_cer_id;
hr_utility.set_location('num of oipls updated'||sql%rowcount,20);
update ben_copy_entity_results
set information1 = p_scale_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='SCALE'
and copy_entity_result_id = p_scale_cer_id;
hr_utility.set_location('num of scales updated'||sql%rowcount,20);
update ben_copy_entity_results
set information255 = p_scale_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='CPP'
and information258 = p_scale_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information255 = p_scale_id
where table_alias = 'OPT'
and copy_entity_txn_id = p_copy_entity_txn_id
and information256 = p_scale_cer_id;
hr_utility.set_location('num of opts updated'||sql%rowcount,20);
update ben_copy_entity_results
set information260 = p_scale_id
where table_alias = 'COP'
and copy_entity_txn_id = p_copy_entity_txn_id
and information259 = p_scale_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information253 = p_grade_id
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and information252 = p_grade_cer_id;
hr_utility.set_location('num of plips updated'||sql%rowcount,20);
update ben_copy_entity_results
set information223 = p_grade_id,
information294 = p_grade_id
where copy_entity_result_id = p_grade_cer_id;
hr_utility.set_location('num of pl updated'||sql%rowcount,20);
update ben_copy_entity_results
set information255 = p_grade_id
where table_alias = 'HRRATE'
and copy_entity_txn_id = p_copy_entity_txn_id
and information277 = p_grade_cer_id;
hr_utility.set_location('num of hrate updated'||sql%rowcount,20);
select max(sequence) into l_max_seq
from per_grades
where business_group_id = p_business_group_id;
select context_business_group_id
into l_bg_id
from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
If P_Date_Track_Mode = 'UPDATE_OVERRIDE' Then
l_Del_Dt_Mode := 'DELETE';
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
update fnd_sessions
set effective_date = p_effective_date
where session_id = userenv('sessionid');
insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'), p_effective_date);
update ben_copy_entity_results
set dml_operation = 'DELETE'
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias in ('COP','OPT','CPP','HRRATE','ABR')
and information104 = 'UNLINK';
hr_utility.set_location('num of unlinks updated'||sql%rowcount,2);
hr_utility.set_location('issues in marking recs for delete',1);
hr_utility.set_location('calling delete obj',1);
/* l_return := pqh_gsp_del_grade_ladder_obj.delete_from_ben
(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date,
p_datetrack_mode => 'DELETE'); */
Delete_Step(p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date,
P_Date_Track_Mode => l_Del_Dt_Mode);
l_return := Delete_Rate (p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date);
l_return := delete_option (p_copy_entity_txn_id => p_copy_entity_txn_id,
p_effective_date => p_effective_date);
pqh_gsp_hr_to_stage.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);
elsif p_datetrack_mode = 'UPDATE' then
l_datetrack_mode := 'UPDATE_OVERRIDE';
select information50,information41,substr(information5,1,80)
into l_gl_currency,l_gl_freq,l_gl_name
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd ='DISPLAY'
and table_alias = 'PGM';
hr_utility.set_location('issues in selecting pgm row ',10);
pqh_gsp_stage_to_ben.cre_update_elig_prfl(
p_copy_entity_txn_id => p_copy_entity_txn_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id);
hr_utility.set_location('Elpros created/updated',20);
update pqh_copy_entity_txns
set status ='COMPLETED'
where 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 Not In ('PQH_GSP_TASK_LIST','PQH_CORPS_TASK_LIST');
hr_utility.set_location('grade row checked for update',30);
hr_utility.set_location('Scale row updated',40);
hr_utility.set_location('grade spine row updated',40);
hr_utility.set_location('option row updated',50);
hr_utility.set_location('oipl row updated',60);
hr_utility.set_location('Hrate row updated',70);
select grade_spine_id,parent_spine_id
into l_grade_spine_id,l_parent_spine_id
from per_grade_spines_f
where grade_id = p_grade_id
and p_effective_date between effective_start_date and effective_end_date;
/* delete_steps(p_grade_spine_id => l_grade_spine_id,
p_effective_date => p_effective_date);
delete_grade_spine(p_grade_spine_id => l_grade_spine_id,
p_effective_date => p_effective_date); */
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and Dml_Operation <> 'DELETE';
select information1
into l_scale_id
from ben_copy_entity_results
where copy_entity_result_id = grd_spine.information258;
hr_utility.set_location('scale was created but deleted',10);
select information223
into l_grade_id
from ben_copy_entity_results
where copy_entity_result_id = grd_spine.information252;
select grade_id
into l_grade_id
from per_grade_spines_f
where grade_id = l_grade_id
and rownum < 2 ;
select date_from
into l_grd_effstdt
from per_grades
where grade_id = l_grade_id;
hr_utility.set_location('grd_spine exists,ceiling step updated',10);
l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode
(p_table_name => 'PER_GRADE_SPINES_F',
p_key_column_name => 'GRADE_SPINE_ID',
p_key_column_value => l_grade_spine_id,
p_effective_date => p_effective_date);
hr_grade_scale_api.update_grade_scale
(
p_effective_date => p_effective_date --l_grd_effstdt
,p_datetrack_mode => l_dt_mode
,p_grade_spine_id => l_grade_spine_id
,p_object_version_number => l_gs_ovn
,p_business_group_id => p_business_group_id
,p_parent_spine_id => l_scale_id
,p_grade_id => l_grade_id
,p_ceiling_step_id => l_ceiling_step_id
,p_starting_step => l_starting_step
,p_effective_start_date => l_gs_esd
,p_effective_end_date => l_gs_eed
);
hr_utility.set_location('issue in update grade scale'||l_ceiling_step_id,23);
update per_grade_spines_f
set ceiling_step_id = l_ceiling_step_id
where grade_spine_id = l_grade_spine_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
hr_utility.set_location('grd_spine exists,starting step updated',10);
update per_grade_spines_f
set starting_step = l_starting_step
where grade_spine_id = l_grade_spine_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'OPT'
and dml_operation in ('INSERT','UPDATE','UPD_INS')
order by information253 desc; -- do highest seq. first
select information1
into l_scale_id
from ben_copy_entity_results
where copy_entity_result_id = point_rec.information256;
if point_rec.dml_operation ='INSERT'
and point_rec.information257 is null
and l_scale_id is not null then
hr_utility.set_location('going for ins',20);
elsif point_rec.dml_operation in ('UPDATE','UPD_INS')
and point_rec.information257 is not null
and point_rec.information255 is not null then
hr_utility.set_location('going for upd',30);
pqh_cpd_hr_to_stage.update_point(p_point_id => l_point_id,
p_point_ovn => l_point_ovn,
p_information_category => point_rec.information101,
p_information1 => point_rec.information173,
p_information2 => point_rec.information175,
p_information3 => point_rec.information179,
p_information4 => point_rec.information181,
p_information5 => point_rec.information182,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_parent_spine_id => l_scale_id,
p_sequence => point_rec.information253,
p_spinal_point => point_rec.information98);
hr_progression_point_api.update_progression_point
(p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_parent_spine_id => l_scale_id
,p_sequence => point_rec.information253
,p_spinal_point => point_rec.information98
,p_spinal_point_id => l_point_id
,p_object_version_number => l_point_ovn
);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN'
and dml_operation in ('INSERT','UPDATE','UPD_INS');
if grd_rec.dml_operation = 'INSERT'
and l_grade_id is null
and grd_rec.information221 is not null
and grd_rec.information5 is not null then
hr_utility.set_location('new grade is being created'||l_proc,20);
elsif grd_rec.dml_operation in ('UPDATE','UPD_INS')
and l_ovn is not null
and grd_rec.information221 is not null
and grd_rec.information5 is not null
and l_grade_id is not null then
hr_utility.set_location('grade is being updated'||l_grade_id,60);
hr_grade_api.update_grade(p_date_from => grd_rec.information307
,p_effective_date => p_effective_date
,p_date_to => grd_rec.information308
,p_short_name => grd_rec.information102
,p_grade_id => l_grade_id
,p_object_version_number => l_ovn
,p_concat_segments => l_concat_segments
,p_grade_definition_id => grd_rec.information221
,p_name => grd_rec.information5);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'COP'
and dml_operation = 'INSERT';
select information280
into l_grd_sp_id
from ben_copy_entity_results
where copy_entity_result_id = step_rec.gs_parent_entity_result_id;
select information257
into l_point_id
from ben_copy_entity_results
where copy_entity_result_id = step_rec.information262;
if step_rec.dml_operation ='INSERT'
and step_rec.information253 is null
and step_rec.information104 = 'LINK'
and l_grd_sp_id is not null
and l_point_id is not null then
hr_utility.set_location('going for ins',20);
SELECT pgs.EFFECTIVE_START_DATE
INTO l_grd_effstdt
FROM per_grade_spines_f pgs
WHERE pgs.grade_spine_id = l_grd_sp_id
AND p_effective_date BETWEEN pgs.effective_start_date
AND pgs.effective_end_date;
hr_utility.set_location('ceiling step, update grd_sp',23);
update per_grade_spines_f
set ceiling_step_id = l_step_id
where grade_spine_id = l_grd_sp_id
and p_effective_date between effective_start_date and effective_end_date;
hr_utility.set_location('num of grd_sps updated'||sql%rowcount,20);
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias ='SCALE'
and dml_operation in ('INSERT','UPDATE');
if scl_rec.dml_operation = 'INSERT'
and l_scale_id is null
and scl_rec.information98 is not null then
hr_utility.set_location('new scale is being created'||l_proc,20);
elsif scl_rec.dml_operation ='UPDATE' and l_scale_id is not null
and scl_rec.information98 is not null and l_scale_ovn is not null then
hr_utility.set_location('scale is being updated'||l_proc,60);
pqh_cpd_hr_to_stage.update_scale(p_scale_id => l_scale_id,
p_scale_ovn => l_scale_ovn,
p_information_category => scl_rec.information101,
p_information1 => scl_rec.information112,
p_information2 => scl_rec.information113,
p_business_group_id => p_business_group_id,
p_name => scl_rec.information98,
p_effective_date => p_effective_date ,
p_increment_frequency => scl_rec.information253,
p_increment_period => scl_rec.information99);
hr_pay_scale_api.update_pay_scale
(p_name => scl_rec.information98
,p_increment_frequency => scl_rec.information253
,p_increment_period => scl_rec.information99
,p_parent_spine_id => l_scale_id
,p_object_version_number => l_scale_ovn
) ;
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'HRRATE'
and dml_operation in ('INSERT','UPDATE')
order by INFORMATION277,INFORMATION278,INFORMATION2;
if hrr_rec.dml_operation = 'INSERT'
and nvl(hrr_rec.datetrack_mode,'CORRECTION') <> 'UPDATE_REPLACE' then
l_dml_operation := 'INSERT';
elsif hrr_rec.dml_operation = 'INSERT' and hrr_rec.datetrack_mode = 'UPDATE_REPLACE' then
l_dml_operation := 'UPDATE';
elsif hrr_rec.dml_operation = 'UPDATE' then
l_dml_operation := 'UPDATE';
select information223
into l_grd_sp_id
from ben_copy_entity_results
where copy_entity_result_id = hrr_rec.INFORMATION277;
select information257
into l_grd_sp_id
from ben_copy_entity_results
where copy_entity_result_id = hrr_rec.INFORMATION278;
if l_dml_operation = 'INSERT'
and l_hrrate_id is null
and l_grd_sp_id is not null
and l_payrate_id is not null then
hr_utility.set_location('new hrrate is being created'||l_proc,20);
elsif l_dml_operation ='UPDATE'
and l_hrrate_id is not null
and l_grd_sp_id is not null
and l_hrr_ovn is not null
and l_payrate_id is not null then
hr_utility.set_location('hrrate is being updated'||l_proc,60);
l_dt_mode := pqh_gsp_stage_to_ben.get_update_mode
(p_table_name => 'PAY_GRADE_RULES_F',
p_key_column_name => 'GRADE_RULE_ID',
p_key_column_value => l_hrrate_id,
p_effective_date => l_effective_date);
hr_rate_values_api.update_rate_value
(p_effective_date => l_effective_date
,p_currency_code => p_gl_currency
,p_maximum => hrr_rec.information295
,p_mid_value => hrr_rec.information296
,p_minimum => hrr_rec.information294
,p_value => nvl(hrr_rec.information297,0)
,p_grade_rule_id => l_hrrate_id
,p_datetrack_mode => l_dt_mode
,p_object_version_number => l_hrr_ovn
,p_effective_start_date => l_hrr_esd
,p_effective_end_date => l_hrr_eed);