[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'N'
INTO l_status
FROM sys.dual
WHERE exists (SELECT 'Y'
FROM per_all_people_f pp
WHERE (p_person_id IS NULL
OR p_person_id <> pp.person_id)
AND pp.business_group_id +0 = p_business_group_id
AND pp.applicant_number = p_person_number);
SELECT 'N'
INTO l_status
FROM sys.dual
WHERE exists (select 'Y'
FROM per_all_people_f pp
WHERE (p_person_id IS NULL
OR p_person_id <> pp.person_id)
AND pp.business_group_id = p_business_group_id
AND (pp.employee_number = p_person_number
OR (pp.npw_number = p_person_number
AND EXISTS
(SELECT null
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id
AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
SELECT 'N'
INTO l_status
FROM sys.dual
WHERE exists (select 'Y'
FROM per_all_people_f pp
WHERE (p_person_id IS NULL
OR p_person_id <> pp.person_id)
AND pp.business_group_id = p_business_group_id
AND (pp.npw_number = p_person_number
OR (pp.employee_number = p_person_number
AND EXISTS
(SELECT null
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id
AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
select fnd.effective_date
from fnd_sessions fnd
where fnd.session_id = userenv('sessionid');
SELECT next_value
, rowid
INTO l_person_number
, l_rowid
FROM per_number_generation_controls
WHERE business_group_id = p_business_group_id
AND type = p_person_type
FOR UPDATE OF next_value ;
UPDATE per_number_generation_controls
SET next_value = l_person_number + 1
WHERE rowid = l_rowid ;
SELECT pbg.method_of_generation_apl_num
, pbg.legislation_code
INTO l_method_of_generation
, l_legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id;
select person_id
into l_person_id
from per_people_f ppf
where ppf.applicant_number = p_applicant_number
and ppf.business_group_id +0 = p_business_group_id
and rownum = 1;
select null
into p_applicant_number
from sys.dual
where (p_person_id is not null
and not exists (select '1'
from per_assignments_f paf
where assignment_type = 'A'
and paf.person_id = p_person_id
)
);
select applicant_number into p_applicant_number
from per_people_f
where person_id = p_person_id
and applicant_number is not null
and rownum = 1;
SELECT pbg.method_of_generation_emp_num
, pbg.legislation_code
INTO l_method_of_generation
, l_legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id;
select person_id
into l_person_id
from per_people_f ppf
where ppf.employee_number = p_employee_number
and ppf.business_group_id +0 = p_business_group_id
and rownum = 1;
select null
into p_employee_number
from sys.dual
where (p_person_id is not null
and not exists (select '1'
from per_assignments_f paf
where assignment_type = 'E'
and paf.person_id = p_person_id
)
);
SELECT pbg.method_of_generation_cwk_num
, pbg.method_of_generation_emp_num
, pbg.legislation_code
INTO l_method_of_generation
, l_method_of_gen_emp
, l_legislation_code
FROM per_business_groups pbg
WHERE pbg.business_group_id = p_business_group_id;
select person_id
into l_person_id
from per_people_f ppf
where ppf.npw_number = p_npw_number
and ppf.business_group_id +0 = p_business_group_id
and rownum = 1;
select null
into p_npw_number
from sys.dual
where (p_person_id is not null
and not exists (select '1'
from per_assignments_f paf
where assignment_type = 'C'
and paf.person_id = p_person_id
)
);
select p.employee_number
, p.applicant_number
, p.npw_number
into p_employee_number
, p_applicant_number
, p_npw_number
from per_people p
where p.person_id = p_person_id;
select '1'
from user_objects
where object_name = p_pkg
and object_type = 'PACKAGE';
select legislation_code
from per_business_groups_perf -- #3907786 - Changed to per_business_groups_perf
where business_group_id = p_business_group_id;
SELECT meaning
INTO l_title_meaning
FROM hr_lookups
WHERE lookup_type = 'TITLE'
AND p_title = lookup_code;
SELECT rtrim(substrb(DECODE(p_pre_name_adjunct,'','',p_pre_name_adjunct||' ')||
p_last_name||','||DECODE(l_title_meaning,'','',
' '||l_title_meaning)||DECODE(p_first_name,'','',
' '||p_first_name)||DECODE(p_middle_names,'','',
' '||p_middle_names)||
DECODE(p_suffix,'','',' '||p_suffix)||
DECODE(p_known_as,'','',
' ('||p_known_as||')'),1,240))
INTO l_full_name
FROM sys.dual ;
l_proc_call := 'SELECT rtrim(substrb( '|| l_package_name ||'.'||l_procedure_name||'(:p_first_name,:p_middle_names,:p_last_name,:p_known_as,:p_title,';
SELECT 'Y'
INTO l_status
FROM sys.dual
WHERE EXISTS (SELECT 'Duplicate Person Exists'
FROM per_all_people_f pp
WHERE /* Perform case insensitive check on last name */
/* trying to use the index on last name */
upper(pp.last_name) = upper(p_last_name)
AND ( pp.last_name like l_ul_check
OR pp.last_name like l_lu_check
OR pp.last_name like l_uu_check
OR pp.last_name like l_ll_check
)
AND (upper(pp.first_name) = upper(p_first_name)
OR p_first_name IS NULL
OR pp.first_name IS NULL)
AND (pp.date_of_birth = p_date_of_birth
OR p_date_of_birth IS NULL
OR pp.date_of_birth IS NULL)
AND ((p_person_id IS NOT NULL
AND p_person_id <> pp.person_id)
OR p_person_id IS NULL)
AND pp.business_group_id +0 = p_business_group_id);
SELECT org_information9
INTO l_legislation_code
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = p_business_group_id;
SELECT 'Y'
INTO l_status
FROM sys.dual
WHERE exists(SELECT '1'
FROM per_all_people_f pp
WHERE (p_person_id IS NULL
OR p_person_id <> pp.person_id)
AND p_national_identifier = pp.national_identifier
AND pp.business_group_id +0 = p_business_group_id
);
SELECT org_information9
INTO l_legislation_code
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = p_business_group_id;
Validates whether a person can be deleted from the HR database.
This is the weak validation performed prior to delete using the
Delete Person form.
*/
--
PROCEDURE weak_predel_validation (p_person_id IN number,
p_session_date IN date)
IS
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.weak_predel_validation(p_person_id,
p_session_date);
If additional data is found then the delete of this person from
the calling module is invalid as it is beyond its scope. The Delete
Person form should therefore be used (which only performs
weak_predel_validation) if a delete really is required.
p_person_mode - 'A' check for applicants
'E' check for employees
'O' check for other types
NOTE
No validation is required for security (PER_PERSON_LIST* tables) as
this is implicit for the person via assignment criteria. The
rows in these tables can just be deleted.
*/
PROCEDURE strong_predel_validation (p_person_id IN number,
p_session_date IN date)
IS
--
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.strong_predel_validation(p_person_id,
p_session_date);
then delete this contact also. Otherwise do nothing.
NOTES
p_person_id non-contact in relationship
p_contact_person_id contact in this relationship - the person
who the check is performed against.
p_contact_relationship_id relationship which is currently being
considered for this contact.
*/
--
PROCEDURE check_contact (p_person_id IN number,
p_contact_person_id IN number,
p_contact_relationship_id IN number,
p_session_date IN date)
IS
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.check_contact(p_person_id,
p_contact_person_id,
p_contact_relationship_id,
p_session_date);
delete_a_person
DESCRIPTION
Validates whether a person can be deleted from the HR database.
It is assumed that weak_predel_validation and the other application
*_delete_person.*_predel_valdation procedures have been successfully
completed first.
Cascades are all performed according to the locking ladder.
NOTE
P_FORM_CALL is set to 'Y' if this procedure is called from a forms
module. In this case, the deletes are performed post-delete and a
row therefore may not exist in per_people_f (for this person_id).
For this reason the existance check will be ignored.
*/
--
PROCEDURE delete_a_person (p_person_id IN number,
p_form_call IN boolean,
p_session_date IN date)
IS
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.delete_person(p_person_id,
-- p_form_call,
p_session_date);
END delete_a_person;
people_default_deletes
DESCRIPTION
Delete routine for deleting information set up as default when people
are created. Used primarily for delete on PERPEEPI (Enter Person).
The strong_predel_validation should first be performed to ensure that
no additional info (apart from default) has been entered.
NOTE
See delete_a_person for p_form_call details. Further, p_form_call is
set to TRUE when this procedure is called from check_contact as
there is no need to check the existance of the contact.
*/
--
PROCEDURE people_default_deletes (p_person_id IN number,
p_form_call IN boolean)
IS
--
--
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.people_default_deletes(p_person_id);
END people_default_deletes;
applicant_default_deletes
DESCRIPTION
Delete routine for deleting information set up as default when
applicants are entered. Used primarily for delete on PERREAQE
(Applicant Quick Entry). The strong_predel_validation should first be
performed to ensure that no additional info (apart from default) has
been entered.
NOTE
See delete_a_person for p_form_call details.
*/
--
PROCEDURE applicant_default_deletes (p_person_id IN number,
p_form_call IN boolean)
IS
--
--
BEGIN
--
-- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
--
hr_person_internal.applicant_default_deletes(p_person_id);
END applicant_default_deletes;
select 'Y'
into p_test_func
from sys.dual
where exists(
select 'Future Person Type exists'
from per_people_f ppf
,per_person_types ppt
,per_startup_person_types pst
where ppf.person_type_id = ppt.person_type_id
and ppf.person_id = p_person_id
and ppf.business_group_id +0 = ppt.business_group_id+0
and ppf.business_group_id +0 = p_business_group_id
and (((p_effective_start_date < ppf.effective_start_date)
and p_check_all = 'Y')
or (p_effective_start_date = ppf.effective_start_date))
and ppt.system_person_type <> pst.system_person_type
and pst.system_person_type = p_system_person_type
union
select 'Future Person Type exists'
from per_periods_of_service pps
where pps.person_id = p_person_id
and p_effective_start_date < pps.date_start
union --fix for bug 6730008
select 'Future Person Type exists'
from per_periods_of_placement pps
where p_system_person_type='OTHER'
and pps.person_id = p_person_id
and p_effective_start_date < nvl(pps.actual_termination_date,p_effective_start_date)
);
select 'Y'
into p_test_func
from sys.dual
where exists
(
-- code change start for bug 3957689
select 'Previous Person type exists'
from per_all_people_f ppf ,
per_person_types ppt ,
per_startup_person_types pst ,
per_person_type_usages_f ptu
where ppf.person_id = p_person_id
and ppf.business_group_id +0= p_business_group_id
and ppf.business_group_id +0= ppt.business_group_id +0
and pst.system_person_type = p_system_person_type
and ppt.system_person_type <> pst.system_person_type
and ppf.person_id = ptu.person_id
and ptu.person_type_id = ppt.person_type_id
/* and p_effective_start_date between
ptu.effective_start_date and ptu.effective_end_date --- fix for bug 6161469 */
union
select 'Previous Person type exists'
from per_periods_of_service pps
where pps.person_id = p_person_id
and p_effective_start_date > nvl(pps.actual_termination_date,
p_effective_start_date)
union
select 'Previous Person type exists'
from per_periods_of_placement ppp
where ppp.person_id = p_person_id
and p_effective_start_date > nvl(ppp.actual_termination_date,
p_effective_start_date)); --fix for bug 5961371.
select 'Previous Person type exists'
from per_people_f ppf
,per_person_types ppt
,per_startup_person_types pst
where ppf.person_type_id = ppt.person_type_id
and ppf.person_id = p_person_id
and ppf.business_group_id +0= ppt.business_group_id +0
and ppf.business_group_id +0= p_business_group_id
and p_effective_start_date > ppf.effective_start_date
and ppt.system_person_type <> pst.system_person_type
and pst.system_person_type = p_system_person_type
union
select 'Previous Person type exists'
from per_periods_of_service pps
where pps.person_id = p_person_id
and p_effective_start_date > nvl(pps.actual_termination_date,
p_effective_start_date));
select 'Error : Primary address exists'
into v_dummy
from sys.dual
where exists (select 'address exists'
from per_addresses pa
where pa.person_id = p_person_id
and pa.business_group_id +0 = p_business_group_id
and (pa.address_id <> p_address_id
or p_address_id is null)
and pa.primary_flag = l_primary_flag
and (p_date_from between pa.date_from
and nvl(pa.date_to,p_end_of_time)
or nvl(p_date_to,p_end_of_time) between
pa.date_from and nvl(pa.date_to,p_end_of_time))
);