[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_loc_selected_datasources VARCHAR2(255);
g_loc_is_datasource_selected VARCHAR2(1) := 'N';
PROCEDURE do_update_location(
p_location_rec IN OUT NOCOPY location_rec_type,
p_do_addr_val IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE update_location_search(
p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_new_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
);
p_create_update_flag IN VARCHAR2,
p_location_rec IN location_rec_type,
p_old_location_rec IN location_rec_type DEFAULT NULL,
x_return_status IN OUT NOCOPY VARCHAR2
);
SELECT 'Y'
FROM hz_locations hl
WHERE hl.location_id = p_location_rec.location_id;
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.insert_row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
hz_utility_v2pub.debug(p_message=>'TimeZone Id not found. Messages are deleted from msg stack',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
fnd_msg_pub.delete_msg(i);
hz_locations_pkg.insert_row (
x_location_id => p_location_rec.location_id,
x_attribute_category => p_location_rec.attribute_category,
x_attribute1 => p_location_rec.attribute1,
x_attribute2 => p_location_rec.attribute2,
x_attribute3 => p_location_rec.attribute3,
x_attribute4 => p_location_rec.attribute4,
x_attribute5 => p_location_rec.attribute5,
x_attribute6 => p_location_rec.attribute6,
x_attribute7 => p_location_rec.attribute7,
x_attribute8 => p_location_rec.attribute8,
x_attribute9 => p_location_rec.attribute9,
x_attribute10 => p_location_rec.attribute10,
x_attribute11 => p_location_rec.attribute11,
x_attribute12 => p_location_rec.attribute12,
x_attribute13 => p_location_rec.attribute13,
x_attribute14 => p_location_rec.attribute14,
x_attribute15 => p_location_rec.attribute15,
x_attribute16 => p_location_rec.attribute16,
x_attribute17 => p_location_rec.attribute17,
x_attribute18 => p_location_rec.attribute18,
x_attribute19 => p_location_rec.attribute19,
x_attribute20 => p_location_rec.attribute20,
x_orig_system_reference => p_location_rec.orig_system_reference,
x_country => p_location_rec.country,
x_address1 => p_location_rec.address1,
x_address2 => p_location_rec.address2,
x_address3 => p_location_rec.address3,
x_address4 => p_location_rec.address4,
x_city => p_location_rec.city,
x_postal_code => p_location_rec.postal_code,
x_state => p_location_rec.state,
x_province => p_location_rec.province,
x_county => p_location_rec.county,
x_address_key => p_location_rec.address_key,
x_address_style => p_location_rec.address_style,
x_validated_flag => p_location_rec.validated_flag,
x_address_lines_phonetic => p_location_rec.address_lines_phonetic,
x_po_box_number => p_location_rec.po_box_number,
x_house_number => p_location_rec.house_number,
x_street_suffix => p_location_rec.street_suffix,
x_street => p_location_rec.street,
x_street_number => p_location_rec.street_number,
x_floor => p_location_rec.floor,
x_suite => p_location_rec.suite,
x_postal_plus4_code => p_location_rec.postal_plus4_code,
x_position => p_location_rec.position,
x_location_directions => p_location_rec.location_directions,
x_address_effective_date => p_location_rec.address_effective_date,
x_address_expiration_date => p_location_rec.address_expiration_date,
x_clli_code => p_location_rec.clli_code,
x_language => p_location_rec.language,
x_short_description => p_location_rec.short_description,
x_description => p_location_rec.description,
x_content_source_type => p_location_rec.content_source_type,
x_loc_hierarchy_id => p_location_rec.loc_hierarchy_id,
x_sales_tax_geocode => p_location_rec.sales_tax_geocode,
x_sales_tax_inside_city_limits => p_location_rec.sales_tax_inside_city_limits,
x_fa_location_id => p_location_rec.fa_location_id,
x_geometry => p_location_rec.geometry,
x_object_version_number => 1,
x_timezone_id => p_location_rec.timezone_id,
x_created_by_module => p_location_rec.created_by_module,
x_application_id => p_location_rec.application_id,
x_geometry_status_code => p_location_rec.geometry_status_code,
x_actual_content_source => p_location_rec.actual_content_source,
-- Bug 2670546.
x_delivery_point_code => p_location_rec.delivery_point_code
);
hz_location_profiles_pkg.Insert_Row (
x_location_profile_id => l_location_profile_id
,x_location_id => x_location_id
,x_actual_content_source => p_location_rec.actual_content_source
,x_effective_start_date => sysdate
,x_effective_end_date => l_end_date
,x_validation_sst_flag => 'Y'
,x_validation_status_code => NULL
,x_date_validated => NULL
,x_address1 => p_location_rec.address1
,x_address2 => p_location_rec.address2
,x_address3 => p_location_rec.address3
,x_address4 => p_location_rec.address4
,x_city => p_location_rec.city
,x_postal_code => p_location_rec.postal_code
,x_prov_state_admin_code => l_prov_state_admin_code
,x_county => p_location_rec.county
,x_country => p_location_rec.country
,x_object_version_number => 1
);
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.insert_row (-) ' ||
'x_location_id = ' || p_location_rec.location_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'hz_location_profiles_pkg.insert_row (-) ' ||
'l_location_profile_id = ' || l_location_profile_id,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
PROCEDURE do_update_location(
p_location_rec IN OUT NOCOPY LOCATION_REC_TYPE,
p_do_addr_val IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
l_allow_update_std VARCHAR2(1);
hz_utility_v2pub.debug(p_message=>'do_update_location (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT hl.object_version_number,
hl.rowid,
hl.geometry,
hl.country,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.postal_code,
hl.state,
hl.province,
hl.county,
hl.content_source_type,
hl.actual_content_source,
-- Bug 4693719 : select ACS
hl.actual_content_source,
hl.date_validated,
hl.validation_status_code
INTO l_object_version_number,
l_rowid,
l_geometry,
db_country,
db_address1,
db_address2,
db_address3,
db_address4,
db_city,
db_postal_code,
db_state,
db_province,
db_county,
db_content_source_type,
l_profile_content_source,
db_actual_content_source,
l_date_validated,
l_validation_status_code
FROM hz_locations hl
WHERE hl.location_id = p_location_rec.location_id
FOR UPDATE OF hl.location_id NOWAIT;
l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
IF(l_allow_update_std = 'N' AND
l_date_validated IS NOT NULL AND
l_validation_status_code IS NOT NULL) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
hz_utility_v2pub.debug(p_message=>'hz_locations_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_location_rec.actual_content_source, 'USER_ENTERED');
hz_locations_pkg.update_row (
x_rowid => l_rowid,
x_location_id => p_location_rec.location_id,
x_attribute_category => p_location_rec.attribute_category,
x_attribute1 => p_location_rec.attribute1,
x_attribute2 => p_location_rec.attribute2,
x_attribute3 => p_location_rec.attribute3,
x_attribute4 => p_location_rec.attribute4,
x_attribute5 => p_location_rec.attribute5,
x_attribute6 => p_location_rec.attribute6,
x_attribute7 => p_location_rec.attribute7,
x_attribute8 => p_location_rec.attribute8,
x_attribute9 => p_location_rec.attribute9,
x_attribute10 => p_location_rec.attribute10,
x_attribute11 => p_location_rec.attribute11,
x_attribute12 => p_location_rec.attribute12,
x_attribute13 => p_location_rec.attribute13,
x_attribute14 => p_location_rec.attribute14,
x_attribute15 => p_location_rec.attribute15,
x_attribute16 => p_location_rec.attribute16,
x_attribute17 => p_location_rec.attribute17,
x_attribute18 => p_location_rec.attribute18,
x_attribute19 => p_location_rec.attribute19,
x_attribute20 => p_location_rec.attribute20,
x_orig_system_reference => p_location_rec.orig_system_reference,
x_country => p_location_rec.country,
x_address1 => p_location_rec.address1,
x_address2 => p_location_rec.address2,
x_address3 => p_location_rec.address3,
x_address4 => p_location_rec.address4,
x_city => p_location_rec.city,
x_postal_code => p_location_rec.postal_code,
x_state => p_location_rec.state,
x_province => p_location_rec.province,
x_county => p_location_rec.county,
x_address_key => p_location_rec.address_key,
x_address_style => p_location_rec.address_style,
x_validated_flag => p_location_rec.validated_flag,
x_address_lines_phonetic => p_location_rec.address_lines_phonetic,
x_po_box_number => p_location_rec.po_box_number,
x_house_number => p_location_rec.house_number,
x_street_suffix => p_location_rec.street_suffix,
x_street => p_location_rec.street,
x_street_number => p_location_rec.street_number,
x_floor => p_location_rec.floor,
x_suite => p_location_rec.suite,
x_postal_plus4_code => p_location_rec.postal_plus4_code,
x_position => p_location_rec.position,
x_location_directions => p_location_rec.location_directions,
x_address_effective_date => p_location_rec.address_effective_date,
x_address_expiration_date => p_location_rec.address_expiration_date,
x_clli_code => p_location_rec.clli_code,
x_language => p_location_rec.language,
x_short_description => p_location_rec.short_description,
x_description => p_location_rec.description,
-- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
x_content_source_type => NULL,
x_loc_hierarchy_id => p_location_rec.loc_hierarchy_id,
x_sales_tax_geocode => p_location_rec.sales_tax_geocode,
x_sales_tax_inside_city_limits => p_location_rec.sales_tax_inside_city_limits,
x_fa_location_id => p_location_rec.fa_location_id,
x_geometry => p_location_rec.geometry,
x_object_version_number => p_object_version_number,
x_timezone_id => p_location_rec.timezone_id,
x_created_by_module => p_location_rec.created_by_module,
x_application_id => p_location_rec.application_id,
x_geometry_status_code => p_location_rec.geometry_status_code,
-- Bug 4693719 : Pass correct value for ACS
x_actual_content_source => l_acs,
-- Bug 2670546
x_delivery_point_code => p_location_rec.delivery_point_code
);
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.update_row (-) ',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT hps.party_id
FROM hz_party_sites hps
WHERE hps.location_id = p_location_rec.location_id
AND hps.identifying_address_flag = 'Y';
SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE party_id = l_party_id
FOR UPDATE NOWAIT;
UPDATE hz_parties
SET country = DECODE(p_location_rec.country,
NULL, db_country,
fnd_api.g_miss_char, NULL,
p_location_rec.country),
address1 = DECODE(p_location_rec.address1,
NULL, db_address1,
fnd_api.g_miss_char, NULL,
p_location_rec.address1),
address2 = DECODE(p_location_rec.address2,
NULL, db_address2,
fnd_api.g_miss_char, NULL,
p_location_rec.address2),
address3 = DECODE(p_location_rec.address3,
NULL, db_address3,
fnd_api.g_miss_char, NULL,
p_location_rec.address3),
address4 = DECODE(p_location_rec.address4,
NULL, db_address4,
fnd_api.g_miss_char, NULL,
p_location_rec.address4),
city = DECODE(p_location_rec.city,
NULL, db_city,
fnd_api.g_miss_char, NULL,
p_location_rec.city),
postal_code = DECODE(p_location_rec.postal_code,
NULL, db_postal_code,
fnd_api.g_miss_char, NULL,
p_location_rec.postal_code),
state = DECODE(p_location_rec.state,
NULL, db_state,
fnd_api.g_miss_char, NULL,
p_location_rec.state),
province = DECODE(p_location_rec.province,
NULL, db_province,
fnd_api.g_miss_char, NULL,
p_location_rec.province),
county = DECODE(p_location_rec.county,
NULL, db_county,
fnd_api.g_miss_char, NULL,
p_location_rec.county),
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 party_id = l_party_id;
HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment (
p_location_id => p_location_rec.location_id,
p_do_addr_val => p_do_addr_val,
x_addr_val_status => x_addr_val_status,
x_addr_warn_msg => x_addr_warn_msg,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
hz_utility_v2pub.debug(p_message=>'do_update_location (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END do_update_location;
* For non-profile entities, the concept of select/de-select data-sources is obsoleted.
-- IF g_loc_mixnmatch_enabled IS NULL THEN
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_LOCATIONS',
p_entity_attr_id => g_loc_entity_attr_id,
p_mixnmatch_enabled => g_loc_mixnmatch_enabled,
p_selected_datasources => g_loc_selected_datasources );
p_selected_datasources => g_loc_selected_datasources,
p_content_source_type => l_location_rec.content_source_type,
p_actual_content_source => l_location_rec.actual_content_source,
x_is_datasource_selected => g_loc_is_datasource_selected,
x_return_status => x_return_status );
p_create_update_flag => 'C',
p_location_rec => l_location_rec,
x_return_status => x_return_status
);
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_LOCATIONS',
p_instance_pk1_value => x_location_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_TOKEN('OPER_NAME','INSERT');
g_loc_is_datasource_selected = 'Y'*/
THEN
IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
HZ_BUSINESS_EVENT_V2PVT.create_location_event (
l_location_rec);
* select/de-select data-sources is obsoleted.
*/
PROCEDURE create_location (
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_location_rec IN LOCATION_REC_TYPE,
x_location_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_addr_val_status VARCHAR2(30);
* PROCEDURE update_location
*
* DESCRIPTION
* Updates location(overloaded procedure with address validation).
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_rec Location record.
* p_do_addr_val Do address validation if 'Y'
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* OUT:
* x_addr_val_status Address validation status based on address validation level.
* x_addr_warn_msg Warning message if x_addr_val_status is 'W'
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-04-2005 Baiju Nair o Created.
*
*/
PROCEDURE update_location (
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_location_rec IN LOCATION_REC_TYPE,
p_do_addr_val IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_standalone_location IS
SELECT 1
FROM hz_party_sites
WHERE location_id = p_location_rec.location_id
AND status NOT IN ('M', 'D')
AND ROWNUM = 1;
SAVEPOINT update_location;
hz_utility_v2pub.debug(p_message=>'update_location (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_LOCATIONS',
p_instance_pk1_value => l_location_rec.location_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.
HZ_MIXNM_UTILITY.LoadDataSources(
p_entity_name => 'HZ_LOCATIONS',
p_entity_attr_id => g_loc_entity_attr_id,
p_mixnmatch_enabled => g_loc_mixnmatch_enabled,
p_selected_datasources => g_loc_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_loc_is_datasource_selected :=
HZ_MIXNM_UTILITY.isDataSourceSelected (
p_selected_datasources => g_loc_selected_datasources,
p_actual_content_source => l_old_location_rec.actual_content_source );
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
hz_utility_v2pub.debug(p_message=>'TimeZone Id not found. Messages are deleted from msg stack',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
fnd_msg_pub.delete_msg(i);
p_create_update_flag => 'U',
p_location_rec => l_location_rec,
p_old_location_rec => l_old_location_rec,
x_return_status => x_return_status
);
do_update_location(
l_location_rec,
p_do_addr_val,
p_object_version_number,
x_addr_val_status,
x_addr_warn_msg,
x_return_status);
update_location_search(l_old_location_rec,l_location_rec);
g_loc_is_datasource_selected = 'Y'*/
THEN
l_old_location_rec.orig_system := p_location_rec.orig_system;
HZ_BUSINESS_EVENT_V2PVT.update_location_event (
l_location_rec,
l_old_location_rec);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_location (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_location;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_location (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_location;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_location (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
ROLLBACK TO update_location;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
hz_utility_v2pub.debug(p_message=>'update_location (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END update_location;
* PROCEDURE update_location
*
* DESCRIPTION
* Updates location.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_location_event
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_rec Location record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* For non-profile entities, the concept of
* select/de-select data-sources is obsoleted.
*
*/
PROCEDURE update_location (
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_location_rec IN LOCATION_REC_TYPE,
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_addr_val_status VARCHAR2(30);
update_location(
p_init_msg_list => p_init_msg_list,
p_location_rec => p_location_rec,
p_do_addr_val => 'N',
p_object_version_number => p_object_version_number,
x_addr_val_status => l_addr_val_status,
x_addr_warn_msg => l_addr_warn_msg,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END update_location;
* hz_locations_PKG.Select_Row
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_id Location ID.
* IN/OUT:
* OUT:
* x_location_rec Location record.
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 25-JAN-2002 Joe del Callar Bug 2200569: added the column
* geometry status for spatial data
* integration.
* 14-NOV-2003 Rajib Ranjan Borah o Bug 2670546.Reintroduced column
* delivery_point_code
*/
PROCEDURE get_location_rec (
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_location_id IN NUMBER,
x_location_rec OUT NOCOPY location_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.Select_Row (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_locations_pkg.select_row (
x_location_id => x_location_rec.location_id,
x_attribute_category => x_location_rec.attribute_category,
x_attribute1 => x_location_rec.attribute1,
x_attribute2 => x_location_rec.attribute2,
x_attribute3 => x_location_rec.attribute3,
x_attribute4 => x_location_rec.attribute4,
x_attribute5 => x_location_rec.attribute5,
x_attribute6 => x_location_rec.attribute6,
x_attribute7 => x_location_rec.attribute7,
x_attribute8 => x_location_rec.attribute8,
x_attribute9 => x_location_rec.attribute9,
x_attribute10 => x_location_rec.attribute10,
x_attribute11 => x_location_rec.attribute11,
x_attribute12 => x_location_rec.attribute12,
x_attribute13 => x_location_rec.attribute13,
x_attribute14 => x_location_rec.attribute14,
x_attribute15 => x_location_rec.attribute15,
x_attribute16 => x_location_rec.attribute16,
x_attribute17 => x_location_rec.attribute17,
x_attribute18 => x_location_rec.attribute18,
x_attribute19 => x_location_rec.attribute19,
x_attribute20 => x_location_rec.attribute20,
x_orig_system_reference => x_location_rec.orig_system_reference,
x_country => x_location_rec.country,
x_address1 => x_location_rec.address1,
x_address2 => x_location_rec.address2,
x_address3 => x_location_rec.address3,
x_address4 => x_location_rec.address4,
x_city => x_location_rec.city,
x_postal_code => x_location_rec.postal_code,
x_state => x_location_rec.state,
x_province => x_location_rec.province,
x_county => x_location_rec.county,
x_address_key => x_location_rec.address_key,
x_address_style => x_location_rec.address_style,
x_validated_flag => x_location_rec.validated_flag,
x_address_lines_phonetic => x_location_rec.address_lines_phonetic,
x_po_box_number => x_location_rec.po_box_number,
x_house_number => x_location_rec.house_number,
x_street_suffix => x_location_rec.street_suffix,
x_street => x_location_rec.street,
x_street_number => x_location_rec.street_number,
x_floor => x_location_rec.floor,
x_suite => x_location_rec.suite,
x_postal_plus4_code => x_location_rec.postal_plus4_code,
x_position => x_location_rec.position,
x_location_directions => x_location_rec.location_directions,
x_address_effective_date => x_location_rec.address_effective_date,
x_address_expiration_date => x_location_rec.address_expiration_date,
x_clli_code => x_location_rec.clli_code,
x_language => x_location_rec.language,
x_short_description => x_location_rec.short_description,
x_description => x_location_rec.description,
x_content_source_type => x_location_rec.content_source_type,
x_loc_hierarchy_id => x_location_rec.loc_hierarchy_id,
x_sales_tax_geocode => x_location_rec.sales_tax_geocode,
x_sales_tax_inside_city_limits => x_location_rec.sales_tax_inside_city_limits,
x_fa_location_id => x_location_rec.fa_location_id,
x_geometry => x_location_rec.geometry,
x_timezone_id => x_location_rec.timezone_id,
x_created_by_module => x_location_rec.created_by_module,
x_application_id => x_location_rec.application_id,
x_geometry_status_code => x_location_rec.geometry_status_code,
x_actual_content_source => x_location_rec.actual_content_source,
-- Bug 2670546
x_delivery_point_code => x_location_rec.delivery_point_code
);
hz_utility_v2pub.debug(p_message=>'hz_locations_pkg.select_row (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
SELECT COUNTRY,
STATE,
CITY,
POSTAL_CODE,
ADDRESS1,
STREET,
STREET_SUFFIX,
STREET_NUMBER,
HOUSE_NUMBER
FROM hz_locations
WHERE GEOMETRY IS NULL OR
GEOMETRY = hz_geometry_default
FOR UPDATE OF GEOMETRY;
UPDATE hz_locations SET GEOMETRY = l_loc_rec.geometry
WHERE CURRENT OF c_locations;
| update_location_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_old_location_rec |
| p_location_rec |
| OUTPUT |
| |
| |
| HISTORY |
| 15-Mar-2004 Ramesh Ch Created |
*----------------------------------------------------------------------------*/
PROCEDURE update_location_search(p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_new_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
)
IS
Cursor c_locations(p_location_id NUMBER) IS
SELECT ac.CUST_ACCT_SITE_ID
FROM HZ_LOCATIONS loc, HZ_PARTY_SITES ps,
HZ_CUST_ACCT_SITES_ALL ac
WHERE loc.LOCATION_ID=p_location_id
AND loc.LOCATION_ID = ps.LOCATION_ID
AND ps.PARTY_SITE_ID=ac.PARTY_SITE_ID;
savepoint update_location_search;
update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
ROLLBACK TO update_location_search;
p_create_update_flag IN VARCHAR2,
p_location_rec IN location_rec_type,
p_old_location_rec IN location_rec_type DEFAULT NULL,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
-- check floor
IF (p_create_update_flag = 'C' AND
p_location_rec.floor IS NOT NULL AND
p_location_rec.floor <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.floor IS NOT NULL AND
p_location_rec.floor <> p_old_location_rec.floor)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.house_number IS NOT NULL AND
p_location_rec.house_number <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.house_number IS NOT NULL AND
p_location_rec.house_number <> p_old_location_rec.house_number)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.po_box_number IS NOT NULL AND
p_location_rec.po_box_number <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.po_box_number IS NOT NULL AND
p_location_rec.po_box_number <> p_old_location_rec.po_box_number)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.street IS NOT NULL AND
p_location_rec.street <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.street IS NOT NULL AND
p_location_rec.street <> p_old_location_rec.street)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.street_number IS NOT NULL AND
p_location_rec.street_number <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.street_number IS NOT NULL AND
p_location_rec.street_number <> p_old_location_rec.street_number)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.street_suffix IS NOT NULL AND
p_location_rec.street_suffix <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.street_suffix IS NOT NULL AND
p_location_rec.street_suffix <> p_old_location_rec.street_suffix)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
IF (p_create_update_flag = 'C' AND
p_location_rec.suite IS NOT NULL AND
p_location_rec.suite <> FND_API.G_MISS_CHAR) OR
(p_create_update_flag = 'U' AND
p_location_rec.suite IS NOT NULL AND
p_location_rec.suite <> p_old_location_rec.suite)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');