APPS.PQH_GSP_UTILITY SQL Statements

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

Line: 17

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

Line: 27

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

Line: 54

      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 ;

Line: 79

 select
  pg.name
  ,bpgm.name
 from
  ben_pgm_f bpgm,
  ben_pl_f bp,
  BEN_PL_TYP_F bpt,
  ben_plip_f bplip,
  per_grade_spines_f pgs,
  per_grades pg
 where
  bp.pl_typ_id = bpt.pl_typ_id
  and bpt.OPT_TYP_CD = 'GSP'
  and bp.mapping_table_name = 'PER_GRADES'
  and bp.pl_id = bplip.pl_id
  and bplip.pgm_id = bpgm.pgm_id
  and pgs.grade_id = bp.mapping_table_pk_id
  and pgs.parent_spine_id = p_parent_spine_id
  and pg.grade_id = pgs.grade_id;

Line: 135

 select
  pg.name
 ,bpgm.name
 from
  ben_pgm_f bpgm,
  ben_pl_f bp,
  BEN_PL_TYP_F bpt,
  ben_plip_f bplip,
  per_grade_spines_f pgs,
  per_grades pg,
  pay_rates pr,
  per_parent_spines pps
 where
  bp.pl_typ_id = bpt.pl_typ_id
  and bpt.OPT_TYP_CD = 'GSP'
  and bp.mapping_table_name = 'PER_GRADES'
  and bp.pl_id = bplip.pl_id
  and bplip.pgm_id = bpgm.pgm_id
  and pgs.grade_id = bp.mapping_table_pk_id
  and pr.rate_id = p_rate_id
  and pg.grade_id = pgs.grade_id
  and pr.parent_spine_id = pgs.parent_spine_id
  and pr.rate_type = 'SP'
  and pr.name = pps.name
  and pps.parent_spine_id = pgs.parent_spine_id;

Line: 210

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

Line: 280

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;

Line: 291

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;

Line: 303

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;

Line: 315

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;

Line: 327

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: 337

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

Line: 348

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;

Line: 509

	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;

Line: 565

	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;

Line: 574

	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;

Line: 583

	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;

Line: 592

	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;

Line: 601

	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;

Line: 610

	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;

Line: 619

	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;

Line: 657

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

Line: 736

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

Line: 746

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;

Line: 755

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

Line: 767

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;

Line: 885

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

Line: 940

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

Line: 1013

       If   (  p_action = 'UPDATE'  OR p_action = 'REMOVE' OR p_action = 'GRDQUOTA') Then
           return 'E';

Line: 1032

         Elsif (p_action = 'UPDATE') Then

	   -- If Steps Exists for the Grade then Enable Update Button
	   -- Else Disable It
	      Open Csr_steps_exists  ;

Line: 1073

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

Line: 1100

               elsif p_action = 'UPDATE' Then
          if g_debug then
                    hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,50);

Line: 1121

               elsif p_action = 'UPDATE'Then

	            if g_debug then
                       hr_utility.set_location('p_copy_entity_result_id '||p_copy_entity_result_id ,80);

Line: 1149

    hr_utility.set_location('Update is through' ,50);

Line: 1179

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

Line: 1191

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;

Line: 1207

select information228
from ben_copy_entity_results
where copy_entity_result_id = l_grd_cer_id;

Line: 1274

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;

Line: 1282

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;

Line: 1290

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;

Line: 1298

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;

Line: 1305

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

Line: 1318

Select Substr(Display_name,1,50)
From Pqh_table_Route
Where Table_Alias = p_Table_Alias;

Line: 1478

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;

Line: 1496

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;

Line: 1565

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

Line: 1587

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

Line: 1593

delete from pqh_copy_entity_txns where copy_entity_txn_id = p_pqh_copy_entity_txn_id;

Line: 1594

end delete_transaction;

Line: 1603

delete from pqh_copy_entity_attribs where copy_entity_txn_id = p_pqh_copy_entity_txn_id;

Line: 1604

delete from ben_copy_entity_results where copy_entity_txn_id = p_pqh_copy_entity_txn_id;

Line: 1615

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;

