[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_cpt_selected_datasources VARCHAR2(255);
g_cpt_is_datasource_selected VARCHAR2(1) := 'N';
PROCEDURE do_update_contact_point (
p_contact_point_rec IN OUT NOCOPY contact_point_rec_type,
p_edi_rec IN OUT NOCOPY edi_rec_type,
p_eft_rec IN OUT NOCOPY eft_rec_type,
p_email_rec IN OUT NOCOPY email_rec_type,
p_phone_rec IN OUT NOCOPY phone_rec_type,
p_telex_rec IN OUT NOCOPY telex_rec_type,
p_web_rec IN OUT NOCOPY web_rec_type,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE update_contact_point_search(
p_cp_rec IN HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE,
p_old_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
p_new_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
p_old_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
p_new_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type
);
SELECT country
FROM hz_locations
WHERE location_id = (SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_site_id);
SELECT 'Y'
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = p_contact_point_type
/* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
g_cpt_selected_datasources, actual_content_source ) = 'Y'*/
AND status = 'A'
AND rownum = 1;
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => l_edi_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => l_eft_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_email_rec => l_email_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_phone_rec => l_phone_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_telex_rec => l_telex_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_web_rec => l_web_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => l_edi_rec,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec,
p_web_rec => l_web_rec,
p_rowid => NULL,
x_return_status => x_return_status);
p_create_update_flag => 'C',
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => l_eft_rec,
p_rowid => NULL,
x_return_status => x_return_status);
select territory_code into l_country_code
from hz_phone_country_codes
where phone_country_code = l_phone_rec.phone_country_code
and rownum = 1;
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
-- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
-- There is no need to check if the data-source is selected.
IF c_cp%NOTFOUND /*AND
-- Bug 2197181: added for mix-n-match project
g_cpt_is_datasource_selected = 'Y'*/
THEN
-- First active and visible contact point per type for this entity
p_contact_point_rec.primary_flag := 'Y';
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
hz_utility_v2pub.debug(p_message=>'HZ_CONTACT_POINTS_PKG.Insert_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_contact_points_pkg.insert_row (
x_contact_point_id => p_contact_point_rec.contact_point_id,
x_contact_point_type => p_contact_point_rec.contact_point_type,
x_status => p_contact_point_rec.status,
x_owner_table_name => p_contact_point_rec.owner_table_name,
x_owner_table_id => p_contact_point_rec.owner_table_id,
x_primary_flag => p_contact_point_rec.primary_flag,
x_orig_system_reference => p_contact_point_rec.orig_system_reference,
x_attribute_category => p_contact_point_rec.attribute_category,
x_attribute1 => p_contact_point_rec.attribute1,
x_attribute2 => p_contact_point_rec.attribute2,
x_attribute3 => p_contact_point_rec.attribute3,
x_attribute4 => p_contact_point_rec.attribute4,
x_attribute5 => p_contact_point_rec.attribute5,
x_attribute6 => p_contact_point_rec.attribute6,
x_attribute7 => p_contact_point_rec.attribute7,
x_attribute8 => p_contact_point_rec.attribute8,
x_attribute9 => p_contact_point_rec.attribute9,
x_attribute10 => p_contact_point_rec.attribute10,
x_attribute11 => p_contact_point_rec.attribute11,
x_attribute12 => p_contact_point_rec.attribute12,
x_attribute13 => p_contact_point_rec.attribute13,
x_attribute14 => p_contact_point_rec.attribute14,
x_attribute15 => p_contact_point_rec.attribute15,
x_attribute16 => p_contact_point_rec.attribute16,
x_attribute17 => p_contact_point_rec.attribute17,
x_attribute18 => p_contact_point_rec.attribute18,
x_attribute19 => p_contact_point_rec.attribute19,
x_attribute20 => p_contact_point_rec.attribute20,
x_edi_transaction_handling => l_edi_rec.edi_transaction_handling,
x_edi_id_number => l_edi_rec.edi_id_number,
x_edi_payment_method => l_edi_rec.edi_payment_method,
x_edi_payment_format => l_edi_rec.edi_payment_format,
x_edi_remittance_method => l_edi_rec.edi_remittance_method,
x_edi_remittance_instruction => l_edi_rec.edi_remittance_instruction,
x_edi_tp_header_id => l_edi_rec.edi_tp_header_id,
x_edi_ece_tp_location_code => l_edi_rec.edi_ece_tp_location_code,
x_eft_transmission_program_id => l_eft_rec.eft_transmission_program_id,
x_eft_printing_program_id => l_eft_rec.eft_printing_program_id,
x_eft_user_number => l_eft_rec.eft_user_number,
x_eft_swift_code => l_eft_rec.eft_swift_code,
x_email_format => l_email_rec.email_format,
x_email_address => l_email_rec.email_address,
x_phone_calling_calendar => l_phone_rec.phone_calling_calendar,
x_last_contact_dt_time => l_phone_rec.last_contact_dt_time,
x_timezone_id => l_phone_rec.timezone_id,
x_phone_area_code => l_phone_rec.phone_area_code,
x_phone_country_code => l_phone_rec.phone_country_code,
x_phone_number => l_phone_rec.phone_number,
x_phone_extension => l_phone_rec.phone_extension,
x_phone_line_type => l_phone_rec.phone_line_type,
x_telex_number => l_telex_rec.telex_number,
x_web_type => l_web_rec.web_type,
x_url => l_web_rec.url,
x_content_source_type => p_contact_point_rec.content_source_type,
x_raw_phone_number => l_phone_rec.raw_phone_number,
x_object_version_number => 1,
x_contact_point_purpose => p_contact_point_rec.contact_point_purpose,
x_primary_by_purpose => p_contact_point_rec.primary_by_purpose,
x_created_by_module => p_contact_point_rec.created_by_module,
x_application_id => p_contact_point_rec.application_id,
x_transposed_phone_number => l_transposed_phone_number,
x_actual_content_source => p_contact_point_rec.actual_content_source
);
hz_utility_v2pub.debug(p_message=>'HZ_CONTACT_POINTS_PKG.Insert_Row (-) ' ||
'x_contact_point_id = ' ||p_contact_point_rec.contact_point_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
PROCEDURE do_update_contact_point (
p_contact_point_rec IN OUT NOCOPY contact_point_rec_type,
p_edi_rec IN OUT NOCOPY edi_rec_type,
p_eft_rec IN OUT NOCOPY eft_rec_type,
p_email_rec IN OUT NOCOPY email_rec_type,
p_phone_rec IN OUT NOCOPY phone_rec_type,
p_telex_rec IN OUT NOCOPY telex_rec_type,
p_web_rec IN OUT NOCOPY web_rec_type,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- do_update_contact_point
SELECT country
FROM hz_locations
WHERE location_id = (SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_owner_table_id);
SELECT 'Y'
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = p_contact_point_type
AND contact_point_id <> p_contact_point_rec.contact_point_id
/* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
g_cpt_selected_datasources, actual_content_source ) = 'Y'*/
AND status = 'A'
AND rownum = 1;
SELECT rowid, url, email_address,contact_point_id,contact_point_purpose,
phone_line_type,phone_area_code,phone_country_code,phone_number,phone_extension
FROM hz_contact_points
WHERE contact_point_id = (
SELECT MIN(contact_point_id)
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = p_contact_point_type
/* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
g_cpt_selected_datasources, actual_content_source ) = 'Y'*/
AND status = 'A'
AND contact_point_id <> p_contact_point_id);
hz_utility_v2pub.debug(p_message=>'do_update_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT rowid, object_version_number,
owner_table_name, owner_table_id,
contact_point_type, phone_line_type,phone_country_code, phone_area_code,
phone_number,phone_extension,primary_flag, status, contact_point_purpose,
url, email_address, primary_by_purpose, actual_content_source
INTO l_rowid, l_object_version_number,
l_owner_table_name, l_owner_table_id,
l_contact_point_type,l_phone_line_type,
l_phone_country_code, l_phone_area_code, l_phone_number,l_phone_extension,
l_primary_flag, l_status, l_contact_point_purpose,
l_url, l_email_address, l_primary_by_purpose, db_actual_content_source
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_rec.contact_point_id
FOR UPDATE NOWAIT;
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => l_edi_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => l_eft_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_email_rec => l_email_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_phone_rec => l_phone_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_telex_rec => l_telex_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_web_rec => l_web_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => l_edi_rec,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec,
p_web_rec => l_web_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
p_create_update_flag => 'U',
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => l_eft_rec,
p_rowid => l_rowid,
x_return_status => x_return_status);
select territory_code into l_country_code
from hz_phone_country_codes
where phone_country_code = l_phone_rec.phone_country_code
and rownum = 1;
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
-- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
-- There is no need to check if the data-source is selected.
IF c_setpf%NOTFOUND /*AND
-- Bug 2197181: added for mix-n-match project
g_cpt_is_datasource_selected = 'Y'*/
THEN
-- First visible, active contact point per type for this entity
p_contact_point_rec.primary_flag := 'Y';
UPDATE hz_contact_points
SET primary_flag = 'Y'
WHERE rowid = l_contact_point_rowid;
g_cpt_is_datasource_selected = 'Y') *//*OR
(p_contact_point_rec.primary_by_purpose IS NULL AND
l_primary_by_purpose = 'Y')*/
--Bug 6755308 Change End
THEN
do_unset_primary_by_purpose(l_owner_table_name,
l_owner_table_id,
l_contact_point_type,
l_contact_point_purpose,
p_contact_point_rec.contact_point_id);
hz_utility_v2pub.debug(p_message=>'hz_contact_points_pkg.update_row (+) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
l_acs := nvl(p_contact_point_rec.actual_content_source, 'USER_ENTERED');
hz_contact_points_pkg.update_row (
x_rowid => l_rowid,
x_contact_point_id => p_contact_point_rec.contact_point_id,
x_contact_point_type => p_contact_point_rec.contact_point_type,
x_status => p_contact_point_rec.status,
x_owner_table_name => p_contact_point_rec.owner_table_name,
x_owner_table_id => p_contact_point_rec.owner_table_id,
x_primary_flag => p_contact_point_rec.primary_flag,
x_orig_system_reference => p_contact_point_rec.orig_system_reference,
x_attribute_category => p_contact_point_rec.attribute_category,
x_attribute1 => p_contact_point_rec.attribute1,
x_attribute2 => p_contact_point_rec.attribute2,
x_attribute3 => p_contact_point_rec.attribute3,
x_attribute4 => p_contact_point_rec.attribute4,
x_attribute5 => p_contact_point_rec.attribute5,
x_attribute6 => p_contact_point_rec.attribute6,
x_attribute7 => p_contact_point_rec.attribute7,
x_attribute8 => p_contact_point_rec.attribute8,
x_attribute9 => p_contact_point_rec.attribute9,
x_attribute10 => p_contact_point_rec.attribute10,
x_attribute11 => p_contact_point_rec.attribute11,
x_attribute12 => p_contact_point_rec.attribute12,
x_attribute13 => p_contact_point_rec.attribute13,
x_attribute14 => p_contact_point_rec.attribute14,
x_attribute15 => p_contact_point_rec.attribute15,
x_attribute16 => p_contact_point_rec.attribute16,
x_attribute17 => p_contact_point_rec.attribute17,
x_attribute18 => p_contact_point_rec.attribute18,
x_attribute19 => p_contact_point_rec.attribute19,
x_attribute20 => p_contact_point_rec.attribute20,
x_edi_transaction_handling => l_edi_rec.edi_transaction_handling,
x_edi_id_number => l_edi_rec.edi_id_number,
x_edi_payment_method => l_edi_rec.edi_payment_method,
x_edi_payment_format => l_edi_rec.edi_payment_format,
x_edi_remittance_method => l_edi_rec.edi_remittance_method,
x_edi_remittance_instruction => l_edi_rec.edi_remittance_instruction,
x_edi_tp_header_id => l_edi_rec.edi_tp_header_id,
x_edi_ece_tp_location_code => l_edi_rec.edi_ece_tp_location_code,
x_eft_transmission_program_id => l_eft_rec.eft_transmission_program_id,
x_eft_printing_program_id => l_eft_rec.eft_printing_program_id,
x_eft_user_number => l_eft_rec.eft_user_number,
x_eft_swift_code => l_eft_rec.eft_swift_code,
x_email_format => l_email_rec.email_format,
x_email_address => l_email_rec.email_address,
x_phone_calling_calendar => l_phone_rec.phone_calling_calendar,
x_last_contact_dt_time => l_phone_rec.last_contact_dt_time,
x_timezone_id => l_phone_rec.timezone_id,
x_phone_area_code => l_phone_rec.phone_area_code,
x_phone_country_code => l_phone_rec.phone_country_code,
x_phone_number => l_phone_rec.phone_number,
x_phone_extension => l_phone_rec.phone_extension,
x_phone_line_type => l_phone_rec.phone_line_type,
x_telex_number => l_telex_rec.telex_number,
x_web_type => l_web_rec.web_type,
x_url => l_web_rec.url,
-- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
x_content_source_type => NULL,
x_raw_phone_number => l_phone_rec.raw_phone_number,
x_object_version_number => p_object_version_number,
x_contact_point_purpose => p_contact_point_rec.contact_point_purpose,
x_primary_by_purpose => p_contact_point_rec.primary_by_purpose,
x_created_by_module => p_contact_point_rec.created_by_module,
x_application_id => p_contact_point_rec.application_id,
x_transposed_phone_number => l_transposed_phone_number,
-- Bug 4693719 : Pass correct value for ACS
x_actual_content_source => l_acs
);
hz_utility_v2pub.debug(p_message=>'hz_contact_points_PKG.Update_Row (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'do_update_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_contact_point;
SELECT contact_point_id
FROM hz_contact_points CP
WHERE CP.owner_table_name = p_owner_table_name
AND CP.owner_table_id = p_owner_table_id
AND CP.contact_point_type = p_contact_point_type
AND CP.contact_point_id <> p_contact_point_id
AND CP.primary_flag = 'Y'
AND ROWNUM = 1
FOR UPDATE NOWAIT;
UPDATE hz_contact_points
SET primary_flag = 'N',
--Bug number 2914238 .Updated the who columns.
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = p_contact_point_type
AND contact_point_id <> l_contact_point_id
-- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
AND primary_flag = 'Y';
UPDATE hz_parties
SET url = p_url,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE party_id = p_party_id;
UPDATE hz_parties
/* Bug No : 4355133*/
SET email_address = SUBSTRB(p_email_address,1,320),
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE party_id = p_party_id;
UPDATE hz_parties
SET primary_phone_contact_pt_id = p_phone_contact_pt_id,
primary_phone_purpose = p_phone_purpose,
primary_phone_line_type = p_phone_line_type,
primary_phone_country_code = p_phone_country_code,
primary_phone_area_code = p_phone_area_code,
primary_phone_number = p_phone_number,
primary_phone_extension = p_phone_extension,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = sysdate
WHERE party_id = p_party_id;
UPDATE hz_contact_points
SET primary_by_purpose = 'N',
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
request_id = hz_utility_v2pub.request_id,
program_id = hz_utility_v2pub.program_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_update_date = hz_utility_v2pub.program_update_date
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = p_contact_point_type
AND contact_point_purpose = p_contact_point_purpose
AND contact_point_id <> l_contact_point_id
-- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
AND primary_by_purpose = 'Y';
SELECT pf.phone_format_style, pf.country_code_display_flag,
pf.area_code_size, pcc.phone_country_code
FROM hz_phone_country_codes pcc, hz_phone_formats pf
WHERE pcc.territory_code = p_territory_code
AND pcc.territory_code = pf.territory_code;
hz_utility_v2pub.debug(p_message=>'hz_contact_points_PKG.Select_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_contact_points_pkg.select_row (
x_contact_point_id => x_contact_point_rec.contact_point_id,
x_contact_point_type => x_contact_point_rec.contact_point_type,
x_status => x_contact_point_rec.status,
x_owner_table_name => x_contact_point_rec.owner_table_name,
x_owner_table_id => x_contact_point_rec.owner_table_id,
x_primary_flag => x_contact_point_rec.primary_flag,
x_orig_system_reference => x_contact_point_rec.orig_system_reference,
x_attribute_category => x_contact_point_rec.attribute_category,
x_attribute1 => x_contact_point_rec.attribute1,
x_attribute2 => x_contact_point_rec.attribute2,
x_attribute3 => x_contact_point_rec.attribute3,
x_attribute4 => x_contact_point_rec.attribute4,
x_attribute5 => x_contact_point_rec.attribute5,
x_attribute6 => x_contact_point_rec.attribute6,
x_attribute7 => x_contact_point_rec.attribute7,
x_attribute8 => x_contact_point_rec.attribute8,
x_attribute9 => x_contact_point_rec.attribute9,
x_attribute10 => x_contact_point_rec.attribute10,
x_attribute11 => x_contact_point_rec.attribute11,
x_attribute12 => x_contact_point_rec.attribute12,
x_attribute13 => x_contact_point_rec.attribute13,
x_attribute14 => x_contact_point_rec.attribute14,
x_attribute15 => x_contact_point_rec.attribute15,
x_attribute16 => x_contact_point_rec.attribute16,
x_attribute17 => x_contact_point_rec.attribute17,
x_attribute18 => x_contact_point_rec.attribute18,
x_attribute19 => x_contact_point_rec.attribute19,
x_attribute20 => x_contact_point_rec.attribute20,
x_edi_transaction_handling => x_edi_rec.edi_transaction_handling,
x_edi_id_number => x_edi_rec.edi_id_number,
x_edi_payment_method => x_edi_rec.edi_payment_method,
x_edi_payment_format => x_edi_rec.edi_payment_format,
x_edi_remittance_method => x_edi_rec.edi_remittance_method,
x_edi_remittance_instruction => x_edi_rec.edi_remittance_instruction,
x_edi_tp_header_id => x_edi_rec.edi_tp_header_id,
x_edi_ece_tp_location_code => x_edi_rec.edi_ece_tp_location_code,
x_eft_transmission_program_id => x_eft_rec.eft_transmission_program_id,
x_eft_printing_program_id => x_eft_rec.eft_printing_program_id,
x_eft_user_number => x_eft_rec.eft_user_number,
x_eft_swift_code => x_eft_rec.eft_swift_code,
x_email_format => x_email_rec.email_format,
x_email_address => x_email_rec.email_address,
x_phone_calling_calendar => x_phone_rec.phone_calling_calendar,
x_last_contact_dt_time => x_phone_rec.last_contact_dt_time,
x_timezone_id => x_phone_rec.timezone_id,
x_phone_area_code => x_phone_rec.phone_area_code,
x_phone_country_code => x_phone_rec.phone_country_code,
x_phone_number => x_phone_rec.phone_number,
x_phone_extension => x_phone_rec.phone_extension,
x_phone_line_type => x_phone_rec.phone_line_type,
x_telex_number => x_telex_rec.telex_number,
x_web_type => x_web_rec.web_type,
x_url => x_web_rec.url,
x_content_source_type => x_contact_point_rec.content_source_type,
x_raw_phone_number => x_phone_rec.raw_phone_number,
x_contact_point_purpose => x_contact_point_rec.contact_point_purpose,
x_primary_by_purpose => x_contact_point_rec.primary_by_purpose,
x_created_by_module => x_contact_point_rec.created_by_module,
x_application_id => x_contact_point_rec.application_id,
x_transposed_phone_number => l_transposed_phone_number,
x_actual_content_source => x_contact_point_rec.actual_content_source
);
hz_utility_v2pub.debug(p_message=>'HZ_CONTACT_POINTS_PKG.Select_Row (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
* For non-profile entities, the concept of select/de-select data sources is obsoleted.
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_CONTACT_POINTS',
p_entity_attr_id => g_cpt_entity_attr_id,
p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
p_selected_datasources => g_cpt_selected_datasources );
p_selected_datasources => g_cpt_selected_datasources,
p_content_source_type => l_contact_point_rec.content_source_type,
p_actual_content_source => l_contact_point_rec.actual_content_source,
x_is_datasource_selected => g_cpt_is_datasource_selected,
x_return_status => x_return_status );
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_CONTACT_POINTS',
p_instance_pk1_value => x_contact_point_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
g_cpt_is_datasource_selected = 'Y'*/
THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
hz_business_event_v2pvt.create_contact_point_event(
l_contact_point_rec,
l_edi_rec,
l_eft_rec,
l_email_rec,
l_phone_rec,
l_telex_rec,
l_web_rec);
PROCEDURE update_contact_point_main (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_edi_rec IN edi_rec_type := g_miss_edi_rec,
p_eft_rec IN eft_rec_type := g_miss_eft_rec,
p_email_rec IN email_rec_type := g_miss_email_rec,
p_phone_rec IN phone_rec_type := g_miss_phone_rec,
p_telex_rec IN telex_rec_type := g_miss_telex_rec,
p_web_rec IN web_rec_type := g_miss_web_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_contact_point_rec contact_point_rec_type := p_contact_point_rec;
hz_utility_v2pub.debug(p_message=>'update_contact_point_main (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_CONTACT_POINTS',
p_instance_pk1_value => l_contact_point_rec.contact_point_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
* There is no need to check if the data-source is selected.
-- IF g_cpt_mixnmatch_enabled IS NULL THEN
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_CONTACT_POINTS',
p_entity_attr_id => g_cpt_entity_attr_id,
p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
p_selected_datasources => g_cpt_selected_datasources );
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
* There is no need to check if the data-source is selected.
g_cpt_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_cpt_selected_datasources,
p_actual_content_source => l_old_contact_point_rec.actual_content_source );
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
do_update_contact_point (
l_contact_point_rec,
l_edi_rec,
l_eft_rec,
l_email_rec,
l_phone_rec,
l_telex_rec,
l_web_rec,
p_object_version_number,
x_return_status);
update_contact_point_search(l_old_contact_point_rec,
l_old_phone_rec,
l_phone_rec,
l_old_email_rec,
l_email_rec
);
g_cpt_is_datasource_selected = 'Y'*/
THEN
l_old_contact_point_rec.orig_system := p_contact_point_rec.orig_system;
hz_business_event_v2pvt.update_contact_point_event (
l_contact_point_rec,
l_old_contact_point_rec,
l_edi_rec,
l_old_edi_rec,
l_eft_rec,
l_old_eft_rec,
l_email_rec,
l_old_email_rec,
l_phone_rec,
l_old_phone_rec,
l_telex_rec,
l_old_telex_rec,
l_web_rec,
l_old_web_rec);
hz_utility_v2pub.debug(p_message=>'update_contact_point_main (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_contact_point_main;
PROCEDURE update_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_edi_rec IN edi_rec_type := g_miss_edi_rec,
p_email_rec IN email_rec_type := g_miss_email_rec,
p_phone_rec IN phone_rec_type := g_miss_phone_rec,
p_telex_rec IN telex_rec_type := g_miss_telex_rec,
p_web_rec IN web_rec_type := g_miss_web_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_contact_point;
hz_utility_v2pub.debug(p_message=>'update_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => p_edi_rec,
p_email_rec => p_email_rec,
p_phone_rec => p_phone_rec,
p_telex_rec => p_telex_rec,
p_web_rec => p_web_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_contact_point;
PROCEDURE update_edi_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_edi_rec IN edi_rec_type := g_miss_edi_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_edi_contact_point;
hz_utility_v2pub.debug(p_message=>'update_edi_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => p_edi_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
hz_utility_v2pub.debug(p_message=>'update_edi_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_edi_contact_point;
hz_utility_v2pub.debug(p_message=>'update_edi_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_edi_contact_point;
hz_utility_v2pub.debug(p_message=>'update_edi_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_edi_contact_point;
hz_utility_v2pub.debug(p_message=>'update_edi_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_edi_contact_point;
PROCEDURE update_web_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_web_rec IN web_rec_type := g_miss_web_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_web_contact_point;
hz_utility_v2pub.debug(p_message=>'update_web_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_web_rec => p_web_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_web_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_web_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_web_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_web_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_web_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_web_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_web_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_web_contact_point;
PROCEDURE update_eft_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_eft_rec IN eft_rec_type := g_miss_eft_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_eft_contact_point;
hz_utility_v2pub.debug(p_message=>'update_eft_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => p_eft_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
hz_utility_v2pub.debug(p_message=>'update_eft_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_eft_contact_point;
hz_utility_v2pub.debug(p_message=>'update_eft_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_eft_contact_point;
hz_utility_v2pub.debug(p_message=>'update_eft_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_eft_contact_point;
hz_utility_v2pub.debug(p_message=>'update_eft_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_eft_contact_point;
PROCEDURE update_phone_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_phone_rec IN phone_rec_type := g_miss_phone_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_phone_contact_point;
hz_utility_v2pub.debug(p_message=>'update_phone_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_phone_rec => p_phone_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_phone_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_phone_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_phone_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_phone_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_phone_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_phone_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_phone_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_phone_contact_point;
PROCEDURE update_telex_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_telex_rec IN telex_rec_type := g_miss_telex_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_telex_contact_point;
hz_utility_v2pub.debug(p_message=>'update_telex_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_telex_rec => p_telex_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_telex_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_telex_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_telex_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_telex_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_telex_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_telex_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_telex_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_telex_contact_point;
PROCEDURE update_email_contact_point (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_contact_point_rec IN contact_point_rec_type,
p_email_rec IN email_rec_type := g_miss_email_rec,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
SAVEPOINT update_email_contact_point;
hz_utility_v2pub.debug(p_message=>'update_email_contact_point (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
update_contact_point_main(
p_init_msg_list => p_init_msg_list,
p_contact_point_rec => p_contact_point_rec,
p_email_rec => p_email_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_email_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_email_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_email_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_email_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_email_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_email_contact_point;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_email_contact_point (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_email_contact_point;
SELECT territory_code
FROM hz_phone_country_codes
WHERE phone_country_code = p_country_code;
| update_contact_point_search |
| |
| DESCRIPTION |
| This procedure updates the address_text column of |
| hz_cust_acct_sites_all with the NULL value |
| only to change the address_text column status |
| so that interMedia index can be created on it to perform text searches. |
| |
| NOTE :- After Calling this procedure the user has to execute the |
| Customer Text Data Creation concurrent program to see the changes. |
| |
| PARAMETERS |
| INPUT |
| p_cp_rec |
| p_old_phone_rec |
| p_new_phone_rec |
| p_old_email_rec |
| p_new_email_rec |
| |
| |
| |
| OUTPUT |
| |
| |
| HISTORY |
| 15-Mar-2004 Ramesh Ch Created |
*----------------------------------------------------------------------------*/
PROCEDURE update_contact_point_search(p_cp_rec IN HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE,
p_old_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
p_new_phone_rec IN HZ_CONTACT_POINT_V2PUB.phone_rec_type,
p_old_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type,
p_new_email_rec IN HZ_CONTACT_POINT_V2PUB.email_rec_type
)
IS
----(Party level Contact Point)
CURSOR c_pl_cp(p_party_id NUMBER) IS
SELECT ac.CUST_ACCT_SITE_ID
FROM HZ_CUST_ACCOUNTS c,HZ_CUST_ACCT_SITES_ALL ac
WHERE c.party_id=p_party_id
AND c.cust_account_id = ac.cust_account_id;
SELECT ac.CUST_ACCT_SITE_ID
FROM HZ_PARTY_SITES ps,
HZ_CUST_ACCT_SITES_ALL ac
WHERE ps.PARTY_SITE_ID=p_party_site_id
AND ps.PARTY_SITE_ID=ac.PARTY_SITE_ID;
SELECT distinct ac.CUST_ACCT_SITE_ID
FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
WHERE rel.party_id=p_party_id
AND ar.ROLE_TYPE = 'CONTACT'
AND rel.party_id=ar.party_id
AND rel.subject_id=p.party_id
AND ar.cust_account_id = ac.cust_account_id
AND (ar.cust_acct_site_id is null);
SELECT distinct ac.CUST_ACCT_SITE_ID
FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
WHERE rel.party_id=p_party_id
AND ar.ROLE_TYPE = 'CONTACT'
AND ar.party_id = rel.party_id
AND p.party_id = rel.subject_id
AND ar.cust_account_id = ac.cust_account_id
AND ar.cust_acct_site_id = ac.cust_acct_site_id;
SELECT party_type
FROM HZ_PARTIES
WHERE party_id=p_party_id;
savepoint update_contact_point_search;
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
ROLLBACK TO update_contact_point_search;