APPS.PER_GRADE_SPINES_PKG SQL Statements

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

Line: 4

PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_Grade_Spine_Id               IN OUT NOCOPY NUMBER,
                     X_Effective_Start_Date                DATE,
                     X_Effective_End_Date                  DATE,
                     X_Business_Group_Id                   NUMBER,
                     X_Parent_Spine_Id                     NUMBER,
                     X_Grade_Id                            NUMBER,
                     X_Ceiling_Step_Id              IN OUT NOCOPY NUMBER
 ) IS
   CURSOR C IS SELECT rowid FROM per_grade_spines_f
             WHERE grade_spine_id = X_Grade_Spine_Id;

Line: 16

    CURSOR C2 IS SELECT per_grade_spines_s.nextval
                   FROM sys.dual;

Line: 19

    CURSOR C3 is select per_spinal_point_steps_s.nextval
                 from sys.dual;

Line: 32

  INSERT INTO per_grade_spines(
          grade_spine_id,
          effective_start_date,
          effective_end_date,
          business_group_id,
          parent_spine_id,
          grade_id,
          ceiling_step_id
         ) VALUES (
          X_Grade_Spine_Id,
          X_Effective_Start_Date,
          X_Effective_End_Date,
          X_Business_Group_Id,
          X_Parent_Spine_Id,
          X_Grade_Id,
          X_Ceiling_Step_Id

  );

Line: 58

END Insert_Row;

Line: 73

                   X_program_update_date                   DATE
) IS
  CURSOR C IS
SELECT *
      FROM   per_grade_spines_f
      WHERE  rowid = chartorowid(X_Rowid)
      FOR UPDATE of Grade_Spine_Id NOWAIT;

Line: 125

      AND (   (Recinfo.program_update_date = X_program_update_date)
           OR (    (Recinfo.program_update_date IS NULL)
               AND (X_program_update_date IS NULL)))
          ) then
    return;

Line: 138

PROCEDURE Update_Row(X_Rowid                               VARCHAR2,
                     X_Grade_Spine_Id                      NUMBER,
                     X_Effective_Start_Date                DATE,
                     X_Effective_End_Date                  DATE,
                     X_Business_Group_Id                   NUMBER,
                     X_Parent_Spine_Id                     NUMBER,
                     X_Grade_Id                            NUMBER,
                     X_Ceiling_Step_Id                     NUMBER
) IS
BEGIN
  UPDATE per_grade_spines_f
  SET
    grade_spine_id                            =    X_Grade_Spine_Id,
    effective_start_date                      =    X_Effective_Start_Date,
    effective_end_date                        =    X_Effective_End_Date,
    business_group_id                         =    X_Business_Group_Id,
    parent_spine_id                           =    X_Parent_Spine_Id,
    grade_id                                  =    X_Grade_Id,
    ceiling_step_id                           =    X_Ceiling_Step_Id
  WHERE rowid = chartorowid(X_rowid);

Line: 161

  hr_utility.set_message_token('PROCEDURE','update_row');

Line: 166

END Update_Row;

Line: 170

PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN

-- Start of fix for Bug 2694503.

  DECLARE
    l_grade_spine_id  per_grade_spines_f.grade_spine_id%Type;

Line: 178

	select grade_spine_id
        into  l_grade_spine_id
        from per_grade_spines_f
        where rowid = chartorowid(X_Rowid);

Line: 183

        Delete from per_spinal_point_steps_f
        where grade_spine_id = l_grade_spine_id;

Line: 194

  DELETE FROM per_grade_spines_f
  WHERE  rowid = chartorowid(X_Rowid);

Line: 199

   hr_utility.set_message_token('PROCEDURE','delete_row');

Line: 203

END Delete_Row;

Line: 213

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

Line: 225

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_assignments_f a
     where a.special_ceiling_step_id = sps.step_id
     and a.special_ceiling_step_id is not null);

Line: 274

select 'x'
from per_grade_spines_f
where grade_id = p_grd_id
and p_sess between effective_start_date and effective_end_date;

Line: 306

                     p_last_update_date IN DATE,
                     p_last_updated_by IN NUMBER,
                     p_last_update_login IN NUMBER,
                     p_created_by IN NUMBER,
                     p_creation_date IN DATE,
                     p_information_category in VARCHAR2) IS

begin

insert into per_spinal_point_steps_f(
       step_id,
       grade_spine_id,
       spinal_point_id,
       sequence,
       effective_start_date,
       effective_end_date,
       business_group_id,
       last_update_date,
       last_updated_by,
       last_update_login,
       created_by,
       creation_date,
       information_category)
values (
       p_step_id,
       p_grade_spine_id,
       p_spinal_point_id,
       p_sequence,
       p_effective_start_date,
       p_effective_end_date,
       p_business_group_id,
       p_last_update_date,
       p_last_updated_by,
       p_last_update_login,
       p_created_by,
       p_creation_date,
       p_information_category);

Line: 359

                     p_last_update_date IN DATE,
                     p_last_updated_by IN NUMBER,
                     p_last_update_login IN NUMBER,
                     p_created_by IN NUMBER,
                     p_creation_date IN DATE,
                     p_information_category in VARCHAR2,
                     p_object_version_number in number,
                     p_effective_date in date) IS

--
-- declare local variables
--
  l_proc              varchar2(72) := 'per_grade_spines_pkg.first_step_api';

Line: 379

    select pps.name
          ,psp.spinal_point
          ,pgs.grade_id
    from   per_parent_spines pps
          ,per_grade_spines_f pgs
          ,per_spinal_points psp
    where  pgs.grade_spine_id = p_grade_spine_id
    and    p_effective_date between
           pgs.effective_start_date and pgs.effective_end_date
    and    pps.parent_spine_id = pgs.parent_spine_id
    and    psp.spinal_point_id = p_spinal_point_id;

