The following lines contain the word 'select', 'insert', 'update' or 'delete':
select oi.org_information1,
oi.org_information2,
oi.org_information3,
oi.org_information4,
oi.org_information5
from hr_organization_information oi
where oi.organization_id = org_id
and oi.org_information_context = 'GHR_US_ORG_INFORMATION';
procedure update_position_info
(p_pos_data_rec in position_data_rec_type
)
IS
l_proc varchar2(25) := 'update_position_info';
procedure update_position_kff
IS
l_proc varchar2(25) := 'update_position_kff';
SELECT name,position_definition_id,
organization_id,object_version_number,business_group_id
FROM hr_all_positions_f pos
WHERE position_id = c_position_id -- Venkat -- Position DT
and p_pos_data_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
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_position_definitions
WHERE position_definition_id = c_position_definition_id;
l_datetrack_mode := pos_return_update_mode
(p_position_id => p_pos_data_rec.position_id,
p_effective_date => p_pos_data_rec.effective_date);
hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_mode,25);
hr_position_api.update_position(
p_position_id => p_pos_data_rec.position_id,
p_object_version_number => l_object_version_number ,
p_segment1 => l_segment_tab(1),
p_segment2 => l_segment_tab(2),
p_segment3 => l_segment_tab(3),
p_segment4 => l_segment_tab(4),
p_segment5 => l_segment_tab(5),
p_segment6 => l_segment_tab(6),
p_segment7 => l_segment_tab(7),
p_segment8 => l_segment_tab(8),
p_segment9 => l_segment_tab(9),
p_segment10 => l_segment_tab(10),
p_segment11 => l_segment_tab(11),
p_segment12 => l_segment_tab(12),
p_segment13 => l_segment_tab(13),
p_segment14 => l_segment_tab(14),
p_segment15 => l_segment_tab(15),
p_segment16 => l_segment_tab(16),
p_segment17 => l_segment_tab(17),
p_segment18 => l_segment_tab(18),
p_segment19 => l_segment_tab(19),
p_segment20 => l_segment_tab(20),
p_segment21 => l_segment_tab(21),
p_segment22 => l_segment_tab(22),
p_segment23 => l_segment_tab(23),
p_segment24 => l_segment_tab(24),
p_segment25 => l_segment_tab(25),
p_segment26 => l_segment_tab(26),
p_segment27 => l_segment_tab(27),
p_segment28 => l_segment_tab(28),
p_segment29 => l_segment_tab(29),
p_segment30 => l_segment_tab(30),
p_position_definition_id => l_position_definition_id,
p_name => l_position_name,
p_valid_grades_changed_warning => l_valid_grades_changed_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_pos_data_rec.effective_date,
p_datetrack_mode => l_datetrack_mode);
hr_utility.set_location('after update_position_api ' || l_proc ,25);
procedure update_pos_organization
IS
l_object_version_number number;
SELECT name,position_definition_id,
organization_id,object_version_number,business_group_id
,location_id -- Bug 3219207 added by Ashley
FROM hr_all_positions_f pos
WHERE position_id = c_position_id -- Venkat -- Position DT
and p_pos_data_rec.effective_date between
pos.effective_start_date and pos.effective_end_date;
hr_utility.set_location('update_pos_organization ' ,25);
l_datetrack_mode := pos_return_update_mode
(p_position_id => p_pos_data_rec.position_id,
p_effective_date => p_pos_data_rec.effective_date);
hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_mode,25);
hr_position_api.update_position(
p_position_id => p_pos_data_rec.position_id,
p_object_version_number => l_object_version_number ,
p_location_id => p_pos_data_rec.location_id,
p_position_definition_id => l_position_definition_id,
p_name => l_pos_name,
p_valid_grades_changed_warning => l_valid_grade_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_pos_data_rec.effective_date,
p_datetrack_mode => l_datetrack_mode);
UPDATE hr_all_positions_f
SET organization_id = p_pos_data_rec.organization_id
WHERE position_id = p_pos_data_rec.position_id
AND effective_start_date = l_effective_start_date;
hr_position_api.update_position(
p_position_id => p_pos_data_rec.position_id,
p_object_version_number => l_object_version_number ,
p_location_id => l_location_id,
p_position_definition_id => l_position_definition_id,
p_name => l_pos_name,
p_valid_grades_changed_warning => l_valid_grade_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_pos_data_rec.effective_date,
p_datetrack_mode => l_datetrack_mode
);
UPDATE hr_all_positions_f
SET organization_id = p_pos_data_rec.organization_id
WHERE position_id = p_pos_data_rec.position_id
AND effective_start_date = l_effective_start_date;
procedure update_pos_end_date
IS
cursor c_get_position_details(c_position_id IN number, c_effective_date date)
IS
SELECT name,position_definition_id,
organization_id,object_version_number,business_group_id
FROM hr_all_positions_f
WHERE position_id = c_position_id
AND nvl(c_effective_date,sysdate)
between effective_start_date and effective_end_Date;
SELECT shared_type_id,shared_type_name from per_shared_types
WHERE system_type_cd = 'ELIMINATED';
hr_utility.set_location('update_pos_end_date ', 25);
l_datetrack_mode := pos_return_update_mode
(p_position_id => p_pos_data_rec.position_id,
p_effective_date => p_pos_data_rec.effective_date);
hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_mode,25);
hr_position_api.update_position(
p_position_id => p_pos_data_rec.position_id,
p_object_version_number => c_get_position_details_rec.object_version_number,
p_position_definition_id => c_get_position_details_rec.position_definition_id,
p_name => l_pos_name,
--p_date_end => p_pos_data_rec.effective_date,
p_availability_status_id => l_shared_type_id,
p_valid_grades_changed_warning => l_valid_grade_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_pos_data_rec.effective_date,
p_datetrack_mode => l_datetrack_mode);
hr_utility.set_location('After update of position',1);
procedure update_pos_job_id
IS
l_effective_start_Date date;
SELECT name,position_definition_id,
job_id,object_version_number,business_group_id
FROM hr_all_positions_f
WHERE position_id = c_position_id
and nvl(c_effective_date,sysdate) between
effective_start_date and effective_end_date;
hr_utility.set_location('update_pos_job_id ' ,25);
l_datetrack_mode := pos_return_update_mode
(p_position_id => p_pos_data_rec.position_id,
p_effective_date => p_pos_data_rec.effective_date);
hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_mode,25);
hr_position_api.update_position(
p_position_id => p_pos_data_rec.position_id,
p_object_version_number => l_object_version_number ,
p_position_definition_id => l_position_definition_id,
p_name => l_pos_name,
p_valid_grades_changed_warning => l_valid_grade_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_pos_data_rec.effective_date,
p_datetrack_mode => l_datetrack_mode);
UPDATE hr_all_positions_f
SET job_id = p_pos_data_rec.job_id
WHERE position_id = p_pos_data_rec.position_id
and effective_start_date = l_effective_start_date;
update_pos_organization;
update_position_kff;
update_pos_end_date;
update_pos_job_id;
FUNCTION pos_return_update_mode
(p_position_id IN hr_all_positions_f.position_id%type,
p_effective_date IN date)
RETURN varchar2 is
l_proc varchar2(72) := 'return_update_mode';
cursor c_update_mode_pos is
select pos.effective_start_date ,
pos.effective_end_date
from hr_all_positions_f pos
where pos.position_id = p_position_id
and p_effective_date
between pos.effective_start_date
and pos.effective_end_date;
cursor c_update_mode_pos1 is
select pos.effective_start_date ,
pos.effective_end_date
from hr_all_positions_f pos
where pos.position_id = p_position_id
and p_effective_date < pos.effective_start_date;
for update_mode in c_update_mode_pos loop
hr_utility.set_location(l_proc,10);
l_esd := update_mode.effective_start_date;
l_eed := update_mode.effective_end_date;
l_mode := 'UPDATE'; -- to end date a row and then create a new row
for update_mode1 in c_update_mode_pos1 loop
hr_utility.set_location(l_proc,35);
l_mode := 'UPDATE_CHANGE_INSERT'; -- to insert a row between 2 existing rows
hr_utility.set_location('UPDATE_MODE : ' || l_mode,2);
End pos_return_update_mode;
PROCEDURE update_positions_location
(p_position_id IN hr_all_positions_f.position_id%TYPE,
p_location_id IN hr_all_positions_f.location_id%TYPE,
p_effective_date IN date) IS
l_position_definition_id hr_all_positions_f.position_definition_id%TYPE;
SELECT name, position_definition_id, location_id, object_version_number
FROM hr_all_positions_f
WHERE position_id = p_position_id
and nvl(p_effective_date,sysdate) between
effective_start_date and effective_end_date;
l_datetrack_mode := pos_return_update_mode
(p_position_id => p_position_id,
p_effective_date => p_effective_date);
hr_utility.set_location('UPDATE_MODE Position : ' || l_datetrack_mode,99);
hr_utility.set_location('update_pos_location ', 99);
hr_position_api.update_position(
p_position_id => p_position_id,
p_object_version_number => l_object_version_number,
p_location_id => p_location_id,
p_position_definition_id => l_position_definition_id,
p_name => l_position_name,
p_valid_grades_changed_warning => l_valid_grade_warning,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode);
hr_utility.set_location('After update of positions location ',99);
end ghr_sf52_pos_update;