[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_act_access_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ams_act_access
WHERE activity_access_id = l_access_id;
SELECT *
FROM ams_campaign_schedules_vl
WHERE campaign_id = l_camp_id;
SELECT 1
FROM DUAL
WHERE NOT EXISTS(SELECT 1
FROM ams_act_access
WHERE ams_act_access.act_access_to_object_id = p_access_rec.act_access_to_object_id
AND ams_act_access.arc_act_access_to_object = p_access_rec.arc_act_access_to_object
AND ams_act_access.user_or_role_id = p_access_rec.user_or_role_id
AND ams_act_access.delete_flag='Y');
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
INSERT INTO ams_act_access(
activity_access_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
act_access_to_object_id,
arc_act_access_to_object,
user_or_role_id,
arc_user_or_role_type,
active_from_date,
active_to_date,
admin_flag,
approver_flag,
owner_flag,
delete_flag )
VALUES(
l_access_rec.activity_access_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object_version_number
l_access_rec.act_access_to_object_id,
l_access_rec.arc_act_access_to_object,
l_access_rec.user_or_role_id,
l_access_rec.arc_user_or_role_type,
l_access_rec.active_from_date,
l_access_rec.active_to_date,
decode(l_access_rec.owner_flag,'Y','Y',nvl(l_access_rec.admin_flag,'N') ),
l_access_rec.approver_flag,
l_access_rec.owner_flag,
'N' );
ams_access_denorm_pvt.insert_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_access_rec.act_access_to_object_id,
p_object_type => l_access_rec.arc_act_access_to_object,
p_edit_metrics => l_access_rec.admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
INSERT INTO ams_act_access(
activity_access_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
act_access_to_object_id,
arc_act_access_to_object,
user_or_role_id,
arc_user_or_role_type,
active_from_date,
active_to_date,
admin_flag,
approver_flag,
owner_flag,
delete_flag )
SELECT
l_access_rec.activity_access_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object_version_number
l_schedule_rec.schedule_id,
'CSCH',
l_access_rec.user_or_role_id,
l_access_rec.arc_user_or_role_type,
l_access_rec.active_from_date,
l_access_rec.active_to_date,
decode(l_access_rec.owner_flag,'Y','Y',nvl(l_access_rec.admin_flag,'N') ),
l_access_rec.approver_flag,
l_access_rec.owner_flag,
'N'
FROM DUAL
WHERE NOT EXISTS ( SELECT 1 FROM AMS_ACT_ACCESS WHERE act_access_to_object_id = l_schedule_rec.schedule_id
and arc_act_access_to_object = 'CSCH'
and user_or_role_id = l_access_rec.user_or_role_id
and arc_user_or_role_type = 'USER');
ams_access_denorm_pvt.insert_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_schedule_rec.schedule_id,
p_object_type => 'CSCH',
p_edit_metrics => nvl(l_access_rec.admin_flag,'N'),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
PROCEDURE delete_access(
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_access_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_access';
SELECT arc_act_access_to_object,
act_access_to_object_id,
arc_user_or_role_type,
user_or_role_id,
admin_flag
FROM ams_act_access
WHERE activity_access_id = l_access_id;
SELECT 'x'
FROM ams_act_access
WHERE arc_act_access_to_object = p_obj_type
AND act_access_to_object_id = p_obj_id;
SELECT *
FROM ams_campaign_schedules_vl
WHERE campaign_id = l_camp_id;
SELECT act_access_to_object_id
FROM ams_act_access
WHERE activity_access_id = l_activity_access_id;
SELECT arc_user_or_role_type
FROM ams_act_access
WHERE activity_access_id = l_activity_access_id;
SELECT user_or_role_id
FROM ams_act_access
WHERE activity_access_id = l_activity_access_id; */
SAVEPOINT delete_access;
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
DELETE FROM ams_act_access
WHERE activity_access_id = p_access_id
AND object_version_number = p_object_version;
DELETE FROM ams_act_access
WHERE act_access_to_object_id = l_schedule_rec.schedule_id
AND arc_act_access_to_object = 'CSCH'
AND user_or_role_id = l_role_id
AND arc_user_or_role_type = l_role_type;
ams_access_denorm_pvt.delete_resource (
p_resource_id => l_role_id,
p_object_id => l_schedule_rec.schedule_id,
p_object_type => 'CSCH',
p_edit_metrics => l_admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
ams_access_denorm_pvt.delete_resource (
p_resource_id => l_role_id,
p_object_id => l_object_id,
p_object_type => l_object_type,
p_edit_metrics => l_admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
UPDATE ams_act_access
SET delete_flag = 'Y',
last_update_date = sysdate,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id
WHERE activity_access_id = p_access_id
AND object_version_number = p_object_version;
UPDATE ams_act_access
SET delete_flag = 'Y',
last_update_date = sysdate,
last_update_login = fnd_global.conc_login_id,
last_updated_by = fnd_global.user_id
WHERE act_access_to_object_id = l_schedule_rec.schedule_id
AND arc_act_access_to_object = 'CSCH'
AND user_or_role_id = l_role_id
AND arc_user_or_role_type = l_role_type;
ROLLBACK TO delete_access;
ROLLBACK TO delete_access;
ROLLBACK TO delete_access;
END delete_access;
SELECT activity_access_id
FROM ams_act_access
WHERE activity_access_id = p_access_id
AND object_version_number = p_object_version
FOR UPDATE OF activity_access_id NOWAIT;
PROCEDURE update_access(
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_access_rec IN access_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_access';
SELECT *
FROM ams_campaign_schedules_vl
WHERE campaign_id = l_camp_id;
SELECT arc_user_or_role_type
FROM ams_act_access
WHERE activity_access_id = l_activity_access_id;
SELECT user_or_role_id
FROM ams_act_access
WHERE activity_access_id = l_activity_access_id;
SELECT activity_access_id
FROM ams_act_access
WHERE act_access_to_object_id = p_sched_id
AND arc_act_access_to_object = 'CSCH'
AND user_or_role_id = p_user_or_role_id
AND arc_user_or_role_type = p_user_or_role_type;
SAVEPOINT update_access;
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_act_access SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = l_access_rec.object_version_number + 1,
act_access_to_object_id = l_access_rec.act_access_to_object_id,
arc_act_access_to_object = l_access_rec.arc_act_access_to_object,
user_or_role_id = l_access_rec.user_or_role_id,
arc_user_or_role_type = l_access_rec.arc_user_or_role_type,
active_from_date = l_access_rec.active_from_date,
active_to_date = l_access_rec.active_to_date,
admin_flag = l_access_rec.admin_flag,
approver_flag = l_access_rec.approver_flag
WHERE activity_access_id = l_access_rec.activity_access_id
AND object_version_number = l_access_rec.object_version_number;
UPDATE ams_act_access 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,
act_access_to_object_id = l_schedule_rec.schedule_id,
arc_act_access_to_object = 'CSCH',
user_or_role_id = l_access_rec.user_or_role_id,
arc_user_or_role_type = l_access_rec.arc_user_or_role_type,
active_from_date = l_access_rec.active_from_date,
active_to_date = l_access_rec.active_to_date,
admin_flag = l_access_rec.admin_flag,
approver_flag = l_access_rec.approver_flag
WHERE activity_access_id = l_key_id;
ams_access_denorm_pvt.update_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_schedule_rec.schedule_id,
p_object_type => 'CSCH',
p_edit_metrics => l_access_rec.admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
ams_access_denorm_pvt.update_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_access_rec.act_access_to_object_id,
p_object_type => l_access_rec.arc_act_access_to_object,
p_edit_metrics => l_access_rec.admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
ams_access_denorm_pvt.delete_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_access_rec.act_access_to_object_id,
p_object_type => l_access_rec.arc_act_access_to_object,
p_edit_metrics => l_access_rec.admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
ams_access_denorm_pvt.insert_resource (
p_resource_id => l_access_rec.user_or_role_id,
p_object_id => l_access_rec.act_access_to_object_id,
p_object_type => l_access_rec.arc_act_access_to_object,
p_edit_metrics => l_access_rec.admin_flag,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
ROLLBACK TO update_access;
ROLLBACK TO update_access;
ROLLBACK TO update_access;
END update_access;
PROCEDURE update_object_owner(
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_object_type IN VARCHAR2,
p_object_id IN NUMBER,
p_resource_id IN NUMBER,
p_old_resource_id IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_owner';
SELECT activity_access_id, object_version_number
FROM ams_act_access
WHERE act_access_to_object_id = p_object_id
AND arc_act_access_to_object = p_object_type
AND user_or_role_id = p_resource_id
AND arc_user_or_role_type = 'USER';
SAVEPOINT update_owner;
delete_access( -- remove it first to avoid unique constraint violation
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_access_id => l_access_id,
p_object_version => l_obj_ver_no
);
UPDATE ams_act_access 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,
act_access_to_object_id = p_object_id,
arc_act_access_to_object = p_object_type,
user_or_role_id = p_resource_id
WHERE act_access_to_object_id = p_object_id
AND arc_act_access_to_object = p_object_type
-- needs user_or_role_id to be specified Bug 3578905
-- child budgets have 2 owners. Parent Budget owner is also a owner
-- will result in unique constraint violation
AND user_or_role_id = p_old_resource_id
AND owner_flag = 'Y';
ams_access_denorm_pvt.delete_resource (
p_resource_id => p_old_resource_id,
p_object_id => p_object_id,
p_object_type => p_object_type,
p_edit_metrics => 'Y',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
ams_access_denorm_pvt.insert_resource (
p_resource_id => p_resource_id,
p_object_id => p_object_id,
p_object_type => p_object_type,
p_edit_metrics => 'Y',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
ROLLBACK TO update_owner;
ROLLBACK TO update_owner;
ROLLBACK TO update_owner;
END update_object_owner;
SELECT 'x'
FROM ams_act_access
WHERE act_access_to_object_id = l_obj_id
AND arc_act_access_to_object = l_obj_type
AND user_or_role_id = l_user_id
AND arc_user_or_role_type = l_user_type
AND delete_flag='N';
SELECT 'Y'
FROM ams_act_access
WHERE user_or_role_id = p_user_or_role_id
AND arc_user_or_role_type = 'USER'
AND arc_act_access_to_object = p_object_type
AND act_access_to_object_id = p_object_id
AND owner_flag = 'Y';
SELECT group_id
FROM jtf_rs_group_members
WHERE resource_id = p_user_id
AND delete_flag = 'N' ;
SELECT parent_group_id
FROM jtf_rs_groups_denorm
WHERE group_id = c_grp_no
AND NVL(start_date_active,SYSDATE) = SYSDATE
AND NVL(end_date_active,SYSDATE) = SYSDATE ;
FUNCTION check_update_access(
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_user_or_role_id IN NUMBER,
p_user_or_role_type IN VARCHAR2
)
RETURN VARCHAR2
IS
x_access VARCHAR2(1) := 'N';
SELECT decode(edit_metrics_yn,'Y','F','R')
FROM ams_act_access_denorm
WHERE resource_id = p_user_or_role_id
AND object_id = p_object_id
AND object_type = p_object_type;
END check_update_access;
SELECT source_code
FROM ams_source_codes
WHERE source_code_for_id = p_object_id
AND arc_source_code_for = p_object_type;
SELECT private_flag
FROM ams_campaigns_all_b
--FROM ams_campaigns_vl
-- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
WHERE campaign_id = c_act_access_to_object_id;
SELECT private_flag
-- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
--FROM ams_event_offers_vl
FROM ams_event_offers_all_b
WHERE event_offer_id = c_act_access_to_object_id;
SELECT private_flag
-- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
-- FROM ams_event_headers_vl
FROM ams_event_headers_all_b
WHERE event_header_id = c_act_access_to_object_id;
SELECT private_flag
FROM ams_deliverables_vl
WHERE deliverable_id = c_act_access_to_object_id;
SELECT admin_flag
FROM ams_act_access
WHERE act_access_to_object_id = c_act_access_to_object_id
AND arc_act_access_to_object = c_arc_act_access_to_object
AND user_or_role_id = c_user_or_role_id
AND arc_user_or_role_type = c_arc_user_or_role_type;
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM ams_act_access
WHERE arc_act_access_to_object = p_object_type
AND act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND (active_from_date IS NULL OR active_from_date <= SYSDATE)
AND (active_to_date IS NULL OR active_to_date >= SYSDATE)
AND user_or_role_id = c_grp_id) ;
) is SELECT 'x'
FROM ams_act_access
WHERE user_or_role_id = p_inp_user_id
AND arc_user_or_role_type = p_inp_user_type
AND act_access_to_object_id = p_inp_obj_id
AND arc_act_access_to_object= p_inp_obj_type
AND delete_flag = 'N' ;
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM ams_act_access
WHERE act_access_to_object_id = p_access_rec.act_access_to_object_id
AND arc_act_access_to_object = p_access_rec.arc_act_access_to_object
AND user_or_role_id = p_access_rec.user_or_role_id
AND arc_user_or_role_type = p_access_rec.arc_user_or_role_type
AND activity_access_id <> p_access_rec.activity_access_id);
IF p_validation_mode = JTF_PLSQL_API.G_UPDATE THEN
OPEN c_user_exist;
x_Access_rec.last_update_date := FND_API.g_miss_date;
x_Access_rec.last_updated_by := FND_API.g_miss_num;
x_Access_rec.last_update_login := FND_API.g_miss_num;
x_Access_rec.delete_flag := FND_API.g_miss_char;
SELECT *
FROM ams_act_access
WHERE activity_access_id = p_Access_rec.activity_access_id;
IF p_Access_rec.delete_flag = FND_API.g_miss_char THEN
x_complete_rec.delete_flag := l_Access_rec.delete_flag ;
SELECT resource_id
FROM jtf_rs_group_members
-- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
--FROM jtf_rs_group_members_vl
WHERE group_id = p_group_id
AND resource_id = p_resource_id
AND delete_flag = 'N' ;