Line: 396

  insert into per_spinal_point_steps_f(
       step_id,
       grade_spine_id,
       spinal_point_id,
       sequence,
       effective_start_date,
       effective_end_date,
       business_group_id,
       last_update_date,
       last_updated_by,
       last_update_login,
       created_by,
       creation_date,
       information_category,
       object_version_number)
  values (
       p_step_id,
       p_grade_spine_id,
       p_spinal_point_id,
       p_sequence,
       p_effective_start_date,
       p_effective_end_date,
       p_business_group_id,
       p_last_update_date,
       p_last_updated_by,
       p_last_update_login,
       p_created_by,
       p_creation_date,
       p_information_category,
       p_object_version_number);

Line: 468

      ,p_datetrack_mode          => 'INSERT'
      );

Line: 500

select 'x'
from sys.dual
where exists
      (select null
       from per_all_assignments_f a,
            per_spinal_point_placements_f p,
            per_spinal_point_steps_f s
       where a.assignment_id = p.assignment_id
       and   a.effective_start_date <= p_val_start
       and   a.effective_end_date >= p_val_end
       and   p.effective_start_date <= p_val_start
       and   p.effective_end_date >= p_val_end
       and   s.effective_start_date <= p_val_start
       and   s.effective_end_date >= p_val_end
       and   p.step_id = s.step_id
       and   a.special_ceiling_step_id is null
       and   s.grade_spine_id = p_gspine_id
       and   s.sequence > p_new_ceil);

Line: 558

delete from per_spinal_point_placements_f
where placement_id = p_placement_id
and step_id = p_step_id
and effective_start_date = p_eff_start_date;

Line: 568

procedure update_placement(p_placement_id in number,
			   p_step_id in number,
			   p_eff_start_date in date,
			   p_newdate in date) is
--
-- performs a date effective delete/opening of the placement record setting the
-- EED to the date the grade spine is being ended/opened up until.
--
begin
--
hr_utility.set_location('per_grade_spines_pkg.update_placement',1);

Line: 580

update per_spinal_point_placements_f
set effective_end_date = p_newdate
where placement_id = p_placement_id
and step_id = p_step_id
and effective_start_date = p_eff_start_date;

Line: 586

hr_utility.set_location('per_grade_spines_pkg.update_placement',2);

Line: 588

end update_placement;

Line: 602

delete from per_spinal_point_steps_f
where step_id = p_step_id
and grade_spine_id = p_grade_spine_id
and effective_start_date = p_eff_start_date;

Line: 612

procedure update_step(p_step_id in number,
		      p_eff_start_date in date,
		      p_grade_spine_id in number,
		      p_newdate in date) is
--
-- performs a date effective delete/opening of the step record setting the EED
-- to the date the grade spine is being ended/opened up until.
--
begin
--
hr_utility.set_location('per_grade_spines_pkg.update_step',1);

Line: 624

update per_spinal_point_steps_f
set effective_end_date = p_newdate
where step_id = p_step_id
and grade_spine_id = p_grade_spine_id
and effective_start_date = p_eff_start_date;

Line: 630

hr_utility.set_location('per_grade_spines_pkg.update_step',2);

Line: 632

end update_step;

Line: 648

select effective_start_date -1
from per_grade_spines_f
where grade_id = p_grade_id
and effective_start_date > p_eff_end_date
and grade_spine_id <> p_gspine_id;

Line: 694

select 1
from per_all_assignments_f paa,
     per_spinal_point_steps_f psps
where psps.grade_spine_id = p_gspine_id
and paa.special_ceiling_step_id = psps.step_id
and p_sess < paa.effective_end_date;

Line: 705

select step_id,
       effective_start_date,
       effective_end_date
from per_spinal_point_steps_f
where grade_spine_id = p_gspine_id;

Line: 712

select placement_id,
       effective_start_date,
       effective_end_date
from per_spinal_point_placements_f
where step_id = p_step_id;

Line: 766

            update_placement(l_placement_id,
			     l_step_id,
			     l_plc_eff_start,
			     p_sess);

Line: 783

      update_step(l_step_id,
	          l_stp_eff_start,
	          p_gspine_id,
	          p_sess);

Line: 818

select 1
from per_grade_spines_f
where grade_spine_id = p_gspine_id
and effective_start_date > p_eff_end_date;

Line: 826

select step_id,
       effective_start_date
from per_spinal_point_steps_f
where grade_spine_id = p_gspine_id
and effective_end_date = p_eff_end_date;

Line: 836

select placement_id,
       effective_start_date
from per_spinal_point_placements_f
where step_id = p_step_id
and effective_end_date = p_eff_end_date;

Line: 848

select a.effective_start_date -1
from per_spinal_point_placements_f p,
     per_all_assignments_f a
where p.placement_id = p_plc_id
and p.effective_start_date = p_plc_start
and a.assignment_id = p.assignment_id
and a.effective_start_date > p_plc_start
and a.grade_id <> p_grd_id;

Line: 861

select max(a.effective_end_date)
from per_spinal_point_placements_f p,
     per_all_assignments_f a
where p.placement_id = p_plc_id
and p.assignment_id = a.assignment_id;

Line: 929

	 -- perform the update
	 --
         update_placement(l_plcmnt_id,
		          l_step_id,
		          l_plc_eff_start,
		          l_plc_opento_date);

Line: 940

   update_step(l_step_id,
	       l_stp_eff_start,
	       p_gspine_id,
	       l_stp_opento_date);