[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_dml(p_rec in out nocopy per_add_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'insert_dml';
insert into per_addresses
( address_id,
business_group_id,
person_id,
date_from,
primary_flag,
derived_locale,
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,
add_information13,
add_information14,
add_information15,
add_information16,
add_information17,
add_information18,
add_information19,
add_information20,
party_id, -- HR/TCA merge
geometry,
object_version_number
)
Values
( p_rec.address_id,
p_rec.business_group_id,
p_rec.person_id,
p_rec.date_from,
p_rec.primary_flag,
p_rec.derived_locale,
p_rec.style,
p_rec.address_line1,
p_rec.address_line2,
p_rec.address_line3,
p_rec.address_type,
p_rec.comments,
p_rec.country,
p_rec.date_to,
p_rec.postal_code,
p_rec.region_1,
p_rec.region_2,
p_rec.region_3,
p_rec.telephone_number_1,
p_rec.telephone_number_2,
p_rec.telephone_number_3,
p_rec.town_or_city,
p_rec.request_id,
p_rec.program_application_id,
p_rec.program_id,
p_rec.program_update_date,
p_rec.addr_attribute_category,
p_rec.addr_attribute1,
p_rec.addr_attribute2,
p_rec.addr_attribute3,
p_rec.addr_attribute4,
p_rec.addr_attribute5,
p_rec.addr_attribute6,
p_rec.addr_attribute7,
p_rec.addr_attribute8,
p_rec.addr_attribute9,
p_rec.addr_attribute10,
p_rec.addr_attribute11,
p_rec.addr_attribute12,
p_rec.addr_attribute13,
p_rec.addr_attribute14,
p_rec.addr_attribute15,
p_rec.addr_attribute16,
p_rec.addr_attribute17,
p_rec.addr_attribute18,
p_rec.addr_attribute19,
p_rec.addr_attribute20,
p_rec.add_information13,
p_rec.add_information14,
p_rec.add_information15,
p_rec.add_information16,
p_rec.add_information17,
p_rec.add_information18,
p_rec.add_information19,
p_rec.add_information20,
p_rec.party_id, -- HR/TCA merge
p_rec.geometry,
p_rec.object_version_number
);
End insert_dml;
Procedure pre_insert(p_rec in out nocopy per_add_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'pre_insert';
Cursor C_Sel1 is select per_addresses_s.nextval from sys.dual;
select null
from per_addresses
where address_id = per_add_ins.g_address_id_i;
l_last_update date;
Select max(date_from) into l_last_update
From per_addresses_v
Where person_id = p_rec.person_id and
primary_flag = 'Y' and
date_from <= p_rec.date_from;
If l_date < l_last_update
Then
Select
nvl(add_information17,''),
nvl(add_information18,''),
nvl(add_information19,''),
nvl(add_information20,'')
into
p_rec.add_information17,
p_rec.add_information18,
p_rec.add_information19,
p_rec.add_information20
From per_addresses_v
Where
person_id = p_rec.person_id and
primary_flag = 'Y' and
date_from =
(Select max(date_from)
From per_addresses_v
Where date_from between l_date and p_rec.date_from and
person_id = p_rec.person_id and
primary_flag = 'Y');
Select town_or_city,
region_1,
region_2,
postal_code
Into p_rec.add_information18,
p_rec.add_information19,
p_rec.add_information17,
p_rec.add_information20
From per_addresses_v
Where person_id = p_rec.person_id and
primary_flag = 'Y' and
l_date between date_from and
nvl(date_to, TO_DATE('31/12/4712', 'DD/MM/YYYY')) and
region_2 = 'IN';
End pre_insert;
Procedure post_insert(p_rec in per_add_shd.g_rec_type
,p_effective_date in date
,p_validate_county in boolean
) is
--
l_proc varchar2(72) := g_package||'post_insert';
per_add_rki.after_insert
(p_address_id => p_rec.address_id
,p_business_group_id => p_rec.business_group_id
,p_person_id => p_rec.person_id
,p_date_from => p_rec.date_from
,p_primary_flag => p_rec.primary_flag
,p_style => p_rec.style
,p_address_line1 => p_rec.address_line1
,p_address_line2 => p_rec.address_line2
,p_address_line3 => p_rec.address_line3
,p_address_type => p_rec.address_type
,p_comments => p_rec.comments
,p_country => p_rec.country
,p_date_to => p_rec.date_to
,p_postal_code => p_rec.postal_code
,p_region_1 => p_rec.region_1
,p_region_2 => p_rec.region_2
,p_region_3 => p_rec.region_3
,p_telephone_number_1 => p_rec.telephone_number_1
,p_telephone_number_2 => p_rec.telephone_number_2
,p_telephone_number_3 => p_rec.telephone_number_3
,p_town_or_city => p_rec.town_or_city
,p_request_id => p_rec.request_id
,p_program_application_id => p_rec.program_application_id
,p_program_id => p_rec.program_id
,p_program_update_date => p_rec.program_update_date
,p_addr_attribute_category => p_rec.addr_attribute_category
,p_addr_attribute1 => p_rec.addr_attribute1
,p_addr_attribute2 => p_rec.addr_attribute2
,p_addr_attribute3 => p_rec.addr_attribute3
,p_addr_attribute4 => p_rec.addr_attribute4
,p_addr_attribute5 => p_rec.addr_attribute5
,p_addr_attribute6 => p_rec.addr_attribute6
,p_addr_attribute7 => p_rec.addr_attribute7
,p_addr_attribute8 => p_rec.addr_attribute8
,p_addr_attribute9 => p_rec.addr_attribute9
,p_addr_attribute10 => p_rec.addr_attribute10
,p_addr_attribute11 => p_rec.addr_attribute11
,p_addr_attribute12 => p_rec.addr_attribute12
,p_addr_attribute13 => p_rec.addr_attribute13
,p_addr_attribute14 => p_rec.addr_attribute14
,p_addr_attribute15 => p_rec.addr_attribute15
,p_addr_attribute16 => p_rec.addr_attribute16
,p_addr_attribute17 => p_rec.addr_attribute17
,p_addr_attribute18 => p_rec.addr_attribute18
,p_addr_attribute19 => p_rec.addr_attribute19
,p_addr_attribute20 => p_rec.addr_attribute20
,p_add_information13 => p_rec.add_information13
,p_add_information14 => p_rec.add_information14
,p_add_information15 => p_rec.add_information15
,p_add_information16 => p_rec.add_information16
,p_add_information17 => p_rec.add_information17
,p_add_information18 => p_rec.add_information18
,p_add_information19 => p_rec.add_information19
,p_add_information20 => p_rec.add_information20
,p_object_version_number => p_rec.object_version_number
,p_effective_date => p_effective_date
,p_validate_county => p_validate_county
,p_party_id => p_rec.party_id -- HR/TCA merge
);
End post_insert;
per_add_bus.insert_validate(p_rec
,p_effective_date
,p_validate_county
);
pre_insert(p_rec);
insert_dml(p_rec);
post_insert(p_rec
,p_effective_date
,p_validate_county
);
,p_program_update_date in date default null
,p_addr_attribute_category in varchar2 default null
,p_addr_attribute1 in varchar2 default null
,p_addr_attribute2 in varchar2 default null
,p_addr_attribute3 in varchar2 default null
,p_addr_attribute4 in varchar2 default null
,p_addr_attribute5 in varchar2 default null
,p_addr_attribute6 in varchar2 default null
,p_addr_attribute7 in varchar2 default null
,p_addr_attribute8 in varchar2 default null
,p_addr_attribute9 in varchar2 default null
,p_addr_attribute10 in varchar2 default null
,p_addr_attribute11 in varchar2 default null
,p_addr_attribute12 in varchar2 default null
,p_addr_attribute13 in varchar2 default null
,p_addr_attribute14 in varchar2 default null
,p_addr_attribute15 in varchar2 default null
,p_addr_attribute16 in varchar2 default null
,p_addr_attribute17 in varchar2 default null
,p_addr_attribute18 in varchar2 default null
,p_addr_attribute19 in varchar2 default null
,p_addr_attribute20 in varchar2 default null
,p_add_information13 in varchar2 default null
,p_add_information14 in varchar2 default null
,p_add_information15 in varchar2 default null
,p_add_information16 in varchar2 default null
,p_add_information17 in varchar2 default null
,p_add_information18 in varchar2 default null
,p_add_information19 in varchar2 default null
,p_add_information20 in varchar2 default null
,p_object_version_number out nocopy number
,p_party_id in number default null
,p_validate in boolean default false
,p_effective_date in date
,p_validate_county in boolean default true
) is
--
l_rec per_add_shd.g_rec_type;
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_information13,
p_add_information14,
p_add_information15,
p_add_information16,
p_add_information17,
p_add_information18,
p_add_information19,
p_add_information20,
null,
p_party_id -- HR/TCA merge
);