[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c1 is select per_jobs_s.nextval
from sys.dual;
cursor csr_name is select null
from per_jobs j
where ((p_job_id is not null
and j.job_id <> p_job_id)
or p_job_id is null)
and j.business_group_id + 0 = p_business_group_id
and j.name = p_name;
cursor csr_date_from is select null
from per_valid_grades vg
where vg.job_id = p_job_id
and p_date_from > vg.date_from;
cursor csr_job is select to_char(id_flex_num)
from per_job_groups_v
where p_job_group_id = job_group_id;
cursor csr_date_to is select null
from per_valid_grades vg
where vg.business_group_id + 0 = p_business_group_id
and vg.job_id = p_job_id
and nvl(vg.date_to, p_end_of_time) > p_date_to;
cursor csr_date_from is select null
from per_valid_grades vg
where vg.business_group_id + 0 = p_business_group_id
and vg.job_id = p_job_id
and vg.date_from > p_date_to;
PROCEDURE update_valid_grades(p_business_group_id number,
p_job_id number,
p_date_to date,
p_end_of_time date) is
--
begin
--
-- Update valid grade end dates to match the end date of the
-- job where the end date of the job is earlier than the end
-- date of the valid grade.or the previous end dates matched.
--
--
update per_valid_grades vg
set vg.date_to =
(select least(nvl(p_date_to, p_end_of_time),
nvl(g.date_to, p_end_of_time))
from per_grades g
where g.grade_id = vg.grade_id
and g.business_group_id + 0 = p_business_group_id)
where vg.business_group_id + 0 = p_business_group_id
and vg.job_id = p_job_id
and nvl(vg.date_to, p_end_of_time) > p_date_to;
hr_utility.set_message_token('PROCEDURE','update_valid_grades');
end update_valid_grades;
PROCEDURE delete_valid_grades(p_business_group_id number,
p_job_id number,
p_date_to date) is
--
begin
--
-- Valid grades are deleted if the end date of the job
-- has been made earlier than the start date of the
-- valid grade.
--
--
delete from per_valid_grades vg
where vg.business_group_id + 0 = p_business_group_id
and vg.job_id = p_job_id
and vg.date_from > p_date_to;
hr_utility.set_message_token('PROCEDURE','delete_valid_grades');
end delete_valid_grades;
PROCEDURE check_delete_record(p_job_id number,
p_business_group_id number) is
--
-- Changed 01-Oct-99 SCNair (per_all_positions to hr_all_positions_f) date track requirement
--
cursor csr_position is select null
from hr_all_positions_f pst1
where pst1.job_id = p_job_id;
cursor csr_assignment is select null
from per_all_assignments_f a
where a.job_id = p_job_id
and a.job_id is not null;
cursor csr_grade is select null
from per_valid_grades vg1
where vg1.business_group_id + 0 = p_business_group_id
and vg1.job_id = p_job_id;
cursor csr_requirement is select null
from per_job_requirements jre1
where jre1.job_id = p_job_id;
cursor csr_evaluation is select null
from per_job_evaluations jev1
where jev1.job_id = p_job_id;
cursor csr_elementp is select null
from per_career_path_elements cpe1
where cpe1.parent_job_id = p_job_id;
cursor csr_elements is select null
from per_career_path_elements cpe1
where cpe1.subordinate_job_id = p_job_id;
cursor csr_budget is select null
from per_budget_elements bde1
where bde1.job_id = p_job_id
and bde1.job_id is not null;
cursor csr_vacancy is select null
from per_vacancies vac
where vac.job_id = p_job_id
and vac.job_id is not null;
cursor csr_link is select null
from pay_element_links_f eln
where eln.job_id = p_job_id
and eln.job_id is not null;
cursor csr_role is select null
from per_roles rol
where rol.job_id = p_job_id
and rol.job_id is not null;
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 1);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 2);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 3);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 4);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 5);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 6);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 7);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 8);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 9);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 10);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 11);
l_sql_text := 'select null '
||'from sys.dual '
||'where exists( select null '
||' from po_position_controls '
||' where job_id = '
||to_char(p_job_id)
||' ) ';
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 11);
hr_utility.set_location('PER_JOBS_PKG.check_delete_record', 12);
end check_delete_record;
select jbe.job_evaluation_id,
jbe.date_evaluated
from per_job_evaluations jbe
where jbe.job_id = csr_job_evaluations.p_job_id;