The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
CURSOR C2 IS SELECT per_grade_spines_s.nextval
FROM sys.dual;
CURSOR C3 is select per_spinal_point_steps_s.nextval
from sys.dual;
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
);
END Insert_Row;
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;
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;
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);
hr_utility.set_message_token('PROCEDURE','update_row');
END Update_Row;
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;
select grade_spine_id
into l_grade_spine_id
from per_grade_spines_f
where rowid = chartorowid(X_Rowid);
Delete from per_spinal_point_steps_f
where grade_spine_id = l_grade_spine_id;
DELETE FROM per_grade_spines_f
WHERE rowid = chartorowid(X_Rowid);
hr_utility.set_message_token('PROCEDURE','delete_row');
END Delete_Row;
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);
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);
select 'x'
from per_grade_spines_f
where grade_id = p_grd_id
and p_sess between effective_start_date and effective_end_date;
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);
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';
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;
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);
,p_datetrack_mode => 'INSERT'
);
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);
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;
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);
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;
hr_utility.set_location('per_grade_spines_pkg.update_placement',2);
end update_placement;
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;
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);
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;
hr_utility.set_location('per_grade_spines_pkg.update_step',2);
end update_step;
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;
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;
select step_id,
effective_start_date,
effective_end_date
from per_spinal_point_steps_f
where grade_spine_id = p_gspine_id;
select placement_id,
effective_start_date,
effective_end_date
from per_spinal_point_placements_f
where step_id = p_step_id;
update_placement(l_placement_id,
l_step_id,
l_plc_eff_start,
p_sess);
update_step(l_step_id,
l_stp_eff_start,
p_gspine_id,
p_sess);
select 1
from per_grade_spines_f
where grade_spine_id = p_gspine_id
and effective_start_date > p_eff_end_date;
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;
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;
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;
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;
-- perform the update
--
update_placement(l_plcmnt_id,
l_step_id,
l_plc_eff_start,
l_plc_opento_date);
update_step(l_step_id,
l_stp_eff_start,
p_gspine_id,
l_stp_opento_date);