The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hoi.org_information14, hoi.org_information9
from hr_organization_information hoi
, per_all_people_f per
where per.person_id = p_person_id
and hoi.organization_id = per.business_group_id
and hoi.org_information_context||'' = 'Business Group Information';
select pet.system_person_type
from per_person_types pet
where p_person_type_id = pet.person_type_id
and p_business_group_id = pet.business_group_id + 0;
select pet.system_person_type,
pet.business_group_id,
pet.active_flag
from per_person_types pet
where l_person_type_id = pet.person_type_id;
select pet.system_person_type
from per_all_people_f per,
per_person_types pet
where per.person_id = p_person_id
and l_start_date between per.effective_start_date
and per.effective_end_date
and pet.person_type_id = per.person_type_id;
if p_datetrack_mode = 'UPDATE_OVERRIDE' then
--
if g_debug then
hr_utility.set_location(l_proc, 5);
if p_datetrack_mode = 'INSERT' then
if l_new_system_person_type not in ('EMP', 'APL', 'OTHER') then
hr_utility.set_message(801, 'HR_7977_PER_INV_TYPE_FOR_INS');
if (p_datetrack_mode = 'UPDATE_CHANGE_INSERT')
then
if not (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y')
then
hr_utility.set_message(801, 'HR_7724_PER_DT_MODE_SPT_CHANGE');
select null
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date <= p_validation_end_date
and asg.effective_end_date >= p_validation_start_date
and asg.payroll_id is not null;
select null
from hz_parties
where party_id = p_party_id;
select pbg.method_of_generation_emp_num
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select system_person_type
from per_person_types
where person_type_id = p_old_person_type_id
and business_group_id = p_business_group_id;
select pbg.method_of_generation_cwk_num
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
if not ((nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') and per_per_shd.g_old_rec.npw_number is null) then
hr_utility.set_message(800, 'HR_289657_PER_INV_CWK_UPD');
if not ((nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') and per_per_shd.g_old_rec.npw_number is null) then
hr_utility.set_message(800,'PER_289635_CWK_NUM_NOT_NULL');
SELECT rule_type
FROM pay_legislative_field_info plfi
WHERE upper(plfi.field_name) = 'TOWN_OF_BIRTH'
AND plfi.legislation_code = p_legislation_code;
SELECT rule_type
FROM pay_legislative_field_info plfi
WHERE upper(plfi.field_name) = 'REGION_OF_BIRTH'
AND plfi.legislation_code = p_legislation_code;
Select null
from fnd_territories_vl ftv
where ftv.territory_code = p_country_of_birth;
Select min(effective_start_date)
from per_all_people_f
where p_person_id = person_id;
select min(date_start)
from per_periods_of_service
where p_person_id = person_id;
select original_date_of_hire from per_all_people_f where
person_id=p_person_id and
p_original_date_of_hire between effective_start_date and effective_end_date ;
select pbg.method_of_generation_apl_num
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select system_person_type
from per_person_types
where person_type_id = p_old_person_type_id
and business_group_id = p_business_group_id;
select null
from po_vendors
where vendor_id = p_vendor_id;
procedure chk_delete
(p_person_id in per_all_people_f.person_id%TYPE
,p_business_group_id in per_all_people_f.business_group_id%TYPE
,p_person_type_id in per_all_people_f.person_type_id%TYPE
,p_effective_date in date
,p_datetrack_mode in varchar2) is
--
l_exists varchar2(1);
l_proc varchar2(72) := g_package||'chk_delete';
select null
from sys.dual
where exists
(select null
from per_all_people_f ppf,
per_person_types ppt
where ppf.effective_start_date > p_effective_date
and ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type <> c_system_person_type
);
if p_datetrack_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')
then
--
l_system_person_type := return_system_person_type
(p_person_type_id,
p_business_group_id
);
if p_datetrack_mode in ('ZAP','DELETE') then
--
-- Fix for 3908271 starts here.
-- Comment out the following calls.
--
/*
hr_person_delete.weak_predel_validation(p_person_id => p_person_id
,p_session_date => p_effective_date);
hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
,p_session_date => p_effective_date);
end chk_delete;
Procedure check_non_updateable_args(p_rec in per_per_shd.g_rec_type
,p_effective_date in date) is
--
l_proc varchar2(72) := g_package||'check_non_updateable_args';
end check_non_updateable_args;
Procedure dt_update_validate
(
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_person_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
End dt_delete_validate;
Procedure insert_validate
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_name_combination_warning out nocopy boolean,
p_dob_null_warning out nocopy boolean,
p_orig_hire_warning out nocopy boolean) is
--
l_proc varchar2(72);
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_rec.business_group_id;
select date_from
from per_business_groups pbg
where pbg.business_group_id = p_rec.business_group_id;
l_proc := g_package||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_name_combination_warning out nocopy boolean,
p_dob_null_warning out nocopy boolean,
p_orig_hire_warning out nocopy boolean) is
--
l_proc varchar2(72);
select legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_rec.business_group_id;
l_proc := g_package||'update_validate';
dt_update_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date
);
End update_validate;
Procedure delete_validate
(p_rec in per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72);
l_proc := g_package||'delete_validate';
chk_delete
(p_person_id => p_rec.person_id
,p_person_type_id => per_per_shd.g_old_rec.person_type_id
,p_business_group_id => per_per_shd.g_old_rec.business_group_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
);
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_person_id => p_rec.person_id);
End delete_validate;
select pet.person_type_id
from per_person_types pet
where pet.system_person_type = p_expected_sys_type
and pet.business_group_id = p_business_group_id
and pet.active_flag = 'Y'
and pet.default_flag = 'Y';
select pet.system_person_type,
pet.active_flag,
pet.business_group_id
from per_person_types pet
where pet.person_type_id = p_person_type_id;
SELECT o3.org_information9
FROM hr_organization_information o3
WHERE o3.org_information_context = 'Business Group Information'
AND o3.organization_id IN
(
SELECT business_group_id
FROM per_all_people_f
WHERE person_id = p_person_id
and rownum = 1);
select ppt.system_person_type
from per_all_people_f ppf
, per_person_types ppt
where p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id;
select null
from sys.dual
where exists(select null
from per_all_people_f ppf
, per_person_types ppt
where ppf.effective_start_date between
p_validation_start_date
and p_validation_end_date
and ppf.person_id = p_person_id
and ppt.person_type_id = ppf.person_type_id
and ppt.system_person_type <> l_person_type);