[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
from per_assignments_f a
where a.assignment_id = p_ass_id
and a.effective_start_date >= p_sess
and a.grade_id <> p_grd_id;
procedure b_delete_valid(p_ass_id IN NUMBER,
p_pmt_id IN NUMBER,
p_eed IN DATE) IS
l_exists VARCHAR2(1);
select 'x'
from per_spinal_point_placements_f
where assignment_id = p_ass_id
and placement_id <> p_pmt_id
and effective_start_date > p_eed;
hr_utility.set_location('per_spinal_pt_plcmt_pkg.b_delete_valid',1);
end b_delete_valid;
select p1.spinal_point,
(nvl(gs.starting_step,1) + count(*))-1 count ,
p1.spinal_point_id
from per_spinal_points p1,
per_spinal_point_steps_f s2,
per_spinal_point_steps_f s1,
per_grade_spines_f gs
where p1.spinal_point_id = s1.spinal_point_id
and s1.grade_spine_id = s2.grade_spine_id
and s1.grade_spine_id = gs.grade_spine_id
and s1.sequence >= s2.sequence
and p_sess between s1.effective_start_date and s1.effective_end_date
and p_sess between s2.effective_start_date and s2.effective_end_date
and p_sess between gs.effective_start_date and gs.effective_end_date
and s1.step_id = p_step_id
group by p1.spinal_point,gs.starting_step, p1.spinal_point_id;
select meaning
from hr_lookups
where lookup_type = 'PLACEMENT_REASON'
and lookup_code = p_rsn;
select 'x'
from per_spinal_point_placements_f
where assignment_id = p_ass_id
and effective_end_date >= p_sess;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Placement_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Assignment_Id NUMBER,
X_Step_Id NUMBER,
X_Auto_Increment_Flag VARCHAR2,
X_Parent_Spine_Id NUMBER,
X_Reason VARCHAR2,
X_Information1 VARCHAR2,
X_Information2 VARCHAR2,
X_Information3 VARCHAR2,
X_Information4 VARCHAR2,
X_Information5 VARCHAR2,
X_Information6 VARCHAR2,
X_Information7 VARCHAR2,
X_Information8 VARCHAR2,
X_Information9 VARCHAR2,
X_Information10 VARCHAR2,
X_Information11 VARCHAR2,
X_Information12 VARCHAR2,
X_Information13 VARCHAR2,
X_Information14 VARCHAR2,
X_Information15 VARCHAR2,
X_Information16 VARCHAR2,
X_Information17 VARCHAR2,
X_Information18 VARCHAR2,
X_Information19 VARCHAR2,
X_Information20 VARCHAR2,
X_Information21 VARCHAR2,
X_Information22 VARCHAR2,
X_Information23 VARCHAR2,
X_Information24 VARCHAR2,
X_Information25 VARCHAR2,
X_Information26 VARCHAR2,
X_Information27 VARCHAR2,
X_Information28 VARCHAR2,
X_Information29 VARCHAR2,
X_Information30 VARCHAR2,
X_Information_category VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM per_spinal_point_placements_f
WHERE placement_id = x_placement_id;
CURSOR C2 IS SELECT per_spinal_point_placements_s.nextval FROM sys.dual;
INSERT INTO per_spinal_point_placements_f(
placement_id,
effective_start_date,
effective_end_date,
business_group_id,
assignment_id,
step_id,
auto_increment_flag,
parent_spine_id,
reason,
information1,
information2,
information3,
information4,
information5,
information6,
information7,
information8,
information9,
information10,
information11,
information12,
information13,
information14,
information15,
information16,
information17,
information18,
information19,
information20,
information21,
information22,
information23,
information24,
information25,
information26,
information27,
information28,
information29,
information30,
information_category
) VALUES (
X_Placement_Id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
X_Assignment_Id,
X_Step_Id,
X_Auto_Increment_Flag,
X_Parent_Spine_Id,
X_Reason,
X_Information1,
X_Information2,
X_Information3,
X_Information4,
X_Information5,
X_Information6,
X_Information7,
X_Information8,
X_Information9,
X_Information10,
X_Information11,
X_Information12,
X_Information13,
X_Information14,
X_Information15,
X_Information16,
X_Information17,
X_Information18,
X_Information19,
X_Information20,
X_Information21,
X_Information22,
X_Information23,
X_Information24,
X_Information25,
X_Information26,
X_Information27,
X_Information28,
X_Information29,
X_Information30,
X_Information_category
);
END Insert_Row;
SELECT *
FROM per_spinal_point_placements_f
WHERE rowid = chartorowid(X_Rowid)
FOR UPDATE of placement_id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Placement_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Assignment_Id NUMBER,
X_Step_Id NUMBER,
X_Auto_Increment_Flag VARCHAR2,
X_Parent_Spine_Id NUMBER,
X_Reason VARCHAR2,
X_Information1 VARCHAR2,
X_Information2 VARCHAR2,
X_Information3 VARCHAR2,
X_Information4 VARCHAR2,
X_Information5 VARCHAR2,
X_Information6 VARCHAR2,
X_Information7 VARCHAR2,
X_Information8 VARCHAR2,
X_Information9 VARCHAR2,
X_Information10 VARCHAR2,
X_Information11 VARCHAR2,
X_Information12 VARCHAR2,
X_Information13 VARCHAR2,
X_Information14 VARCHAR2,
X_Information15 VARCHAR2,
X_Information16 VARCHAR2,
X_Information17 VARCHAR2,
X_Information18 VARCHAR2,
X_Information19 VARCHAR2,
X_Information20 VARCHAR2,
X_Information21 VARCHAR2,
X_Information22 VARCHAR2,
X_Information23 VARCHAR2,
X_Information24 VARCHAR2,
X_Information25 VARCHAR2,
X_Information26 VARCHAR2,
X_Information27 VARCHAR2,
X_Information28 VARCHAR2,
X_Information29 VARCHAR2,
X_Information30 VARCHAR2,
X_Information_category VARCHAR2
) IS
BEGIN
UPDATE per_spinal_point_placements_f
SET
placement_id = X_Placement_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
assignment_id = X_Assignment_Id,
step_id = X_Step_Id,
auto_increment_flag = X_Auto_Increment_Flag,
parent_spine_id = X_Parent_Spine_Id,
reason = X_Reason,
information1 = X_Information1,
information2 = X_Information2,
information3 = X_Information3,
information4 = X_Information4,
information5 = X_Information5,
information6 = X_Information6,
information7 = X_Information7,
information8 = X_Information8,
information9 = X_Information9,
information10 = X_Information10,
information11 = X_Information11,
information12 = X_Information12,
information13 = X_Information13,
information14 = X_Information14,
information15 = X_Information15,
information16 = X_Information16,
information17 = X_Information17,
information18 = X_Information18,
information19 = X_Information19,
information20 = X_Information20,
information21 = X_Information21,
information22 = X_Information22,
information23 = X_Information23,
information24 = X_Information24,
information25 = X_Information25,
information26 = X_Information26,
information27 = X_Information27,
information28 = X_Information28,
information29 = X_Information29,
information30 = X_Information30,
information_category = X_Information_category
WHERE rowid = chartorowid(X_Rowid);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM per_spinal_point_placements_f
WHERE rowid = chartorowid(X_rowid);
END Delete_Row;
select asg.effective_end_date,
g.grade_id,
g.name,
sp.spinal_point,
ps.name,
ps.parent_spine_id,
sp.sequence,
pgm.name
from per_grades_vl g,
per_grade_spines_f gs,
per_parent_spines ps,
per_spinal_points sp,
per_spinal_point_steps_f sps,
per_all_assignments_f asg,
ben_pgm_f pgm
where asg.assignment_id = p_ass_id
and asg.grade_id is not null
and asg.grade_id = g.grade_id
and asg.grade_ladder_pgm_id = pgm.pgm_id(+)
and g.grade_id = gs.grade_id
and ps.parent_spine_id = gs.parent_spine_id
and sps.spinal_point_id = sp.spinal_point_id
and nvl(asg.special_ceiling_step_id,gs.ceiling_step_id) = sps.step_id
and p_sess between asg.effective_start_date and asg.effective_end_date
and p_sess between sps.effective_start_date and sps.effective_end_date
and p_sess between gs.effective_start_date and gs.effective_end_date
and p_sess between NVL(pgm.effective_start_date,p_sess)
and nvl(pgm.effective_end_date,p_sess)
and asg.business_group_id + 0 = p_bgroup_id;
select (nvl(gs.starting_step,1)+count(*))-1
from per_spinal_points p2,
per_spinal_point_steps_f s2,
per_grade_spines_f gs
where s2.spinal_point_id = p2.spinal_point_id
and p_ceil_seq >= p2.sequence
and p_sess between s2.effective_start_date and s2.effective_end_date
and p_sess between gs.effective_start_date and gs.effective_end_date
and gs.grade_spine_id = s2.grade_spine_id
and gs.grade_id = p_grd_id
and gs.parent_spine_id = p_parent_id
group by gs.starting_step;
select meaning
from fnd_lookups
where lookup_type = 'YES_NO'
and lookup_code = 'Y';
select asg.business_group_id,
ses.effective_date
from per_assignments_f asg,
fnd_sessions ses
where asg.assignment_id = p_ass_id
and ses.session_id = userenv('SESSIONID')
and ses.effective_date between
asg.effective_start_date and asg.effective_end_date;