APPS.PQH_GSP_BEN_VALIDATIONS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 25

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);

Line: 34

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);

Line: 89

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);

Line: 167

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);

Line: 182

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;

Line: 260

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';

Line: 313

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);

Line: 373

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);

Line: 440

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);

Line: 458

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);

Line: 466

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);

Line: 575

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';

Line: 631

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';

Line: 641

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;

Line: 704

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);

Line: 765

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);

Line: 834

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);

Line: 911

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);

Line: 1006

 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' ;

Line: 1016

 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' ;

Line: 1025

 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;

Line: 1246

1) Cannot Delete a Grade Ladder if Employee Placements exists on this Grade Ladder.
*/

l_proc       varchar2(72) := g_package||'pgm_del_val';

Line: 1342

            hr_utility.set_location(' PGM Insert Validations ', 90);

Line: 1357

           hr_utility.set_location(' PGM Update Validations ', 95);

Line: 1379

           hr_utility.set_location(' PGM Delete Validations ', 100);

Line: 1430

  *** 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';

Line: 1452

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);

Line: 1462

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';

Line: 1527

	-- Cannot Update Status
	--

	if g_debug then
	   hr_utility.set_location('Old Plan Status  : '||ben_pln_shd.g_old_rec.pl_stat_cd,165);

Line: 1640

       hr_utility.set_location('Plan Insert Validations',40);

Line: 1648

      hr_utility.set_location('Plan Update Validations',45);

Line: 1660

       hr_utility.set_location('Plan Delete Validations',50);

Line: 1763

1) PLIP Record cannot be deleted if employee placemnents exists on this PLIP
*/
l_proc          Varchar2(72) := g_package||'plip_del_val';

Line: 1833

            hr_utility.set_location('PLIP Insert Validations',45);

Line: 1843

              hr_utility.set_location('PLIP Update Validations',55);

Line: 1854

            hr_utility.set_location('PLIP Delete Validations',65);

Line: 1906

      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';

Line: 1922

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';

Line: 2052

      hr_utility.set_location('Perform OPT Insert Validations',45);

Line: 2063

      hr_utility.set_location('Perform OPT Update Validations',50);

Line: 2075

      hr_utility.set_location('Perform OPT Delete Validations',55);

Line: 2187

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';

Line: 2258

       hr_utility.set_location('OIPL Insert Validations',55);

Line: 2266

       hr_utility.set_location('OIPL Update Validations',65);

Line: 2276

      hr_utility.set_location('OIPL Delete Validations',75);

Line: 2333

    1.	Cannot Update Standard Rate Status
    2.	Cannot Update Activity Type

*/
l_proc        Varchar2(72) := g_package||'abr_upd_val';

Line: 2356

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);

Line: 2367

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);

Line: 2538

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);

Line: 2576

          hr_utility.set_location('Perform ABR Insert Validations',60);

Line: 2586

          hr_utility.set_location('Perform ABR Update Validations',70);

Line: 2603

       hr_utility.set_location('Perform ABR Delete Validations',80);