[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
vgr.valid_grade_id valid_grade_id
,vgr.object_version_number object_version_number
,vgr.date_from date_from
,vgr.date_to date_to
from per_valid_grades vgr
where vgr.job_id = p_job_id;
select pjg.ID_FLEX_NUM
from per_job_groups pjg
where pjg.job_group_id = p_job_group_id;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from per_job_definitions
where job_definition_id = l_job_definition_id;
procedure update_job
(p_validate in boolean default false
,p_job_id in number
,p_object_version_number in out nocopy number
,p_date_from in date default hr_api.g_date
,p_comments in varchar2 default hr_api.g_varchar2
,p_date_to in date default hr_api.g_date
,p_benchmark_job_flag in varchar2 default hr_api.g_varchar2
,p_benchmark_job_id in number default hr_api.g_number
,p_emp_rights_flag in varchar2 default hr_api.g_varchar2
,p_attribute_category in varchar2 default hr_api.g_varchar2
,p_attribute1 in varchar2 default hr_api.g_varchar2
,p_attribute2 in varchar2 default hr_api.g_varchar2
,p_attribute3 in varchar2 default hr_api.g_varchar2
,p_attribute4 in varchar2 default hr_api.g_varchar2
,p_attribute5 in varchar2 default hr_api.g_varchar2
,p_attribute6 in varchar2 default hr_api.g_varchar2
,p_attribute7 in varchar2 default hr_api.g_varchar2
,p_attribute8 in varchar2 default hr_api.g_varchar2
,p_attribute9 in varchar2 default hr_api.g_varchar2
,p_attribute10 in varchar2 default hr_api.g_varchar2
,p_attribute11 in varchar2 default hr_api.g_varchar2
,p_attribute12 in varchar2 default hr_api.g_varchar2
,p_attribute13 in varchar2 default hr_api.g_varchar2
,p_attribute14 in varchar2 default hr_api.g_varchar2
,p_attribute15 in varchar2 default hr_api.g_varchar2
,p_attribute16 in varchar2 default hr_api.g_varchar2
,p_attribute17 in varchar2 default hr_api.g_varchar2
,p_attribute18 in varchar2 default hr_api.g_varchar2
,p_attribute19 in varchar2 default hr_api.g_varchar2
,p_attribute20 in varchar2 default hr_api.g_varchar2
,p_job_information_category in varchar2 default hr_api.g_varchar2
,p_job_information1 in varchar2 default hr_api.g_varchar2
,p_job_information2 in varchar2 default hr_api.g_varchar2
,p_job_information3 in varchar2 default hr_api.g_varchar2
,p_job_information4 in varchar2 default hr_api.g_varchar2
,p_job_information5 in varchar2 default hr_api.g_varchar2
,p_job_information6 in varchar2 default hr_api.g_varchar2
,p_job_information7 in varchar2 default hr_api.g_varchar2
,p_job_information8 in varchar2 default hr_api.g_varchar2
,p_job_information9 in varchar2 default hr_api.g_varchar2
,p_job_information10 in varchar2 default hr_api.g_varchar2
,p_job_information11 in varchar2 default hr_api.g_varchar2
,p_job_information12 in varchar2 default hr_api.g_varchar2
,p_job_information13 in varchar2 default hr_api.g_varchar2
,p_job_information14 in varchar2 default hr_api.g_varchar2
,p_job_information15 in varchar2 default hr_api.g_varchar2
,p_job_information16 in varchar2 default hr_api.g_varchar2
,p_job_information17 in varchar2 default hr_api.g_varchar2
,p_job_information18 in varchar2 default hr_api.g_varchar2
,p_job_information19 in varchar2 default hr_api.g_varchar2
,p_job_information20 in varchar2 default hr_api.g_varchar2
,p_segment1 in varchar2 default hr_api.g_varchar2
,p_segment2 in varchar2 default hr_api.g_varchar2
,p_segment3 in varchar2 default hr_api.g_varchar2
,p_segment4 in varchar2 default hr_api.g_varchar2
,p_segment5 in varchar2 default hr_api.g_varchar2
,p_segment6 in varchar2 default hr_api.g_varchar2
,p_segment7 in varchar2 default hr_api.g_varchar2
,p_segment8 in varchar2 default hr_api.g_varchar2
,p_segment9 in varchar2 default hr_api.g_varchar2
,p_segment10 in varchar2 default hr_api.g_varchar2
,p_segment11 in varchar2 default hr_api.g_varchar2
,p_segment12 in varchar2 default hr_api.g_varchar2
,p_segment13 in varchar2 default hr_api.g_varchar2
,p_segment14 in varchar2 default hr_api.g_varchar2
,p_segment15 in varchar2 default hr_api.g_varchar2
,p_segment16 in varchar2 default hr_api.g_varchar2
,p_segment17 in varchar2 default hr_api.g_varchar2
,p_segment18 in varchar2 default hr_api.g_varchar2
,p_segment19 in varchar2 default hr_api.g_varchar2
,p_segment20 in varchar2 default hr_api.g_varchar2
,p_segment21 in varchar2 default hr_api.g_varchar2
,p_segment22 in varchar2 default hr_api.g_varchar2
,p_segment23 in varchar2 default hr_api.g_varchar2
,p_segment24 in varchar2 default hr_api.g_varchar2
,p_segment25 in varchar2 default hr_api.g_varchar2
,p_segment26 in varchar2 default hr_api.g_varchar2
,p_segment27 in varchar2 default hr_api.g_varchar2
,p_segment28 in varchar2 default hr_api.g_varchar2
,p_segment29 in varchar2 default hr_api.g_varchar2
,p_segment30 in varchar2 default hr_api.g_varchar2
,p_concat_segments in varchar2 default hr_api.g_varchar2
,p_approval_authority in number default hr_api.g_number
,p_language_code in varchar2 default hr_api.userenv_lang
--
-- bug 2242339 change p_job_definition_id from an out to an in/out parameter
-- to enable value to be passed into program when known and required.
--
,p_job_definition_id in out nocopy number
,p_name out nocopy varchar2
,p_valid_grades_changed_warning out nocopy boolean
-- Defaulting it for fix 3138252
,p_effective_date in date default hr_api.g_date --Added for Bug# 1760707
) is
--
-- Declare cursors and local variables
--
-- bug 2242339 initialize l_job_definition_id and segment variables with
-- values where these are passed into program.
--
l_job_id per_jobs.job_id%TYPE;
l_proc varchar2(72) := g_package||'update_job';
select pjg.ID_FLEX_NUM
from per_job_groups pjg
where pjg.JOB_GROUP_ID = (select job_group_id
from per_jobs
where JOB_ID = p_job_id);
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
from per_job_definitions
where job_definition_id = l_job_definition_id;
savepoint update_job;
hr_utility.set_location('Entering: call - update_job_b',35);
hr_job_api_bk2.update_job_b
(p_job_id => p_job_id
,p_date_from => l_date_from
,p_comments => p_comments
,p_date_to => l_date_to
,p_approval_authority => p_approval_authority
,p_benchmark_job_flag => p_benchmark_job_flag
,p_benchmark_job_id => p_benchmark_job_id
,p_emp_rights_flag => p_emp_rights_flag
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_job_information_category => p_job_information_category
,p_job_information1 => p_job_information1
,p_job_information2 => p_job_information2
,p_job_information3 => p_job_information3
,p_job_information4 => p_job_information4
,p_job_information5 => p_job_information5
,p_job_information6 => p_job_information6
,p_job_information7 => p_job_information7
,p_job_information8 => p_job_information8
,p_job_information9 => p_job_information9
,p_job_information10 => p_job_information10
,p_job_information11 => p_job_information11
,p_job_information12 => p_job_information12
,p_job_information13 => p_job_information13
,p_job_information14 => p_job_information14
,p_job_information15 => p_job_information15
,p_job_information16 => p_job_information16
,p_job_information17 => p_job_information17
,p_job_information18 => p_job_information18
,p_job_information19 => p_job_information19
,p_job_information20 => p_job_information20
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_concat_segments => p_concat_segments
,p_name => p_name
,p_object_version_number => p_object_version_number
,p_job_definition_id => p_job_definition_id
,p_effective_date => p_effective_date --Added for bug# 1760707
);
(p_module_name => 'UPDATE_JOB'
,p_hook_type => 'BP'
);
end; -- End of API User Hook for the before hook of update_job
hr_utility.set_location('Entering: call - update_job_a',55);
hr_job_api_bk2.update_job_a
(p_job_id => p_job_id
,p_date_from => l_date_from
,p_comments => p_comments
,p_date_to => l_date_to
,p_approval_authority => p_approval_authority
,p_benchmark_job_flag => p_benchmark_job_flag
,p_benchmark_job_id => p_benchmark_job_id
,p_emp_rights_flag => p_emp_rights_flag
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_job_information_category => p_job_information_category
,p_job_information1 => p_job_information1
,p_job_information2 => p_job_information2
,p_job_information3 => p_job_information3
,p_job_information4 => p_job_information4
,p_job_information5 => p_job_information5
,p_job_information6 => p_job_information6
,p_job_information7 => p_job_information7
,p_job_information8 => p_job_information8
,p_job_information9 => p_job_information9
,p_job_information10 => p_job_information10
,p_job_information11 => p_job_information11
,p_job_information12 => p_job_information12
,p_job_information13 => p_job_information13
,p_job_information14 => p_job_information14
,p_job_information15 => p_job_information15
,p_job_information16 => p_job_information16
,p_job_information17 => p_job_information17
,p_job_information18 => p_job_information18
,p_job_information19 => p_job_information19
,p_job_information20 => p_job_information20
,p_segment1 => l_segment1
,p_segment2 => l_segment2
,p_segment3 => l_segment3
,p_segment4 => l_segment4
,p_segment5 => l_segment5
,p_segment6 => l_segment6
,p_segment7 => l_segment7
,p_segment8 => l_segment8
,p_segment9 => l_segment9
,p_segment10 => l_segment10
,p_segment11 => l_segment11
,p_segment12 => l_segment12
,p_segment13 => l_segment13
,p_segment14 => l_segment14
,p_segment15 => l_segment15
,p_segment16 => l_segment16
,p_segment17 => l_segment17
,p_segment18 => l_segment18
,p_segment19 => l_segment19
,p_segment20 => l_segment20
,p_segment21 => l_segment21
,p_segment22 => l_segment22
,p_segment23 => l_segment23
,p_segment24 => l_segment24
,p_segment25 => l_segment25
,p_segment26 => l_segment26
,p_segment27 => l_segment27
,p_segment28 => l_segment28
,p_segment29 => l_segment29
,p_segment30 => l_segment30
,p_concat_segments => p_concat_segments
,p_name => p_name
,p_object_version_number => p_object_version_number
,p_job_definition_id => p_job_definition_id
,p_effective_date => p_effective_date --Added for Bug# 1760707
);
(p_module_name => 'UPDATE_JOB'
,p_hook_type => 'AP'
);
end; -- End of API User Hook for the after hook of update_job
ROLLBACK TO update_job;
rollback to update_job;
end update_job;
procedure delete_job
(p_validate in boolean
,p_job_id in number
,p_object_version_number in out nocopy number) IS
l_object_version_number number(9);
l_proc varchar2(72) := g_package||'DELETE_JOB';
savepoint DELETE_JOB;
hr_job_api_bk3.delete_job_b
(p_validate => p_validate
,p_job_id => p_job_id
,p_object_version_number => p_object_version_number);
(p_module_name => 'DELETE_JOB'
,p_hook_type => 'BP'
);
hr_job_api_bk3.delete_job_a
(p_validate => p_validate
,p_job_id => p_job_id
,p_object_version_number => l_object_version_number);
(p_module_name => 'DELETE_JOB'
,p_hook_type => 'AP'
);
rollback to DELETE_JOB;
rollback to DELETE_JOB;
end delete_job;
cursor c1 is select per_jobs_s.nextval
from sys.dual;
cursor csr_job is select to_char(id_flex_num)
from per_job_groups_v
where p_job_group_id = job_group_id;
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;