The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_act_resources_s.NEXTVAL
FROM dual;
SELECT start_date_time FROM ams_agendas_b
WHERE agenda_id = id_in
AND agenda_type = type_in;
SELECT system_status_code FROM ams_user_statuses_v
WHERE user_status_id = id_in;
INSERT INTO AMS_ACT_RESOURCES
(
activity_resource_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
act_resource_used_by_id,
arc_act_resource_used_by,
resource_id,
role_cd,
user_status_id,
SYSTEM_STATUS_CODE,
start_date_time,
end_date_time,
description,
--TOP_LEVEL_PARENT_ID
--TOP_LEVEL_PARENT_TYPE
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
l_act_resource_rec.activity_resource_id,
sysdate,
FND_GLOBAL.User_Id,
sysdate,
FND_GLOBAL.User_Id,
FND_GLOBAL.Conc_Login_Id,
1, -- object_version_number
l_act_Resource_rec.act_resource_used_by_id,
l_act_Resource_rec.arc_act_resource_used_by,
l_act_Resource_rec.resource_id,
l_act_Resource_rec.role_cd,
l_act_resource_rec.user_status_id,
l_act_resource_rec.system_status_code,
l_act_resource_rec.start_date_time,
l_act_resource_rec.end_date_time,
l_act_resource_rec.description,
--l_act_resource_rec.top_level_parent_id,
--l_act_resource_rec.top_level_parent_type,
l_act_Resource_rec.attribute_category,
l_act_Resource_rec.attribute1,
l_act_Resource_rec.attribute2,
l_act_Resource_rec.attribute3,
l_act_Resource_rec.attribute4,
l_act_Resource_rec.attribute5,
l_act_Resource_rec.attribute6,
l_act_Resource_rec.attribute7,
l_act_Resource_rec.attribute8,
l_act_Resource_rec.attribute9,
l_act_Resource_rec.attribute10,
l_act_Resource_rec.attribute11,
l_act_Resource_rec.attribute12,
l_act_Resource_rec.attribute13,
l_act_Resource_rec.attribute14,
l_act_Resource_rec.attribute15
);
PROCEDURE Update_Act_Resource
( 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_act_Resource_rec IN act_Resource_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Act_Resource';
SELECT start_date_time FROM ams_agendas_b
WHERE agenda_id = id_in
AND agenda_type = type_in;
SELECT system_status_code FROM ams_user_statuses_v
WHERE user_status_id = id_in;
SAVEPOINT Update_Act_Resource_PVT;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
update AMS_ACT_RESOURCES
set
last_update_date = sysdate
,last_updated_by = FND_GLOBAL.User_Id
,last_update_login = FND_GLOBAL.Conc_Login_Id
,object_version_number = l_act_Resource_rec.object_version_number+1
,act_resource_used_by_id = l_act_resource_rec.act_resource_used_by_id
,arc_act_resource_used_by = l_act_resource_rec.arc_act_resource_used_by
,resource_id = l_act_resource_rec.resource_id
,role_cd = l_act_resource_rec.role_cd
,user_status_id = l_act_resource_rec.user_status_id
,system_status_code = l_act_resource_rec.system_status_code
,start_date_time = l_act_resource_rec.start_date_time
,end_date_time = l_act_resource_rec.end_date_time
,description = l_act_resource_rec.description
--,top_level_parten_id = l_act_resource_rec.top_level_parten_id
--,top_level_parten_type = l_act_resource_rec.top_level_parten_type
,attribute_category = l_act_Resource_rec.attribute_category
,attribute1 = l_act_Resource_rec.attribute1
,attribute2 = l_act_Resource_rec.attribute2
,attribute3 = l_act_Resource_rec.attribute3
,attribute4 = l_act_Resource_rec.attribute4
,attribute5 = l_act_Resource_rec.attribute5
,attribute6 = l_act_Resource_rec.attribute6
,attribute7 = l_act_Resource_rec.attribute7
,attribute8 = l_act_Resource_rec.attribute8
,attribute9 = l_act_Resource_rec.attribute9
,attribute10 = l_act_Resource_rec.attribute10
,attribute11 = l_act_Resource_rec.attribute11
,attribute12 = l_act_Resource_rec.attribute12
,attribute13 = l_act_Resource_rec.attribute13
,attribute14 = l_act_Resource_rec.attribute14
,attribute15 = l_act_Resource_rec.attribute15
WHERE activity_resource_id = l_act_Resource_rec.activity_resource_id
AND object_version_number = l_act_Resource_rec.object_version_number;
ROLLBACK TO Update_Act_Resource_PVT;
ROLLBACK TO Update_Act_Resource_PVT;
ROLLBACK TO Update_Act_Resource_PVT;
END Update_Act_Resource;
PROCEDURE Delete_Act_Resource
( 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_act_Resource_id IN NUMBER,
p_object_version IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Act_Resource';
SAVEPOINT Delete_Act_Resource_PVT;
DELETE FROM ams_act_resources
WHERE ACTIVITY_RESOURCE_ID = p_act_Resource_id
AND p_object_version = p_object_version;
ROLLBACK TO Delete_Act_Resource_PVT;
ROLLBACK TO Delete_Act_Resource_PVT;
ROLLBACK TO Delete_Act_Resource_PVT;
END Delete_Act_Resource;
SELECT activity_resource_id
FROM AMS_ACT_RESOURCES
WHERE activity_resource_id = p_act_resource_id
AND object_version_number = p_object_version
FOR UPDATE of activity_resource_id NOWAIT;
SELECT coordinator_id
FROM ams_agendas_v
WHERE agenda_id = l_session_id;
SELECT start_date_time, end_date_time
FROM AMS_agendas_b
WHERE agenda_id = id_in
AND agenda_TYPE = type_in;
SELECT event_start_date_time, event_end_date_time
FROM ams_event_offers_all_b
WHERE event_offer_id = id_in
AND event_object_type = type_in;
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_act_Resource_rec.act_resource_used_by_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 = ( SELECT parent_id
FROM ams_agendas_b
WHERE agenda_id = p_act_Resource_rec.act_resource_used_by_id));
SELECT 1 into l_count
FROM ams_act_resources_v
WHERE act_resource_used_by_id = p_act_Resource_rec.act_resource_used_by_id
AND arc_act_resource_used_by = p_act_Resource_rec.arc_act_resource_used_by
AND resource_id = p_act_Resource_rec.resource_id
AND system_status_code = 'CONFIRMED'
-- AND system_status_code = p_act_Resource_rec.system_status_code
AND
(start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time
OR
end_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time
OR
p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time);
end update by dbiswas on Apr 28, 2003
*/
x_return_status := FND_API.G_RET_STS_SUCCESS;
x_act_Resource_rec.last_update_date := FND_API.g_miss_date;
x_act_Resource_rec.last_updated_by := FND_API.g_miss_num;
x_act_Resource_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_act_resources
WHERE activity_resource_id = p_act_Resource_rec.activity_resource_id;
IF p_act_Resource_rec.last_updated_by = FND_API.g_miss_num THEN
x_act_Resource_rec.last_updated_by := l_act_Resource_rec.last_updated_by;
IF p_act_Resource_rec.last_update_date = FND_API.g_miss_date THEN
x_act_Resource_rec.last_update_date := l_act_Resource_rec.last_update_date;
SELECT count(*)
FROM ams_act_resources
WHERE resource_id = id_in
AND arc_act_resource_used_by = 'SESSION'
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
CURSOR C_check_sessions_update IS
SELECT count(*)
FROM ams_act_resources
WHERE resource_id = p_act_Resource_rec.resource_id
AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
AND arc_act_resource_used_by = 'SESSION'
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
SELECT count(*)
FROM ams_act_resources
WHERE resource_id = p_act_Resource_rec.resource_id
AND act_resource_used_by_id <> ( SELECT parent_id
FROM ams_agendas_b
WHERE agenda_id = (SELECT parent_id
FROM ams_agendas_b
WHERE agenda_id = p_act_Resource_rec.act_resource_used_by_id))
AND arc_act_resource_used_by IN ('EVEO', 'EONE')
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
SELECT count(*)
FROM ams_act_resources
WHERE resource_id = p_act_Resource_rec.resource_id
AND arc_act_resource_used_by IN ('EVEO', 'EONE')
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
CURSOR C_check_events_update IS
SELECT count(*)
FROM ams_act_resources
WHERE resource_id = p_act_Resource_rec.resource_id
AND arc_act_resource_used_by IN ('EVEO', 'EONE')
AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
SELECT count(*)
FROM ams_act_resources
WHERE arc_act_resource_used_by = 'SESSION'
AND system_status_code = 'CONFIRMED'
AND role_cd <> 'COORDINATOR'
AND act_resource_used_by_id IN ( SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id <> p_act_Resource_rec.act_resource_used_by_id
AND parent_type IN ('EVEO', 'EONE'))
AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
OR p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
OR start_date_time BETWEEN p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);
ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
THEN
/* checking across all the sessions excluding itself for date overlap */
OPEN C_check_sessions_update;
FETCH C_check_sessions_update INTO l_count;
CLOSE C_check_sessions_update;
ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
THEN
/* checking across all the events except itself, for date overlap */
OPEN C_check_events_update;
FETCH C_check_events_update INTO l_count;
CLOSE C_check_events_update;