[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(person_id) FROM per_addresses
WHERE person_id = p_person_id
AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
SELECT descriptive_flex_context_name
FROM fnd_descr_flex_contexts_vl
WHERE application_id = 800
AND descriptive_flexfield_name = 'Address Structure'
AND descriptive_flex_context_code = p_style
AND enabled_flag = 'Y'
AND descriptive_flex_context_code NOT IN ('Global Data Elements','GENERIC')
AND (hr_general.chk_geocodes_installed = 'Y'
OR descriptive_flex_context_code NOT IN ('CA','US'));
SELECT
SUBSTRB(hr_general.decode_lookup('ADDRESS_TYPE',address_type),1,80) address_type_meaning,
style,
primary_flag,
address_line1,
address_line2,
address_line3,
country,
postal_code,
region_1,
region_2,
region_3,
telephone_number_1,
telephone_number_2,
telephone_number_3,
town_or_city,
add_information13,
add_information14,
add_information15,
add_information16,
add_information17,
add_information18,
add_information19,
add_information20,
date_from,
date_to
FROM per_addresses
WHERE person_id = p_person_id
AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
SELECT COUNT(parent_id) FROM per_phones
WHERE parent_id = p_person_id
AND parent_table = 'PER_ALL_PEOPLE_F'
AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
SELECT
SUBSTRB(hr_general.decode_lookup('PHONE_TYPE',phone_type),1,80) phone_type_meaning,
phone_number,
date_from,
date_to
FROM per_phones
WHERE parent_id = p_person_id
AND parent_table = 'PER_ALL_PEOPLE_F'
AND p_effective_date BETWEEN date_from AND NVL(date_to,p_effective_date);
SELECT COUNT(contact_relationship_id) FROM per_contact_extra_info_f
WHERE contact_relationship_id = p_contact_relationship_id
AND information_type LIKE p_information_type_group || '%'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
p_last_update_date OUT NOCOPY DATE,
p_last_updated_by OUT NOCOPY NUMBER,
p_last_update_login OUT NOCOPY NUMBER,
p_created_by OUT NOCOPY NUMBER,
p_creation_date OUT NOCOPY DATE,
p_request_id OUT NOCOPY NUMBER,
p_program_application_id OUT NOCOPY NUMBER,
p_program_id OUT NOCOPY NUMBER,
p_program_update_date OUT NOCOPY DATE) IS
--
CURSOR cel_extra_info_detail IS
SELECT
pceif.contact_extra_info_id,
pceif.information_type,
pcitv.description,
pceif.cei_information_category,
pceif.cei_information1,
pceif.cei_information2,
pceif.cei_information3,
pceif.cei_information4,
pceif.cei_information5,
pceif.cei_information6,
pceif.cei_information7,
pceif.cei_information8,
pceif.cei_information9,
pceif.cei_information10,
pceif.cei_information11,
pceif.cei_information12,
pceif.cei_information13,
pceif.cei_information14,
pceif.cei_information15,
pceif.cei_information16,
pceif.cei_information17,
pceif.cei_information18,
pceif.cei_information19,
pceif.cei_information20,
pceif.cei_information21,
pceif.cei_information22,
pceif.cei_information23,
pceif.cei_information24,
pceif.cei_information25,
pceif.cei_information26,
pceif.cei_information27,
pceif.cei_information28,
pceif.cei_information29,
pceif.cei_information30,
pceif.effective_start_date,
pceif.effective_end_date,
pceif.cei_attribute_category,
pceif.cei_attribute1,
pceif.cei_attribute2,
pceif.cei_attribute3,
pceif.cei_attribute4,
pceif.cei_attribute5,
pceif.cei_attribute6,
pceif.cei_attribute7,
pceif.cei_attribute8,
pceif.cei_attribute9,
pceif.cei_attribute10,
pceif.cei_attribute11,
pceif.cei_attribute12,
pceif.cei_attribute13,
pceif.cei_attribute14,
pceif.cei_attribute15,
pceif.cei_attribute16,
pceif.cei_attribute17,
pceif.cei_attribute18,
pceif.cei_attribute19,
pceif.cei_attribute20,
pceif.object_version_number,
pceif.last_update_date,
pceif.last_updated_by,
pceif.last_update_login,
pceif.created_by,
pceif.creation_date,
pceif.request_id,
pceif.program_application_id,
pceif.program_id,
pceif.program_update_date
FROM
per_contact_extra_info_f pceif,
per_contact_info_types_vl pcitv
WHERE pceif.contact_relationship_id = p_contact_relationship_id
AND pceif.information_type LIKE p_information_type_group || '%'
AND p_effective_date BETWEEN pceif.effective_start_date AND pceif.effective_end_date
AND pceif.information_type = pcitv.information_type
-- /* Added by keyazawa at 2003/10/02 for bugfix 3047148. */
and exists(
select null
from per_contact_relationships pcr,
per_business_groups_perf pbg,
per_info_type_security_cit_v pitsc
-- /* This relation is to fetch legislation code, ideally this should be parameter. */
where pcr.contact_relationship_id = pceif.contact_relationship_id
and pbg.business_group_id = pcr.business_group_id
-- /* This sql should be called by EBS, ideally these should be parameter.
-- fnd_global is better than fnd_profile.value. */
and pitsc.application_id = fnd_global.resp_appl_id
and pitsc.responsibility_id = fnd_global.resp_id
and pitsc.information_type = pceif.information_type
and pitsc.legislation_code = nvl(pbg.legislation_code,pitsc.legislation_code));
p_last_update_date := celrec_extra_info_detail.last_update_date;
p_last_updated_by := celrec_extra_info_detail.last_updated_by;
p_last_update_login := celrec_extra_info_detail.last_update_login;
p_program_update_date := celrec_extra_info_detail.program_update_date;
p_last_update_date OUT NOCOPY DATE,
p_last_updated_by OUT NOCOPY NUMBER,
p_last_update_login OUT NOCOPY NUMBER,
p_created_by OUT NOCOPY NUMBER,
p_creation_date OUT NOCOPY DATE,
p_request_id OUT NOCOPY NUMBER,
p_program_application_id OUT NOCOPY NUMBER,
p_program_id OUT NOCOPY NUMBER,
p_program_update_date OUT NOCOPY DATE,
p_extra_info_count OUT NOCOPY NUMBER) IS
--
l_extra_info_count NUMBER := extra_info_count(p_contact_relationship_id, p_effective_date, p_information_type_group);
p_last_update_date := NULL;
p_last_updated_by := NULL;
p_last_update_login := NULL;
p_program_update_date := NULL;
p_last_update_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_created_by => p_created_by,
p_creation_date => p_creation_date,
p_request_id => p_request_id,
p_program_application_id => p_program_application_id,
p_program_id => p_program_id,
p_program_update_date => p_program_update_date);
SELECT assignment_id FROM per_all_assignments_f
WHERE person_id = p_person_id
AND primary_flag = 'Y'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;