The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT AMS_MESSAGES_B_S.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM AMS_MESSAGES_VL
WHERE message_id = msg_id;
AMS_Utility_PVT.debug_message(l_full_name || ': insert');
INSERT INTO AMS_MESSAGES_B
(
message_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
date_effective_from,
date_effective_to,
active_flag,
message_type_code,
owner_user_id,
country_id,
custom_setup_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
l_msg_rec.message_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1,
l_msg_rec.date_effective_from,
l_msg_rec.date_effective_to,
l_msg_rec.active_flag,
l_msg_rec.message_type_code,
l_msg_rec.owner_user_id,
l_msg_rec.country_id,
1000,
l_msg_rec.attribute_category,
l_msg_rec.attribute1,
l_msg_rec.attribute2,
l_msg_rec.attribute3,
l_msg_rec.attribute4,
l_msg_rec.attribute5,
l_msg_rec.attribute6,
l_msg_rec.attribute7,
l_msg_rec.attribute8,
l_msg_rec.attribute9,
l_msg_rec.attribute10,
l_msg_rec.attribute11,
l_msg_rec.attribute12,
l_msg_rec.attribute13,
l_msg_rec.attribute14,
l_msg_rec.attribute15
);
INSERT INTO AMS_MESSAGES_TL
(
message_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
message_name,
description
)
SELECT
l_msg_rec.message_id,
l.language_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
USERENV('LANG'),
l_msg_rec.message_name,
l_msg_rec.description
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS
(
SELECT NULL
FROM AMS_MESSAGES_TL t
WHERE t.message_id = l_msg_rec.message_id
AND t.language = l.language_code
);
PROCEDURE update_msg
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_msg_rec IN msg_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_msg';
SAVEPOINT update_msg;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status,
p_msg_rec => l_msg_rec
);
AMS_Utility_PVT.debug_message(l_full_name||': update');
UPDATE AMS_MESSAGES_B SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = l_msg_rec.object_version_number + 1,
date_effective_from = l_msg_rec.date_effective_from,
date_effective_to = l_msg_rec.date_effective_to,
active_flag = l_msg_rec.active_flag,
message_type_code = l_msg_rec.message_type_code,
owner_user_id = l_msg_rec.owner_user_id,
attribute_category = l_msg_rec.attribute_category,
attribute1 = l_msg_rec.attribute1,
attribute2 = l_msg_rec.attribute2,
attribute3 = l_msg_rec.attribute3,
attribute4 = l_msg_rec.attribute4,
attribute5 = l_msg_rec.attribute5,
attribute6 = l_msg_rec.attribute6,
attribute7 = l_msg_rec.attribute7,
attribute8 = l_msg_rec.attribute8,
attribute9 = l_msg_rec.attribute9,
attribute10 = l_msg_rec.attribute10,
attribute11 = l_msg_rec.attribute11,
attribute12 = l_msg_rec.attribute12,
attribute13 = l_msg_rec.attribute13,
attribute14 = l_msg_rec.attribute14,
attribute15 = l_msg_rec.attribute15
WHERE message_id = l_msg_rec.message_id
AND object_version_number = l_msg_rec.object_version_number;
UPDATE AMS_MESSAGES_TL SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
source_lang = USERENV('LANG'),
message_name = l_msg_rec.message_name,
description = l_msg_rec.description
WHERE message_id = l_msg_rec.message_id
AND USERENV('LANG') IN (language, source_lang);
ROLLBACK TO update_msg;
ROLLBACK TO update_msg;
ROLLBACK TO update_msg;
END update_msg;
PROCEDURE delete_msg
(
p_api_version IN NUMBER,
P_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_msg_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_msg';
SAVEPOINT delete_msg;
AMS_Utility_PVT.debug_message(l_full_name || ': delete');
DELETE FROM AMS_MESSAGES_TL
WHERE message_id = p_msg_id;
DELETE FROM AMS_MESSAGES_B
WHERE message_id = p_msg_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_msg;
ROLLBACK TO delete_msg;
ROLLBACK TO delete_msg;
END delete_msg;
SELECT message_id
FROM AMS_MESSAGES_B
WHERE message_id = p_msg_id
AND object_version_number = p_object_version
FOR UPDATE OF message_id NOWAIT;
SELECT message_id
FROM AMS_MESSAGES_TL
WHERE message_id = p_msg_id
AND USERENV('LANG') IN (language, source_lang)
FOR UPDATE OF message_id NOWAIT;
AND p_validation_mode = JTF_PLSQL_API.g_update THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_MSG_NO_MSG_ID');
AND p_validation_mode = JTF_PLSQL_API.g_update
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_NO_OBJ_VER_NUM');
SELECT ''
FROM AMS_MESSAGES_TL
WHERE message_id <> l_msg_id
AND message_name = l_msg_name
AND language = USERENV('LANG');
SELECT ''
FROM AMS_MESSAGES_TL
WHERE message_name = l_msg_name
AND language = USERENV('LANG');
SELECT * FROM AMS_MESSAGES_VL
WHERE message_id = p_msg_rec.message_id;
x_msg_rec.last_update_date := FND_API.g_miss_date;
x_msg_rec.last_updated_by := FND_API.g_miss_num;
x_msg_rec.last_update_login := FND_API.g_miss_num;