The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select pl.Pl_Typ_Id
From Ben_PL_F pl
Where pl.Pl_Id = p_pl_id
And pl.Business_Group_id = p_Business_Group_Id
And p_effective_date BETWEEN Pl.effective_start_date
And nvl(Pl.effective_end_date,hr_general.end_of_time);
Select OPT_TYP_CD
From BEN_PL_TYP_F Type
Where Pl_Typ_Id = l_pl_Typ_Id
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 plip.Pl_Id,plip.Pgm_Id
From Ben_PLIP_F plip
Where plip.Plip_Id = p_plip_id
And plip.Business_Group_id = p_Business_Group_Id
And p_effective_date BETWEEN Plip.effective_start_date
And nvl(Plip.effective_end_date,hr_general.end_of_time);
SELECT MAPPING_TABLE_PK_ID -- GRADE_ID
FROM BEN_PLIP_F PLIP,
BEN_PL_F PL
WHERE PLIP.PLIP_ID = p_plip_id
And PLIP.PLIP_STAT_CD ='A'
AND PLIP.PL_ID =PL.PL_ID
AND PL.MAPPING_TABLE_NAME = 'PER_GRADES'
AND PL.BUSINESS_GROUP_ID = p_business_group_id
AND p_effective_date BETWEEN PLIP.EFFECTIVE_START_DATE
And nvl(PLIP.Effective_End_Date, hr_general.end_of_time)
AND p_effective_date BETWEEN PL.EFFECTIVE_START_DATE
And nvl(PL.Effective_End_Date, hr_general.end_of_time);
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_PL_TYP_OPT_TYP_F
Where OPT_ID = p_opt_id
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 PL_TYP_OPT_TYP_CD = 'GSP';
Select Opt_Id
From BEN_OIPL_F
Where OIPL_ID = p_oipl_id
And OIPL_STAT_CD = 'A'
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 Step.step_id
From per_spinal_points point,
per_parent_spines Scale,
per_grade_spines_f spine,
per_spinal_point_steps_f step
Where point.SPINAL_POINT_ID = p_spinal_point_Id
And point.Business_Group_Id = p_business_group_id
And Scale.PARENT_SPINE_ID = point.PARENT_SPINE_ID
And Scale.PARENT_SPINE_ID = spine.PARENT_SPINE_ID
And spine.GRADE_SPINE_ID = step.GRADE_SPINE_ID
And step.SPINAL_POINT_ID = point.SPINAL_POINT_ID
And spine.Grade_Id = p_Grade_Id
AND p_effective_date BETWEEN spine.EFFECTIVE_START_DATE
And nvl(spine.Effective_End_Date, hr_general.end_of_time)
AND p_effective_date BETWEEN step.EFFECTIVE_START_DATE
And nvl(step.Effective_End_Date, hr_general.end_of_time);
Select pl.MAPPING_TABLE_PK_ID, -- Grade_Id
opt.MAPPING_TABLE_PK_ID -- spinal_point_id
From BEN_OIPL_F oipl,
BEN_OPT_F opt,
BEN_PL_F pl
Where oipl.Oipl_Id = p_oipl_id
And oipl.OIPL_STAT_CD = 'A'
And oipl.Pl_id = pl.PL_ID
And oipl.Business_Group_Id= p_business_group_id
And oipl.opt_id = opt.opt_id
and opt.MAPPING_TABLE_NAME= 'PER_SPINAL_POINTS'
And pl.MAPPING_TABLE_NAME = 'PER_GRADES'
AND p_effective_date BETWEEN oipl.EFFECTIVE_START_DATE
And nvl(oipl.Effective_End_Date, hr_general.end_of_time)
AND p_effective_date BETWEEN PL.EFFECTIVE_START_DATE
And nvl(PL.Effective_End_Date, hr_general.end_of_time);
select Effective_Start_date, Effective_End_Date
from per_spinal_point_placements_f
where step_id = l_step_id
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 Effective_Start_Date, Effective_End_Date
from per_all_assignments_f
where special_ceiling_step_id = l_step_id
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 Ben_Pgm_F
Where Pgm_id = p_Pgm_Id
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 PGM_STAT_CD = 'A';
Select PGM_TYP_CD
From Ben_Pgm_F
Where Pgm_id = p_Pgm_Id
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_STAT_CD = 'A';
Select NULL
From Per_All_Assignments_F Assgt
Where 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 BEN_PGM_F PGM,
BEN_PLIP_F PLIP,
BEN_PL_F PL
WHERE PGM.PGM_ID = p_Pgm_Id
AND PGM.PGM_TYP_CD = 'GSP'
AND PGM.BUSINESS_GROUP_ID = p_Business_Group_Id
AND PGM.PGM_ID = PLIP.PGM_ID
AND PLIP.PL_ID = PL.PL_ID
AND PL.MAPPING_TABLE_NAME = 'PER_GRADES'
AND PL.MAPPING_TABLE_PK_ID IS NOT NULL
And PGM.PGM_STAT_CD = 'A'
AND PLIP.PLIP_STAT_CD = 'A'
AND PL.PL_STAT_CD ='A'
AND p_Effective_Date BETWEEN pgm.effective_start_date
AND nvl(pgm.effective_end_date,hr_general.end_of_time)
AND p_Effective_Date BETWEEN plip.effective_start_date
AND nvl(plip.effective_end_date,hr_general.end_of_time)
AND p_Effective_Date BETWEEN pl.effective_start_date
AND nvl(pl.effective_end_date,hr_general.end_of_time);
Select Null
From Ben_PL_F pl,
Ben_Plip_F plip,
Ben_Pgm_F Pgm
Where pl.pl_id = p_pl_id
And pl.MAPPING_TABLE_NAME = 'PER_GRADES'
And pl.MAPPING_TABLE_PK_ID IS NOT NULL
And pl.pl_id = plip.pl_id
And plip.pgm_id = pgm.pgm_id
And pgm.Business_Group_Id = p_Business_Group_Id
And pgm.Pgm_Typ_Cd = 'GSP'
And pl.PL_STAT_CD = 'A'
And plip.PLIP_STAT_CD = 'A'
And pgm.PGM_STAT_CD = 'A'
And p_effective_date BETWEEN Pl.effective_start_date
and nvl(Pl.effective_end_date,hr_general.end_of_time)
And p_effective_date BETWEEN Plip.effective_start_date
and nvl(Plip.effective_end_date,hr_general.end_of_time)
And p_effective_date BETWEEN Pgm.effective_start_date
and nvl(Pgm.effective_end_date,hr_general.end_of_time);
Select Null
FROM BEN_OPT_F opt,
BEN_OIPL_F oipl,
BEN_PL_F pl
WHERE opt.opt_id = p_opt_id
AND opt.Business_group_id = p_Business_Group_Id
AND opt.opt_id = oipl.opt_id
AND oipl.pl_id = pl.pl_id
AND pl.MAPPING_TABLE_NAME = 'PER_GRADES'
AND pl.MAPPING_TABLE_PK_ID IS NOT NULL
And oipl.OIPL_STAT_CD = 'A'
AND pl.PL_STAT_CD = 'A'
AND p_effective_date BETWEEN opt.effective_start_date
AND nvl(opt.effective_end_date,hr_general.end_of_time)
AND p_effective_date BETWEEN oipl.effective_start_date
AND nvl(oipl.effective_end_date,hr_general.end_of_time)
AND p_effective_date BETWEEN pl.effective_start_date
AND nvl(pl.effective_end_date,hr_general.end_of_time);
Select oipl.PL_ID
FROM BEN_OPT_F opt,
BEN_OIPL_F oipl
WHERE opt.opt_id = p_opt_id
AND opt.Business_group_id = p_Business_Group_Id
AND opt.opt_id = oipl.opt_id
And oipl.OIPL_STAT_CD = 'A'
AND p_effective_date BETWEEN opt.effective_start_date
AND nvl(opt.effective_end_date,hr_general.end_of_time)
AND p_effective_date BETWEEN oipl.effective_start_date
AND nvl(oipl.effective_end_date,hr_general.end_of_time);
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 PGM_TYP_CD ='GSP' ;
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 PGM_TYP_CD ='GSP' ;
Select Null
From ben_pgm_f
Where pgm_id <> nvl(p_pgm_id,-1)
And PGM_TYP_CD ='GSP'
And Dflt_Pgm_Flag = 'Y'
And business_group_id = p_business_group_id
And effective_end_date>= p_effective_date;
1) Cannot Delete a Grade Ladder if Employee Placements exists on this Grade Ladder.
*/
l_proc varchar2(72) := g_package||'pgm_del_val';
hr_utility.set_location(' PGM Insert Validations ', 90);
hr_utility.set_location(' PGM Update Validations ', 95);
hr_utility.set_location(' PGM Delete Validations ', 100);
*** 2. Cannot Update Plan Status from Activate to Inactive/Closed/Pending
2) User Cannot change Grade which is mapped to Plan if
Plan is mapped to Grade and
Plan is attached to Grade Ladder through PLIP and
PLIP has Employee placements
*/
l_proc Varchar2(72) := g_package||'pl_upd_val';
Select OPT_TYP_CD
From BEN_PL_TYP_F Type
Where Pl_Typ_Id = l_plan_Typ_Id
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 PLIP_ID
From BEN_PLIP_F
Where Pl_Id = p_pl_Id
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 PLIP_STAT_CD = 'A';
-- Cannot Update Status
--
if g_debug then
hr_utility.set_location('Old Plan Status : '||ben_pln_shd.g_old_rec.pl_stat_cd,165);
hr_utility.set_location('Plan Insert Validations',40);
hr_utility.set_location('Plan Update Validations',45);
hr_utility.set_location('Plan Delete Validations',50);
1) PLIP Record cannot be deleted if employee placemnents exists on this PLIP
*/
l_proc Varchar2(72) := g_package||'plip_del_val';
hr_utility.set_location('PLIP Insert Validations',45);
hr_utility.set_location('PLIP Update Validations',55);
hr_utility.set_location('PLIP Delete Validations',65);
1. Cannot Update Option Status from Activate to Inactive/Closed/Pending
2. Cannot Update Option Type.
2) User Cannot change Step which is mapped to Option if
Option is mapped to Step and
Option is attached to Grade Ladder through OIPL and
OIPL has Employee placements
*/
l_proc Varchar2(72) := g_package||'opt_upd_val';
Select oipl_id
From BEN_OIPL_F
Where opt_id = p_opt_id
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 OIPL_STAT_CD = 'A';
hr_utility.set_location('Perform OPT Insert Validations',45);
hr_utility.set_location('Perform OPT Update Validations',50);
hr_utility.set_location('Perform OPT Delete Validations',55);
1) OIPL cannot be deleted if the OIPL is mapped to Step and the Steps have employee assignments.
*/
l_proc Varchar2(72) := g_package||'oipl_del_val';
hr_utility.set_location('OIPL Insert Validations',55);
hr_utility.set_location('OIPL Update Validations',65);
hr_utility.set_location('OIPL Delete Validations',75);
1. Cannot Update Standard Rate Status
2. Cannot Update Activity Type
*/
l_proc Varchar2(72) := g_package||'abr_upd_val';
Select PLIP_ID
From BEN_PLIP_F
Where PL_ID = p_pl_id
AND PLIP_STAT_CD = 'A'
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 OIPL_ID
From BEN_OIPL_F
Where OPT_ID = p_opt_id
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 ACTY_TYP_CD
From BEN_ACTY_BASE_RT_F
Where ACTY_BASE_RT_ID = p_abr_id
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);
hr_utility.set_location('Perform ABR Insert Validations',60);
hr_utility.set_location('Perform ABR Update Validations',70);
hr_utility.set_location('Perform ABR Delete Validations',80);