[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pei.pei_information5
FROM per_people_extra_info pei
WHERE pei.information_type = 'PQP_OSS_PERSON_DETAILS'
AND pei.pei_information_category = 'PQP_OSS_PERSON_DETAILS'
AND pei.person_id = c_person_id;
l_last_update_date Date;
If p_action = 'UPDATE' AND
Not l_return_flag THEN
Hr_Utility.set_location('..p_action : '||p_action,9 );
Pqp_Hrtca_Integration.Update_Address_HR_To_TCA
(p_business_group_id => p_addr_rec_new.business_group_id
,p_person_id => p_addr_rec_new.person_id
,p_party_id => p_addr_rec_new.party_id
,p_address_id => p_addr_rec_new.address_id
,p_effective_date => p_effective_date
,p_per_addr_rec_new => p_addr_rec_new
,p_per_addr_rec_old => p_addr_rec_old
-- TCA
,p_party_type => 'PERSON'
,p_action => p_action
,p_status => 'A'
-- In Out Variables
,p_location_id => l_location_id
,p_party_site_id => l_party_site_id
,p_last_update_date => l_hz_loc_upd_dt
,p_party_site_ovn => l_party_site_ovn
,p_location_ovn => l_location_ovn
,p_rowid => l_hz_loc_rowid
-- Out Variables
,p_return_status => l_return_status
,p_msg_data => l_msg_data
);
ELSIF p_action = 'INSERT' AND
Not l_return_flag THEN
Hr_Utility.set_location('..p_action : '||p_action,9 );
,p_last_update_date => l_last_update_date
,p_party_site_ovn => l_party_site_ovn
,p_location_ovn => l_location_ovn
,p_rowid => l_rowid
-- Out Variables
,p_return_status => l_return_status
,p_msg_data => l_msg_data
);
procedure insert_row(p_row_id in out nocopy VARCHAR2
,p_address_id in out nocopy NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_from DATE
,p_primary_flag VARCHAR2
,p_style VARCHAR2
,p_address_line1 VARCHAR2
,p_address_line2 VARCHAR2
,p_address_line3 VARCHAR2
,p_address_type VARCHAR2
,p_comments VARCHAR2
,p_country VARCHAR2
,p_date_to DATE
,p_postal_code VARCHAR2
,p_region_1 VARCHAR2
,p_region_2 VARCHAR2
,p_region_3 VARCHAR2
,p_telephone_number_1 VARCHAR2
,p_telephone_number_2 VARCHAR2
,p_telephone_number_3 VARCHAR2
,p_town_or_city VARCHAR2
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_addr_attribute_category VARCHAR2
,p_addr_attribute1 VARCHAR2
,p_addr_attribute2 VARCHAR2
,p_addr_attribute3 VARCHAR2
,p_addr_attribute4 VARCHAR2
,p_addr_attribute5 VARCHAR2
,p_addr_attribute6 VARCHAR2
,p_addr_attribute7 VARCHAR2
,p_addr_attribute8 VARCHAR2
,p_addr_attribute9 VARCHAR2
,p_addr_attribute10 VARCHAR2
,p_addr_attribute11 VARCHAR2
,p_addr_attribute12 VARCHAR2
,p_addr_attribute13 VARCHAR2
,p_addr_attribute14 VARCHAR2
,p_addr_attribute15 VARCHAR2
,p_addr_attribute16 VARCHAR2
,p_addr_attribute17 VARCHAR2
,p_addr_attribute18 VARCHAR2
,p_addr_attribute19 VARCHAR2
,p_addr_attribute20 VARCHAR2
-- ***** Start new code for bug 2711964 **************
,p_add_information13 VARCHAR2
,p_add_information14 VARCHAR2
,p_add_information15 VARCHAR2
,p_add_information16 VARCHAR2
-- ***** End new code for bug 2711964 ***************
,p_add_information17 VARCHAR2
,p_add_information18 VARCHAR2
,p_add_information19 VARCHAR2
,p_add_information20 VARCHAR2
,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
) is
--
-- Local Variables
--
l_default_primary VARCHAR2(1);
per_addresses_pkg.insert_row(p_row_id
,p_address_id
,p_business_group_id
,p_person_id
,p_date_from
,p_primary_flag
,p_style
,p_address_line1
,p_address_line2
,p_address_line3
,p_address_type
,p_comments
,p_country
,p_date_to
,p_postal_code
,p_region_1
,p_region_2
,p_region_3
,p_telephone_number_1
,p_telephone_number_2
,p_telephone_number_3
,p_town_or_city
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_addr_attribute_category
,p_addr_attribute1
,p_addr_attribute2
,p_addr_attribute3
,p_addr_attribute4
,p_addr_attribute5
,p_addr_attribute6
,p_addr_attribute7
,p_addr_attribute8
,p_addr_attribute9
,p_addr_attribute10
,p_addr_attribute11
,p_addr_attribute12
,p_addr_attribute13
,p_addr_attribute14
,p_addr_attribute15
,p_addr_attribute16
,p_addr_attribute17
,p_addr_attribute18
,p_addr_attribute19
,p_addr_attribute20
-- ***** Start new code for bug 2711964 **************
,p_add_information13
,p_add_information14
,p_add_information15
,p_add_information16
-- ***** End new code for bug 2711964 ***************
,p_add_information17
,p_add_information18
,p_add_information19
,p_add_information20
,p_end_of_time
,l_default_primary
);
end insert_row;
procedure insert_row(p_row_id in out nocopy VARCHAR2
,p_address_id in out nocopy NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_from DATE
,p_primary_flag VARCHAR2
,p_style VARCHAR2
,p_address_line1 VARCHAR2
,p_address_line2 VARCHAR2
,p_address_line3 VARCHAR2
,p_address_type VARCHAR2
,p_comments VARCHAR2
,p_country VARCHAR2
,p_date_to DATE
,p_postal_code VARCHAR2
,p_region_1 VARCHAR2
,p_region_2 VARCHAR2
,p_region_3 VARCHAR2
,p_telephone_number_1 VARCHAR2
,p_telephone_number_2 VARCHAR2
,p_telephone_number_3 VARCHAR2
,p_town_or_city VARCHAR2
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_addr_attribute_category VARCHAR2
,p_addr_attribute1 VARCHAR2
,p_addr_attribute2 VARCHAR2
,p_addr_attribute3 VARCHAR2
,p_addr_attribute4 VARCHAR2
,p_addr_attribute5 VARCHAR2
,p_addr_attribute6 VARCHAR2
,p_addr_attribute7 VARCHAR2
,p_addr_attribute8 VARCHAR2
,p_addr_attribute9 VARCHAR2
,p_addr_attribute10 VARCHAR2
,p_addr_attribute11 VARCHAR2
,p_addr_attribute12 VARCHAR2
,p_addr_attribute13 VARCHAR2
,p_addr_attribute14 VARCHAR2
,p_addr_attribute15 VARCHAR2
,p_addr_attribute16 VARCHAR2
,p_addr_attribute17 VARCHAR2
,p_addr_attribute18 VARCHAR2
,p_addr_attribute19 VARCHAR2
,p_addr_attribute20 VARCHAR2
-- ***** Start new code for bug 2711964 **************
,p_add_information13 VARCHAR2
,p_add_information14 VARCHAR2
,p_add_information15 VARCHAR2
,p_add_information16 VARCHAR2
-- ***** End new code for bug 2711964 ***************
,p_add_information17 VARCHAR2
,p_add_information18 VARCHAR2
,p_add_information19 VARCHAR2
,p_add_information20 VARCHAR2
,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
,p_default_primary IN OUT NOCOPY VARCHAR2
) is
--
cursor c1 is select per_addresses_s.nextval
from sys.dual;
cursor c2 is select rowid
from per_addresses
where address_id = p_address_id;
cursor c3 is select *
from per_addresses
where address_id = p_address_id;
select max(party_id)
from per_all_people_f
where person_id = p_person_id;
SELECT fpi.status
FROM fnd_product_installations fpi,
per_people_f ppf,
per_business_groups pbg
WHERE fpi.application_id = 801
AND p_person_id = ppf.person_id
AND p_business_group_id = pbg.business_group_id
AND pbg.legislation_code = 'US'
AND p_person_id = ppf.person_id
AND ppf.current_employee_flag = 'Y'
AND p_style = 'US'
AND p_primary_flag = 'Y';
SELECT pbg.legislation_code
from per_business_groups pbg
where p_business_group_id = pbg.business_group_id;
hr_utility.set_location('Insert_row',1);
insert into per_addresses(
address_id
,business_group_id
,person_id
,date_from
,primary_flag
,style
,address_line1
,address_line2
,address_line3
,address_type
,comments
,country
,date_to
,postal_code
,region_1
,region_2
,region_3
,telephone_number_1
,telephone_number_2
,telephone_number_3
,town_or_city
,request_id
,program_application_id
,program_id
,program_update_date
,addr_attribute_category
,addr_attribute1
,addr_attribute2
,addr_attribute3
,addr_attribute4
,addr_attribute5
,addr_attribute6
,addr_attribute7
,addr_attribute8
,addr_attribute9
,addr_attribute10
,addr_attribute11
,addr_attribute12
,addr_attribute13
,addr_attribute14
,addr_attribute15
,addr_attribute16
,addr_attribute17
,addr_attribute18
,addr_attribute19
,addr_attribute20
-- ***** Start new code for bug 2711964 **************
,add_information13
,add_information14
,add_information15
,add_information16
-- ***** End new code for bug 2711964 ***************
,add_information17
,add_information18
,add_information19
,add_information20
,party_id
)
values
(p_address_id
,p_business_group_id
,p_person_id
,p_date_from
,p_primary_flag
,p_style
,p_address_line1
,p_address_line2
,p_address_line3
,p_address_type
,p_comments
,p_country
,p_date_to
,p_postal_code
,p_region_1
,p_region_2
,p_region_3
,p_telephone_number_1
,p_telephone_number_2
,p_telephone_number_3
,p_town_or_city
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_addr_attribute_category
,p_addr_attribute1
,p_addr_attribute2
,p_addr_attribute3
,p_addr_attribute4
,p_addr_attribute5
,p_addr_attribute6
,p_addr_attribute7
,p_addr_attribute8
,p_addr_attribute9
,p_addr_attribute10
,p_addr_attribute11
,p_addr_attribute12
,p_addr_attribute13
,p_addr_attribute14
,p_addr_attribute15
,p_addr_attribute16
,p_addr_attribute17
,p_addr_attribute18
,p_addr_attribute19
,p_addr_attribute20
-- ***** Start new code for bug 2711964 **************
,p_add_information13
,p_add_information14
,p_add_information15
,p_add_information16
-- ***** End new code for bug 2711964 ***************
,p_add_information17
,p_add_information18
,p_add_information19
,p_add_information20
,l_party_id
);
,p_action => 'INSERT'
,p_effective_date => p_date_from
);
end insert_row;
procedure delete_row(p_row_id VARCHAR2) is
--
-- local variables
--
l_person_id NUMBER;
per_addresses_pkg.delete_row(p_row_id
,l_person_id
,l_business_group_id
,l_end_of_time
,l_default_primary);
end delete_row;
procedure delete_row(p_row_id VARCHAR2
,p_person_id NUMBER
,p_business_group_id NUMBER
,p_end_of_time DATE
,p_default_primary IN OUT NOCOPY VARCHAR2) is
--
begin
delete from per_addresses pa
where pa.rowid = chartorowid(p_row_id);
end delete_row;
cursor addr is select *
from per_addresses
where rowid = chartorowid(p_row_id)
for update nowait;
procedure update_row(p_row_id VARCHAR2
,p_address_id NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_from DATE
,p_primary_flag VARCHAR2
,p_style VARCHAR2
,p_address_line1 VARCHAR2
,p_address_line2 VARCHAR2
,p_address_line3 VARCHAR2
,p_address_type VARCHAR2
,p_comments VARCHAR2
,p_country VARCHAR2
,p_date_to DATE
,p_postal_code VARCHAR2
,p_region_1 VARCHAR2
,p_region_2 VARCHAR2
,p_region_3 VARCHAR2
,p_telephone_number_1 VARCHAR2
,p_telephone_number_2 VARCHAR2
,p_telephone_number_3 VARCHAR2
,p_town_or_city VARCHAR2
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_addr_attribute_category VARCHAR2
,p_addr_attribute1 VARCHAR2
,p_addr_attribute2 VARCHAR2
,p_addr_attribute3 VARCHAR2
,p_addr_attribute4 VARCHAR2
,p_addr_attribute5 VARCHAR2
,p_addr_attribute6 VARCHAR2
,p_addr_attribute7 VARCHAR2
,p_addr_attribute8 VARCHAR2
,p_addr_attribute9 VARCHAR2
,p_addr_attribute10 VARCHAR2
,p_addr_attribute11 VARCHAR2
,p_addr_attribute12 VARCHAR2
,p_addr_attribute13 VARCHAR2
,p_addr_attribute14 VARCHAR2
,p_addr_attribute15 VARCHAR2
,p_addr_attribute16 VARCHAR2
,p_addr_attribute17 VARCHAR2
,p_addr_attribute18 VARCHAR2
,p_addr_attribute19 VARCHAR2
,p_addr_attribute20 VARCHAR2
,p_add_information17 VARCHAR2
,p_add_information18 VARCHAR2
,p_add_information19 VARCHAR2
,p_add_information20 VARCHAR2
,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
) is
--
-- Local Variables
--
l_default_primary VARCHAR2(1);
hr_utility.set_location('update_row',1);
PER_ADDRESSES_PKG.update_row(p_row_id
,p_address_id
,p_business_group_id
,p_person_id
,p_date_from
,p_primary_flag
,p_style
,p_address_line1
,p_address_line2
,p_address_line3
,p_address_type
,p_comments
,p_country
,p_date_to
,p_postal_code
,p_region_1
,p_region_2
,p_region_3
,p_telephone_number_1
,p_telephone_number_2
,p_telephone_number_3
,p_town_or_city
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,p_addr_attribute_category
,p_addr_attribute1
,p_addr_attribute2
,p_addr_attribute3
,p_addr_attribute4
,p_addr_attribute5
,p_addr_attribute6
,p_addr_attribute7
,p_addr_attribute8
,p_addr_attribute9
,p_addr_attribute10
,p_addr_attribute11
,p_addr_attribute12
,p_addr_attribute13
,p_addr_attribute14
,p_addr_attribute15
,p_addr_attribute16
,p_addr_attribute17
,p_addr_attribute18
,p_addr_attribute19
,p_addr_attribute20
,p_add_information17
,p_add_information18
,p_add_information19
,p_add_information20
,p_end_of_time
,l_default_primary
);
end update_row;
procedure update_row(p_row_id VARCHAR2
,p_address_id NUMBER
,p_business_group_id NUMBER
,p_person_id NUMBER
,p_date_from DATE
,p_primary_flag VARCHAR2
,p_style VARCHAR2
,p_address_line1 VARCHAR2
,p_address_line2 VARCHAR2
,p_address_line3 VARCHAR2
,p_address_type VARCHAR2
,p_comments VARCHAR2
,p_country VARCHAR2
,p_date_to DATE
,p_postal_code VARCHAR2
,p_region_1 VARCHAR2
,p_region_2 VARCHAR2
,p_region_3 VARCHAR2
,p_telephone_number_1 VARCHAR2
,p_telephone_number_2 VARCHAR2
,p_telephone_number_3 VARCHAR2
,p_town_or_city VARCHAR2
,p_request_id NUMBER
,p_program_application_id NUMBER
,p_program_id NUMBER
,p_program_update_date DATE
,p_addr_attribute_category VARCHAR2
,p_addr_attribute1 VARCHAR2
,p_addr_attribute2 VARCHAR2
,p_addr_attribute3 VARCHAR2
,p_addr_attribute4 VARCHAR2
,p_addr_attribute5 VARCHAR2
,p_addr_attribute6 VARCHAR2
,p_addr_attribute7 VARCHAR2
,p_addr_attribute8 VARCHAR2
,p_addr_attribute9 VARCHAR2
,p_addr_attribute10 VARCHAR2
,p_addr_attribute11 VARCHAR2
,p_addr_attribute12 VARCHAR2
,p_addr_attribute13 VARCHAR2
,p_addr_attribute14 VARCHAR2
,p_addr_attribute15 VARCHAR2
,p_addr_attribute16 VARCHAR2
,p_addr_attribute17 VARCHAR2
,p_addr_attribute18 VARCHAR2
,p_addr_attribute19 VARCHAR2
,p_addr_attribute20 VARCHAR2
,p_add_information17 VARCHAR2
,p_add_information18 VARCHAR2
,p_add_information19 VARCHAR2
,p_add_information20 VARCHAR2
,p_end_of_time DATE DEFAULT to_date('31-12-4712','DD-MM-YYYY')
,p_default_primary IN OUT NOCOPY VARCHAR2
) is
/* Need to check that US payroll is installed.
This will now be checked in default_tax_with_validation_package */
--
/*
CURSOR get_install_info IS
SELECT fpi.status
FROM fnd_product_installations fpi,
per_people_f ppf,
per_business_groups pbg
WHERE fpi.application_id = 801
AND p_person_id = ppf.person_id
AND ppf.current_employee_flag = 'Y'
AND p_business_group_id = pbg.business_group_id
AND pbg.legislation_code = 'US'
AND p_style = 'US'
AND p_primary_flag = 'Y';
SELECT pbg.legislation_code
from per_business_groups pbg
where p_business_group_id = pbg.business_group_id;
select *
from per_addresses
where rowid = chartorowid(p_row_id);
update per_addresses pa set
pa.address_id = p_address_id
,pa.business_group_id = p_business_group_id
,pa.person_id = p_person_id
,pa.date_from = p_date_from
,pa.primary_flag = p_primary_flag
,pa.style = p_style
,pa.address_line1 = p_address_line1
,pa.address_line2 = p_address_line2
,pa.address_line3 = p_address_line3
,pa.address_type = p_address_type
,pa.comments = p_comments
,pa.country = p_country
,pa.date_to = p_date_to
,pa.postal_code = p_postal_code
,pa.region_1 = p_region_1
,pa.region_2 = p_region_2
,pa.region_3 = p_region_3
,pa.telephone_number_1 = p_telephone_number_1
,pa.telephone_number_2 = p_telephone_number_2
,pa.telephone_number_3 = p_telephone_number_3
,pa.town_or_city = p_town_or_city
,pa.request_id = p_request_id
,pa.program_application_id = p_program_application_id
,pa.program_id = p_program_id
,pa.program_update_date = p_program_update_date
,pa.addr_attribute_category = p_addr_attribute_category
,pa.addr_attribute1 = p_addr_attribute1
,pa.addr_attribute2 = p_addr_attribute2
,pa.addr_attribute3 = p_addr_attribute3
,pa.addr_attribute4 = p_addr_attribute4
,pa.addr_attribute5 = p_addr_attribute5
,pa.addr_attribute6 = p_addr_attribute6
,pa.addr_attribute7 = p_addr_attribute7
,pa.addr_attribute8 = p_addr_attribute8
,pa.addr_attribute9 = p_addr_attribute9
,pa.addr_attribute10 = p_addr_attribute10
,pa.addr_attribute11 = p_addr_attribute11
,pa.addr_attribute12 = p_addr_attribute12
,pa.addr_attribute13 = p_addr_attribute13
,pa.addr_attribute14 = p_addr_attribute14
,pa.addr_attribute15 = p_addr_attribute15
,pa.addr_attribute16 = p_addr_attribute16
,pa.addr_attribute17 = p_addr_attribute17
,pa.addr_attribute18 = p_addr_attribute18
,pa.addr_attribute19 = p_addr_attribute19
,pa.addr_attribute20 = p_addr_attribute20
,pa.add_information17 = p_add_information17
,pa.add_information18 = p_add_information18
,pa.add_information19 = p_add_information19
,pa.add_information20 = p_add_information20
where pa.rowid = chartorowid(p_row_id);
,p_action => 'UPDATE'
,p_effective_date => p_date_from
);
end update_row;
select 'Y'
from per_addresses pa
, fnd_sessions fs
where pa.business_group_id + 0 = p_business_group_id
and pa.person_id = p_person_id
and pa.primary_flag = 'Y'
and fs.session_id(+) = userenv('sessionid')
and nvl(fs.effective_date,sysdate) between pa.date_from
and nvl(pa.date_to,p_end_of_time);
select 'Y'
from per_addresses pa1
where pa1.primary_flag = 'N'
and pa1.person_id = p_person_id
and not exists (select 'x'
from per_addresses pa2
where pa2.person_id = pa1.person_id
and pa2.primary_flag = 'Y'
and pa2.address_id <> pa1.address_id
and pa2.date_from <=pa1.date_from
and nvl(pa2.date_to, p_end_of_time) >=
nvl(pa1.date_to, p_end_of_time));
select descriptive_flex_context_name, descriptive_flex_context_code
from fnd_descr_flex_contexts_vl
where (descriptive_flex_context_code = p_legislation_code
or (p_legislation_code = descriptive_flex_context_code
and p_legislation_code in ('CA','US')
and l_geocodes_installed = 'Y'))
and descriptive_flexfield_name = 'Address Structure'
and application_id = 800 -- bug fix 3648688.
and enabled_flag = 'Y'
;
select descriptive_flex_context_name,descriptive_flex_context_code
from fnd_descr_flex_contexts_vl
where substr(descriptive_flex_context_code,1,2)= p_legislation_code
and descriptive_flexfield_name = 'Address Structure'
and application_id = 800 -- bug fix 3648688.
and enabled_flag = 'Y';
select ft.territory_short_name
into p_default_country
from fnd_territories_vl ft
, fnd_descr_flex_contexts fdfc
where ft.territory_code = p_legislation_code
and fdfc.descriptive_flex_context_code = ft.territory_code
and fdfc.descriptive_flexfield_name ='Address Structure'
and fdfc.application_id = 800 -- bug fix 3648688
and fdfc.enabled_flag = 'Y';
select 'Y'
from per_addresses pa
where pa.person_id = p_person_id;
select 'Y'
from per_addresses pa
where person_id = p_person_id
and date_from = l_date_from
and primary_flag = 'Y';
select 'Y'
from per_addresses pa
where person_id = p_person_id
and primary_flag = 'Y';
select min(date_from)
from per_addresses
where person_id = p_person_id;
select effective_start_date
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.payroll_id is not null
and asg.assignment_type ='E' -- fix for bug14248791
order by effective_start_date;
select date_to
from per_addresses pa
where date_from = l_date_from
and person_id = p_person_id
and primary_flag = 'Y';
select date_from,
date_to
from per_addresses pa
where person_id = p_person_id
and primary_flag = 'Y'
order by date_from;
select 'Y'
from per_addresses pa
where person_id = p_person_id
and primary_flag = 'Y'
and exists (select 'Y'
from per_addresses pa2
where pa.person_id = pa2.person_id
and pa.address_id <> pa2.address_id
and pa2.primary_flag = 'Y'
and ((pa2.date_from between pa.date_from and
nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY')))
or (pa.date_from between pa2.date_from
and nvl(pa2.date_to,to_date('31-12-4712','DD-MM-YYYY')) )
));
select 'Y'
from per_person_type_usages_f p, per_person_types t
where p.person_id = p_person_id
and p.person_type_id = t.person_type_id
and t.system_person_type in ('EMP','CWK')
and nvl(date1,to_date('31-12-4712','DD-MM-YYYY')) between effective_start_date and effective_end_date-1;
select effective_start_date, effective_end_date
from per_person_type_usages_f p, per_person_types t
where p.person_id = p_person_id
and p.person_type_id = t.person_type_id
and t.system_person_type in ('EMP','CWK')
and effective_start_date > nvl(date1,to_date('31-12-4712','DD-MM-YYYY')) ;
select 1
from per_addresses pa
where pa.address_type is not null
and pa.person_id = p_person_id
and exists( select 1
from per_addresses pa2
where pa2.address_id <> pa.address_id
and pa2.address_type is not null
and pa.address_type = pa2.address_type
and pa.person_id = pa2.person_id
and ((pa.date_from between pa2.date_from and nvl(pa2.date_to,
p_end_of_time))
or
(nvl(pa.date_to,p_end_of_time) between pa2.date_from and
nvl(pa2.date_to, p_end_of_time))
));
/* select 'Y'
into v_dummy
from per_addresses pa
where person_id = p_person_id
and primary_flag = 'N'
and date_from between l_date_from and l_date_to;
select 'Y'
into v_dummy
from per_addresses pa
where person_id = p_person_id
and primary_flag = 'Y'
and l_date_to between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY')) ;