[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_agendas_b_s.NEXTVAL
FROM DUAL;
SELECT count(*)
FROM ams_agendas_v
WHERE agenda_id = l_agenda_id;
SELECT *
FROM ams_agendas_b
WHERE default_track_flag = 'Y'
AND active_flag = 'Y'
AND parent_id = p_agenda_rec.parent_id;
SELECT coordinator_id
FROM ams_event_offers_vl
WHERE event_offer_id = id_in;
SELECT parent_id
FROM ams_agendas_v
WHERE agenda_id = id_in ;
SELECT coordinator_id
FROM ams_agendas_v
WHERE agenda_id = p_agenda_rec.parent_id;
SELECT timezone_id
FROM ams_event_offers_vl
WHERE event_offer_id = id_in;
INSERT INTO AMS_AGENDAS_B
(
agenda_id,
setup_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
application_id,
agenda_type,
room_id,
active_flag,
default_track_flag,
start_date_time,
end_date_time,
coordinator_id,
timezone_id,
parent_type,
parent_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
l_agenda_rec.agenda_id,
l_agenda_rec.setup_type_id,
sysdate,
FND_GLOBAL.User_Id,
sysdate,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1, -- object_version_number
l_agenda_rec.application_id,
l_agenda_rec.agenda_type,
l_agenda_rec.room_id,
NVL(l_agenda_rec.active_flag, 'Y'),
NVL(l_agenda_rec.default_track_flag, 'N'),
l_agenda_rec.start_date_time,
l_agenda_rec.end_date_time,
nvl(l_agenda_rec.coordinator_id,l_coordinator_id),
l_agenda_rec.timezone_id,
l_agenda_rec.parent_type,
l_agenda_rec.parent_id,
l_agenda_rec.attribute_category,
l_agenda_rec.attribute1,
l_agenda_rec.attribute2,
l_agenda_rec.attribute3,
l_agenda_rec.attribute4,
l_agenda_rec.attribute5,
l_agenda_rec.attribute6,
l_agenda_rec.attribute7,
l_agenda_rec.attribute8,
l_agenda_rec.attribute9,
l_agenda_rec.attribute10,
l_agenda_rec.attribute11,
l_agenda_rec.attribute12,
l_agenda_rec.attribute13,
l_agenda_rec.attribute14,
l_agenda_rec.attribute15
);
INSERT INTO ams_agendas_tl(
agenda_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
agenda_name,
description
)
SELECT
l_agenda_rec.agenda_id,
l.language_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
USERENV('LANG'),
l_agenda_rec.agenda_name,
l_agenda_rec.description
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS(
SELECT NULL
FROM ams_agendas_tl t
WHERE t.agenda_id = l_agenda_rec.agenda_id
AND t.language = l.language_code );
PROCEDURE Update_Agenda
( 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,
p_agenda_rec IN agenda_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Agenda';
SELECT activity_resource_id,object_version_number
FROM ams_act_resources
WHERE ACT_RESOURCE_USED_BY_ID = p_agenda_rec.agenda_id
AND role_cd = 'COORDINATOR'
AND resource_id = p_agenda_rec.coordinator_id;
SELECT start_date_time, end_date_time
FROM ams_agendas_v
WHERE agenda_id = p_agenda_rec.agenda_id;
SAVEPOINT Update_Agenda_PVT;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
coordinator(new) is attached as Resources for that Session.If so delete
him from resources and then update the Session. For Track, no resources
are attached, so the following logic is not needed for Tracks.
*/
IF(l_agenda_rec.agenda_type = 'SESSION')
THEN
OPEN c_resources(l_agenda_rec.agenda_id);
AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
( p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_Resource_id => l_act_res_id,
p_object_version => l_obj_ver_num
);
associated to them and then update their status to 'UNCONFIRMED'.
If the Session start time is increased, make start time of resources
(associated to it) whose start time is greater than it, equal to it.
If the Session end time is decreased, make end time of resources
(associated to it) whose end time is lesser than it, equal to it.
If the start time is decreased or end time is increased, it will have no
effect on the resources.
*/
OPEN c_olddate;
UPDATE ams_act_resources
SET system_status_code = 'UNCONFIRMED',
object_version_number = object_version_number + 1,
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y'),
start_date_time = start_date_time + l_DateDiff,
end_date_time = end_date_time + l_DateDiff
WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
AND system_status_code <> 'CANCELLED';
UPDATE ams_act_resources
SET object_version_number = object_version_number + 1,
start_date_time = p_agenda_rec.start_date_time,
end_date_time = p_agenda_rec.end_date_time
WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
AND ( (start_date_time < p_agenda_rec.start_date_time
AND end_date_time > p_agenda_rec.end_date_time)
OR
(start_date_time = p_agenda_rec.start_date_time
AND end_date_time > p_agenda_rec.end_date_time)
OR
(end_date_time = p_agenda_rec.end_date_time
AND start_date_time < p_agenda_rec.start_date_time) )
AND system_status_code <> 'CANCELLED';
UPDATE ams_act_resources
SET object_version_number = object_version_number + 1,
-- start_date_time = p_agenda_rec.start_date_time,
-- end_date_time = p_agenda_rec.end_date_time,
system_status_code = 'UNCONFIRMED',
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
AND start_date_time < p_agenda_rec.start_date_time
AND end_date_time > p_agenda_rec.end_date_time
AND system_status_code <> 'CANCELLED';
UPDATE ams_act_resources
SET object_version_number = object_version_number + 1,
-- end_date_time = p_agenda_rec.end_date_time,
system_status_code = 'UNCONFIRMED',
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
AND end_date_time > p_agenda_rec.end_date_time
AND system_status_code <> 'CANCELLED';
UPDATE ams_act_resources
SET object_version_number = object_version_number + 1,
-- start_date_time = p_agenda_rec.start_date_time,
system_status_code = 'UNCONFIRMED',
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE act_resource_used_by_id = p_agenda_rec.agenda_id
AND start_date_time < p_agenda_rec.start_date_time
AND system_status_code <> 'CANCELLED';
UPDATE AMS_AGENDAS_B
SET
setup_type_id = l_agenda_rec.setup_type_id
,last_update_date = sysdate
,last_updated_by = FND_GLOBAL.User_Id
,last_update_login = FND_GLOBAL.Conc_Login_Id
,object_version_number = l_agenda_rec.object_version_number+1
,room_id = l_agenda_rec.room_id
,start_date_time = l_agenda_rec.start_date_time
,end_date_time = l_agenda_rec.end_date_time
,coordinator_id = l_agenda_rec.coordinator_id
,timezone_id = l_agenda_rec.timezone_id
,parent_type = l_agenda_rec.parent_type
,parent_id = l_agenda_rec.parent_id
,attribute_category = l_agenda_rec.attribute_category
,attribute1 = l_agenda_rec.attribute1
,attribute2 = l_agenda_rec.attribute2
,attribute3 = l_agenda_rec.attribute3
,attribute4 = l_agenda_rec.attribute4
,attribute5 = l_agenda_rec.attribute5
,attribute6 = l_agenda_rec.attribute6
,attribute7 = l_agenda_rec.attribute7
,attribute8 = l_agenda_rec.attribute8
,attribute9 = l_agenda_rec.attribute9
,attribute10 = l_agenda_rec.attribute10
,attribute11 = l_agenda_rec.attribute11
,attribute12 = l_agenda_rec.attribute12
,attribute13 = l_agenda_rec.attribute13
,attribute14 = l_agenda_rec.attribute14
,attribute15 = l_agenda_rec.attribute15
WHERE agenda_id = l_agenda_rec.agenda_id
AND object_version_number = l_agenda_rec.object_version_number;
UPDATE ams_agendas_tl SET
agenda_name = l_agenda_rec.agenda_name,
description = l_agenda_rec.description,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
source_lang = USERENV('LANG')
WHERE agenda_id = l_agenda_rec.agenda_id
AND USERENV('LANG') IN (language, source_lang);
ROLLBACK TO Update_Agenda_PVT;
ROLLBACK TO Update_Agenda_PVT;
ROLLBACK TO Update_Agenda_PVT;
END Update_Agenda;
PROCEDURE Delete_Agenda
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_agenda_id IN NUMBER,
p_object_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Agenda';
SELECT *
FROM ams_agendas_b
WHERE agenda_id = p_agenda_id;
SELECT activity_resource_id,object_version_number
FROM ams_act_resources
WHERE act_resource_used_by_id = l_session_id;
SELECT count(event_offer_id)
FROM ams_event_offers_vl
WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
AND event_offer_id = ( SELECT parent_id
FROM ams_agendas_v
WHERE agenda_id = ( SELECT parent_id
FROM ams_agendas_v
WHERE agenda_id = p_agenda_id));
SELECT agenda_id, object_version_number
FROM ams_agendas_v
WHERE parent_id = p_agenda_id;
SAVEPOINT Delete_Agenda_PVT;
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
/* When deleting the Session, delete all the Resources attached to it */
IF (l_agenda_row.agenda_type = 'SESSION')
THEN
OPEN c_resources(p_agenda_id);
AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
( p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_Resource_id => l_act_res_id,
p_object_version => l_obj_ver_num
);
UPDATE ams_agendas_b
SET active_flag = 'N',
object_version_number = object_version_number + 1
WHERE agenda_id = l_agenda_id
AND object_version_number = l_obj_ver_num;
/* Deleting the resources attached to deleted Session */
OPEN c_resources(l_agenda_id);
AMS_ACTRESOURCE_PVT.DELETE_ACT_RESOURCE
( p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_Resource_id => l_act_res_id,
p_object_version => l_obj_ver_num
);
UPDATE ams_agendas_b
SET active_flag = 'N',
object_version_number = object_version_number + 1
WHERE agenda_id = p_agenda_id
AND object_version_number = p_object_version;
ROLLBACK TO Delete_Agenda_PVT;
ROLLBACK TO Delete_Agenda_PVT;
ROLLBACK TO Delete_Agenda_PVT;
END Delete_Agenda;
SELECT agenda_id
FROM AMS_AGENDAS_V
WHERE agenda_id = p_agenda_id
AND object_version_number = p_object_version
FOR UPDATE of agenda_id NOWAIT;
SELECT 1 FROM DUAL
WHERE EXISTS (SELECT 1 from ams_agendas_v
WHERE agenda_name = p_agenda_rec.agenda_name
AND parent_id = p_agenda_rec.parent_id);
SELECT event_start_date_time, event_end_date_time
FROM ams_event_offers_all_b
WHERE event_offer_id = l_offer_id;
SELECT parent_id
FROM ams_agendas_v
WHERE agenda_id = id_in;
SELECT min(start_date_time), max(end_date_time)
FROM ams_act_resources
WHERE act_resource_used_by_id = id_in
and arc_act_resource_used_by = 'SESSION'
and system_status_code = 'CONFIRMED';
SELECT count(event_offer_id)
FROM ams_event_offers_all_b
WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
AND event_offer_id = p_agenda_rec.parent_id;
SELECT count(event_offer_id)
FROM ams_event_offers_all_b
WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
AND event_offer_id = ( SELECT parent_id
FROM ams_agendas_b
WHERE agenda_id = p_agenda_rec.parent_id);
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
check_evo_update_ok_items(
p_agenda_rec => p_agenda_rec,
x_return_status => x_return_status
);
/* IF p_validation_mode = JTF_PLSQL_API.g_update THEN
open c_get_resource_dates(p_agenda_rec.agenda_id);
x_agenda_rec.last_update_date := FND_API.g_miss_date;
x_agenda_rec.last_updated_by := FND_API.g_miss_num;
x_agenda_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM AMS_AGENDAS_B
WHERE agenda_id = p_agenda_rec.agenda_id;
IF p_agenda_rec.last_updated_by = FND_API.g_miss_num THEN
x_agenda_rec.last_updated_by := l_agenda_rec.last_updated_by;
select parent_id
from ams_agendas_v
where agenda_id = id_in;
SELECT MIN(start_date_time), MAX(end_date_time)
from ams_agendas_v
where parent_id = id_in
and active_flag = 'Y';
UPDATE ams_agendas_b
SET start_date_time = l_min_time,
end_date_time = l_max_time,
object_version_number = object_version_number + 1
WHERE agenda_id = l_parent_id;
UPDATE ams_event_offers_all_b
SET event_start_date_time = l_min_time,
event_end_date_time = l_max_time,
object_version_number = object_version_number + 1
WHERE event_offer_id = l_parent_id;
UPDATE ams_event_offers_all_b
SET event_start_date_time = l_min_time,
event_end_date_time = l_max_time,
object_version_number = object_version_number + 1
WHERE event_offer_id = l_parent_id;
delete from ams_agendas_tl T
where not exists
(select NULL
from ams_agendas_b B
where B.AGENDA_ID = T.AGENDA_ID
);
update ams_agendas_tl T set (
DESCRIPTION
) = (select
B.DESCRIPTION
from ams_agendas_tl B
where B.AGENDA_ID = T.AGENDA_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.AGENDA_ID,
T.LANGUAGE
) in (select
SUBT.AGENDA_ID,
SUBT.LANGUAGE
from ams_agendas_tl SUBB, ams_agendas_tl SUBT
where SUBB.AGENDA_ID = SUBT.AGENDA_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.AGENDA_NAME <> SUBT.AGENDA_NAME
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into ams_agendas_tl (
AGENDA_ID,
LANGUAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SOURCE_LANG,
AGENDA_NAME,
DESCRIPTION,
SECURITY_GROUP_ID
) select
B.AGENDA_ID,
L.LANGUAGE_CODE,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.SOURCE_LANG,
B.AGENDA_NAME,
B.DESCRIPTION,
B.SECURITY_GROUP_ID
from ams_agendas_tl B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from ams_agendas_tl T
where T.AGENDA_ID = B.AGENDA_ID
and T.LANGUAGE = L.LANGUAGE_CODE);