[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* sunkumar: 02-10-03 overloaded insert resource to error_message also */
PROCEDURE insert_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, 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) := 'insert_resource';
insert_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
PROCEDURE insert_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
insert_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
/* sunkumar: 02-10-03 overloaded update resource to add error_message also */
PROCEDURE update_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, 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_resource';
update_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
PROCEDURE update_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
update_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
/* sunkumar: 02-10-03 overloaded delete resource to add error_message also */
PROCEDURE delete_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, 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_resource';
delete_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
PROCEDURE delete_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
delete_resource( p_resource_id => p_resource_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
PROCEDURE insert_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
INSERT INTO ams_act_access_denorm
( access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
, p_resource_id
, p_edit_metrics
, p_object_type
, p_object_id
, ams_access_pvt.get_source_code(p_object_type,p_object_id)
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM ams_act_access_denorm
WHERE resource_id = p_resource_id
AND object_type = p_object_type
AND object_id = p_object_id
);
UPDATE ams_act_access_denorm
SET edit_metrics_yn = p_edit_metrics,
last_updated_by = l_user_id,
last_update_date = l_sysdate,
last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id = p_resource_id
AND edit_metrics_yn = 'N' ;
END insert_resource;
PROCEDURE update_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
SELECT edit_metrics_yn
FROM ams_act_access_denorm
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id = p_resource_id;
UPDATE ams_act_access_denorm
SET edit_metrics_yn = p_edit_metrics
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id = p_resource_id
AND edit_metrics_yn = 'N';
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = p_edit_metrics
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id = p_resource_id
AND edit_metrics_yn = 'Y'
AND not exists ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
AND aac.admin_flag='Y' );
PROCEDURE delete_resource( p_resource_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
DELETE FROM AMS_ACT_ACCESS_DENORM aacd
WHERE resource_id = p_resource_id
AND object_type = p_object_type
AND object_id = p_object_id
AND not exists ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm -- INtroduce soft DELETE flag FOR resources.
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.resource_id = p_resource_id
AND jgm.delete_flag = 'N'
AND aac.delete_flag = 'N'
UNION ALL
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 = 'USER'
AND user_or_role_id = aacd.resource_id
);
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id = p_resource_id
AND resource_id not IN (SELECT jgm.resource_id
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N')
AND edit_metrics_yn = 'Y' ;
PROCEDURE insert_group( p_group_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = p_edit_metrics
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = p_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
)
AND edit_metrics_yn = 'N' ;
INSERT INTO ams_act_access_denorm
( access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
, resource_id
, p_edit_metrics
, p_object_type
, p_object_id
, ams_access_pvt.get_source_code(p_object_type,p_object_id)
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
FROM (
SELECT DISTINCT resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = p_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND NOT EXISTS ( SELECT 1
FROM ams_act_access_denorm
WHERE resource_id = jgm.resource_id
AND object_type = p_object_type
AND object_id = p_object_id)
);
end insert_group;
PROCEDURE update_group( p_group_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = p_edit_metrics
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND edit_metrics_yn = 'N'
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = p_group_id
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N' );
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = p_edit_metrics
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND edit_metrics_yn = 'Y'
/* Roliing back perf suggested change
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
AND NVL(aac.admin_flag,'N')='N' --anchaudh 21-MAR-03
)
*/
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
AND aac.admin_flag='Y'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac
WHERE aac.act_access_to_object_id = p_object_id
AND aac.arc_act_access_to_object = p_object_type
AND aac.user_or_role_id = aacd.resource_id
AND aac.arc_user_or_role_type = 'USER'
AND aac.delete_flag = 'N'
AND aac.admin_flag = 'Y'
);
END update_group;
PROCEDURE delete_group( p_group_id IN NUMBER
, p_object_type IN VARCHAR2
, p_object_id IN NUMBER
, p_edit_metrics IN VARCHAR2
)
IS
l_user_id NUMBER := fnd_global.user_id;
DELETE FROM ams_act_access_denorm aacd
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = p_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
-- delete every group even if it was end dated earlier than SYSDATE - SVEERAVE 05/15/02
-- AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N' )
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND aac.delete_flag = 'N'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag = 'N'
AND jgm.resource_id = aacd.resource_id
UNION ALL
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 = 'USER'
AND user_or_role_id = aacd.resource_id ) ;
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = p_object_type
AND object_id = p_object_id
AND resource_id NOT IN ( SELECT jgm.resource_id
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.admin_flag = 'Y'
AND aac.delete_flag = 'N'
UNION ALL
SELECT user_or_role_id
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 = 'USER'
AND user_or_role_id = aacd.resource_id
AND admin_flag = 'Y'
)
AND edit_metrics_yn = 'Y';
end delete_group;
PROCEDURE bulk_all_delete_group
IS
TYPE user_role_tbl IS TABLE OF ams_act_access.user_or_role_id%TYPE ;
CURSOR csr_delete_groups IS
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
and delete_flag = 'Y' ;
Ams_Utility_Pvt.Write_Conc_Log('The program bulk_all_delete_group started... ');
OPEN csr_delete_groups ;
FETCH csr_delete_groups BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
CLOSE csr_delete_groups ;
-- Bulk delete
FORALL i IN 1..l_tbl_object_id.COUNT
DELETE FROM ams_act_access_denorm aacd
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND jgm.delete_flag='N' )
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND aac.delete_flag = 'N'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag = 'N'
AND jgm.resource_id = aacd.resource_id
UNION ALL
SELECT 1
FROM ams_act_access
WHERE arc_act_access_to_object = l_tbl_object(i)
AND act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = aacd.resource_id ) ;
Ams_Utility_Pvt.Write_Conc_Log('Bulk delete operation done ');
-- Bulk Update for admin flag
FORALL i IN 1..l_tbl_object_id.COUNT
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = fnd_global.user_id
, last_update_date = sysdate
, last_update_login = fnd_global.user_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id NOT IN ( SELECT jgm.resource_id
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.admin_flag = 'Y'
AND aac.delete_flag = 'N'
UNION ALL
SELECT user_or_role_id
FROM ams_act_access
WHERE arc_act_access_to_object = l_tbl_object(i)
AND act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = aacd.resource_id
AND admin_flag = 'Y'
)
AND edit_metrics_yn = 'Y'
AND edit_metrics_yn = NVL(l_tbl_admin_flag(i),'N');
END bulk_all_delete_group ;
CURSOR csr_delete_groups IS
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
and delete_flag = 'N' ;
OPEN csr_delete_groups ;
FETCH csr_delete_groups BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
CLOSE csr_delete_groups ;
-- Bulk update for admin flag
FORALL i IN 1..l_tbl_object_id.COUNT
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'Y'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND edit_metrics_yn = 'N'
AND l_tbl_admin_flag(i)='Y'
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag = 'N'
);
-- Bulk insert
FORALL i IN 1..l_tbl_object_id.COUNT
INSERT INTO ams_act_access_denorm
( access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
, resource_id
, l_tbl_admin_flag(i)
, l_tbl_object(i)
, l_tbl_object_id(i)
, ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
FROM (
SELECT DISTINCT resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND NOT EXISTS ( SELECT 1
FROM ams_act_access_denorm
WHERE resource_id = jgm.resource_id
AND object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i))
);
-- Bulk access update for admin flag as true
FORALL i IN 1..l_tbl_object_id.COUNT
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'Y'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND edit_metrics_yn = 'N'
AND l_tbl_admin_flag(i)='Y'
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = l_tbl_user_role(i)
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N' );
-- Bulk access update for admin flag as false
FORALL i IN 1..l_tbl_object_id.COUNT
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = l_tbl_admin_flag(i)
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND edit_metrics_yn = 'Y'
AND l_tbl_admin_flag(i)='N'
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
AND aac.admin_flag='Y'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac
WHERE aac.act_access_to_object_id = l_tbl_object_id(i)
AND aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.user_or_role_id = aacd.resource_id
AND aac.arc_user_or_role_type = 'USER'
AND aac.delete_flag = 'N'
AND aac.admin_flag = 'Y'
);
PROCEDURE bulk_insert_group (p_last_run_date IN date)
IS
l_user_id NUMBER := fnd_global.user_id;
CURSOR crs_insert_grp IS
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND last_update_date >= p_last_run_date
and creation_date > p_last_run_date
and delete_flag = 'N' ;
Ams_Utility_Pvt.Write_Conc_Log('bulk_insert_group - start ');
OPEN crs_insert_grp ;
FETCH crs_insert_grp BULK COLLECT INTO l_tbl_user_role,l_tbl_object_id,l_tbl_object,l_tbl_admin_flag ;
CLOSE crs_insert_grp ;
-- Bulk update for admin flag as 'Y'
FORALL i IN 1..l_tbl_object_id.COUNT
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'Y'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
)
AND edit_metrics_yn = 'N'
AND l_tbl_admin_flag(i)='N';
-- Bulk insert
FORALL i IN 1..l_tbl_object_id.COUNT
INSERT INTO ams_act_access_denorm
( access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
, resource_id
, l_tbl_admin_flag(i)
, l_tbl_object(i)
, l_tbl_object_id(i)
, ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
FROM (
SELECT DISTINCT resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND NOT EXISTS ( SELECT 1
FROM ams_act_access_denorm
WHERE resource_id = jgm.resource_id
AND object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i))
);
END bulk_insert_group ;
PROCEDURE bulk_delete_group (p_last_run_date IN date,
p_reason_code varchar2)
IS
l_user_id NUMBER := fnd_global.user_id;
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND last_update_date > p_last_run_date
and delete_flag = 'Y' ;
SELECT jrg.group_id user_or_role_id
, act.act_access_to_object_id act_access_to_object_id
, act.arc_act_access_to_object arc_act_access_to_object
, admin_flag
FROM ams_act_access act,
jtf_rs_groups_b jrg
WHERE act.arc_user_or_role_type = 'GROUP'
AND act.user_or_role_id= jrg.group_id
AND act.delete_flag = 'N'
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active >= p_last_run_date
AND jrg.end_date_active <= TRUNC(SYSDATE) ;
Ams_Utility_Pvt.Write_Conc_Log('bulk_delete_group - start ');
DELETE FROM ams_act_access_denorm aacd
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id IN ( SELECT jgm.resource_id
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = l_tbl_user_role(i)
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
-- delete every group even if it was end dated earlier than SYSDATE - SVEERAVE 05/15/02
-- AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N' )
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND aac.delete_flag = 'N'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag = 'N'
AND jgm.resource_id = aacd.resource_id
UNION ALL
SELECT 1
FROM ams_act_access
WHERE arc_act_access_to_object = l_tbl_object(i)
AND act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = aacd.resource_id ) ;
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id NOT IN ( SELECT jgm.resource_id
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.admin_flag = 'Y'
AND aac.delete_flag = 'N'
UNION ALL
SELECT user_or_role_id
FROM ams_act_access
WHERE arc_act_access_to_object = l_tbl_object(i)
AND act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = aacd.resource_id
AND admin_flag = 'Y'
)
AND edit_metrics_yn = 'Y'
AND edit_metrics_yn = NVL(l_tbl_admin_flag(i),'N');
END bulk_delete_group ;
PROCEDURE bulk_update_group (p_last_run_date IN date)
IS
l_user_id NUMBER := fnd_global.user_id;
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND last_update_date > p_last_run_date
and delete_flag = 'N' ;
Ams_Utility_Pvt.Write_Conc_Log('bulk_update_group - start ');
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'Y'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND edit_metrics_yn = 'N'
AND l_tbl_admin_flag(i)='Y'
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = l_tbl_user_role(i)
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N' );
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND edit_metrics_yn = 'Y'
AND l_tbl_admin_flag(i)='N'
AND EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N'
AND aac.admin_flag='Y'
)
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac
WHERE aac.act_access_to_object_id = l_tbl_object_id(i)
AND aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.user_or_role_id = aacd.resource_id
AND aac.arc_user_or_role_type = 'USER'
AND aac.delete_flag = 'N'
AND aac.admin_flag = 'Y'
);
END bulk_update_group ;
PROCEDURE bulk_insert_resource (p_last_run_date IN date)
IS
l_user_id NUMBER := fnd_global.user_id;
SELECT jgm.resource_id,
aac.act_access_to_object_id,
aac.arc_act_access_to_object,
aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.start_date_active >= p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT jgm.resource_id
, aac.act_access_to_object_id
, aac.arc_act_access_to_object
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT jgm.resource_id
, aac.act_access_to_object_id
, aac.arc_act_access_to_object
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jgm.creation_date > p_last_run_date
);
Ams_Utility_Pvt.Write_Conc_Log('bulk_insert_resource - start ');
INSERT INTO ams_act_access_denorm
( access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
, l_tbl_res(i)
, l_tbl_admin_flag(i)
, l_tbl_object(i)
, l_tbl_object_id(i)
, ams_access_pvt.get_source_code(l_tbl_object(i),l_tbl_object_id(i))
, l_sysdate
, l_user_id
, l_sysdate
, l_user_id
, l_login_id
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM ams_act_access_denorm
WHERE resource_id = l_tbl_res(i)
AND object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
);
UPDATE ams_act_access_denorm
SET edit_metrics_yn = l_tbl_admin_flag(i),
last_updated_by = l_user_id,
last_update_date = l_sysdate,
last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id = l_tbl_res(i)
AND edit_metrics_yn = 'N'
AND l_tbl_admin_flag(i) = 'Y' ;
END bulk_insert_resource ;
PROCEDURE bulk_delete_resource (p_last_run_date IN date)
IS
l_user_id NUMBER := fnd_global.user_id;
SELECT jgm.resource_id
,aac.act_access_to_object_id
, aac.arc_act_access_to_object
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active >= p_last_run_date
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT jgm.resource_id,
aac.act_access_to_object_id
, aac.arc_act_access_to_object
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT jgm.resource_id,
aac.act_access_to_object_id
, aac.arc_act_access_to_object
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'Y'
AND jrg.group_id = jgm.group_id
AND jgm.last_update_date >= p_last_run_date
);
Ams_Utility_Pvt.Write_Conc_Log('bulk_delete_group - start ');
DELETE FROM AMS_ACT_ACCESS_DENORM aacd
WHERE resource_id = l_tbl_resource(i)
AND object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND not exists ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.resource_id = l_tbl_resource(i)
AND jgm.delete_flag = 'N'
AND aac.delete_flag = 'N'
UNION ALL
SELECT 1
FROM ams_act_access
WHERE arc_act_access_to_object = l_tbl_object(i)
AND act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = aacd.resource_id
);
UPDATE ams_act_access_denorm aacd
SET edit_metrics_yn = 'N'
, last_updated_by = l_user_id
, last_update_date = l_sysdate
, last_update_login = l_login_id
WHERE object_type = l_tbl_object(i)
AND object_id = l_tbl_object_id(i)
AND resource_id = l_tbl_resource(i)
AND resource_id not IN (SELECT jgm.resource_id
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = l_tbl_object(i)
AND aac.act_access_to_object_id = l_tbl_object_id(i)
AND arc_user_or_role_type = 'GROUP'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id
AND aac.delete_flag = 'N')
AND edit_metrics_yn = 'Y'
AND l_tbl_admin_flag(i) = 'Y' ;
END bulk_delete_resource ;
insert_group( p_group_id => p_group_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
update_group( p_group_id => p_group_id
, p_object_type => p_object_type
, p_object_id => p_object_id
, p_edit_metrics => p_edit_metrics
);
DELETE FROM ams_act_access_denorm aacd
WHERE aacd.object_type = p_object_type
AND aacd.object_id = p_object_id
AND NOT EXISTS ( SELECT 1 --anchaudh 21-MAR-03
FROM jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE jgd.parent_group_id = p_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,SYSDATE) >= TRUNC(SYSDATE)
AND jgm.delete_flag='N'
AND jgm.resource_id = aacd.resource_id) --anchaudh 21-MAR-03
AND NOT EXISTS ( SELECT 1
FROM ams_act_access aac,
jtf_rs_groups_denorm jgd,
jtf_rs_group_members jgm
WHERE aac.arc_act_access_to_object = p_object_type
AND aac.act_access_to_object_id = p_object_id
AND arc_user_or_role_type = 'GROUP'
AND aac.delete_flag = 'N'
AND user_or_role_id = jgd.parent_group_id
AND jgd.group_id = jgm.group_id
AND jgd.start_date_active <= TRUNC(SYSDATE)
AND NVL(jgd.end_date_active,TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND jgm.delete_flag = 'N'
AND jgm.resource_id = aacd.resource_id)
AND NOT EXISTS ( --anchaudh 21-MAR-03
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 = 'USER'
AND user_or_role_id = aacd.resource_id ) ;
SELECT arc_user_or_role_type
,user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
FROM ams_act_access
WHERE act_access_to_object_id = p_object_id
AND arc_act_access_to_object = p_object_type
AND arc_user_or_role_type = 'GROUP'
AND delete_flag = 'N';
DELETE FROM ams_act_access_denorm aacd
WHERE aacd.object_type = p_object_type
AND aacd.object_id = p_object_id;
INSERT INTO ams_act_access_denorm
(
access_denorm_id
, resource_id
, edit_metrics_yn
, object_type
, object_id
, source_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
SELECT ams_act_access_denorm_s.nextval
,user_or_role_id
,admin_flag
,arc_act_access_to_object
,act_access_to_object_id
,ams_access_pvt.get_source_code(arc_act_access_to_object,act_access_to_object_id)
,l_sysdate
,l_user_id
,l_sysdate
,l_user_id
,l_login_id
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 = 'USER';
insert_group( object_rec.user_or_role_id
,object_rec.arc_act_access_to_object
,object_rec.act_access_to_object_id
,object_rec.admin_flag
);
DELETE FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND arc_act_access_to_object = p_object_type
AND act_access_to_object_id = p_object_id
AND delete_flag = 'Y' ;
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
,delete_flag
,creation_date
,last_update_date
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND last_update_date >= p_last_run_date;
SELECT user_or_role_id
,act_access_to_object_id
,arc_act_access_to_object
,admin_flag
,delete_flag
,creation_date
,last_update_date
FROM ams_act_access
WHERE arc_user_or_role_type = 'GROUP';
SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE application_id = 530
AND concurrent_program_name = 'AMSADENO';
SELECT max(actual_start_date)
FROM fnd_concurrent_requests
WHERE program_application_id = l_program_application_id
AND concurrent_program_id = l_concurrent_program_id
AND status_code = 'C'
AND phase_code = 'C';
SELECT max(last_update_date)
FROM ams_act_access_denorm;
bulk_all_delete_group ;
bulk_insert_group (l_last_run_date) ;
bulk_delete_group (l_last_run_date, 'AMS_ACCESS') ;
bulk_update_group (l_last_run_date) ;
DELETE ams_act_access
WHERE arc_user_or_role_type = 'GROUP'
AND delete_flag = 'Y' ;
SELECT act.act_access_to_object_id
, act.arc_act_access_to_object
, jrg.group_id
, admin_flag
FROM ams_act_access act,
JTF_RS_GROUPS_B jrg
WHERE act.arc_user_or_role_type = 'GROUP'
AND act.user_or_role_id= jrg.group_id
AND jrg.last_update_date >= p_last_run_date
AND jrg.end_date_active <= trunc(sysdate)
AND act.delete_flag = 'N';
SELECT jrg.group_id
, jrg.start_date_active
, act.act_access_to_object_id
, act.arc_act_access_to_object
, jrg.creation_date
, jrg.last_update_date
, jrg.end_date_active
, act.admin_flag
FROM ams_act_access act,
jtf_rs_grp_relations jrg
WHERE act.arc_user_or_role_type = 'GROUP'
AND act.user_or_role_id= jrg.group_id
AND jrg.last_update_date >= p_last_run_date
AND act.delete_flag = 'N';
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.delete_flag
, jgm.creation_date
, jgm.last_update_date
,aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND jrg.group_id = jgm.group_id
AND jrg.start_date_active <= trunc(sysdate)
AND nvl(jrg.end_date_active,trunc(sysdate)) >= trunc(sysdate)
AND jgm.last_update_date >= p_last_run_date
AND aac.delete_flag='N';
SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE application_id = 530
AND concurrent_program_name = 'AMSJDENO';
SELECT MAX(actual_start_date)
FROM fnd_concurrent_requests
WHERE program_application_id = l_program_application_id
AND concurrent_program_id = l_concurrent_program_id
AND status_code = 'C'
AND phase_code = 'C';
SELECT MAX(last_update_date)
FROM ams_act_access_denorm;
delete_group( p_group_id => grp_res_rec.group_id
, p_object_type => grp_res_rec.arc_act_access_to_object
, p_object_id => grp_res_rec.act_access_to_object_id
, p_edit_metrics => grp_res_rec.admin_flag
);
insert_group( p_group_id => grprel_res_rec.group_id
, p_object_type => grprel_res_rec.arc_act_access_to_object
, p_object_id => grprel_res_rec.act_access_to_object_id
, p_edit_metrics => grprel_res_rec.admin_flag
);
delete_group( p_group_id => grprel_res_rec.group_id
, p_object_type => grprel_res_rec.arc_act_access_to_object
, p_object_id => grprel_res_rec.act_access_to_object_id
, p_edit_metrics => grprel_res_rec.admin_flag
);
AND ( grpmembers_rec.delete_flag = 'N') )
THEN
-- dbms_output.put_line(' insert group members');
insert_resource( p_resource_id => grpmembers_rec.resource_id
, p_object_type => grpmembers_rec.arc_act_access_to_object
, p_object_id => grpmembers_rec.act_access_to_object_id
, p_edit_metrics => grpmembers_rec.admin_flag
);
ELSIF ( (grpmembers_rec.delete_flag = 'Y') ) THEN
--dbms_output.put_line(' DELETE group members');
delete_resource( p_resource_id => grpmembers_rec.resource_id
, p_object_type => grpmembers_rec.arc_act_access_to_object
, p_object_id => grpmembers_rec.act_access_to_object_id
, p_edit_metrics => grpmembers_rec.admin_flag
);
SELECT act.act_access_to_object_id
, act.arc_act_access_to_object
, jrg.group_id
, admin_flag
FROM ams_act_access act,
jtf_rs_groups_b jrg
WHERE act.arc_user_or_role_type = 'GROUP'
AND act.user_or_role_id= jrg.group_id
AND act.delete_flag = 'N'
-- AND jrg.last_update_date >= p_last_run_date -- this will not pick any rows which are end dated in future.
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active >= p_last_run_date -- added to pick only the rows which are ending after previous run.
AND jrg.end_date_active <= TRUNC(SYSDATE) ;
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.start_date_active >= p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
OR
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
OR
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jgm.creation_date > p_last_run_date
);
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.start_date_active >= p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.start_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'N'
AND jrg.group_id = jgm.group_id
AND jgm.creation_date > p_last_run_date
);
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active >= p_last_run_date
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
OR
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
OR
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'Y'
AND jrg.group_id = jgm.group_id
AND jgm.last_update_date >= p_last_run_date
);
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active >= p_last_run_date
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jrg.group_id = jgm.group_id
AND jrg.last_update_date > p_last_run_date
AND jrg.end_date_active IS NOT NULL
AND jrg.end_date_active <= TRUNC(SYSDATE)
)
UNION
SELECT aac.act_access_to_object_id
, aac.arc_act_access_to_object
, jgm.resource_id
, jgm.last_update_date
, aac.admin_flag
FROM ams_act_access aac,
jtf_rs_groups_denorm jrg,
jtf_rs_group_members jgm
WHERE
( aac.arc_user_or_role_type = 'GROUP'
AND aac.user_or_role_id= jrg.parent_group_id
AND aac.delete_flag='N'
AND jgm.delete_flag = 'Y'
AND jrg.group_id = jgm.group_id
AND jgm.last_update_date >= p_last_run_date
);
SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE concurrent_program_name = 'AMSJDENO';
SELECT MAX(actual_start_date)
FROM fnd_concurrent_requests
WHERE program_application_id = l_program_application_id
AND concurrent_program_id = l_concurrent_program_id
AND status_code = 'C'
AND phase_code = 'C';
SELECT MIN(last_update_date)
FROM ams_act_access_denorm;
bulk_delete_group (l_last_run_date, 'JTF_ACCESS') ;
bulk_insert_resource(l_last_run_date) ;
bulk_delete_resource(l_last_run_date) ;