The following lines contain the word 'select', 'insert', 'update' or 'delete':
select min(effective_start_date)
into l_plan_type_date
from ben_pl_typ_f
where business_group_id = p_business_group_id
and opt_typ_cd ='GSP'
and pl_typ_stat_cd ='A';
select information308
into l_plan_type_date
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PGM';
select 'Y'
into l_status
from ben_pl_typ_f
where business_group_id = p_business_group_id
and opt_typ_cd ='GSP'
and pl_typ_stat_cd ='A'
and rownum<2 ;
Select copy_entity_result_id
From ben_copy_entity_results
Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND Table_Alias = 'CPP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND nvl(Information104,'PPP') <> 'UNLINK';
Select grdldr.Information1
From Ben_Copy_Entity_Results grdldr
Where grdldr.Table_Alias = 'PGM'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
And Information4 = p_Business_Group_Id;
Select grdldr.Information1
From Ben_Copy_Entity_Results grdldr
Where grdldr.Table_Alias = 'CPD'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id
And Information4 = p_Business_Group_Id;
Select grd.Information253,
grd.Information5
From Ben_Copy_Entity_Results grd
Where grd.Table_Alias = 'CPP'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
Select assignment_id
From per_all_assignments_f assgt
Where grade_id = l_grade_id
AND Grade_Ladder_Pgm_Id = l_pgm_id
AND p_Effective_Date BETWEEN assgt.effective_start_date
AND nvl(assgt.effective_end_date,hr_general.end_of_time)
AND assgt.business_group_id = p_Business_Group_Id;
Select Null
From per_all_assignments_f assgt
Where grade_id = l_grade_id
AND p_Effective_Date BETWEEN assgt.effective_start_date
AND nvl(assgt.effective_end_date,hr_general.end_of_time)
AND assgt.business_group_id = p_Business_Group_Id;
Select Null
From ben_pgm_f
Where pgm_id = l_pgm_id
And pgm_typ_cd = 'GSP'
And Dflt_Pgm_Flag = 'Y'
And business_group_id = p_business_group_id
And p_Effective_Date Between Effective_Start_date
And nvl(Effective_End_Date,hr_general.end_of_time);
select null
from per_gen_hierarchy_nodes
where hierarchy_version_id = l_crpth_hier_ver
and information9 = l_corps_id
and information23 = l_grade_id;
select nvl(pa.decode_function_name, '99') into prev_task from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information100 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information101 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information102 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information103 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information104 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information105 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
select bcer.information106 into st_icon from
ben_copy_entity_results bcer, pqh_table_route ptr, pqh_attributes pa
where
bcer.table_route_id = ptr.table_route_id
and ptr.table_route_id = pa.master_table_route_id
and ptr.table_alias = l_table_alias
and pa.attribute_name = p_gsp_node
and bcer.copy_entity_txn_id = p_copy_enty_txn_id;
Select Decode(nvl(grdldr.INFORMATION18,'N'),'Y','POINT','STEP') /* INFORMATION18 -> Use Progression Points */
FROM Ben_Copy_Entity_Results grdldr
WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grdldr.Table_Alias = 'PGM';
Select grdldr.information1 -- PGM_ID
From Ben_Copy_Entity_Results grdldr
Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grdldr.Table_Alias = 'PGM';
Select grdldr.Information1
From Ben_Copy_Entity_Results grdldr
Where grdldr.Table_Alias = 'CPD'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
Select grd.information253
From Ben_Copy_Entity_Results grd
Where grd.Table_Alias = 'CPP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
From Ben_Copy_Entity_Results step
Where step.Copy_Entity_Result_Id= p_step_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND step.Table_Alias = 'COP');
select null
from per_gen_hierarchy_nodes
where hierarchy_version_id = l_crpth_hier_ver
and information9 = l_corps_id
and information23 = l_grade_id
and information3 = p_step_id;
Select '1'
From Ben_Copy_Entity_Results
Where Table_Alias = 'ELP'
And Gs_Parent_Entity_Result_Id = p_Copy_Entity_Result_Id
And Copy_Entity_Txn_Id = p_copy_entity_txn_id
And Result_type_Cd = 'DISPLAY'
And Nvl(Information104,'PPP') <> 'UNLINK' ;
Add, Update, Remove, Progression Order, Score Icons at Different Levels.
Return Values :
=================
E Enable
D Disable
N NoIcon
p_page has values
=================
GRADE_HGRID -- Grades Page
STEP_HGRID -- Steps Page
PRG_RULE_HGRID -- Progression Rules/ Review And Submit Pages
p_action has values
===================
ADD
UPDATE
REMOVE
PRGORDER
SCORE
GRDQUOTA
CARRERPATH
*/
Cursor Csr_steps_exists
IS
Select Null
From Ben_COpy_Entity_Results
Where Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
And table_alias ='COP'
And Copy_Entity_Txn_Id = p_copy_entity_txn_id
And result_type_cd = 'DISPLAY'
And Nvl(Information104,'PPP') <> 'UNLINK';
If ( p_action = 'UPDATE' OR p_action = 'REMOVE' OR p_action = 'GRDQUOTA') Then
return 'E';
Elsif (p_action = 'UPDATE') Then
-- If Steps Exists for the Grade then Enable Update Button
-- Else Disable It
Open Csr_steps_exists ;
Elsif p_action = 'UPDATE' Then
l_exists := pqh_gsp_utility.CHK_PROFILE_EXISTS(p_copy_entity_result_id => p_copy_entity_result_id,
p_copy_entity_txn_id => p_copy_entity_txn_id);
elsif p_action = 'UPDATE' Then
if g_debug then
hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,50);
elsif p_action = 'UPDATE'Then
if g_debug then
hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,80);
hr_utility.set_location('Update is through' ,50);
Select oipl.Information262, -- Point Cer Id
oipl.Information259, -- Pay Scale Cer Id
oipl.Gs_Parent_Entity_Result_id -- Grade Cer_id
From Ben_Copy_Entity_Results oipl
Where oipl.Copy_Entity_Result_Id = p_copy_entity_result_id
And oipl.Copy_Entity_Txn_Id = p_copy_entity_Txn_id
AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
And oipl.Table_Alias = 'COP';
Select count(*)
From Ben_Copy_Entity_Results opt1,
Ben_Copy_Entity_Results opt2,
Ben_Copy_Entity_Results oipl
Where oipl.Information262 = opt2.copy_entity_result_id
And opt1.Information253 >= opt2.Information253
And opt1.copy_entity_result_id = l_opt_cer_id
And oipl.Information259 = l_pay_scale_cer_id -- Information259 is Pay Scale Cer Id
And oipl.Gs_Parent_Entity_Result_Id = l_grd_cer_id
AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
And Nvl(oipl.Information104,'PPP') <> 'UNLINK'
and oipl.copy_entity_txn_id = l_cet_id
group by opt1.Information263,oipl.Gs_Parent_Entity_Result_Id,opt1.Information98;
select information228
from ben_copy_entity_results
where copy_entity_result_id = l_grd_cer_id;
Select grdldr.information5
From Ben_Copy_Entity_Results grdldr
Where grdldr.Copy_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grdldr.Table_Alias= p_Table_Alias;
Select grd.information5,information98
From Ben_Copy_Entity_Results grd
Where grd.Copy_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grd.Table_Alias = p_Table_Alias;
Select step.information5 , information99
From Ben_Copy_Entity_Results step
Where step.Copy_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND step.Table_Alias = p_Table_Alias;
Select elig.information5
From Ben_Copy_Entity_Results elig
Where elig.Copy_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND elig.Table_Alias = p_Table_Alias;
Select to_number(opt.information173)
From Ben_Copy_Entity_Results Opt
Where opt.Table_Alias = 'OPT'
AND opt.Copy_Entity_Txn_id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And opt.Copy_Entity_Result_Id = ( Select oipl.Information262 -- Point Cer Id
From Ben_Copy_Entity_Results oipl
Where Copy_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And Copy_Entity_Txn_id = p_copy_entity_txn_id
And Table_Alias = 'COP');
Select Substr(Display_name,1,50)
From Pqh_table_Route
Where Table_Alias = p_Table_Alias;
Select null
From Per_Grades
Where name = p_name
And business_group_id = p_business_group_id
And grade_id <> nvl(p_grade_id,-1)
Union ALL
Select null
From ben_copy_entity_results
Where
information5 = p_name
And information4 = p_business_group_id
And table_alias = 'PLN'
And result_type_cd = 'DISPLAY'
And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
And copy_entity_txn_id = p_copy_entity_txn_id;
Select null
From per_grades
Where short_name = p_short_name
And business_group_id = p_business_group_id
And grade_id <> nvl(p_grade_id,-1)
Union ALL
Select null
From ben_copy_entity_results
Where information102 = p_short_name
And information4 = p_business_group_id
And table_alias='PLN'
And result_type_cd = 'DISPLAY'
And copy_entity_result_id <> nvl(p_copy_entity_result_id,-1)
And copy_entity_txn_id=p_copy_entity_txn_id;
Select information98
From ben_copy_entity_results std
Where std.gs_parent_entity_result_id = p_copy_entity_result_id
And std.table_alias = 'ABR'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND p_effective_date between information2 and nvl(information3,to_date('31/12/4712','dd/mm/RRRR'));
procedure delete_transaction
(p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE) IS
begin
del_gl_details_from_stage(p_pqh_copy_entity_txn_id);
delete from pqh_copy_entity_txns where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
end delete_transaction;
delete from pqh_copy_entity_attribs where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
delete from ben_copy_entity_results where copy_entity_txn_id = p_pqh_copy_entity_txn_id;
select pgm_id, object_version_number from ben_pgm_f where pgm_id = p_ben_pgm_id and effective_end_date = hr_general.end_of_time;
select copy_entity_txn_id from pqh_copy_entity_txns where copy_entity_txn_id in
(select copy_entity_txn_id from ben_copy_entity_results
where information1 = p_ben_pgm_id and table_alias = 'PGM' and information_category = 'GRADE_LADDER'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY')
and status = 'SFL';
ben_program_api.delete_Program
(p_pgm_id => p_ben_pgm_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => each_rec.object_version_number
,p_effective_date => p_effective_date_in
,p_datetrack_mode => 'DELETE'
);
delete_transaction(sfl_txn.copy_entity_txn_id);
Select Steps.Step_Id, Steps.Spinal_point_id, Steps.Grade_spine_id
From Per_Spinal_Point_Placements_F Plcmt,
Per_Spinal_Point_Steps_F Steps
Where Plcmt.ASSIGNMENT_ID = P_Entity_id
and P_Effective_Date
Between Plcmt.Effective_Start_Date and Plcmt.Effective_End_Date
and Plcmt.Step_id = Steps.Step_Id
and P_Effective_Date
Between Steps.Effective_Start_Date and Steps.Effective_End_Date;
Select Step.Step_id, Step.Spinal_point_id, Step.Grade_spine_id
From Ben_Oipl_F Oipl,
Ben_pl_F Pl,
Ben_Opt_F Opt,
Per_Spinal_points point,
Per_Grade_Spines_f GSpine,
Per_Spinal_point_Steps_F Step
Where Oipl.Oipl_id = P_Entity_Id
and P_Effective_Date
Between OiPl.Effective_Start_Date and OiPl.Effective_End_Date
and Pl.Pl_id = Oipl.Pl_Id
and P_Effective_Date
Between Pl.Effective_Start_Date and Pl.Effective_End_Date
and Oipl.Opt_id = Opt.Opt_id
and P_Effective_Date
Between Opt.Effective_Start_Date and Opt.Effective_End_Date
and Point.Spinal_Point_id = Opt.Mapping_Table_Pk_Id
and Pl.Mapping_Table_Pk_Id = Gspine.Grade_Id
and P_Effective_Date
Between Gspine.Effective_Start_Date and Gspine.Effective_End_Date
and Step.Grade_Spine_Id = Gspine.Grade_Spine_Id
and P_Effective_Date
Between Step.Effective_Start_Date and Step.Effective_End_Date
and Step.SPINAL_POINT_ID = Point.SPINAL_POINT_ID;
Select pev.screen_entry_value
From pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_entry_values_f pev
Where pee.Assignment_id = P_Assignment_id
and P_Effective_Date
between pee.Effective_Start_Date and pee.Effective_End_Date
and Piv.Input_Value_id = l_Input_Value_id
and P_Effective_Date
Between Piv.Effective_Start_Date and Piv.Effective_End_Date
and pev.ELEMENT_ENTRY_ID = Pee.ELEMENT_ENTRY_ID
and Piv.INPUT_VALUE_ID = Pev.INPUT_VALUE_ID
and P_Effective_Date
Between Pev.Effective_Start_Date and Pev.Effective_End_Date;
Select input_value_id
From Per_Pay_Bases ppb,
Per_All_Assignments_f paf
Where paf.Assignment_Id = p_Assignment_Id
and p_Effective_Date
Between Paf.Effective_Start_Date and Paf.Effective_End_Date
and paf.pay_basis_id = ppb.pay_basis_id;
Select DFLT_INPUT_VALUE_ID
from Ben_Pgm_f pgm,
Per_All_Assignments_f paf
Where paf.Assignment_Id = p_Assignment_Id
and p_Effective_Date
Between Paf.Effective_Start_Date and Paf.Effective_End_Date
and paf.GRADE_LADDER_PGM_ID = pgm.pgm_id
and p_Effective_Date
Between pgm.Effective_Start_date and pgm.Effective_End_Date;
Select Name
from Per_Collective_Agreements
Where Collective_Agreement_Id = P_CAGR_Id;
select information5 grade_ladder_name from ben_copy_entity_results
where copy_entity_txn_id = p_pqh_copy_entity_txn_id
and table_alias = 'PGM'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information_category = 'GRADE_LADDER';
Select Name
From ben_pgm_f
Where pgm_id = p_pgm_id
And business_group_id = p_business_group_id
And effective_end_date>= p_effective_date
And pgm_typ_cd = 'GSP'
And Dflt_Pgm_Flag = 'Y';
Select Null
From ben_pgm_f
Where pgm_id <> nvl(p_pgm_id,-1)
And business_group_id = p_business_group_id
And p_effective_date Between Effective_Start_Date
And nvl(Effective_End_Date, hr_general.end_of_time)
And pgm_typ_cd = 'GSP'
And Dflt_Pgm_Flag = 'Y';
Select Null
FROM BEN_COPY_ENTITY_RESULTS step
WHERE step.copy_entity_txn_id = p_copy_entity_txn_id
AND step.TABLE_ALIAS ='COP'
And nvl(step.INFORMATION104,'PPP') <> 'UNLINK'
AND step.result_type_cd = 'DISPLAY';
Select grd.Copy_Entity_Result_Id, grd.Information5
From Ben_Copy_Entity_Results grd
Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And nvl(grd.Information104,'PPP') <> 'UNLINK'
And grd.Table_Alias = 'CPP'
And grd.Information4 = p_business_group_id;
Select Null
From Ben_Copy_Entity_Results step
Where step.Copy_Entity_txn_Id = p_copy_entity_txn_id
And step.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And step.Table_Alias = 'COP'
And nvl(step.Information104,'PPP') <> 'UNLINK'
And step.Information4 = p_business_group_id;
Select Null
From Ben_Copy_Entity_Results ceiling
Where ceiling.Copy_Entity_txn_Id = p_copy_entity_txn_id
And ceiling.Gs_Parent_Entity_Result_Id = p_copy_entity_result_id
And ceiling.Table_Alias = 'COP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And nvl(ceiling.Information104,'PPP') <> 'UNLINK'
/*Information98 is used to store Ceiling Step Flag */
And ceiling.Information98 = 'Y'
And ceiling.Information4 = p_business_group_id;
Select grd.Information5 ,
grd.Information306,
grd.Information307
From Ben_Copy_Entity_Results grd
Where grd.Copy_Entity_txn_Id = p_copy_entity_txn_id
AND grd.Table_Alias = 'CPP'
AND nvl(grd.Information104,'PPP') <> 'UNLINK'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND p_effective_date Not BETWEEN grd.Information306
AND nvl(grd.Information307,hr_general.end_of_time)
AND Information4 = p_business_group_id;
Select null
From Per_All_Assignments_F
Where Grade_Ladder_Pgm_Id = p_pgm_id
And p_effective_date between effective_start_date
And nvl(effective_end_date, hr_general.end_of_time)
And Business_Group_Id = p_business_group_id;
Select pgm.PGM_STAT_CD -- Activate A , Inactivate I
From BEN_PGM_F pgm
Where pgm.Pgm_Id = p_pgm_id
And pgm.Business_Group_Id = p_business_group_id
And p_effective_date between pgm.effective_start_date
And nvl(pgm.effective_end_date, hr_general.end_of_time);
Select Months_Between(P_Effective_Date,Start_Date) / 12
from Per_All_people_F
Where Person_id = p_Person_Id
and P_Effective_Date
Between Effective_Start_Date and Effective_End_Date;
Select Name
from Fnd_Currencies_Vl
Where Currency_Code = P_Corrency_Code
and Enabled_Flag = 'Y';
Select spinal_point
from per_spinal_points
Where Spinal_point_id = p_Point_id;
Procedure update_or_delete_grade
( p_copy_entity_txn_id in number,
p_grade_result_id in number,
p_effective_date in Date) IS
/* Author : mvankada
Purpose : This procedure Update/Delete the grade record
*/
-- To Get Pgm_Id of PGM based on Txn_id
Cursor csr_pgm_id
IS
Select grdldr.Information1
From Ben_Copy_Entity_Results grdldr
Where grdldr.Table_Alias = 'PGM'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grdldr.Copy_Entity_Txn_Id = p_Copy_Entity_Txn_Id;
Select grd.Information253
From Ben_Copy_Entity_Results grd
Where grd.Table_Alias = 'CPP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And grd.Copy_Entity_Result_Id = p_Grade_Result_Id;
Select pln.Copy_Entity_Result_Id
From Ben_Copy_Entity_Results pln
Where pln.Gs_Mirror_Src_Entity_Result_Id = p_grade_result_id
And pln.Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And pln.Table_Alias = 'PLN';
Select Object_Version_Number
From Ben_Copy_Entity_Results
Where Copy_Entity_Result_Id = l_result_id;
l_proc varchar2(72) := g_package|| 'update_or_delete_grade';
ben_copy_entity_results_api.update_copy_entity_results(
p_copy_entity_result_id => p_grade_result_id,
p_effective_date => p_effective_date,
p_information104 => 'UNLINK',
p_object_version_number => l_ovn,
p_information323 => null);
ben_copy_entity_results_api.update_copy_entity_results(
p_copy_entity_result_id => l_pln_result_id,
p_effective_date => p_effective_date,
p_Gs_Mr_Src_Entity_Result_Id => NULL,
p_object_version_number => l_ovn,
p_information323 => null);
ben_copy_entity_results_api.delete_copy_entity_results(
p_copy_entity_result_id => p_grade_result_id,
p_effective_date => p_effective_date,
p_object_version_number => l_ovn);
End update_or_delete_grade;
Procedure update_or_delete_step
( p_copy_entity_txn_id in Number,
p_step_result_id in Number,
p_step_id in Number,
p_point_result_id in Number,
p_effective_date in Date) IS
/* Author : mvankada
Purpose : This procedure Update/Deletes Step Record
If the Record is in the Staging Area only
1) If Use_Prg_Points = 'STEP' then delete
i) Point Record
ii) Step Record
2) If Use_Prg_Points = 'POINT' then delete
i) Step Record
If the Record is in the Main Tables
1) If Use_Prg_Points = 'STEP' then Update the following recors information104 as UNLINK
i) Point Record
ii) Step Record
2) If Use_Prg_Points = 'POINT' then Update the following recors information104 as UNLINK
i) Step Record
*/
-- To Get information1 (Pgm_ID) of PGM, based on TXN_ID
Cursor csr_pgm_id
IS
Select grdldr.information1 -- PGM_ID
From Ben_Copy_Entity_Results grdldr
Where grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grdldr.Table_Alias = 'PGM';
Select grd.information253
From Ben_Copy_Entity_Results grd
Where grd.Table_Alias = 'CPP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grd.copy_entity_result_id = (Select step.gs_parent_entity_result_id
From Ben_Copy_Entity_Results step
Where step.Copy_Entity_Result_Id= p_step_result_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND step.Table_Alias = 'COP');
Select Object_Version_Number
From Ben_Copy_Entity_Results
Where Copy_Entity_Result_Id = l_result_id;
l_proc varchar2(72) := g_package|| 'update_or_delete_step';
ben_copy_entity_results_api.update_copy_entity_results(
p_copy_entity_result_id => p_point_result_id,
p_effective_date => p_effective_date,
p_information104 => 'UNLINK',
p_object_version_number => l_ovn,
p_information323 => null);
hr_utility.set_location('Point Rec is Updated Sucessfully',90);
ben_copy_entity_results_api.update_copy_entity_results(
p_copy_entity_result_id => p_step_result_id,
p_effective_date => p_effective_date,
p_information104 => 'UNLINK',
p_object_version_number => l_ovn,
p_information323 => null);
hr_utility.set_location('Step Rec is Updated Sucessfully',100);
ben_copy_entity_results_api.delete_copy_entity_results(
p_copy_entity_result_id => p_point_result_id,
p_effective_date => p_effective_date,
p_object_version_number => l_ovn);
hr_utility.set_location('Point Rec is Deleted Sucessfully',120);
ben_copy_entity_results_api.delete_copy_entity_results(
p_copy_entity_result_id => p_step_result_id,
p_effective_date => p_effective_date,
p_object_version_number => l_ovn);
hr_utility.set_location('Step Rec is Deleted Sucessfully',130);
End update_or_delete_step;
select oipl.copy_entity_result_id,
oipl.object_version_number
from ben_copy_entity_results oipl,
ben_copy_entity_results opt
where nvl(oipl.information104,'PPP') <> 'UNLINK'
AND nvl(oipl.result_type_cd,'DISPLAY') = 'DISPLAY'
AND nvl(opt.result_type_cd,'DISPLAY') = 'DISPLAY'
and oipl.table_alias = 'COP'
and opt.table_alias = 'OPT'
and oipl.copy_entity_txn_id = p_copy_entity_txn_id
and oipl.information262 = opt.copy_entity_result_id
and oipl.gs_parent_entity_result_id = p_grd_result_id
order by opt.information253;
ben_copy_entity_results_api.update_copy_entity_results(
p_copy_entity_result_id => i.copy_entity_result_id,
p_effective_date => p_effective_start_date,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_information5 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
p_information100 => fnd_message.get_string('PQH','PQH_GSP_STEP_PROMPT')||stepnum,
p_object_version_number => i.object_version_number,
p_information323 => null);
Select Information253, -- Grade Id
Information5 -- Grade Name
From Ben_Copy_Entity_Results
Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
And Table_Alias = 'CPP'
And Information253 IS NOT NULL
AND Nvl(Information104,'PPP') = 'UNLINK'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And Information4 = p_Business_Group_Id;
Select Null
From per_all_assignments_f assgt
Where grade_id = l_grade_id
AND Grade_Ladder_Pgm_Id = p_pgm_id
AND p_Effective_Date BETWEEN assgt.effective_start_date
AND nvl(assgt.effective_end_date,hr_general.end_of_time)
AND assgt.business_group_id = p_Business_Group_Id;
Select Null
From per_all_assignments_f assgt
Where grade_id = l_grade_id
AND p_Effective_Date BETWEEN assgt.effective_start_date
AND nvl(assgt.effective_end_date,hr_general.end_of_time)
AND assgt.business_group_id = p_Business_Group_Id;
Select Null
From ben_pgm_f
Where pgm_id = p_pgm_id
And pgm_typ_cd = 'GSP'
And Dflt_Pgm_Flag = 'Y'
And business_group_id = p_business_group_id
And p_Effective_Date Between Effective_Start_date
And nvl(Effective_End_Date,hr_general.end_of_time);
Select Information253, -- Step Id
Information5 -- Step Name
INFORMATION98 -- Ceiling Flag
From Ben_Copy_Entity_Results
Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
And Table_Alias = 'COP'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And Information253 IS NOT NULL
AND Nvl(Information104,'PPP') = 'UNLINK'
And Information4 = p_business_group_id ;
Select null
From Ben_Pgm_F
Where Pgm_Id <> nvl(p_pgm_id,-1)
And Name = p_name
And Business_Group_Id = p_business_group_id;
Select null
From Ben_Pgm_F
Where Pgm_Id <> nvl(p_pgm_id,-1)
And Short_Name = nvl(p_short_name,-1)
And Business_Group_Id = p_business_group_id
And p_short_name IS NOT NULL;
Select null
From Ben_Pgm_F
Where Pgm_Id <> nvl(p_pgm_id,-1)
And Short_Code = nvl(p_short_code,-1)
And Business_Group_Id = p_business_group_id
And p_short_code IS NOT NULL;
,p_Update_salary_cd in Varchar2
,p_Use_multi_pay_rates_flag in Varchar2
,p_dflt_element_type_id in number
,p_Dflt_input_value_id in number
,p_Use_scores_cd in Varchar2
,p_Scores_calc_mthd_cd in Varchar2
,p_Scores_calc_rl in number
,p_gsp_allow_override_flag in varchar2
,p_use_variable_rates_flag in varchar2
,p_salary_calc_mthd_cd in varchar2
,p_salary_calc_mthd_rl in number
,p_effective_date in date
,p_short_name in varchar2
,p_short_code in varchar2
) is
l_proc varchar2(72) := g_package||'validate_grade_ladder';
select information1
from Ben_Copy_Entity_Results
where Copy_Entity_Txn_Id = p_copy_entity_txn_id
and Information4 = p_business_group_id
and table_alias = 'PGM' ;
Select Copy_Entity_Result_Id,
Information50, -- Currency_Code
Information41 -- Rate_Period
From Ben_Copy_Entity_Results
Where Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND Table_Alias = 'PGM'
AND Result_Type_Cd = 'DISPLAY'
AND Nvl(Information104,'PPP') <> 'UNLINK'
AND Information4 = p_business_group_id ;
Select Information253, -- Grade Id
Information5 -- Grade Name
From Ben_Copy_Entity_Results
Where Gs_Parent_Entity_Result_Id = l_grdldr_result_id
And Copy_Entity_Txn_Id = p_copy_entity_txn_id
And Table_Alias = 'CPP'
AND Nvl(Information104,'PPP') <> 'UNLINK'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND Information253 IS NOT NULL
AND Information4 = p_business_group_id ;
Select Pgm.ACTY_REF_PERD_CD, -- Rate
Pgm.Pgm_uom -- Currency
From Ben_Pl_f Pl,
Ben_Plip_F Plip,
Ben_Pgm_F Pgm
Where Pl.MAPPING_TABLE_PK_ID = l_grade_Id
and Pl.MAPPING_TABLE_NAME = 'PER_GRADES'
and P_Effective_Date
Between Pl.Effective_Start_Date
and Nvl(Pl.Effective_End_Date,hr_general.end_of_time)
and Plip.Pl_Id = Pl.Pl_Id
and P_Effective_Date
Between Plip.Effective_Start_Date
and Nvl(Plip.Effective_End_Date,hr_general.end_of_time)
and Plip.Pgm_Id = Pgm.Pgm_Id
and P_Effective_Date
Between Pgm.Effective_Start_Date
and Nvl(Pgm.Effective_End_Date,hr_general.end_of_time)
and pgm.Business_group_id = p_Business_Group_Id
and (pgm.ACTY_REF_PERD_CD <> l_rate
or pgm.Pgm_Uom <> l_currency)
and Pgm_Typ_Cd = 'GSP'
and l_pgm_id <> pgm.pgm_id; -- added for bug 7114098
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
and p_effective_date between information2 and nvl(information3, to_date('31/12/4712','dd/mm/RRRR'))
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and INFORMATION161 <> nvl(p_cset_id, -1)
order by information161,information2;
select *
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CRSET'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and dml_operation in ('INSERT','UPDATE') -- Check only insert/ updates crset
order by information161,information2;
Select Name,
Short_Name,
Short_Code
From Ben_Pgm_F
Where Pgm_id Is Not Null
And Pgm_id = p_pgm_id
And Business_Group_Id = p_business_group_id;
Select Information2 EFFECTIVE_START_DATE
From Ben_Copy_Entity_Results
Where Copy_Entity_Txn_id = p_copy_entity_txn_id
And Information4 = p_business_group_id
And Table_Alias = 'PGM'
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
And Information_Category = 'GRADE_LADDER';
select 1 from dual where exists (
select 1
FROM BEN_COPY_ENTITY_RESULTS
WHERE NVL(INFORMATION104,'PPP') NOT IN ('UNLINK')
AND TABLE_ALIAS ='COP'
AND copy_entity_txn_id = p_copy_entity_txn_id
AND result_type_cd = 'DISPLAY'
AND gs_parent_entity_result_id is not null);
select 1 from dual where exists (
select 1 from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and gs_parent_entity_result_id = p_copy_entity_result_id);
select null
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and information262 = p_points_result_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information104 = 'LINK';
select null
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id <> p_copy_entity_result_id
and information98 = p_name
and table_alias = 'SCALE'
union
select null
from per_parent_spines
where name = p_name
and business_group_id = p_business_group_id
and parent_spine_id <> p_parent_spine_id;
select information98,copy_entity_result_id,dml_operation
from ben_copy_entity_results
where copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'CPP'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information258 = p_copy_entity_result_id;
update ben_copy_entity_results
set information98 = p_name,
dml_operation = get_dml_operation(i.dml_operation)
where copy_entity_result_id = i.copy_entity_result_id;
select action_date into l_effective_date from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
Select Pgm.ACTY_REF_PERD_CD,
Nvl(Cur.Precision,2),
Update_Salary_Cd
From Ben_Pgm_f Pgm,
Fnd_Currencies Cur
Where Pgm.Pgm_id = P_Pgm_Id
and P_Effective_Date
Between Pgm.Effective_Start_Date
and Pgm.Effective_End_Date
and Cur.Currency_Code(+) = Pgm.Pgm_Uom;
select Ppb.Pay_Annualization_Factor,Ppb.Pay_basis
From Per_All_Assignments_f asg,
Per_Pay_Bases ppb
where Asg.Assignment_id = P_Assignment_Id
and P_effective_date
between Asg.Effective_Start_Date
and Asg.Effective_End_Date
and Ppb.Pay_Basis_Id = Asg.Pay_Basis_Id;
l_update_Salary_cd Ben_Pgm_F.Update_Salary_Cd%TYPE;
FETCH C_Pgm_rt_Perd into l_ref_perd_cd,l_precision, l_Update_Salary_cd;
If L_Update_Salary_Cd = 'SALARY_BASIS' Then
OPEN c_pay_basis;
Elsif L_Update_Salary_Cd = 'SALARY_ELEMENT' Then
per_pay_proposals_populate.get_payroll(P_Assignment_Id
,P_Effective_Date
,l_Payroll_name
,l_factor);
select count(*) cnt
from ben_copy_entity_results
where table_alias = 'COP'
and copy_entity_txn_id = p_copy_entity_txn_id
and gs_parent_entity_result_id in
(select gs_mirror_src_entity_result_id
from ben_copy_entity_results
where table_alias = 'PLN' and copy_entity_txn_id = p_copy_entity_txn_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY' and copy_entity_result_id = p_grade_cer_id)
And result_type_cd = 'DISPLAY'
And Nvl(Information104,'PPP') <> 'UNLINK';
Select information297
From ben_copy_entity_results
Where copy_entity_txn_id = p_copy_entity_txn_id
and INFORMATION278 = p_point_cer_id
and p_effective_date between information2 and information3
and table_alias = 'HRRATE'
and result_type_cd = 'DISPLAY';
Select Full_name
From Per_All_people_F
Where Person_id = P_Person_id
and Nvl(P_Effective_Date, Sysdate)
Between Effective_Start_Date
and Effective_End_Date;
Select User_Status
From PER_ASSIGNMENT_STATUS_TYPES_TL
where Assignment_Status_Type_Id = P_Assgt_Status_Id
and language = userenv('LANG');
Select 'x'
From per_pay_bases
where business_group_id = p_business_group_id
and rate_id is null
and input_value_id = p_input_value_id
and pay_basis_id <> p_basis_id;
procedure update_oipl_records(
p_effective_date IN DATE,
p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
p_point_name IN ben_copy_entity_results.information99%TYPE,
p_sequence IN ben_copy_entity_results.information263%TYPE,
p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE
)
IS
cursor csr_oipl_records
is
select copy_entity_result_id,object_version_number,dml_operation
from ben_copy_entity_results
where table_alias = 'COP'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information262 = p_copy_entity_result_id
and copy_entity_txn_id = p_copy_entity_txn_id;
ben_copy_entity_results_api.update_copy_entity_results(
p_effective_date => p_effective_date,
p_copy_entity_result_id => r_oipl_record.copy_entity_result_id,
p_information99 => p_point_name,
p_information263 => p_sequence,
p_information323 => null,
p_dml_operation =>get_dml_operation(r_oipl_record.dml_operation),
p_object_version_number => l_ovn
);
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from per_grade_definitions
where grade_definition_id = p_grade_definition_id;
select grade_structure into l_flex_num
from per_business_groups_perf
where business_group_id =p_business_group_id;
p_out_dml_operation := 'UPDATE';
select 'x'
from ben_pgm_f pgm
where pgm.business_group_id = p_asg_bg_id
and pgm.pgm_typ_cd = 'GSP'
and p_effective_date
between pgm.effective_start_date
and nvl(pgm.effective_end_date,to_date('31/12/4712','dd/mm/RRRR'));
SELECT information253
FROM ben_copy_entity_results
WHERE copy_entity_result_id = p_grade_cer_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY';
SELECT grade_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;
UPDATE ben_copy_entity_results
SET information104 = 'UNLINK'
WHERE copy_entity_result_id = p_copy_entity_result_id;
PROCEDURE chk_delete_option (
p_copy_entity_txn_id IN NUMBER,
p_opt_cer_id IN NUMBER,
p_point_id IN NUMBER,
p_opt_id IN NUMBER,
p_pspine_id IN NUMBER,
p_effective_date IN DATE
)
IS
CURSOR csr_steps_for_point
IS
SELECT NULL
FROM ben_copy_entity_results
WHERE copy_entity_txn_id = p_copy_entity_txn_id
AND information262 = p_opt_cer_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND information104 = 'LINK';
END chk_delete_option;
SELECT object_version_number, dml_operation
FROM ben_copy_entity_results
WHERE copy_entity_result_id = p_initial_ceiling_id;
SELECT object_version_number, dml_operation, information253,
information255, information263
FROM ben_copy_entity_results
WHERE copy_entity_result_id = p_final_ceiling_id;
SELECT object_version_number, dml_operation
FROM ben_copy_entity_results
WHERE copy_entity_result_id = p_grade_result_id;
ben_copy_entity_results_api.update_copy_entity_results (
p_copy_entity_result_id => p_final_ceiling_id,
p_effective_date => p_effective_date,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_information98 => 'Y',
p_object_version_number => l_final_step.object_version_number,
p_information323 => NULL,
p_dml_operation => get_dml_operation (
l_final_step.dml_operation
)
);
ben_copy_entity_results_api.update_copy_entity_results (
p_copy_entity_result_id => p_initial_ceiling_id,
p_effective_date => p_effective_date,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_information98 => 'N',
p_object_version_number => l_initial_step.object_version_number,
p_dml_operation => get_dml_operation (
l_initial_step.dml_operation
),
p_information323 => NULL
);
ben_copy_entity_results_api.update_copy_entity_results (
p_copy_entity_result_id => p_grade_result_id,
p_effective_date => p_effective_date,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_information103 => 'Y',
p_information262 => l_final_step_cer_id,
p_information259 => l_final_step_id,
p_object_version_number => l_grade.object_version_number,
p_information323 => NULL,
p_dml_operation => get_dml_operation (
l_grade.dml_operation
)
);
SELECT NULL
FROM ben_copy_entity_results
WHERE table_alias = 'COP'
AND copy_entity_txn_id = p_copy_entity_txn_id
AND gs_parent_entity_result_id = p_grade_result_id
AND result_type_cd = 'DISPLAY'
AND NVL (information104, 'PPP') <> 'UNLINK';
SELECT information253, information255
FROM ben_copy_entity_results
WHERE copy_entity_result_id = p_grade_result_id;
select 'x'
from per_spinal_point_steps_f sps,
per_grade_spines_f gs
where gs.grade_spine_id = sps.grade_spine_id
and gs.parent_spine_id = p_pspine_id
and gs.grade_id = p_grd_id
and exists
(select null
from per_spinal_point_placements_f sp
where sp.step_id = sps.step_id
and (p_effective_date between effective_start_date AND effective_end_date-1
or effective_start_date >= p_effective_date ));
select action_date
into l_effdate
from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;
select copy_entity_txn_id, information297 from ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information277 in (
select copy_entity_result_id
from ben_copy_entity_results
where table_alias ='PLN'
and result_type_cd = 'DISPLAY'
and gs_mirror_src_entity_result_id = p_copy_entity_result_id)
and table_alias = 'HRRATE' and l_action_dt
between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
Select Action_Date
From Pqh_Copy_Entity_txns
Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
select information262 from ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and
copy_entity_result_id = p_copy_entity_result_id;
select copy_entity_Txn_id, information297 from ben_copy_entity_results
where
copy_entity_txn_id = p_copy_entity_txn_id
and information278 = point_cer_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and table_alias = 'HRRATE' and l_action_dt
between nvl(information2, hr_general.start_of_time) and nvl(information3, hr_general.end_of_time);
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_salary_rate in number,
p_gross_index in number,
p_effective_date in date
)
IS
l_salary_rate NUMBER;
pqh_gsp_hr_to_stage.update_frps_point_rate(p_point_cer_id => p_point_cer_id,
p_copy_entity_txn_id => p_copy_entity_txn_id,
p_business_group_id => p_business_group_id,
p_point_value => l_salary_rate,
p_effective_date => p_effective_date);
END update_frps_point_rate;
select grade_id
from per_grade_spines_f
where parent_spine_id = p_parent_spine_id;
select pl_id
from ben_pl_f
where mapping_table_pk_id = p_grade_id
and mapping_table_name = 'PER_GRADES';
select pgm_id
from ben_plip_f
where pl_id = p_pl_id;
select USE_PROG_POINTS_FLAG
from ben_pgm_f
where pgm_id = p_pgm_id;
select information18
from ben_copy_entity_results
where table_alias = 'PGM'
and copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd = 'DISPLAY';
select null
from ben_copy_entity_results
where information160 = p_crset_id
and table_alias = 'CRRATE'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and copy_entity_txn_id = p_copy_entity_txn_id
and information169 in
(select copy_entity_result_id
from ben_copy_entity_results
where table_alias = 'OPT'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and information256 = p_scale_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id);
select information18
from ben_copy_entity_results
where table_alias = 'PGM'
and copy_entity_txn_id = p_copy_entity_txn_id
and result_type_cd = 'DISPLAY';
select Gs_Mirror_Src_Entity_Result_Id,object_version_number
from ben_copy_entity_results
where copy_entity_result_id = p_pl_cer_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and copy_entity_txn_id = p_copy_entity_txn_id
and table_alias = 'PLN';
select information258,copy_entity_result_id,dml_operation
from ben_copy_entity_results
where table_alias = 'CPP'
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and copy_entity_txn_id = p_copy_entity_txn_id
and copy_entity_result_id = p_plip_cer_id;
Select Information98,copy_entity_result_id,dml_operation
from ben_copy_entity_results
where copy_entity_result_id = p_scale_cer_id
and table_alias = 'SCALE'
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set information98 = p_short_name
, dml_operation = get_dml_operation(l_scale_dtls.dml_operation)
where copy_entity_result_id = l_scale_dtls.copy_entity_result_id;
update ben_copy_entity_results
set information98 = p_short_name
,dml_operation = get_dml_operation(l_plip_dtls.dml_operation)
where copy_entity_result_id = l_plip_dtls.copy_entity_result_id;
SELECT copy_entity_result_id,
dml_operation
FROM ben_copy_entity_results
WHERE table_alias = 'COP'
AND copy_entity_txn_id = p_copy_entity_txn_id
AND gs_parent_entity_result_id = p_grade_result_id
AND result_type_cd = 'DISPLAY'
AND NVL (information104, 'PPP') <> 'UNLINK';
Select Action_Date
From Pqh_Copy_Entity_txns
Where Copy_Entity_Txn_id = p_copy_entity_txn_id;
/* update ben_copy_entity_results
set copy_entity_result_id = rec_steps.copy_entity_result_id,
information104 = 'UNLINK',
dml_operation = get_dml_operation(rec_steps.dml_operation)
where copy_entity_result_id = rec_steps.copy_entity_result_id; */
select information307
from ben_copy_entity_results
where copy_entity_result_id = p_pl_cer_id
and copy_entity_txn_id = p_copy_entity_txn_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and table_alias = 'PLN';
select information2,information3,copy_entity_result_id
from ben_copy_entity_results
where information277 =p_pl_cer_id
and nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
and table_alias in ('HRRATE','ABR')
and information2 = p_plan_old_date
and copy_entity_txn_id = p_copy_entity_txn_id;
update ben_copy_entity_results
set information2 = p_start_date
where copy_entity_result_id = i.copy_entity_result_id;
hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);
Select nvl(grdldr.INFORMATION50,'USD') /* INFORMATION50 -> Currency Code */
FROM Ben_Copy_Entity_Results grdldr
WHERE grdldr.Copy_Entity_Txn_Id = p_copy_entity_txn_id
AND nvl(result_type_cd,'DISPLAY') = 'DISPLAY'
AND grdldr.Table_Alias = 'PGM';
select information307
into l_grd_start_date
from ben_copy_entity_results
where copy_entity_result_id = p_copy_entity_result_id;
select information103
from ben_copy_entity_results
where copy_entity_result_id =p_grade_result_id;
update ben_copy_entity_results
set information228 = p_final_start_step
,information103 = l_indicator
where copy_entity_result_id = p_grade_result_id;
SELECT ff.formula_name
FROM ff_formulas_f ff
WHERE ff.formula_id = p_formula_id
AND p_effective_date BETWEEN ff.effective_start_date
AND ff.effective_end_date;
SELECT ettl.element_name
FROM pay_element_types_f_tl ettl
WHERE ettl.LANGUAGE = USERENV ('LANG')
AND ettl.element_type_id = p_element_type_id;
SELECT ptl.NAME
FROM pay_input_values_f_tl ptl
WHERE ptl.LANGUAGE = USERENV ('LANG')
AND ptl.input_value_id = p_input_value_id;
SELECT NULL
FROM ben_pgm_f
WHERE NAME = p_name
AND business_group_id = p_business_group_id
AND pgm_id <> NVL (p_pgm_id, -1);
SELECT NAME
FROM ben_pgm_f
WHERE dflt_pgm_flag = 'Y'
AND business_group_id = p_business_group_id
AND pgm_id <> NVL (p_pgm_id, -1)
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT pgi_information5 rate_ann_factor
FROM ben_pgm_extra_info
WHERE pgm_id = p_pgm_id
AND information_type = 'PQH_GSP_EXTRA_INFO';
update ben_copy_entity_results
set information259 = p_step_id,
information103 = 'Y'
where copy_entity_result_id = p_grade_cer_id
and table_alias = 'CPP';