The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hl.country, hl.address1, hl.address2, hl.address3,
hl.address4, hl.city, hl.postal_code, hl.state,
hl.province, hl.county, hl.actual_content_source,
hl.validation_status_code, hl.date_validated
FROM hz_locations hl
WHERE hl.location_id = l_location_id;
p_create_update_flag => 'C'
,p_location_profile_rec => p_location_profile_rec
,x_return_status => x_return_status );
HZ_LOCATION_PROFILES_PKG.Insert_Row (
x_location_profile_id => l_location_profile_rec.location_profile_id
,x_location_id => l_location_profile_rec.location_id
,x_actual_content_source => l_location_profile_rec.actual_content_source
,x_effective_start_date => l_start_date
,x_effective_end_date => l_end_date
,x_validation_sst_flag => l_location_profile_rec.validation_sst_flag
,x_validation_status_code => l_location_profile_rec.validation_status_code
,x_date_validated => l_location_profile_rec.date_validated
,x_address1 => l_location_profile_rec.address1
,x_address2 => l_location_profile_rec.address2
,x_address3 => l_location_profile_rec.address3
,x_address4 => l_location_profile_rec.address4
,x_city => l_location_profile_rec.city
,x_postal_code => l_location_profile_rec.postal_code
,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
,x_county => l_location_profile_rec.county
,x_country => l_location_profile_rec.country
,x_object_version_number => 1
);
UPDATE HZ_LOCATIONS
SET date_validated = sysdate
, validation_status_code = l_location_profile_rec.validation_status_code
, 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
WHERE location_id = l_location_profile_rec.location_id;
PROCEDURE update_location_profile (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_location_profile_rec IN location_profile_rec_type
-- ,px_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_object_version_number NUMBER;
l_allow_update_std VARCHAR2(1);
SELECT 'X'
FROM hz_location_profiles
WHERE location_id = l_location_id
AND actual_content_source = l_content_source
AND sysdate+.001 between effective_start_date and nvl(effective_end_date, sysdate)
AND rownum = 1;
SELECT 'X'
FROM hz_locations
WHERE location_id = l_location_id
AND date_validated IS NOT NULL
AND validation_status_code IS NOT NULL;
select location_profile_id, rowid, object_version_number
from hz_location_profiles
where sysdate between effective_start_date and nvl(effective_end_date, sysdate)
and location_id = c_location_id
and location_profile_id <> c_location_profile_id;
savepoint update_location_profile_pub;
l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
IF((l_allow_update_std = 'N') AND (l_dummy IS NOT NULL)) THEN
l_validation_sst_flag := 'N';
p_create_update_flag => 'U'
,p_location_profile_rec => l_location_profile_rec
,x_return_status => x_return_status );
SELECT rowid, object_version_number, validation_sst_flag
INTO l_rowid, l_object_version_number, l_orig_sst_flag
FROM hz_location_profiles
WHERE location_id = l_location_profile_rec.location_id
AND actual_content_source = l_location_profile_rec.actual_content_source
AND sysdate+.001 between effective_start_date and nvl(effective_end_date,sysdate)
AND rownum = 1
FOR UPDATE NOWAIT;
HZ_LOCATION_PROFILES_PKG.Update_Row(
x_rowid => l_rowid
,x_location_profile_id => NULL
,x_location_id => NULL
,x_actual_content_source => NULL
,x_effective_start_date => NULL
,x_effective_end_date => l_end_date
,x_validation_sst_flag => l_validation_sst_flag
,x_validation_status_code => l_location_profile_rec.validation_status_code
,x_date_validated => l_location_profile_rec.date_validated
,x_address1 => l_location_profile_rec.address1
,x_address2 => l_location_profile_rec.address2
,x_address3 => l_location_profile_rec.address3
,x_address4 => l_location_profile_rec.address4
,x_city => l_location_profile_rec.city
,x_postal_code => l_location_profile_rec.postal_code
,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
,x_county => l_location_profile_rec.county
,x_country => l_location_profile_rec.country
,x_object_version_number => nvl(l_object_version_number,1)+1
);
SELECT rowid, object_version_number, validation_sst_flag
INTO l_rowid, l_object_version_number, l_orig_sst_flag
FROM hz_location_profiles
WHERE location_id = l_location_profile_rec.location_id
AND actual_content_source = l_location_profile_rec.actual_content_source
AND sysdate+.001 between effective_start_date and nvl(effective_end_date,sysdate)
AND rownum = 1
FOR UPDATE NOWAIT;
HZ_LOCATION_PROFILES_PKG.Update_Row(
x_rowid => l_rowid
,x_location_profile_id => NULL
,x_location_id => NULL
,x_actual_content_source => NULL
,x_effective_start_date => NULL
,x_effective_end_date => sysdate
,x_validation_sst_flag => NULL
,x_validation_status_code => NULL
,x_date_validated => NULL
,x_address1 => NULL
,x_address2 => NULL
,x_address3 => NULL
,x_address4 => NULL
,x_city => NULL
,x_postal_code => NULL
,x_prov_state_admin_code => NULL
,x_county => NULL
,x_country => NULL
,x_object_version_number => nvl(l_object_version_number,1)+1
);
HZ_LOCATION_PROFILES_PKG.Insert_Row (
x_location_profile_id => l_location_profile_rec.location_profile_id
,x_location_id => l_location_profile_rec.location_id
,x_actual_content_source => l_location_profile_rec.actual_content_source
,x_effective_start_date => sysdate
,x_effective_end_date => l_end_date
,x_validation_sst_flag => l_validation_sst_flag
,x_validation_status_code => l_location_profile_rec.validation_status_code
,x_date_validated => l_location_profile_rec.date_validated
,x_address1 => l_location_profile_rec.address1
,x_address2 => l_location_profile_rec.address2
,x_address3 => l_location_profile_rec.address3
,x_address4 => l_location_profile_rec.address4
,x_city => l_location_profile_rec.city
,x_postal_code => l_location_profile_rec.postal_code
,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
,x_county => l_location_profile_rec.county
,x_country => l_location_profile_rec.country
,x_object_version_number => 1
);
HZ_LOCATION_PROFILES_PKG.Insert_Row (
x_location_profile_id => l_location_profile_rec.location_profile_id
,x_location_id => l_location_profile_rec.location_id
,x_actual_content_source => l_location_profile_rec.actual_content_source
,x_effective_start_date => l_startdate
,x_effective_end_date => l_enddate
,x_validation_sst_flag => l_validation_sst_flag
,x_validation_status_code => l_location_profile_rec.validation_status_code
,x_date_validated => l_location_profile_rec.date_validated
,x_address1 => l_location_profile_rec.address1
,x_address2 => l_location_profile_rec.address2
,x_address3 => l_location_profile_rec.address3
,x_address4 => l_location_profile_rec.address4
,x_city => l_location_profile_rec.city
,x_postal_code => l_location_profile_rec.postal_code
,x_prov_state_admin_code => l_location_profile_rec.prov_state_admin_code
,x_county => l_location_profile_rec.county
,x_country => l_location_profile_rec.country
,x_object_version_number => 1
);
UPDATE HZ_LOCATIONS
SET date_validated = sysdate,
validation_status_code = l_location_profile_rec.validation_status_code
WHERE location_id = l_location_profile_rec.location_id;
UPDATE HZ_LOCATION_PROFILES
SET validation_sst_flag = 'N'
WHERE validation_sst_flag = 'Y'
AND sysdate between effective_start_date and nvl(effective_end_date, sysdate)
/* SSM SST Integration and Extension
* Removed the hard-coded value of DNB and will instead check if the
* source system is not of type PURCHASED.
AND actual_content_source <> l_location_profile_rec.actual_content_source
AND actual_content_source not in ('USER_ENTERED','DNB');*/
UPDATE HZ_LOCATIONS
SET date_validated = null,
validation_status_code = null
WHERE location_id = l_location_profile_rec.location_id;
HZ_LOCATION_PROFILES_PKG.Update_Row(
x_rowid => l_all_active_loc_profiles.rowid
,x_location_profile_id => l_all_active_loc_profiles.location_profile_id
,x_location_id => NULL
,x_actual_content_source => NULL
,x_effective_start_date => NULL
,x_effective_end_date => sysdate
,x_validation_sst_flag => NULL
,x_validation_status_code => NULL
,x_date_validated => NULL
,x_address1 => NULL
,x_address2 => NULL
,x_address3 => NULL
,x_address4 => NULL
,x_city => NULL
,x_postal_code => NULL
,x_prov_state_admin_code => NULL
,x_county => NULL
,x_country => NULL
,x_object_version_number => nvl(l_all_active_loc_profiles.object_version_number,1)+1
);
ROLLBACK TO update_location_profile_pub;
ROLLBACK TO update_location_profile_pub;
ROLLBACK TO update_location_profile_pub;
END update_location_profile;
p_create_update_flag IN VARCHAR2,
p_location_profile_rec IN location_profile_rec_type,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
select 'X'
from AR_LOOKUPS
where lookup_type = l_lookup_type
and lookup_code = l_lookup_code;
IF (p_create_update_flag = 'U') THEN
IF(l_actual_content_source = FND_API.G_MISS_CHAR) THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
ELSIF (p_create_update_flag = 'C') THEN
IF(l_actual_content_source IS NULL OR l_actual_content_source = FND_API.G_MISS_CHAR) THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
SELECT 'X'
FROM HZ_LOCATION_PROFILES
WHERE location_profile_id = l_location_profile_id;
UPDATE hz_location_profiles
SET effective_end_date = sysdate
WHERE location_profile_id = p_location_profile_id;
SELECT 'X'
FROM HZ_LOCATION_PROFILES
WHERE location_profile_id = l_location_profile_id;
UPDATE hz_location_profiles
SET validation_status_code = p_validation_status_code
WHERE location_profile_id = p_location_profile_id;
UPDATE hz_locations
SET validation_status_code = p_validation_status_code
WHERE location_id =
( SELECT location_id
FROM HZ_LOCATION_PROFILES
WHERE location_profile_id = p_location_profile_id);