[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from per_all_people_f
where person_id = p_person_id
and p_effective_date
between effective_start_date
and effective_end_date;
procedure delete_person_type_usage
( p_validate in boolean default false
,p_person_type_usage_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2
,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);
l_proc := g_package||'delete_person_type_usage';
savepoint delete_person_type_usage;
ROLLBACK TO delete_person_type_usage;
end delete_person_type_usage;
procedure update_person_type_usage
(
p_validate in boolean default false
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_person_type_usage_id in number
,p_object_version_number in out nocopy number
,p_person_type_id in number default hr_api.g_number
,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_attribute21 in varchar2 default hr_api.g_varchar2
,p_attribute22 in varchar2 default hr_api.g_varchar2
,p_attribute23 in varchar2 default hr_api.g_varchar2
,p_attribute24 in varchar2 default hr_api.g_varchar2
,p_attribute25 in varchar2 default hr_api.g_varchar2
,p_attribute26 in varchar2 default hr_api.g_varchar2
,p_attribute27 in varchar2 default hr_api.g_varchar2
,p_attribute28 in varchar2 default hr_api.g_varchar2
,p_attribute29 in varchar2 default hr_api.g_varchar2
,p_attribute30 in varchar2 default hr_api.g_varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
l_proc := g_package||'update_person_type_usage';
savepoint update_person_type_usage;
ROLLBACK TO update_person_type_usage;
end update_person_type_usage;
select *
from per_person_type_usages_f ptu
where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_type_usage_id = p_person_type_usage_id;
,p_datetrack_update_mode in varchar2 default hr_api.g_update
,p_datetrack_delete_mode in varchar2 default null
) is
--
-- Declare cursors and local variables
--
TYPE spt_list IS TABLE OF per_person_types.system_person_type%type
INDEX BY binary_integer;
cursor csr_delete_person_type_usages
(
p_effective_date in date
,p_person_id in number
,p_person_type_id in number
) is
select ptu.person_type_usage_id
,ptu.object_version_number
from per_person_type_usages_f ptu
where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id
and ptu.person_type_id = p_person_type_id;
l_delete_person_type_usage csr_delete_person_type_usages%rowtype;
select ppt.person_type_id
,ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = p_person_type_id;
cursor csr_update_person_type_usages
(
p_effective_date in date
,p_person_id in number
,p_system_person_type in varchar2
) is
select ptu.person_type_usage_id
,ptu.object_version_number
from per_person_type_usages_f ptu
where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id
and ptu.person_type_id in
(select ppt.person_type_id
from per_person_types ppt
where ( ( p_system_person_type in ('EMP','EX_EMP')
and ppt.system_person_type in ('EMP','EX_EMP') )
or ( p_system_person_type in ('APL','EX_APL')
and ppt.system_person_type in ('APL','EX_APL') )
or ( p_system_person_type in ('CWK','EX_CWK')
and ppt.system_person_type in ('CWK','EX_CWK') )
or ( p_system_person_type = 'OTHER'
and ppt.system_person_type = 'OTHER' )));
l_update_person_type_usage csr_update_person_type_usages%rowtype;
l_update_person_type_usage1 csr_update_person_type_usages%rowtype;
select *
from per_all_people_f
where person_id = p_person_id
and p_effective_date
between effective_start_date
and effective_end_date;
hr_utility.set_location('p_datetrack_update_mode = '||p_datetrack_update_mode,14);
hr_utility.set_location('p_datetrack_delete_mode = '||p_datetrack_delete_mode,15);
if (p_datetrack_delete_mode is not null) then
--
if g_debug then
hr_utility.set_location(l_proc, 10);
open csr_delete_person_type_usages
(p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_person_type_id => p_person_type_id
);
fetch csr_delete_person_type_usages into l_delete_person_type_usage;
if (csr_delete_person_type_usages%notfound) then
--
if g_debug then
hr_utility.set_location(l_proc, 20);
close csr_delete_person_type_usages;
close csr_delete_person_type_usages;
,p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
);
delete_person_type_usage
(p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_delete_mode
,p_object_version_number => l_delete_person_type_usage.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_person_type_usage_id => l_delete_person_type_usage.person_type_usage_id
);
elsif (p_datetrack_update_mode is not null) then
--
if g_debug then
hr_utility.set_location(l_proc, 40);
open csr_update_person_type_usages
(p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_system_person_type => l_person_type.system_person_type
);
fetch csr_update_person_type_usages into l_update_person_type_usage;
if (csr_update_person_type_usages%found) then
-- Added close
close csr_update_person_type_usages;
,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
);
update_person_type_usage
(p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
,p_object_version_number => l_update_person_type_usage.object_version_number
,p_person_type_id => p_person_type_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_person_type_usage_id => l_update_person_type_usage.person_type_usage_id
);
close csr_update_person_type_usages;
open csr_update_person_type_usages
(p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_system_person_type => 'OTHER'
);
fetch csr_update_person_type_usages into l_update_person_type_usage1;
if (csr_update_person_type_usages%found) then
-- Added close
close csr_update_person_type_usages;
,p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
);
delete_person_type_usage
(p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
,p_effective_date => p_effective_date - 1
,p_datetrack_mode => 'DELETE'
,p_object_version_number => l_update_person_type_usage1.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
,p_person_type_usage_id => l_update_person_type_usage1.person_type_usage_id
);
open csr_update_person_type_usages
(p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_system_person_type => system_type(i)
);
fetch csr_update_person_type_usages into l_update_person_type_usage1;
if (csr_update_person_type_usages%found) then
l_no_other := 'N';
close csr_update_person_type_usages;
select ptu.person_type_usage_id
,ptu.object_version_number
,ppt.system_person_type
,ptu.effective_start_date
,ptu.effective_end_date
from per_person_types ppt
,per_person_type_usages_f ptu
where ppt.person_type_id = ptu.person_type_id
and ptu.person_type_usage_id = c_person_type_usage_id
and ptu.person_id = p_person_id
and ( ( p_search_type = c_backwards
and ptu.effective_start_date <= p_effective_date)
or ( p_search_type = c_forwards
and ptu.effective_end_date >= p_effective_date) )
order by decode(p_search_type
,c_backwards,(p_effective_date - ptu.effective_start_date)
,c_forwards,(ptu.effective_end_date - p_effective_date) )
for update of ptu.person_type_usage_id;
--cursor update for bug 5706213
select ptu.person_type_usage_id,ptu.effective_start_date,ptu.effective_end_date,object_version_number
from per_person_type_usages_f ptu ,per_person_types ppt
where ptu.person_type_usage_id <> p_person_type_usage_id
and ptu.person_id = p_person_id
and ppt.PERSON_TYPE_ID = ptu.PERSON_TYPE_ID
and ppt.system_person_type = 'APL'
and ptu.effective_end_date = p_effective_start_date -1;
select max(date_start)
from per_periods_of_service
where person_id=p_person_id
and date_start <= p_effective_date;
select max(date_start)
from per_periods_of_service
where person_id=p_person_id
and date_start <= p_effective_date;
select *
from per_all_people_f
where person_id = p_person_id
and p_effective_date
between effective_start_date
and effective_end_date;
select ptu.person_type_usage_id into c_person_type_usage_id
from per_person_types ppt
,per_person_type_usages_f ptu
where ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type = p_system_person_type
and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id;
delete
from per_person_type_usages_f ptu
where ptu.effective_start_date >= l_effective_start_date
and ptu.effective_end_date <= l_effective_end_date
and ptu.person_type_usage_id = l_person_type_usage_id;
update per_person_type_usages_f ptu
set effective_end_date = l_effective_end_date
where ptu.effective_end_date = (l_effective_start_date - 1)
and ptu.person_type_usage_id = l_person_type_usage_id;
hr_per_type_usage_internal.delete_person_type_usage
(p_person_type_usage_id => csr_ptu_rec.person_type_usage_id
,p_effective_date => csr_ptu_rec.effective_end_date
,p_datetrack_mode => hr_api.g_future_change
,p_object_version_number => l_object_version_number1
,p_effective_start_date => l_effective_start_date1
,p_effective_end_date => l_effective_end_date1
);
select ptu.person_type_usage_id,
ptu.object_version_number,
effective_start_date,
effective_end_date
from per_person_type_usages_f ptu,
per_person_types pt
where ptu.person_id = c_person_id
and (c_date between ptu.effective_start_date
and ptu.effective_end_date
or c_date+1 between ptu.effective_start_date
and ptu.effective_end_date
and c_system_person_type = 'RETIREE'
)
and ptu.person_type_id = pt.person_type_id
and pt.system_person_type = c_system_person_type;
select *
from per_person_type_usages_f
where person_type_usage_id = l_person_type_usages_id
and object_version_number = l_object_version_number;
select *
from per_person_type_usages_f
where person_type_usage_id = l_person_type_usages_id
and effective_end_date = l_ptu_effective_start_date-1;
select ptu.person_type_usage_id
,ptu.object_version_number
,ptu.effective_start_date
,ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.effective_start_date = l_ptu_effective_start_date
and ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EX_APL';
select ptu.person_type_usage_id
,ptu.object_version_number
,ptu.effective_start_date
,ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.effective_end_date = l_ptu_effective_start_date-1
and ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'APL';
select ptu.person_type_usage_id
,ptu.object_version_number
,ptu.effective_start_date
,ptu.effective_end_date
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.effective_end_date = l_ptu_effective_start_date-1
and ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'OTHER';
select 'Y'
from dual
where exists (select p1.assignment_id
from per_all_assignments_f p1,
per_all_assignments_f p2
where p1.assignment_type='A'
and p2.assignment_type='E'
and p1.assignment_id=p2.assignment_id
and p1.person_id=p_person_id);
** Update the PTU record. This will require a direct update since the
** API does not allow for updates to effective_start_date.
**
** NB. Need also to move the end date of any previous EX record
** or OTHER record if one exists as of the day before p_old_date_start
** but raise error if this comes before the effective_start_date on the same row
*/
--
open c1;
update PER_PERSON_TYPE_USAGES_F
set effective_start_date = p_date_start,
object_version_number = object_version_number+1
where person_type_usage_id = l_person_type_usages_id
and object_version_number = l_object_version_number
and effective_start_date = l_ptu_effective_start_date
and effective_end_date = l_ptu_effective_end_date;
update PER_PERSON_TYPE_USAGES_F
set effective_end_date = p_date_start-1,
object_version_number = object_version_number+1
where person_type_usage_id = l_person_type_usages_id
and object_version_number = l_ptu_prev_row.object_version_number;
update PER_PERSON_TYPE_USAGES_F
set effective_end_date = p_date_start-1,
object_version_number = object_version_number+1
where person_type_usage_id = l_prev_other_row.person_type_usage_id
and object_version_number = l_prev_other_row.object_version_number;
update PER_PERSON_TYPE_USAGES_F
set effective_start_date = p_date_start,
object_version_number = object_version_number+1
where person_type_usage_id = l_prev_other_row.person_type_usage_id
and object_version_number = l_prev_other_row.object_version_number;
update PER_PERSON_TYPE_USAGES_F
set effective_end_date = p_date_start-1,
object_version_number = object_version_number+1
where person_type_usage_id = l_prev_other_row.person_type_usage_id
and object_version_number = l_prev_other_row.object_version_number;
select max (effective_start_date),max(effective_end_date)
from per_person_type_usages_f
where person_type_usage_id = c_person_type_usage_id ;
select *
from per_all_people_f
where person_id = p_person_id
and p_effective_date
between effective_start_date
and effective_end_date;
select ptu.person_type_usage_id into c_person_type_usage_id
from per_person_types ppt
,per_person_type_usages_f ptu
where ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type = p_system_person_type
and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = p_person_id;
delete
from per_person_type_usages_f ptu
where ptu.effective_start_date >= l_effective_start_date
and ptu.effective_end_date <= l_effective_end_date
and ptu.person_type_usage_id = c_person_type_usage_id;
update per_person_type_usages_f ptu
set effective_end_date = l_effective_end_date
where ptu.effective_end_date = (l_effective_start_date - 1)
and ptu.person_type_usage_id = c_person_type_usage_id;
update per_person_type_usages_f ptu
set effective_end_date = l_effective_end_date
where ptu.effective_end_date = (l_effective_start_date - 1)
and ptu.person_type_usage_id = ( select distinct (person_type_usage_id)
from per_person_type_usages_f ppf,
per_person_types ppt
where ppf.person_id = p_person_id
and ppt.PERSON_TYPE_ID = ppf.PERSON_TYPE_ID
and ppt.system_person_type = 'APL'
and effective_end_date = l_effective_start_date -1 );