Line: 1618

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

Line: 1637

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

Line: 1648

delete_transaction(sfl_txn.copy_entity_txn_id);

Line: 1675

 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;

Line: 1686

 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;

Line: 1764

  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;

Line: 1780

  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;

Line: 1789

  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;

Line: 1852

Select Name
  from Per_Collective_Agreements
 Where Collective_Agreement_Id = P_CAGR_Id;

Line: 1918

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

Line: 1964

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

Line: 1975

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

Line: 2049

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

Line: 2058

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;

Line: 2068

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;

Line: 2079

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;

Line: 2187

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;

Line: 2252

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;

Line: 2261

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

Line: 2337

 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;

Line: 2361

Select Name
  from Fnd_Currencies_Vl
 Where Currency_Code = P_Corrency_Code
   and Enabled_Flag = 'Y';

Line: 2384

Select spinal_point
  from per_spinal_points
 Where Spinal_point_id = p_Point_id;

Line: 2405

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;

Line: 2425

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;

Line: 2434

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

Line: 2444

Select Object_Version_Number
From   Ben_Copy_Entity_Results
Where  Copy_Entity_Result_Id = l_result_id;

Line: 2453

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

Line: 2490

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

Line: 2513

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

Line: 2527

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

Line: 2538

End update_or_delete_grade;

Line: 2544

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

Line: 2582

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

Line: 2594

Select Object_Version_Number
From   Ben_Copy_Entity_Results
Where  Copy_Entity_Result_Id = l_result_id;

Line: 2606

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

Line: 2650

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

Line: 2659

         hr_utility.set_location('Point Rec is Updated Sucessfully',90);

Line: 2668

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

Line: 2677

         hr_utility.set_location('Step Rec is Updated Sucessfully',100);

Line: 2689

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

Line: 2695

       hr_utility.set_location('Point Rec is Deleted Sucessfully',120);

Line: 2704

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

Line: 2710

        hr_utility.set_location('Step Rec is Deleted Sucessfully',130);

Line: 2717

End update_or_delete_step;

Line: 2732

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;

Line: 2755

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

Line: 2792

 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;

Line: 2805

 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;

Line: 2817

 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: 2827

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

Line: 2940

 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 ;

Line: 2991

 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;

Line: 3040

 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;

Line: 3049

 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;

Line: 3123

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

Line: 3209

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

Line: 3220

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 ;

Line: 3234

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 ;

Line: 3247

 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

Line: 3365

   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;

Line: 3420

   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;

Line: 3559

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;

Line: 3569

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

Line: 3760

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

Line: 3801

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

Line: 3986

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

Line: 4013

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;

Line: 4028

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;

Line: 4042

        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;

Line: 4067

select action_date into l_effective_date from pqh_copy_entity_txns
where copy_entity_txn_id = p_copy_entity_txn_id;

Line: 4108

 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;

Line: 4120

 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;

Line: 4134

l_update_Salary_cd         Ben_Pgm_F.Update_Salary_Cd%TYPE;

Line: 4151

FETCH C_Pgm_rt_Perd into l_ref_perd_cd,l_precision, l_Update_Salary_cd;

Line: 4159

If L_Update_Salary_Cd = 'SALARY_BASIS' Then

   OPEN c_pay_basis;

Line: 4194

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

Line: 4250

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

Line: 4291

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

Line: 4333

 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;

Line: 4358

Select User_Status
  From PER_ASSIGNMENT_STATUS_TYPES_TL
 where Assignment_Status_Type_Id = P_Assgt_Status_Id
   and language = userenv('LANG');

Line: 4384

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;

Line: 4415

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;

Line: 4439

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

Line: 4525

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;

Line: 4567

select grade_structure into l_flex_num
from per_business_groups_perf
where business_group_id =p_business_group_id;

Line: 4623

  p_out_dml_operation := 'UPDATE';

Line: 4643

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

Line: 4714

         SELECT information253
           FROM ben_copy_entity_results
          WHERE copy_entity_result_id = p_grade_cer_id
          and nvl(result_type_cd,'DISPLAY') = 'DISPLAY';

