The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(pgs.starting_step,1)
FROM per_grade_spines_f pgs
WHERE pgs.grade_spine_id = p_grade_spine_id
AND p_effective_date between pgs.effective_start_date
and pgs.effective_end_date;
,p_program_update_date in date default null
,p_ceiling_step_id out nocopy number
,p_grade_spine_id out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number out nocopy number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'create_grade_scale';
select psp.sequence
from per_spinal_points psp
where psp.business_group_id = p_business_group_id
and psp.spinal_point_id = p_ceiling_point_id;
,p_program_update_date => p_program_update_date
);
,p_program_update_date => p_program_update_date
,p_ceiling_step_id => l_ceiling_step_id
,p_starting_step => p_starting_step
,p_grade_spine_id => l_grade_spine_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_program_update_date => p_program_update_date
,p_grade_spine_id => l_grade_spine_id
,p_ceiling_step_id => l_ceiling_step_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
procedure update_grade_scale
(p_validate in boolean default false
,p_effective_date in date default hr_api.g_date
,p_datetrack_mode in varchar2
,p_grade_spine_id in number
,p_object_version_number in out nocopy number
,p_business_group_id in number default hr_api.g_number
,p_parent_spine_id in number default hr_api.g_number
,p_grade_id in number default hr_api.g_number
,p_ceiling_step_id in number default hr_api.g_number
,p_starting_step in number default hr_api.g_number
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_grade_scale';
savepoint update_grade_scale;
hr_grade_scale_bk2.update_grade_scale_b
(p_effective_date => l_effective_date
,p_grade_spine_id => p_grade_spine_id
,p_business_group_id => p_business_group_id
,p_parent_spine_id => p_parent_spine_id
,p_grade_id => p_grade_id
,p_ceiling_step_id => p_ceiling_step_id
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_datetrack_mode => p_datetrack_mode
,p_object_version_number => l_object_version_number
);
(p_module_name => 'UPDATE_GRADE_SCALE'
,p_hook_type => 'BP'
);
,p_program_update_date => p_program_update_date
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_grade_scale_bk2.update_grade_scale_a
(p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_grade_spine_id => p_grade_spine_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_parent_spine_id => p_parent_spine_id
,p_grade_id => p_grade_id
,p_ceiling_step_id => p_ceiling_step_id
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
(p_module_name => 'UPDATE_GRADE_SCALE'
,p_hook_type => 'AP'
);
rollback to update_grade_scale;
rollback to update_grade_scale;
end update_grade_scale;
PROCEDURE delete_child_grade_steps
(p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_grade_spine_id in number
) IS
--
l_proc varchar2(72) := g_package||'delete_child_grade_steps';
select 'x'
from per_spinal_point_steps_f sps
where sps.grade_spine_id = p_grade_spine_id
and exists (select null
from per_spinal_point_placements_f spp
where spp.step_id = sps.step_id
and p_date < spp.effective_end_date);
select 'x'
from per_spinal_point_steps_f sps
where sps.grade_spine_id = p_grade_spine_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
and p_date < a.effective_end_date);
select 'x'
from per_spinal_point_steps_f sps
where sps.grade_spine_id = p_grade_spine_id
and exists (select null
from hr_all_positions_f p
where p.entry_step_id = sps.step_id
and p_date < p.effective_end_date);
select distinct step_id
,min(effective_start_date) min_eff_start_date
,max(effective_end_date) max_eff_end_date
from per_spinal_point_steps_f
where grade_spine_id = p_grade_spine_id
group by step_id;
select object_version_number
from per_spinal_point_steps_f
where step_id = p_step_id
and p_eff_date between effective_start_date and effective_end_date;
select min(effective_start_date)
from per_grade_spines_f
where grade_spine_id = p_grade_spine_id;
if p_datetrack_mode = hr_api.g_delete then
l_date := p_effective_date;
p_datetrack_mode = hr_api.g_delete THEN
--
-- ZAP the step using min effective_start_date as effective_date.
--
hr_utility.set_location(l_proc, 70);
p_datetrack_mode = hr_api.g_delete) THEN
--
-- Get the step details.
--
hr_utility.set_location(l_proc, 110);
hr_grade_step_api.delete_grade_step(
p_validate => p_validate
,p_step_id => grade_step_rec.step_id
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_Effective_Start_Date => l_effective_start_date
,p_Effective_End_Date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_called_from_del_grd_scale => TRUE --bug 4096238
);
procedure delete_grade_scale
(p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_grade_spine_id in number
,p_object_version_number in out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) IS
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_grade_scale';
savepoint delete_grade_scale;
hr_grade_scale_bk3.delete_grade_scale_b
(p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_grade_spine_id => p_grade_spine_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'DELETE_GRADE_SCALE'
,p_hook_type => 'BP'
);
IF p_datetrack_mode in (hr_api.g_delete, hr_api.g_zap) THEN
hr_utility.set_location(l_proc, 25);
delete_child_grade_steps
(p_validate => p_validate
,p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_grade_spine_id => p_grade_spine_id
);
hr_grade_scale_bk3.delete_grade_scale_a
(p_effective_date => l_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_grade_spine_id => p_grade_spine_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
(p_module_name => 'DELETE_GRADE_SCALE'
,p_hook_type => 'AP'
);
rollback to delete_grade_scale;
rollback to delete_grade_scale;
end delete_grade_scale;