[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
do it could be used for both insert and update
13-Jul-95 TMathers 70.8 Added Message HR_7874_GARNISHMENT_IS_PAYEE
14-Jul-95 TMathers 70.9 Add check for mirror relationship.
11-Sep-95 SSDesai 70.10 Added check_beneficiary, check_dependent.
10-Oct-95 SSDesai 70.12 Added messages for check_beneficiary/dependent.
18-Jul-97 RKamiyam 70.13 Added per_information1 to 30 and known_as cols.
02-Mar-98 SKamatka 110.3 Bug #622399: Changed contact_only function.
Contact won't be deleted if it has relationship
with more than one person.
25-Nov-98 ASahay 110.4 Bug 768997 Modified if condition to check for
greater than 1 only
05-Feb-99 LSigrist 110.5 Updated cursors 'get_person', and 'c' with MLS
changes. Also, checked to ensure date formats
are release 11.5 compliant.
16-Feb-99 ASahay 115.3 Bug 820655 Added function multiple_contacts
to_check for multiple relationships
10-Apr-00 KSivagur 115.4 added parameters p_pre_name_adjunct and p_suffix
to the get_person_details procedure.
13-Mar-01 KSivagur 115.5 added parameter per_information_catergory.
21-Aug-01 adhunter 115.6 PTU changes to get person type from new function.
12-Oct-01 adhunter 115.7 1766066: Redid multiple contacts function.
14-Nov-02 MGettins 115.9 Added npw_number parameter to get_person_details.
15-NOV-02 MGettins 115.10 Added dbdrv lines.
04-DEC-02 PKakar 115.11 Added nocopy changes
25-JAN-03 DCasemor 115.12 Overloaded get_person_details so that
p_npw_number is not mandatory.
10-JUL-03 MGettins 115.13 Modified cursor in Contact_Only procedure as
part of fix for bug 3040059.
14-OCT-03 jpthomas 115.13 Modified the cursor to exclude the system person types
for the benefits.
21-JUN-04 adudekul 115.14 Bug 3648765. Fix to performance issues.
13-FEB-05 asgugupt 115.16 Overloaded get_person_details for bug no 4957699.
---------------------------------------------------------------------------*/
PROCEDURE get_person_details
(p_person_id IN NUMBER
,p_session_date IN DATE
,p_first_name IN OUT NOCOPY varchar2
,p_middle_names IN OUT NOCOPY varchar2
,p_pre_name_adjunct IN OUT NOCOPY varchar2
,p_suffix IN OUT NOCOPY varchar2
,p_title IN OUT NOCOPY varchar2
,p_sex IN OUT NOCOPY varchar2
,p_date_of_birth IN OUT NOCOPY DATE
,p_age IN OUT NOCOPY number
,p_employee_number IN OUT NOCOPY varchar2
,p_applicant_number IN OUT NOCOPY varchar2
,p_title_desc IN OUT NOCOPY varchar2
,p_national_identifier IN OUT NOCOPY VARCHAR2
,p_person_type_id IN OUT NOCOPY number
,p_user_person_type IN OUT NOCOPY varchar2
,p_system_person_type IN OUT NOCOPY varchar2
,p_current_employee_flag IN OUT NOCOPY varchar2
,p_current_applicant_flag IN OUT NOCOPY varchar2
,p_current_emp_or_apl_flag IN OUT NOCOPY varchar2
,p_registered_disabled_flag IN OUT NOCOPY varchar2
,p_attribute_category IN OUT NOCOPY varchar2
,p_attribute1 IN OUT NOCOPY varchar2
,p_attribute2 IN OUT NOCOPY varchar2
,p_attribute3 IN OUT NOCOPY varchar2
,p_attribute4 IN OUT NOCOPY varchar2
,p_attribute5 IN OUT NOCOPY varchar2
,p_attribute6 IN OUT NOCOPY varchar2
,p_attribute7 IN OUT NOCOPY varchar2
,p_attribute8 IN OUT NOCOPY varchar2
,p_attribute9 IN OUT NOCOPY varchar2
,p_attribute10 IN OUT NOCOPY varchar2
,p_attribute11 IN OUT NOCOPY varchar2
,p_attribute12 IN OUT NOCOPY varchar2
,p_attribute13 IN OUT NOCOPY varchar2
,p_attribute14 IN OUT NOCOPY varchar2
,p_attribute15 IN OUT NOCOPY varchar2
,p_attribute16 IN OUT NOCOPY varchar2
,p_attribute17 IN OUT NOCOPY varchar2
,p_attribute18 IN OUT NOCOPY varchar2
,p_attribute19 IN OUT NOCOPY varchar2
,p_attribute20 IN OUT NOCOPY varchar2
,p_attribute21 IN OUT NOCOPY varchar2
,p_attribute22 IN OUT NOCOPY varchar2
,p_attribute23 IN OUT NOCOPY varchar2
,p_attribute24 IN OUT NOCOPY varchar2
,p_attribute25 IN OUT NOCOPY varchar2
,p_attribute26 IN OUT NOCOPY varchar2
,p_attribute27 IN OUT NOCOPY varchar2
,p_attribute28 IN OUT NOCOPY varchar2
,p_attribute29 IN OUT NOCOPY varchar2
,p_attribute30 IN OUT NOCOPY varchar2
,p_comment_id IN OUT NOCOPY number
,p_contact_only IN OUT NOCOPY varchar2
,p_per_information_category IN OUT NOCOPY varchar2
,p_per_information1 IN OUT NOCOPY varchar2
,p_per_information2 IN OUT NOCOPY varchar2
,p_per_information3 IN OUT NOCOPY varchar2
,p_per_information4 IN OUT NOCOPY varchar2
,p_per_information5 IN OUT NOCOPY varchar2
,p_per_information6 IN OUT NOCOPY varchar2
,p_per_information7 IN OUT NOCOPY varchar2
,p_per_information8 IN OUT NOCOPY varchar2
,p_per_information9 IN OUT NOCOPY varchar2
,p_per_information10 IN OUT NOCOPY varchar2
,p_per_information11 IN OUT NOCOPY varchar2
,p_per_information12 IN OUT NOCOPY varchar2
,p_per_information13 IN OUT NOCOPY varchar2
,p_per_information14 IN OUT NOCOPY varchar2
,p_per_information15 IN OUT NOCOPY varchar2
,p_per_information16 IN OUT NOCOPY varchar2
,p_per_information17 IN OUT NOCOPY varchar2
,p_per_information18 IN OUT NOCOPY varchar2
,p_per_information19 IN OUT NOCOPY varchar2
,p_per_information20 IN OUT NOCOPY varchar2
,p_per_information21 IN OUT NOCOPY varchar2
,p_per_information22 IN OUT NOCOPY varchar2
,p_per_information23 IN OUT NOCOPY varchar2
,p_per_information24 IN OUT NOCOPY varchar2
,p_per_information25 IN OUT NOCOPY varchar2
,p_per_information26 IN OUT NOCOPY varchar2
,p_per_information27 IN OUT NOCOPY varchar2
,p_per_information28 IN OUT NOCOPY varchar2
,p_per_information29 IN OUT NOCOPY varchar2
,p_per_information30 IN OUT NOCOPY varchar2
,p_known_as IN OUT NOCOPY varchar2
) is
l_npw_number per_all_people_f.npw_number%TYPE;
select per.last_name
,per.first_name
,per.middle_names
,per.pre_name_adjunct
,per.suffix
,per.title
,per.sex
,per.date_of_birth
,trunc(months_between(p_session_date, per.date_of_birth)/12) age
,per.employee_number
,per.applicant_number
,per.full_name
,t.meaning title_desc
,per.national_identifier
,per.person_type_id
,pttl.user_person_type
,pt.system_person_type
,per.current_employee_flag
,per.current_applicant_flag
,per.current_emp_or_apl_flag
,per.registered_disabled_flag
,per.attribute_category
,per.attribute1
,per.attribute2
,per.attribute3
,per.attribute4
,per.attribute5
,per.attribute6
,per.attribute7
,per.attribute8
,per.attribute9
,per.attribute10
,per.attribute11
,per.attribute12
,per.attribute13
,per.attribute14
,per.attribute15
,per.attribute16
,per.attribute17
,per.attribute18
,per.attribute19
,per.attribute20
,per.attribute21
,per.attribute22
,per.attribute23
,per.attribute24
,per.attribute25
,per.attribute26
,per.attribute27
,per.attribute28
,per.attribute29
,per.attribute30
,per.comment_id
,per.per_information_category
,per.per_information1
,per.per_information2
,per.per_information3
,per.per_information4
,per.per_information5
,per.per_information6
,per.per_information7
,per.per_information8
,per.per_information9
,per.per_information10
,per.per_information11
,per.per_information12
,per.per_information13
,per.per_information14
,per.per_information15
,per.per_information16
,per.per_information17
,per.per_information18
,per.per_information19
,per.per_information20
,per.per_information21
,per.per_information22
,per.per_information23
,per.per_information24
,per.per_information25
,per.per_information26
,per.per_information27
,per.per_information28
,per.per_information29
,per.per_information30
,per.known_as
-- fix for bug 4957699 starts here.
,per.date_of_death
-- fix for bug 4957699 starts here.
,npw_number
from hr_lookups t
,per_person_types_tl pttl
,per_person_types pt
,per_all_people_f per
where pt.person_type_id = pttl.person_type_id
and t.lookup_type(+) = 'TITLE'
and t.lookup_code(+) = per.title
and per.person_type_id = pt.person_type_id
and per.person_id = p_person_id
and p_session_date between
per.effective_start_date and per.effective_end_date
and pttl.LANGUAGE = userenv('LANG');
select count(*)
from per_contact_relationships
where contact_person_id = p_person_id;
select 'Y'
from per_all_people_f papf, per_person_types ppt
where papf.person_id = p_person_id
and papf.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'OTHER'
and papf.effective_end_date = hr_general.end_of_time;*/
SELECT 'Y'
FROM per_person_type_usages_f ptu,
per_person_types ppt
WHERE ( ppt.system_person_type like '%EMP%'
or ppt.system_person_type like '%APL%'
or ppt.system_person_type like '%CWK%')
AND ppt.person_type_Id = ptu.person_type_id
AND ptu.person_id = p_person_id
AND ptu.effective_end_Date = hr_general.end_of_time;
select pt.person_type_id
, pttl.user_person_type
, pt.system_person_type
from per_person_types_tl pttl,
per_person_types pt
where pt.system_person_type = 'OTHER'
and pt.default_flag = 'Y'
and pt.active_flag = 'Y'
and pt.business_group_id = p_business_group_id
and pt.person_type_id = pttl.person_type_id
and pttl.LANGUAGE = userenv('LANG');
select a.application_short_name
from fnd_application a
, fnd_form f
where a.application_id = f.application_id
and f.form_name = p_form_name;
select 'Y'
from per_addresses pa
where pa.person_id = p_contact_id;
select pcr1.contact_relationship_id
, pcr1.contact_person_id
from per_contact_relationships pcr1
where exists (select 1
from per_contact_relationships pcr2
where contact_relationship_id = p_contact_relationship_id
and pcr1.contact_person_id = pcr2.person_id
);
is select '1'
from pay_personal_payment_methods_f ppm
, per_assignments_f paf
, per_contact_relationships pcr
, fnd_sessions f
where ppm.payee_type = 'P'
and ppm.payee_id = l_contact_id
and ppm.assignment_id = paf.assignment_id
and paf.person_id = pcr.person_id
and pcr.contact_relationship_id = l_contact_relationship_id
and f.session_id = userenv('sessionid')
and f.effective_date between
ppm.effective_start_date and ppm.effective_end_date;
if p_mode = 'D' -- delete message
then
hr_utility.set_message(801,'PAY_GARNISHOR_PAYMENT_METHOD');
if p_mode = 'D' -- delete message
then
hr_utility.set_message(801,'PAY_GARNISHOR_PAYMENT_METHOD');
select 'Y'
from
fnd_sessions fnd,
per_assignments_f asg,
pay_element_entries_f ent,
ben_beneficiaries_f ben
where ben.source_id = p_contact_id
and ben.source_type = 'P'
and ben.element_entry_id = ent.element_entry_id
and ent.assignment_id = asg.assignment_id
and asg.person_id + 0 = p_person_id
and fnd.session_id = userenv('sessionid')
--
-- check that the contact is currently designated as a beneficiary
-- or is designated as a beneficiary in the future.
--
and (fnd.effective_date between asg.effective_start_date
and asg.effective_end_date
or fnd.effective_date < asg.effective_start_date)
and (fnd.effective_date between ent.effective_start_date
and ent.effective_end_date
or fnd.effective_date < ent.effective_start_date)
and (fnd.effective_date between ben.effective_start_date
and ben.effective_end_date
or fnd.effective_date < ent.effective_start_date);
select 'Y'
from
fnd_sessions fnd,
ben_covered_dependents_f dep
where dep.contact_relationship_id = p_contact_relationship_id
and fnd.session_id = userenv('sessionid')
and (fnd.effective_date between dep.effective_start_date
and dep.effective_end_date
or fnd.effective_date < dep.effective_start_date);