[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;
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;
IF (l_all_access_rec.delete_flag = 'Y') THEN
delete_group( p_group_id => l_all_access_rec.user_or_role_id
, p_object_type => l_all_access_rec.arc_act_access_to_object
, p_object_id => l_all_access_rec.act_access_to_object_id
, p_edit_metrics => l_all_access_rec.admin_flag
);
ELSIF (l_all_access_rec.delete_flag = 'N') THEN
refresh_group( p_group_id => l_all_access_rec.user_or_role_id
, p_object_type => l_all_access_rec.arc_act_access_to_object
, p_object_id => l_all_access_rec.act_access_to_object_id
, p_edit_metrics => l_all_access_rec.admin_flag
);
IF ((access_rec.creation_date > l_last_run_date) AND (access_rec.delete_flag = 'N') ) THEN
insert_group( p_group_id => access_rec.user_or_role_id
, p_object_type => access_rec.arc_act_access_to_object
, p_object_id => access_rec.act_access_to_object_id
, p_edit_metrics => access_rec.admin_flag
);
ELSIF ( (access_rec.last_update_date > l_last_run_date) AND (access_rec.delete_flag = 'Y') ) THEN
delete_group( p_group_id => access_rec.user_or_role_id
, p_object_type => access_rec.arc_act_access_to_object
, p_object_id => access_rec.act_access_to_object_id
, p_edit_metrics => access_rec.admin_flag
);
ELSIF ( (access_rec.last_update_date > l_last_run_date) AND (access_rec.delete_flag = 'N') ) THEN
--dbms_output.put_line('-- Only change that could have happened is that edit metrics could have changed.');
update_group( p_group_id => access_rec.user_or_role_id
, p_object_type => access_rec.arc_act_access_to_object
, p_object_id => access_rec.act_access_to_object_id
, p_edit_metrics => access_rec.admin_flag
);
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;
delete_group( p_group_id => l_del_grp_rec.group_id
, p_object_type => l_del_grp_rec.arc_act_access_to_object
, p_object_id => l_del_grp_rec.act_access_to_object_id
, p_edit_metrics => l_del_grp_rec.admin_flag
);
insert_resource( p_resource_id => l_crt_res_rec.resource_id
, p_object_type => l_crt_res_rec.arc_act_access_to_object
, p_object_id => l_crt_res_rec.act_access_to_object_id
, p_edit_metrics => l_crt_res_rec.admin_flag
);
delete_resource( p_resource_id => l_del_res_rec.resource_id
, p_object_type => l_del_res_rec.arc_act_access_to_object
, p_object_id => l_del_res_rec.act_access_to_object_id
, p_edit_metrics => l_del_res_rec.admin_flag
);