Line: 4721

         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;

Line: 4766

      UPDATE ben_copy_entity_results
         SET information104 = 'UNLINK'
       WHERE copy_entity_result_id = p_copy_entity_result_id;

Line: 4771

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

Line: 4810

   END chk_delete_option;

Line: 4829

            SELECT object_version_number, dml_operation
              FROM ben_copy_entity_results
             WHERE copy_entity_result_id = p_initial_ceiling_id;

Line: 4835

            SELECT object_version_number, dml_operation, information253,
                   information255, information263
              FROM ben_copy_entity_results
             WHERE copy_entity_result_id = p_final_ceiling_id;

Line: 4842

            SELECT object_version_number, dml_operation
              FROM ben_copy_entity_results
             WHERE copy_entity_result_id = p_grade_result_id;

Line: 4885

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

Line: 4909

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

Line: 4926

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

Line: 4965

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

Line: 4975

         SELECT information253, information255
           FROM ben_copy_entity_results
          WHERE copy_entity_result_id = p_grade_result_id;

Line: 4983

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

Line: 5006

    select action_date
    into l_effdate
    from pqh_copy_entity_txns
    where copy_entity_txn_id =    p_copy_entity_txn_id;

Line: 5074

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

Line: 5088

    Select Action_Date
      From Pqh_Copy_Entity_txns
     Where Copy_Entity_Txn_id = p_copy_entity_txn_id;

Line: 5093

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;

Line: 5100

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

Line: 5175

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;

Line: 5193

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

Line: 5198

END update_frps_point_rate;

Line: 5209

select grade_id
from per_grade_spines_f
where parent_spine_id = p_parent_spine_id;

Line: 5214

select pl_id
from ben_pl_f
where mapping_table_pk_id = p_grade_id
and mapping_table_name  = 'PER_GRADES';

Line: 5220

select pgm_id
from ben_plip_f
where pl_id = p_pl_id;

Line: 5225

select USE_PROG_POINTS_FLAG
from ben_pgm_f
where pgm_id = p_pgm_id;

Line: 5274

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

Line: 5281

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

Line: 5321

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

Line: 5329

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

Line: 5338

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;

Line: 5347

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;

Line: 5392

            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;

Line: 5397

            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;

Line: 5413

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

Line: 5423

    Select Action_Date
      From Pqh_Copy_Entity_txns
     Where Copy_Entity_Txn_id = p_copy_entity_txn_id;

Line: 5436

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

Line: 5454

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

Line: 5463

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;

Line: 5509

update ben_copy_entity_results
set information2 = p_start_date
where copy_entity_result_id = i.copy_entity_result_id;

Line: 5513

hr_utility.set_location('Updated record with cer_id:'||i.copy_entity_result_id,60);

Line: 5534

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

Line: 5577

select information307
into l_grd_start_date
from ben_copy_entity_results
where copy_entity_result_id = p_copy_entity_result_id;

Line: 5593

select information103
from ben_copy_entity_results
where copy_entity_result_id =p_grade_result_id;

Line: 5608

update ben_copy_entity_results
set information228 = p_final_start_step
    ,information103 = l_indicator
    where copy_entity_result_id = p_grade_result_id;

Line: 5620

         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;

Line: 5642

         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;

Line: 5663

         SELECT ptl.NAME
           FROM pay_input_values_f_tl ptl
          WHERE ptl.LANGUAGE = USERENV ('LANG')
            AND ptl.input_value_id = p_input_value_id;

Line: 5695

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

Line: 5703

      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;

Line: 5760

 SELECT pgi_information5  rate_ann_factor
 FROM   ben_pgm_extra_info
 WHERE  pgm_id     = p_pgm_id
 AND    information_type = 'PQH_GSP_EXTRA_INFO';

Line: 5784

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

Line: 5797

 Select Steps.spinal_point_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;

Line: 5824

    select value into l_rate_value
    from pay_grade_rules_f
    where grade_or_spinal_point_id = l_Spinal_Point_Id
    and rate_type = 'SP'
    and P_Effective_Date
        between  Effective_Start_Date and Effective_End_Date;