[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_media_b_s.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_media_b
WHERE media_id = x_id);
AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
INSERT INTO ams_media_b (
media_id,
-- standard who columns
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
media_type_code,
inbound_flag,
enabled_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES (
l_media_rec.media_id,
-- standard who columns
SYSDATE,
FND_GLOBAL.User_Id,
SYSDATE,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1, -- object_version_number
l_media_rec.media_type_code,
NVL (l_media_rec.inbound_flag, 'Y'), -- Default is 'Y'. changed from 'N' to 'Y' by julou, 12/06/2000
NVL (l_media_rec.enabled_flag, 'Y'), -- Default is 'Y'
l_media_rec.attribute_category,
l_media_rec.attribute1,
l_media_rec.attribute2,
l_media_rec.attribute3,
l_media_rec.attribute4,
l_media_rec.attribute5,
l_media_rec.attribute6,
l_media_rec.attribute7,
l_media_rec.attribute8,
l_media_rec.attribute9,
l_media_rec.attribute10,
l_media_rec.attribute11,
l_media_rec.attribute12,
l_media_rec.attribute13,
l_media_rec.attribute14,
l_media_rec.attribute15
);
INSERT INTO ams_media_tl (
media_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
media_name,
description
)
SELECT l_media_rec.media_id,
l.language_code,
-- standard who columns
SYSDATE,
FND_GLOBAL.User_Id,
SYSDATE,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
USERENV('LANG'),
l_media_rec.media_name,
l_media_rec.description
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM ams_media_tl t
WHERE t.media_id = l_media_rec.media_id
AND t.language = l.language_code);
PROCEDURE Update_Media (
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_media_rec IN Media_Rec_Type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Media';
SAVEPOINT Update_Media;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message (l_full_name || ': Update');
UPDATE ams_media_b
SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Conc_Login_Id,
object_version_number = object_version_number + 1,
media_type_code = l_media_rec.media_type_code,
inbound_flag = NVL (l_media_rec.inbound_flag, 'Y'), -- changed default value to 'Y', julou 12/06/2000
enabled_flag = NVL (l_media_rec.enabled_flag, 'Y'),
attribute_category = l_media_rec.attribute_category,
attribute1 = l_media_rec.attribute1,
attribute2 = l_media_rec.attribute2,
attribute3 = l_media_rec.attribute3,
attribute4 = l_media_rec.attribute4,
attribute5 = l_media_rec.attribute5,
attribute6 = l_media_rec.attribute6,
attribute7 = l_media_rec.attribute7,
attribute8 = l_media_rec.attribute8,
attribute9 = l_media_rec.attribute9,
attribute10 = l_media_rec.attribute10,
attribute11 = l_media_rec.attribute11,
attribute12 = l_media_rec.attribute12,
attribute13 = l_media_rec.attribute13,
attribute14 = l_media_rec.attribute14,
attribute15 = l_media_rec.attribute15
WHERE media_id = l_media_rec.media_id
AND object_version_number = l_media_rec.object_version_number ;
UPDATE ams_media_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'),
media_name = l_media_rec.media_name,
description = l_media_rec.description
WHERE media_id = l_media_rec.media_id
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO Update_Media;
ROLLBACK TO Update_Media;
ROLLBACK TO Update_Media;
END Update_Media;
PROCEDURE Delete_Media (
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_media_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Media';
SELECT 'Y'
FROM ams_campaigns_all_b
WHERE media_id = l_media_id;*/
SELECT 'Y'
FROM ams_campaign_schedules_b
WHERE activity_id = l_media_id;
SELECT 'Y'
FROM ams_custom_setups_b
WHERE media_id = l_media_id;
SAVEPOINT Delete_Media;
AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
DELETE FROM ams_media_tl
WHERE media_id = p_media_id ;
DELETE FROM ams_media_b
WHERE media_id = p_media_id
AND object_version_number = p_object_version;
ROLLBACK TO Delete_Media;
ROLLBACK TO Delete_Media;
ROLLBACK TO Delete_Media;
END Delete_Media;
SELECT object_version_number
FROM ams_media_b
WHERE media_id = p_media_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
x_media_rec.last_update_date := FND_API.g_miss_date;
x_media_rec.last_updated_by := FND_API.g_miss_num;
x_media_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_media_vl
WHERE media_id = p_media_rec.media_id;
SELECT COUNT(1)
FROM ams_media_vl
WHERE UPPER(media_name) = UPPER(p_media_name) ;
SELECT COUNT(1)
FROM ams_media_vl
WHERE UPPER(media_name) = UPPER(p_media_name)
AND media_id <> p_media_id ;
SELECT ams_media_channels_s.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_media_channels
WHERE media_channel_id = x_id);
AMS_Utility_PVT.debug_message (l_full_name || ': insert');
INSERT INTO ams_media_channels (
media_channel_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
media_id,
channel_id,
active_from_date,
active_to_date
)
VALUES (
l_mediachl_rec.media_channel_id,
SYSDATE, -- last_update_date
FND_GLOBAL.user_id, -- last_updated_by
SYSDATE, -- creation_date
FND_GLOBAL.user_id, -- created_by
FND_GLOBAl.conc_login_id, -- last_update_login
1, -- object_version_number
l_mediachl_rec.media_id,
l_mediachl_rec.channel_id,
l_mediachl_rec.active_from_date,
l_mediachl_rec.active_to_date
);
PROCEDURE Update_MediaChannel (
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_mediachl_rec IN MediaChannel_Rec_Type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_MediaChannel';
SAVEPOINT Update_MediaChannel;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message(l_full_name ||': update');
UPDATE ams_media_channels
SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = object_version_number + 1,
media_id = l_mediachl_rec.media_id,
channel_id = l_mediachl_rec.channel_id,
active_from_date = l_mediachl_rec.active_from_date,
active_to_date = l_mediachl_rec.active_to_date
WHERE media_channel_id = l_mediachl_rec.media_channel_id
AND object_version_number = l_mediachl_rec.object_version_number ;
ROLLBACK TO Update_MediaChannel;
ROLLBACK TO Update_MediaChannel;
ROLLBACK TO Update_MediaChannel;
END Update_MediaChannel;
PROCEDURE Delete_MediaChannel (
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_mediachl_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_MediaChannel';
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT A.campaign_id
FROM ams_campaigns_all_b A, ams_media_channels B -- Perf fix use all_b
WHERE A.active_flag = 'Y'
AND A.arc_channel_from = 'CHLS'
AND A.media_id = B.media_id
AND A.channel_id = B.channel_id
ANd B.media_channel_id = p_mediachl_id);
SELECT marketing_medium_id
FROM ams_campaign_schedules_b a, ams_media_channels b
WHERE a.marketing_medium_id = b.channel_id
-- Added by dbiswas 12/31/02 to allow removal of medium from activity
AND a.activity_id = b.media_id
-- end change 12/31/02
AND b.media_channel_id = p_mediachl_id;
SAVEPOINT Delete_MediaChannel;
AMS_Utility_PVT.debug_message (l_full_name || ': check before delete');
FND_MESSAGE.set_name('AMS', 'AMS_MED_CANNOT_DELETE_CHAN');
AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
DELETE FROM ams_media_channels
WHERE media_channel_id = p_mediachl_id;
ROLLBACK TO Delete_MediaChannel;
ROLLBACK TO Delete_MediaChannel;
ROLLBACK TO Delete_MediaChannel;
END Delete_MediaChannel;
SELECT object_version_number
FROM ams_media_channels
WHERE media_channel_id = p_mediachl_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT active_from_date, active_to_date
FROM AMS_CHANNELS_VL
WHERE channel_id = p_chan_id;
x_mediachl_rec.last_update_date := FND_API.g_miss_date;
x_mediachl_rec.last_updated_by := FND_API.g_miss_num;
x_mediachl_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_media_channels
WHERE media_channel_id = p_mediachl_rec.media_channel_id;
SELECT count(*)
FROM ams_media_channels
WHERE media_id = p_media_id
AND channel_id = p_channel_id
AND active_from_date <= sysdate
AND (active_to_date IS NULL OR active_to_date > SYSDATE) ;
CURSOR c_check_uniqueness_update(p_media_id IN NUMBER, p_channel_id IN NUMBER, p_media_channel_id IN NUMBER) IS
SELECT count(*)
FROM ams_media_channels
WHERE media_id = p_media_id
AND channel_id = p_channel_id
AND media_channel_id <> p_media_channel_id
AND active_from_date <= sysdate
AND (active_to_date IS NULL OR active_to_date > SYSDATE) ;
AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items UPDATE');
OPEN c_check_uniqueness_update(p_mediachl_rec.media_id,p_mediachl_rec.channel_id,p_mediachl_rec.media_channel_id);
FETCH c_check_uniqueness_update INTO l_count;
CLOSE c_check_uniqueness_update;
SELECT active_from_date,
active_to_date
FROM ams_channels_b
WHERE channel_id = p_mediachl_rec.channel_id
;