[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row (
x_location_profile_id IN OUT NOCOPY NUMBER,
x_location_id IN NUMBER,
x_actual_content_source IN VARCHAR2,
x_effective_start_date IN DATE,
x_effective_end_date IN DATE,
x_validation_sst_flag IN VARCHAR2,
x_validation_status_code IN VARCHAR2,
x_date_validated IN DATE,
x_address1 IN VARCHAR2,
x_address2 IN VARCHAR2,
x_address3 IN VARCHAR2,
x_address4 IN VARCHAR2,
x_city IN VARCHAR2,
x_postal_code IN VARCHAR2,
x_prov_state_admin_code IN VARCHAR2,
x_county IN VARCHAR2,
x_country IN VARCHAR2,
x_object_version_number IN NUMBER
) IS
l_success VARCHAR2(1) := 'N';
INSERT INTO hz_location_profiles (
location_profile_id,
location_id,
actual_content_source,
effective_start_date,
effective_end_date,
validation_sst_flag,
validation_status_code,
date_validated,
address1,
address2,
address3,
address4,
city,
postal_code,
prov_state_admin_code,
county,
country,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number
)
VALUES (
DECODE(x_location_profile_id,
fnd_api.g_miss_num, hz_location_profiles_s.NEXTVAL,
NULL, hz_location_profiles_s.NEXTVAL,
x_location_profile_id),
DECODE(x_location_id, fnd_api.g_miss_num, NULL, x_location_id),
DECODE(x_actual_content_source, fnd_api.g_miss_char, NULL, x_actual_content_source),
DECODE(x_effective_start_date, fnd_api.g_miss_date, TO_DATE(NULL), x_effective_start_date),
DECODE(x_effective_end_date, fnd_api.g_miss_date, TO_DATE(NULL), x_effective_end_date),
DECODE(x_validation_sst_flag, fnd_api.g_miss_char, NULL, x_validation_sst_flag),
DECODE(x_validation_status_code, fnd_api.g_miss_char, NULL, x_validation_status_code),
DECODE(x_date_validated, fnd_api.g_miss_date, TO_DATE(NULL), x_date_validated),
DECODE(x_address1, fnd_api.g_miss_char, NULL, x_address1),
DECODE(x_address2, fnd_api.g_miss_char, NULL, x_address2),
DECODE(x_address3, fnd_api.g_miss_char, NULL, x_address3),
DECODE(x_address4, fnd_api.g_miss_char, NULL, x_address4),
DECODE(x_city, fnd_api.g_miss_char, NULL, x_city),
DECODE(x_postal_code, fnd_api.g_miss_char, NULL, x_postal_code),
DECODE(x_prov_state_admin_code, fnd_api.g_miss_char, NULL, x_prov_state_admin_code),
DECODE(x_county, fnd_api.g_miss_char, NULL, x_county),
DECODE(x_country, fnd_api.g_miss_char, NULL, x_country),
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_updated_by,
hz_utility_v2pub.creation_date,
hz_utility_v2pub.created_by,
hz_utility_v2pub.last_update_login,
DECODE(x_object_version_number, fnd_api.g_miss_num, NULL, x_object_version_number)
) RETURNING
location_profile_id
INTO
x_location_profile_id;
SELECT max(LOCATION_PROFILE_ID) INTO l_max_profile_id
FROM HZ_LOCATION_PROFILES;
SELECT HZ_LOCATION_PROFILES_S.NEXTVAL
INTO l_temp_profile_id FROM dual;
END insert_row;
PROCEDURE update_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_location_profile_id IN NUMBER,
x_location_id IN NUMBER,
x_actual_content_source IN VARCHAR2,
x_effective_start_date IN DATE,
x_effective_end_date IN DATE,
x_validation_sst_flag IN VARCHAR2,
x_validation_status_code IN VARCHAR2,
x_date_validated IN DATE,
x_address1 IN VARCHAR2,
x_address2 IN VARCHAR2,
x_address3 IN VARCHAR2,
x_address4 IN VARCHAR2,
x_city IN VARCHAR2,
x_postal_code IN VARCHAR2,
x_prov_state_admin_code IN VARCHAR2,
x_county IN VARCHAR2,
x_country IN VARCHAR2,
x_object_version_number IN NUMBER
) IS
BEGIN
UPDATE hz_location_profiles
SET location_profile_id = DECODE(x_location_profile_id,
NULL, location_profile_id,
fnd_api.g_miss_num, NULL,
x_location_profile_id),
location_id = DECODE(x_location_id,
NULL, location_id,
fnd_api.g_miss_num, NULL,
x_location_id),
actual_content_source = DECODE(x_actual_content_source,
NULL, actual_content_source,
fnd_api.g_miss_char, NULL,
x_actual_content_source),
effective_start_date = DECODE(x_effective_start_date,
NULL, effective_start_date,
fnd_api.g_miss_date, NULL,
x_effective_start_date),
effective_end_date = DECODE(x_effective_end_date,
NULL, effective_end_date,
fnd_api.g_miss_date, NULL,
x_effective_end_date),
validation_sst_flag = DECODE(x_validation_sst_flag,
NULL, validation_sst_flag,
fnd_api.g_miss_char, NULL,
x_validation_sst_flag),
validation_status_code = DECODE(x_validation_status_code,
NULL, validation_status_code,
fnd_api.g_miss_char, NULL,
x_validation_status_code),
date_validated = DECODE(x_date_validated,
NULL, date_validated,
fnd_api.g_miss_date, NULL,
x_date_validated),
address1 = DECODE(x_address1,
NULL, address1,
fnd_api.g_miss_char, NULL,
x_address1),
address2 = DECODE(x_address2,
NULL, address2,
fnd_api.g_miss_char, NULL,
x_address2),
address3 = DECODE(x_address3,
NULL, address3,
fnd_api.g_miss_char, NULL,
x_address3),
address4 = DECODE(x_address4,
NULL, address4,
fnd_api.g_miss_char, NULL,
x_address4),
city = DECODE(x_city,
NULL, city,
fnd_api.g_miss_char, NULL,
x_city),
postal_code = DECODE(x_postal_code,
NULL, postal_code,
fnd_api.g_miss_char, NULL,
x_postal_code),
prov_state_admin_code = DECODE(x_prov_state_admin_code,
NULL, prov_state_admin_code,
fnd_api.g_miss_char, NULL,
x_prov_state_admin_code),
county = DECODE(x_county,
NULL, county,
fnd_api.g_miss_char, NULL,
x_county),
country = DECODE(x_country,
NULL, country,
fnd_api.g_miss_char, NULL,
x_country),
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
creation_date = creation_date,
created_by = created_by,
last_update_login = hz_utility_v2pub.last_update_login,
object_version_number = DECODE(x_object_version_number,
NULL, object_version_number,
fnd_api.g_miss_num, NULL,
x_object_version_number)
WHERE ROWID = x_rowid;
END update_row;
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_creation_date IN DATE,
x_created_by IN NUMBER,
x_last_update_login IN NUMBER,
x_object_version_number IN NUMBER
) IS
CURSOR c IS
SELECT *
FROM hz_location_profiles
WHERE ROWID = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
AND ((recinfo.last_update_date = x_last_update_date)
OR ((recinfo.last_update_date IS NULL)
AND (x_last_update_date IS NULL)))
AND ((recinfo.last_updated_by = x_last_updated_by)
OR ((recinfo.last_updated_by IS NULL)
AND (x_last_updated_by IS NULL)))
AND ((recinfo.creation_date = x_creation_date)
OR ((recinfo.creation_date IS NULL)
AND (x_creation_date IS NULL)))
AND ((recinfo.created_by = x_created_by)
OR ((recinfo.created_by IS NULL)
AND (x_created_by IS NULL)))
AND ((recinfo.last_update_login = x_last_update_login)
OR ((recinfo.last_update_login IS NULL)
AND (x_last_update_login IS NULL)))
AND ((recinfo.object_version_number = x_object_version_number)
OR ((recinfo.object_version_number IS NULL)
AND (x_object_version_number IS NULL)))
)
THEN
RETURN;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE delete_row (x_location_profile_id IN NUMBER) IS
BEGIN
DELETE FROM hz_location_profiles
WHERE location_profile_id = x_location_profile_id;
END delete_row;