[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT map_id
INTO l_map_id
FROM hz_geo_struct_map
WHERE map_id = p_address_usages_rec.map_id;
SELECT lookup_code
INTO l_usage_code
FROM ar_lookups
WHERE lookup_type = 'HZ_GEOGRAPHY_USAGE'
AND lookup_code = p_address_usages_rec.usage_code;
BEGIN --insert row
SELECT HZ_ADDRESS_USAGES_S.nextval
INTO l_usage_id
FROM dual;
hz_address_usages_pkg.insert_row(
l_usage_row_id,
l_usage_id,
p_address_usages_rec.map_id,
p_address_usages_rec.usage_code,
p_address_usages_rec.status_flag,
1,
p_address_usages_rec.created_by_module,
p_address_usages_rec.application_id);
SELECT 'X'
INTO l_map_dtl_extsts
FROM hz_geo_struct_map_dtl
WHERE map_id = p_address_usages_rec.map_id
AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
SELECT HZ_ADDRESS_USAGES_S.nextval
INTO l_usage_dtl_id
FROM dual;
hz_address_usage_dtls_pkg.insert_row(
x_usage_dtl_row_id,
l_usage_dtl_id,
l_usage_id,
p_address_usage_dtls_tbl(i).geography_type,
1,
p_address_usage_dtls_tbl(i).created_by_module,
p_address_usage_dtls_tbl(i).application_id);
END; -- insert row
PROCEDURE update_address_usages
(p_usage_id IN NUMBER,
p_map_id IN NUMBER,
p_usage_code IN VARCHAR2,
p_status_flag IN VARCHAR2,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_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_map_id NUMBER;
db_last_updated_by hz_address_usages.last_updated_by%TYPE;
db_last_update_date hz_address_usages.last_update_date%TYPE;
db_last_update_login hz_address_usages.last_update_login%TYPE;
SAVEPOINT update_address_usages;
SELECT rowid, usage_id, object_version_number, map_id,
usage_code, status_flag, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, created_by_module, application_id
INTO l_usage_row_id, l_usage_id, db_object_version_number, db_map_id,
db_usage_code, db_status_flag, db_created_by, db_creation_date, db_last_updated_by,
db_last_update_date, db_last_update_login, db_created_by_module, db_application_id
FROM hz_address_usages
WHERE usage_id = p_usage_id;
SELECT rowid, usage_id, object_version_number, map_id,
usage_code, status_flag, created_by, creation_date, last_updated_by,
last_update_date, last_update_login, created_by_module, application_id
INTO l_usage_row_id, l_usage_id, db_object_version_number, db_map_id,
db_usage_code, db_status_flag, db_created_by, db_creation_date, db_last_updated_by,
db_last_update_date, db_last_update_login, db_created_by_module, db_application_id
FROM hz_address_usages
WHERE map_id = p_map_id
AND usage_code = p_usage_code;
x_last_updated_by => db_last_updated_by,
x_last_update_date => db_last_update_date,
x_last_update_login => db_last_update_login,
x_object_version_number => x_object_version_number,
x_created_by_module => db_created_by_module,
x_application_id => db_application_id);
hz_address_usages_pkg.Update_Row (
x_rowid => l_usage_row_id,
x_usage_id => l_usage_id,
x_map_id => db_map_id,
x_usage_code => db_usage_code,
x_status_flag => p_status_flag,
x_object_version_number => l_object_version_number,
x_created_by_module => db_created_by_module,
x_application_id => db_application_id);
SELECT count(*)
INTO l_count
FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
WHERE usg.map_id = db_map_id
AND usg.status_flag = 'A'
AND dtl.usage_id = usg.usage_id;
ROLLBACK TO update_address_usages;
ROLLBACK TO update_address_usages;
ROLLBACK TO update_address_usages;
END update_address_usages;
SELECT map_id, usage_code, status_flag
INTO l_map_id, l_usage_code, l_status_flag
FROM hz_address_usages
WHERE usage_id = p_usage_id;
SELECT 'X'
INTO l_map_dtl_extsts
FROM hz_geo_struct_map_dtl
WHERE map_id = l_map_id
AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
SELECT HZ_ADDRESS_USAGES_S.nextval
INTO l_usage_dtl_id
FROM dual;
hz_address_usage_dtls_pkg.insert_row(
x_usage_dtl_row_id,
l_usage_dtl_id,
p_usage_id,
p_address_usage_dtls_tbl(i).geography_type,
1,
p_address_usage_dtls_tbl(i).created_by_module,
p_address_usage_dtls_tbl(i).application_id);
END; -- insert row
PROCEDURE delete_address_usages(
p_usage_id IN NUMBER,
p_address_usage_dtls_tbl IN address_usage_dtls_tbl_type,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_map_id NUMBER;
SELECT usage_dtl_id
FROM hz_address_usage_dtls
WHERE usage_id = p_usage_id;
SAVEPOINT delete_address_usages;
SELECT map_id, usage_code, status_flag
INTO l_map_id, l_usage_code, l_status_flag
FROM hz_address_usages
WHERE usage_id = p_usage_id;
SELECT usage_dtl_id
INTO l_usage_dtl_id
FROM hz_address_usage_dtls
WHERE usage_id = p_usage_id
AND geography_type = p_address_usage_dtls_tbl(i).geography_type;
SELECT count(*)
INTO l_count
FROM hz_address_usage_dtls
WHERE usage_id = p_usage_id;
SELECT usage_dtl_id
INTO l_last_usg_dtl_id
FROM hz_address_usage_dtls
WHERE usage_id = p_usage_id
AND usage_dtl_id <> l_usage_dtl_id;
hz_address_usage_dtls_pkg.delete_row(l_last_usg_dtl_id);
hz_address_usages_pkg.delete_row(p_usage_id);
hz_address_usages_pkg.delete_row(p_usage_id);
hz_address_usage_dtls_pkg.delete_row(l_usage_dtl_id);
hz_address_usage_dtls_pkg.delete_row(i.usage_dtl_id);
hz_address_usages_pkg.delete_row(p_usage_id);
SELECT count(*)
INTO l_count
FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
WHERE usg.map_id = l_map_id
AND usg.status_flag = 'A'
AND dtl.usage_id = usg.usage_id;
ROLLBACK TO delete_address_usages;
ROLLBACK TO delete_address_usages;
ROLLBACK TO delete_address_usages;
END delete_address_usages;