[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select version
into ver
from v$instance
where rownum = 1;
'SELECT RULE_TYPE, ACTION_ID '
||' FROM '||G_AMW_RESP_FUNCTIONS
||' WHERE application_id = :1 '
||' AND responsibility_id = :2 ';
'SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG,CREATION_DATE,CREATED_BY '
||' FROM '||G_AMW_MENU_ENTRIES
||' WHERE menu_id = :1 ';
l_excl_rule_list.delete();
l_excl_act_id_list.delete();
L_FUNC_ID_LIST.delete();
L_RESPVIO_ENTRIES.delete();
SELECT RULE_TYPE, ACTION_ID from fnd_resp_functions
where application_id = p_appl_id
and responsibility_id = p_resp_id;
'SELECT RULE_TYPE, ACTION_ID '
||' FROM '||G_AMW_RESP_FUNCTIONS
||' WHERE application_id = :1 '
||' AND responsibility_id = :2 ';
SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG
from fnd_menu_entries
where MENU_ID = l_sub_menu_id;
'SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG,CREATION_DATE,CREATED_BY '
||' FROM '||G_AMW_MENU_ENTRIES
||' WHERE menu_id = :1 ';
/* Select the list of exclusion rules into our cache */
-- 11.12.2003 tsho: use dynamic sql for AMW
/*
for excl_rec in excl_c loop
EXCLUSIONS(excl_rec.action_id) := excl_rec.rule_type;
'SELECT menu_id '
||' FROM '||G_AMW_RESPONSIBILITY
||' WHERE responsibility_id = :1 '
||' AND application_id = :2 ';
select menu_id
into l_menu_id
from fnd_responsibility
where responsibility_id = p_resp_id
and application_id = p_appl_id;
'SELECT MAINTENANCE_MODE_SUPPORT, CONTEXT_DEPENDENCE '
||' FROM '||G_AMW_FORM_FUNCTIONS_VL
||' WHERE FUNCTION_ID = :1 ';
SELECT MAINTENANCE_MODE_SUPPORT, CONTEXT_DEPENDENCE
INTO L_MAINTENANCE_MODE_SUPPORT, L_CONTEXT_DEPENDENCE
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_ID = l_function_id;
'SELECT person_party_id '
||' FROM '||G_AMW_USER ||' u '
||' WHERE u.user_id = :1 ';
SELECT person_party_id
INTO l_party_id
FROM FND_USER u
WHERE u.user_id = p_user_id;
'SELECT user_id '
||' FROM amw_employees_current_v emp, '
|| G_AMW_USER ||' u '
||' WHERE emp.party_id = u.person_party_id ';
SELECT user_id
FROM amw_employees_current_v emp,
fnd_user u
WHERE emp.party_id = u.person_party_id;
SELECT type_code
FROM amw_constraints_b
WHERE constraint_rev_id=l_constraint_rev_id;
' SELECT gra.grantee_key role_name, '
||' gra.menu_id, '
||' ce.function_id, '
||' min(urasgn.start_date), '
||' ( SELECT asgn.created_by '
||' FROM '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' WHERE asgn.user_name = usr.user_name '
||' AND asgn.role_name = role_name '
||' AND asgn.start_date = start_date '
||' AND rownum=1 '
||' ) created_by, '
||' ''FUNC'' entry_object_type, '
||' usr.user_id, '
||' ce.group_code '
||' FROM '||G_AMW_GRANTS||' gra, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' AMW_CONSTRAINT_ENTRIES ce, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr '
||' WHERE urasgn.user_name = usr.user_name '
||' AND (gra.grantee_orig_system = ''UMX'' OR gra.grantee_orig_system = ''FND_RESP'') '
||' AND urasgn.role_name = gra.GRANTEE_KEY '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND ce.CONSTRAINT_REV_ID = :1 '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.GRANTEE_TYPE = ''GROUP'' '
||' AND gra.start_date <= sysdate '
||' AND (gra.end_date >= sysdate or gra.end_date is null) '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' GROUP BY gra.grantee_key, gra.menu_id,ce.function_id,usr.user_name,usr.user_id,ce.group_code'
||' UNION ALL '
||' SELECT to_char(null) role_name, '
||' gra.menu_id, '
||' ce.function_id, '
||' gra.start_date, '
||' gra.created_by, '
||' ''FUNC'' entry_object_type, '
||' usr.user_id, '
||' ce.group_code '
||' FROM '||G_AMW_GRANTS||' gra, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' AMW_CONSTRAINT_ENTRIES ce, '
||' '||G_AMW_USER||' usr '
||' WHERE (( gra.GRANTEE_KEY = usr.user_name AND gra.GRANTEE_TYPE = ''USER'') '
||' OR (gra.GRANTEE_KEY = ''GLOBAL'' AND gra.GRANTEE_TYPE = ''GLOBAL'')) '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND ce.CONSTRAINT_REV_ID = :2 '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.start_date <= sysdate '
||' AND (gra.end_date >= sysdate or gra.end_date is null) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) ';
' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by, '
||' DECODE((select count(*) '
||' from amw_constraint_waivers_b '
||' where constraint_rev_id= :1 '
||' and object_type=''RESP'' '
||' and pk1 = ur.role_orig_system_id '
||' and pk2 = appl.application_id '
||' and start_date <= sysdate '
||' AND (end_date >= sysdate or end_date is null)),0,''N'',''Y'') waived_flag '
||' FROM WF_USER_ROLES ur, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' '||G_AMW_RESPONSIBILITY||' resp, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' AMW_CONSTRAINT_ENTRIES ce '
||' WHERE ce.CONSTRAINT_REV_ID = :2 '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND cmf.menu_id = resp.menu_id '
||' AND cmf.grant_flag = ''Y'' '
||' AND cmf.function_id = ce.function_id '
||' AND resp.start_date <= sysdate '
||' AND (resp.end_date >= sysdate or resp.end_date is null) '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.start_date<= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND rol.owner_tag=appl.application_short_name '
||' AND resp.application_id=appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND ur.user_name=usr.user_name '
||' AND (ur.user_orig_system = ''FND_USR'' OR ur.user_orig_system = ''PER'') '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name '
||' UNION '
||' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by, '
||' DECODE((select count(*) '
||' from amw_constraint_waivers_b '
||' where constraint_rev_id= :3 '
||' and object_type=''RESP'' '
||' and pk1 = ur.role_orig_system_id '
||' and pk2 = appl.application_id '
||' and start_date <= sysdate '
||' AND (end_date >= sysdate or end_date is null)),0,''N'',''Y'') waived_flag '
||' FROM WF_USER_ROLES ur, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' '||G_AMW_RESPONSIBILITY||' RESP, '
||' '||G_AMW_REQUEST_GROUP_UNITS||' RGU, '
||' AMW_CONSTRAINT_ENTRIES ACE '
||' WHERE ACE.CONSTRAINT_REV_ID=:4 '
||' AND ACE.OBJECT_TYPE=''CP'' '
||' AND RESP.GROUP_APPLICATION_ID IS NOT NULL '
||' AND RESP.REQUEST_GROUP_ID IS NOT NULL '
||' AND RGU.APPLICATION_ID=RESP.GROUP_APPLICATION_ID '
||' AND RGU.REQUEST_GROUP_ID=RESP.REQUEST_GROUP_ID '
||' AND RGU.REQUEST_UNIT_TYPE = ''P'' '
||' AND RGU.UNIT_APPLICATION_ID=ACE.APPLICATION_ID '
||' AND RGU.REQUEST_UNIT_ID=ACE.FUNCTION_ID '
||' AND RESP.START_DATE<=SYSDATE '
||' AND (RESP.END_DATE>= SYSDATE or RESP.END_DATE IS NULL) '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.start_date<= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND rol.owner_tag = appl.application_short_name '
||' AND resp.application_id = appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND ur.user_name=usr.user_name '
||' AND (ur.user_orig_system = ''FND_USR'' OR ur.user_orig_system = ''PER'') '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name ';
' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by ,'
||' ce.group_code '
||' FROM WF_USER_ROLES ur, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' '||G_AMW_RESPONSIBILITY||' resp, '
||' AMW_CONSTRAINT_ENTRIES ce '
||' WHERE ce.CONSTRAINT_REV_ID = :1 '
||' AND ce.object_type = ''RESP'' '
||' AND ce.function_id = resp.responsibility_id '
||' AND ce.application_id = resp.application_id '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.owner_tag = appl.application_short_name '
||' AND resp.application_id = appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND ur.user_name = usr.user_name '
||' AND rol.start_date <= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND resp.start_date <= sysdate '
||' AND (resp.end_date >= sysdate OR resp.end_date IS NULL) '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate OR urasgn.end_date IS NULL) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate OR usr.end_date IS NULL) '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name,ce.group_code ';
G_USER_VIOLATIONS_LIST.delete();
G_USER_RESP_VIO_LIST.delete();
G_CST_USER_ID_LIST.DELETE();
G_UPV_APPLICATION_ID_LIST.DELETE();
G_UPV_RESPONSIBILITY_ID_LIST.DELETE();
G_UPV_ACCESS_GIVEN_DATE_LIST.DELETE();
G_UPV_ACCESS_GIVEN_BY_LIST.DELETE();
G_UPV_GROUP_CODE_LIST.DELETE();
L_RESP_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
G_CST_USER_ID_LIST.DELETE();
G_UPV_APPLICATION_ID_LIST.DELETE();
G_UPV_RESPONSIBILITY_ID_LIST.DELETE();
G_UPV_ACCESS_GIVEN_DATE_LIST.DELETE();
G_UPV_ACCESS_GIVEN_BY_LIST.DELETE();
G_UPV_GROUP_CODE_LIST.DELETE();
G_UPV_ROLE_NAME_LIST.delete();
G_UPV_MENU_ID_LIST.delete();
G_UPV_FUNCTION_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.delete();
G_UPV_ACCESS_GIVEN_BY_LIST.delete();
G_UPV_ENTRY_OBJECT_TYPE_LIST.delete();
G_CST_USER_ID_LIST.delete();
G_UPV_GROUP_CODE_LIST.delete();
L_FUNC_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
G_UPV_ROLE_NAME_LIST.delete();
G_UPV_MENU_ID_LIST.delete();
G_UPV_FUNCTION_ID_LIST.delete();
G_UPV_ENTRY_OBJECT_TYPE_LIST.delete();
G_UPV_GROUP_CODE_LIST.delete();
G_CST_USER_ID_LIST.delete();
G_UPV_APPLICATION_ID_LIST.delete();
G_UPV_RESPONSIBILITY_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.delete();
G_UPV_ACCESS_GIVEN_BY_LIST.delete();
l_user_access_waived_resp_list.delete();
L_RESP_FUNC_ID_LIST.delete();
L_RESPVIO_ENTRIES.delete();
L_FUNC_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
G_CST_USER_ID_LIST.delete();
G_UPV_APPLICATION_ID_LIST.delete();
G_UPV_RESPONSIBILITY_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.delete();
G_UPV_ACCESS_GIVEN_BY_LIST.delete();
l_user_access_waived_resp_list.delete();
SELECT type_code
FROM amw_constraints_b
WHERE constraint_rev_id=l_constraint_rev_id;
'SELECT distinct ur.user_id '
||' FROM '||G_AMW_USER_RESP_GROUPS||' ur '
||' ,'||G_AMW_USER||' u '
||' WHERE ur.user_id = u.user_id '
||' AND ur.start_date <= sysdate AND (ur.end_date >= sysdate or ur.end_date is null) '
||' AND u.start_date <= sysdate AND (u.end_date >= sysdate or u.end_date is null) '
||' AND ur.responsibility_id in ( '
||' SELECT responsibility_id '
||' FROM '||G_AMW_RESPONSIBILITY
||' WHERE start_date <= sysdate AND (end_date >= sysdate or end_date is null) '
||' AND menu_id in ( '
||' SELECT menu_id '
||' FROM '||G_AMW_COMPILED_MENU_FUNCTIONS
||' WHERE grant_flag = ''Y'' '
||' AND function_id in ( '
||' SELECT constraintEntry.FUNCTION_ID '
||' FROM AMW_CONSTRAINT_ENTRIES constraintEntry '
||' WHERE constraintEntry.CONSTRAINT_REV_ID = :1 '
||' ) '
||' ) '
||' ) ';
'SELECT u.user_id, u.user_name '
||' FROM '||G_AMW_USER||' u '
||' WHERE u.start_date <= sysdate AND (u.end_date >= sysdate or u.end_date is null) '
||' AND u.user_name in ( '
||' SELECT ur.user_name '
||' FROM '||G_AMW_USER_ROLES||' ur '
||' ,'||G_AMW_RESPONSIBILITY||' resp '
||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf '
||' ,AMW_CONSTRAINT_ENTRIES ce '
||' WHERE ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND resp.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND cmf.grant_flag = ''Y'' '
||' AND ce.CONSTRAINT_REV_ID = :1 '
||' AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
||' UNION ALL '
||' SELECT ur.user_name '
||' FROM '||G_AMW_USER_ROLES||' ur '
||' ,'||G_AMW_RESPONSIBILITY||' resp '
||' ,'||G_AMW_REQUEST_GROUP_UNITS||' rgu '
||' ,AMW_CONSTRAINT_ENTRIES ce '
||' WHERE ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND resp.request_group_id = rgu.request_group_id '
||' AND rgu.request_unit_type = ''P'' '
||' AND rgu.request_unit_id = ce.function_id AND ce.object_type = ''CP'' '
||' AND ce.CONSTRAINT_REV_ID = :2 '
||' AND resp.start_date <= sysdate AND (resp.end_date >= sysdate or resp.end_date is null) '
||' UNION ALL '
||' SELECT ur.user_name '
||' FROM '||G_AMW_USER_ROLES||' ur '
||' ,'||G_AMW_GRANTS||' gra '
||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf '
||' ,AMW_CONSTRAINT_ENTRIES ce '
||' WHERE (ur.role_orig_system = ''UMX'' OR ur.role_orig_system = ''FND_RESP'') '
||' AND ur.ROLE_NAME = gra.GRANTEE_KEY '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.GRANTEE_TYPE = ''GROUP'' '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND ce.CONSTRAINT_REV_ID = :3 '
||' AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
||' UNION ALL '
||' SELECT ur.user_name '
||' FROM '||G_AMW_USER_ROLES||' ur '
||' ,'||G_AMW_GRANTS||' gra '
||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf '
||' ,AMW_CONSTRAINT_ENTRIES ce '
||' WHERE ur.user_name = gra.GRANTEE_KEY '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.GRANTEE_TYPE = ''USER'' '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND ce.CONSTRAINT_REV_ID = :4 '
||' AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
||' ) ';
'SELECT distinct ur.user_id, to_char(null) user_name '
||' FROM '||G_AMW_USER_RESP_GROUPS||' ur'
||' ,'||G_AMW_USER||' u'
||' WHERE ur.user_id = u.user_id '
||' AND ur.start_date <= sysdate AND (ur.end_date >= sysdate or ur.end_date is null) '
||' AND u.start_date <= sysdate AND (u.end_date >= sysdate or u.end_date is null) '
||' AND ur.responsibility_id in ( '
||' SELECT constraintEntry.FUNCTION_ID '
||' FROM AMW_CONSTRAINT_ENTRIES constraintEntry '
||' WHERE constraintEntry.CONSTRAINT_REV_ID = :1 '
||' ) ';
G_CST_USER_ID_LIST.DELETE();
G_CST_USER_NAME_LIST.DELETE();
SELECT type_code
FROM amw_constraints_b
WHERE constraint_rev_id=l_constraint_rev_id;
' SELECT gra.grantee_key role_name, '
||' gra.menu_id, '
||' ce.function_id, '
||' min(urasgn.start_date), '
||' ( SELECT asgn.created_by '
||' FROM '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' WHERE asgn.user_name = usr.user_name '
||' AND asgn.role_name = role_name '
||' AND asgn.start_date = start_date '
||' AND rownum=1 '
||' ) created_by, '
||' ''FUNC'' entry_object_type, '
||' usr.user_id ,'
||' ce.group_code '
||' FROM '||G_AMW_GRANTS||' gra, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' AMW_CONSTRAINT_ENTRIES ce, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr, '
||' AMW_VIOLATION_USERS vu '
||' WHERE urasgn.user_name = usr.user_name '
||' AND (gra.grantee_orig_system = ''UMX'' OR gra.grantee_orig_system = ''FND_RESP'') '
||' AND urasgn.role_name = gra.GRANTEE_KEY '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND ce.CONSTRAINT_REV_ID = :1 '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.GRANTEE_TYPE = ''GROUP'' '
||' AND gra.start_date <= sysdate '
||' AND (gra.end_date >= sysdate or gra.end_date is null) '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' AND vu.violation_id =:2 '
||' AND vu.violated_by_id = usr.user_id '
||' GROUP BY gra.grantee_key, gra.menu_id,ce.function_id,usr.user_name,usr.user_id, ce.group_code '
||' UNION ALL '
||' SELECT to_char(null) role_name, '
||' gra.menu_id, '
||' ce.function_id, '
||' gra.start_date, '
||' gra.created_by, '
||' ''FUNC'' entry_object_type, '
||' usr.user_id ,'
||' ce.group_code '
||' FROM '||G_AMW_GRANTS||' gra, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' AMW_CONSTRAINT_ENTRIES ce, '
||' '||G_AMW_USER||' usr , '
||' AMW_VIOLATION_USERS vu '
||' WHERE (( gra.GRANTEE_KEY = usr.user_name AND gra.GRANTEE_TYPE = ''USER'') '
||' OR (gra.GRANTEE_KEY = ''GLOBAL'' AND gra.GRANTEE_TYPE = ''GLOBAL'')) '
||' AND gra.menu_id = cmf.menu_id '
||' AND cmf.function_id = ce.function_id '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND ce.CONSTRAINT_REV_ID = :3 '
||' AND gra.INSTANCE_TYPE = ''GLOBAL'' '
||' AND gra.OBJECT_ID = -1 '
||' AND gra.start_date <= sysdate '
||' AND (gra.end_date >= sysdate or gra.end_date is null) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' AND vu.violation_id =:4 '
||' AND vu.violated_by_id = usr.user_id ';
' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from WF_USER_ROLE_ASSIGNMENTS asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by, '
||' DECODE((select count(*) '
||' from amw_constraint_waivers_b '
||' where constraint_rev_id= :1 '
||' and object_type=''RESP'' '
||' and pk1 = ur.role_orig_system_id '
||' and pk2 = appl.application_id '
||' and start_date <= sysdate '
||' AND (end_date >= sysdate or end_date is null)),0,''N'',''Y'') waived_flag '
||' FROM WF_USER_ROLES ur, '
||' WF_USER_ROLE_ASSIGNMENTS urasgn, '
||' FND_USER usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' FND_RESPONSIBILITY resp, '
||' FND_COMPILED_MENU_FUNCTIONS cmf, '
||' AMW_CONSTRAINT_ENTRIES ce , '
||' AMW_VIOLATION_USERS vu '
||' WHERE ce.CONSTRAINT_REV_ID = :2 '
||' AND (ce.object_type is null OR ce.object_type = ''FUNC'') '
||' AND cmf.menu_id = resp.menu_id '
||' AND cmf.grant_flag = ''Y'' '
||' AND cmf.function_id = ce.function_id '
||' AND resp.start_date <= sysdate '
||' AND (resp.end_date >= sysdate or resp.end_date is null) '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.start_date<= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND rol.owner_tag=appl.application_short_name '
||' AND resp.application_id=appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND (ur.user_orig_system = ''FND_USR'' OR ur.user_orig_system = ''PER'') '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND ur.user_name=usr.user_name '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' AND vu.violation_id =:3 '
||' AND vu.violated_by_id = usr.user_id '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name '
||' UNION '
||' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from WF_USER_ROLE_ASSIGNMENTS asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by, '
||' DECODE((select count(*) '
||' from amw_constraint_waivers_b '
||' where constraint_rev_id= :4 '
||' and object_type=''RESP'' '
||' and pk1 = ur.role_orig_system_id '
||' and pk2 = appl.application_id '
||' and start_date <= sysdate '
||' AND (end_date >= sysdate or end_date is null)),0,''N'',''Y'') waived_flag '
||' FROM WF_USER_ROLES ur, '
||' WF_USER_ROLE_ASSIGNMENTS urasgn, '
||' FND_USER usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' FND_RESPONSIBILITY RESP, '
||' FND_REQUEST_GROUP_UNITS RGU, '
||' AMW_CONSTRAINT_ENTRIES ACE , '
||' AMW_VIOLATION_USERS vu '
||' WHERE ACE.CONSTRAINT_REV_ID=:5 '
||' AND ACE.OBJECT_TYPE=''CP'' '
||' AND RESP.GROUP_APPLICATION_ID IS NOT NULL '
||' AND RESP.REQUEST_GROUP_ID IS NOT NULL '
||' AND RGU.APPLICATION_ID=RESP.GROUP_APPLICATION_ID '
||' AND RGU.REQUEST_GROUP_ID=RESP.REQUEST_GROUP_ID '
||' AND RGU.REQUEST_UNIT_TYPE = ''P'' '
||' AND RGU.UNIT_APPLICATION_ID=ACE.APPLICATION_ID '
||' AND RGU.REQUEST_UNIT_ID=ACE.FUNCTION_ID '
||' AND RESP.START_DATE<=SYSDATE '
||' AND (RESP.END_DATE>= SYSDATE or RESP.END_DATE IS NULL) '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.start_date<= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND rol.owner_tag = appl.application_short_name '
||' AND resp.application_id = appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate or urasgn.end_date is null) '
||' AND ur.user_name=usr.user_name '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate or usr.end_date is null) '
||' AND vu.violation_id =:6 '
||' AND vu.violated_by_id = usr.user_id '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name ';
' SELECT usr.user_id, '
||' appl.application_id, '
||' ur.role_orig_system_id, '
||' min(urasgn.start_date) start_date, '
||' ( select asgn.created_by '
||' from '||G_AMW_USER_ROLE_ASSIGNMENTS||' asgn '
||' where asgn.user_name = ur.user_name '
||' and asgn.role_name = ur.role_name '
||' and asgn.start_date = start_date '
||' and rownum=1 '
||' ) created_by ,'
||' ce.group_code '
||' FROM WF_USER_ROLES ur, '
||' '||G_AMW_USER_ROLE_ASSIGNMENTS||' urasgn, '
||' '||G_AMW_USER||' usr, '
||' WF_LOCAL_ROLES rol, '
||' FND_APPLICATION_VL appl, '
||' '||G_AMW_RESPONSIBILITY||' resp, '
||' AMW_CONSTRAINT_ENTRIES ce , '
||' AMW_VIOLATION_USERS vu '
||' WHERE ce.CONSTRAINT_REV_ID = :1 '
||' AND ce.object_type = ''RESP'' '
||' AND ce.function_id = resp.responsibility_id '
||' AND ce.application_id = resp.application_id '
||' AND ur.role_orig_system_id = resp.responsibility_id '
||' AND ur.role_orig_system = ''FND_RESP'' '
||' AND ur.role_name = rol.name '
||' AND ur.role_orig_system = rol.orig_system '
||' AND ur.role_orig_system_id = rol.orig_system_id '
||' AND ur.partition_id = rol.partition_id '
||' AND rol.owner_tag = appl.application_short_name '
||' AND resp.application_id = appl.application_id '
||' AND ur.user_name = urasgn.user_name '
||' AND ur.role_name = urasgn.role_name '
||' AND ur.user_name = usr.user_name '
||' AND (ur.user_orig_system = ''FND_USR'' OR ur.user_orig_system = ''PER'') '
||' AND rol.start_date <= sysdate '
||' AND (rol.expiration_date IS NULL OR rol.expiration_date>=sysdate) '
||' AND vu.violation_id =:2 '
||' AND vu.violated_by_id = usr.user_id '
||' AND resp.start_date <= sysdate '
||' AND (resp.end_date >= sysdate OR resp.end_date IS NULL) '
||' AND urasgn.start_date <= sysdate '
||' AND (urasgn.end_date >= sysdate OR urasgn.end_date IS NULL) '
||' AND usr.start_date <= sysdate '
||' AND (usr.end_date >= sysdate OR usr.end_date IS NULL) '
||' GROUP BY usr.user_id,ur.user_name,appl.application_id,ur.role_orig_system_id, ur.role_name,ce.group_code ';
G_USER_VIOLATIONS_LIST.delete();
G_USER_RESP_VIO_LIST.delete();
G_CST_USER_ID_LIST.Delete();
G_UPV_APPLICATION_ID_LIST.Delete();
G_UPV_RESPONSIBILITY_ID_LIST.Delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.Delete();
G_UPV_ACCESS_GIVEN_BY_LIST.Delete();
G_UPV_GROUP_CODE_LIST.Delete();
L_RESP_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
G_CST_USER_ID_LIST.Delete();
G_UPV_APPLICATION_ID_LIST.Delete();
G_UPV_RESPONSIBILITY_ID_LIST.Delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.Delete();
G_UPV_ACCESS_GIVEN_BY_LIST.Delete();
G_UPV_GROUP_CODE_LIST.Delete();
G_UPV_ROLE_NAME_LIST.delete();
G_UPV_MENU_ID_LIST.delete();
G_UPV_FUNCTION_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.delete();
G_UPV_ACCESS_GIVEN_BY_LIST.delete();
G_UPV_ENTRY_OBJECT_TYPE_LIST.delete();
G_CST_USER_ID_LIST.delete();
G_UPV_GROUP_CODE_LIST.delete();
L_FUNC_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
G_UPV_ROLE_NAME_LIST.delete();
G_UPV_MENU_ID_LIST.delete();
G_UPV_FUNCTION_ID_LIST.delete();
G_UPV_ENTRY_OBJECT_TYPE_LIST.delete();
G_CST_USER_ID_LIST.delete();
G_UPV_APPLICATION_ID_LIST.delete();
G_UPV_RESPONSIBILITY_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.delete();
G_UPV_ACCESS_GIVEN_BY_LIST.delete();
l_user_access_waived_resp_list.delete();
G_UPV_GROUP_CODE_LIST.delete();
L_RESP_FUNC_ID_LIST.delete();
L_RESPVIO_ENTRIES.delete();
L_FUNC_ID_LIST.delete();
L_USERVIO_ENTRIES.delete();
'SELECT u.user_id, u.user_name '
||' FROM AMW_VIOLATION_USERS vu '
||' ,'||G_AMW_USER||' u '
||' WHERE vu.violation_id = :1 '
||' AND vu.violated_by_id = u.user_id ';
G_CST_USER_ID_LIST.DELETE();
G_CST_USER_NAME_LIST.DELETE();
SELECT AMW_VIOLATION_S.NEXTVAL
FROM dual;
AMW_VIOLATIONS_PKG.insert_row(x_rowid => l_row_id,
x_violation_id => l_violation_id,
x_constraint_rev_id => p_constraint_rev_id,
x_request_id => l_request_id,
x_request_date => SYSDATE,
x_requested_by_id => G_PARTY_ID,
x_violator_num => NULL,
x_status_code => 'NA',
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_created_by => G_USER_ID,
x_creation_date => SYSDATE,
x_last_update_login => G_LOGIN_ID,
x_security_group_id => G_SECURITY_GROUP_ID,
x_object_version_number => 1);
PROCEDURE Update_Violation (
p_violation_id IN NUMBER,
p_constraint_rev_id IN NUMBER,
p_revalidate_flag IN VARCHAR2 := NULL
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Violation';
SELECT count(*)
FROM amw_violation_users
WHERE violation_id = l_violation_id
AND (waived_flag is NULL OR waived_flag <> 'Y');
SELECT count(*)
FROM amw_violation_users
WHERE violation_id = l_violation_id
AND (waived_flag is NULL OR waived_flag <> 'Y')
AND (corrected_flag is NULL OR corrected_flag <> 'Y');
SELECT count(*)
FROM amw_violation_resp
WHERE violation_id = l_violation_id
AND waived_flag = 'N'
AND corrected_flag = 'N';
AMW_VIOLATIONS_PKG.update_row(x_violation_id => p_violation_id,
x_constraint_rev_id => p_constraint_rev_id,
x_violator_num => l_violator_num,
x_status_code => l_violation_status,
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_last_update_login => G_LOGIN_ID,
x_security_group_id => G_SECURITY_GROUP_ID,
x_object_version_number => 1,
x_reval_request_id => l_reval_request_id, -- 05.20.2005 tsho: AMW.E revalidation
x_reval_request_date => l_reval_request_date, -- 05.20.2005 tsho: AMW.E revalidation
x_reval_requested_by_id => l_reval_requested_by_id -- 05.20.2005 tsho: AMW.E revalidation
);
END Update_Violation;
PROCEDURE Update_Violation_User (
p_user_violation_id IN NUMBER := NULL,
p_violation_id IN NUMBER,
p_violated_by_id IN NUMBER,
p_corrected_flag IN VARCHAR2 := NULL
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Violation_User';
SELECT user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id
AND violated_by_id = l_violated_by_id;
AMW_VIOLATION_USERS_PKG.UPDATE_ROW (
x_user_violation_id => l_user_violation_id,
x_violation_id => p_violation_id,
x_violated_by_id => p_violated_by_id,
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_last_update_login => G_USER_ID,
x_security_group_id => G_SECURITY_GROUP_ID,
x_waived_flag => null,
x_corrected_flag => p_corrected_flag);
DELETE FROM AMW_VIOLAT_USER_ENTRIES
WHERE USER_VIOLATION_ID=l_user_violation_id;
END Update_Violation_User;
SELECT AMW_USER_VIOLATION_S.NEXTVAL
FROM dual;
'SELECT person_party_id '
||' FROM '||G_AMW_USER
||' WHERE user_id = :1 ';
SELECT person_party_id
INTO l_party_id
FROM FND_USER
WHERE user_id = p_user_id;
AMW_VIOLATION_USERS_PKG.insert_row(x_rowid => l_row_id,
x_user_violation_id => l_user_violation_id,
x_violation_id => p_violation_id,
x_violated_by_id => l_party_id,
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_created_by => G_USER_ID,
x_creation_date => SYSDATE,
x_last_update_login => G_LOGIN_ID,
x_security_group_id => NULL);
INSERT INTO AMW_VIOLAT_USER_ENTRIES VALUES (
SYSDATE, -- last_update_date
G_USER_ID, -- last_updated_by
G_LOGIN_ID, -- last_update_login
SYSDATE, -- creation_date
G_USER_ID, -- created_by
NULL, -- security_group_id
l_user_violation_id, -- user_violation_id
G_PV_RESPONSIBILITY_ID_LIST(i), -- responsibility_id
G_PV_MENU_ID_LIST(i), -- menu_id
G_PV_FUNCTION_ID_LIST(i), -- function_id
G_PV_ACCESS_GIVEN_DATE_LIST(i), -- access_given_date
G_PV_ACCESS_GIVEN_BY_LIST(i), -- access_given_by_id
NULL, -- role_name
NULL, -- object_type
G_PV_APPLICATION_ID_LIST(i), -- application_id
null);
G_PV_FUNCTION_ID_LIST.DELETE();
G_PV_MENU_ID_LIST.DELETE();
G_PV_RESPONSIBILITY_ID_LIST.DELETE();
G_PV_APPLICATION_ID_LIST.DELETE();
G_PV_ACCESS_GIVEN_DATE_LIST.DELETE();
G_PV_ACCESS_GIVEN_BY_LIST.DELETE();
G_PV_GROUP_CODE_LIST.DELETE();
G_PV_PROGRAM_APPL_ID_LIST.DELETE();
G_PV_OBJECT_TYPE_LIST.DELETE();
G_UPV_FUNCTION_ID_LIST.DELETE();
G_UPV_MENU_ID_LIST.DELETE();
G_UPV_RESPONSIBILITY_ID_LIST.DELETE();
G_UPV_APPLICATION_ID_LIST.DELETE();
G_UPV_PROGRAM_APPL_ID_LIST.delete();
G_UPV_ACCESS_GIVEN_DATE_LIST.DELETE();
G_UPV_ACCESS_GIVEN_BY_LIST.DELETE();
G_UPV_ROLE_NAME_LIST.DELETE();
G_UPV_GROUP_CODE_LIST.DELETE();
G_UPV_ENTRY_OBJECT_TYPE_LIST.DELETE();
SELECT function_id
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
'SELECT responsibility_id, responsibility_application_id, start_date, created_by '
||' FROM '||G_AMW_USER_RESP_GROUPS
||' WHERE user_id = :1 and (end_date >= sysdate or end_date is null)';
'SELECT outer.responsibility_id, outer.responsibility_application_id, outer.start_date, outer.created_by '
||' FROM '||G_AMW_USER_RESP_GROUPS ||' outer '
||' WHERE outer.start_date = ('
||' SELECT min(innter.start_date) '
||' FROM '||G_AMW_USER_RESP_GROUPS ||' inner '
||' WHERE inner.user_id = :1 '
||' AND inner.responsibility_id = outer.responsibility_id '
||' AND inner.responsibility_application_id = outer.responsibility_application_id '
||' AND inner.start_date <= sysdate AND (inner.end_date >= sysdate or inner.end_date is null) '
||' )';
SELECT responsibility_id,
responsibility_application_id,
start_date,
created_by
from FND_USER_RESP_GROUPS
where user_id = l_user_id
and (end_date >= sysdate or end_date is null);
SELECT responsibility_id,
responsibility_application_id,
start_date,
created_by
BULK COLLECT INTO l_user_resp_id_list,
l_user_resp_app_id_list,
l_user_resp_start_date_list,
l_user_resp_created_by_list
FROM FND_USER_RESP_GROUPS
WHERE user_id = G_USER_ID_LIST(i)
AND (end_date >= sysdate or end_date is null);
SELECT function_id
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
'SELECT responsibility_id, responsibility_application_id, start_date, created_by '
||' FROM '||G_AMW_USER_RESP_GROUPS
||' WHERE user_id = :1 and (end_date >= sysdate or end_date is null)';
'SELECT outer.responsibility_id, outer.responsibility_application_id, outer.start_date, outer.created_by '
||' FROM '||G_AMW_USER_RESP_GROUPS ||' outer '
||' WHERE outer.start_date = ('
||' SELECT min(innter.start_date) '
||' FROM '||G_AMW_USER_RESP_GROUPS ||' inner '
||' WHERE inner.user_id = :1 '
||' AND inner.responsibility_id = outer.responsibility_id '
||' AND inner.responsibility_application_id = outer.responsibility_application_id '
||' AND inner.start_date <= sysdate AND (inner.end_date >= sysdate or inner.end_date is null) '
||' )';
SELECT responsibility_id,
responsibility_application_id,
start_date,
created_by
from FND_USER_RESP_GROUPS
where user_id = l_user_id
and (end_date >= sysdate or end_date is null);
SELECT responsibility_id,
responsibility_application_id,
start_date,
created_by
BULK COLLECT INTO l_user_resp_id_list,
l_user_resp_app_id_list,
l_user_resp_start_date_list,
l_user_resp_created_by_list
FROM FND_USER_RESP_GROUPS
WHERE user_id = G_USER_ID_LIST(i)
AND (end_date >= sysdate or end_date is null);
'SELECT DISTINCT resp.responsibility_id, '
||' resp.application_id, '
||' resp.menu_id, '
||' ce.function_id, '
||' ce.group_code '
||' FROM AMW_CONSTRAINT_ENTRIES ce, '
||' '||G_AMW_COMPILED_MENU_FUNCTIONS||' cmf, '
||' '||G_AMW_RESPONSIBILITY||' resp '
||' WHERE ce.CONSTRAINT_REV_ID = :1 '
||' AND (ce.OBJECT_TYPE is null OR ce.OBJECT_TYPE = ''FUNC'') '
||' AND cmf.function_id=ce.FUNCTION_ID '
||' AND cmf.grant_flag = ''Y'' '
||' AND resp.menu_id = cmf.menu_id '
||' AND resp.start_date <= sysdate '
||' AND (resp.end_date >= sysdate or resp.end_date is null) ';
G_PNTL_RESP_ID_LIST.DELETE();
G_PNTL_APPL_ID_LIST.DELETE();
G_PNTL_MENU_ID_LIST.DELETE();
G_PNTL_FUNCTION_ID_LIST.DELETE();
G_PNTL_GRP_CODE_LIST.DELETE();
G_PNTL_RESP_VIO_LIST.DELETE();
L_FUNC_ID_LIST.DELETE();
L_RESPVIO_ENTRIES.DELETE();
G_PNTL_RESP_ID_LIST.DELETE();
G_PNTL_APPL_ID_LIST.DELETE();
G_PNTL_MENU_ID_LIST.DELETE();
G_PNTL_FUNCTION_ID_LIST.DELETE();
G_PNTL_GRP_CODE_LIST.DELETE();
' SELECT RESP.APPLICATION_ID, '
||' RESP.RESPONSIBILITY_ID, '
||' RESP.REQUEST_GROUP_ID MENU_ID, '
||' ACE.FUNCTION_ID, '
||' ACE.OBJECT_TYPE, '
||' ACE.APPLICATION_ID, '
||' ACE.GROUP_CODE, '
||' RGU.CREATION_DATE ACCESS_GIVEN_DATE, '
||' RGU.CREATED_BY ACCESS_GIVEN_BY '
||' FROM '||G_AMW_RESPONSIBILITY||' RESP, '
||' '||G_AMW_REQUEST_GROUP_UNITS||' RGU, '
||' AMW_CONSTRAINT_ENTRIES ACE '
||' WHERE RESP.GROUP_APPLICATION_ID IS NOT NULL '
||' AND RESP.REQUEST_GROUP_ID IS NOT NULL '
||' AND RGU.REQUEST_UNIT_TYPE = ''P'' '
||' AND ACE.OBJECT_TYPE=''CP'' '
||' AND ACE.CONSTRAINT_REV_ID=:1 '
||' AND RESP.GROUP_APPLICATION_ID=RGU.APPLICATION_ID '
||' AND RESP.REQUEST_GROUP_ID=RGU.REQUEST_GROUP_ID '
||' AND RGU.UNIT_APPLICATION_ID=ACE.APPLICATION_ID '
||' AND RGU.REQUEST_UNIT_ID=ACE.FUNCTION_ID '
||' AND RESP.START_DATE<=SYSDATE AND (RESP.END_DATE>= SYSDATE or RESP.END_DATE IS NULL) '
||' UNION ALL '
||' SELECT RESP.APPLICATION_ID, '
||' RESP.RESPONSIBILITY_ID, '
||' RESP.REQUEST_GROUP_ID MENU_ID, '
||' ACE.FUNCTION_ID, '
||' ACE.OBJECT_TYPE, '
||' ACE.APPLICATION_ID, '
||' ACE.GROUP_CODE, '
||' RGU.CREATION_DATE ACCESS_GIVEN_DATE, '
||' RGU.CREATED_BY ACCESS_GIVEN_BY '
||' FROM '||G_AMW_RESPONSIBILITY||' RESP , '
||' '||G_AMW_REQUEST_GROUP_UNITS||' RGU, '
||' AMW_CONSTRAINT_ENTRIES ACE, '
||' '||G_AMW_CONCURRENT_PROGRAMS_VL||' CON '
||' WHERE RESP.GROUP_APPLICATION_ID IS NOT NULL '
||' AND RESP.REQUEST_GROUP_ID IS NOT NULL '
||' AND RGU.REQUEST_UNIT_TYPE = ''A'' '
||' AND ACE.OBJECT_TYPE=''CP'' '
||' AND ACE.CONSTRAINT_REV_ID=:2 '
||' AND RESP.GROUP_APPLICATION_ID=RGU.APPLICATION_ID '
||' AND RESP.REQUEST_GROUP_ID=RGU.REQUEST_GROUP_ID '
||' AND RGU.UNIT_APPLICATION_ID=ACE.APPLICATION_ID '
||' AND RGU.REQUEST_UNIT_ID=ACE.APPLICATION_ID '
||' AND RGU.UNIT_APPLICATION_ID=CON.APPLICATION_ID '
||' AND CON.CONCURRENT_PROGRAM_ID = ACE.FUNCTION_ID '
||' AND RESP.START_DATE<=SYSDATE AND (RESP.END_DATE>= SYSDATE or RESP.END_DATE IS NULL)' ;
'SELECT action_id '
||' FROM '||G_AMW_RESP_FUNCTIONS
||' WHERE application_id = :1 '
||' AND responsibility_id = :2 '
||' AND rule_type = ''F'' ';
G_RESP_VIOLATIONS_LIST.delete();
L_FUNC_ID_LIST.delete();
L_RESPVIO_ENTRIES.delete();
G_UNEXCL_FUNC_ID_LIST.delete();
G_UNEXCL_GRP_CODE_LIST.delete();
l_excl_func_list.delete();
l_excl_func_id_list.delete();
L_FUNC_ID_LIST.delete();
L_RESPVIO_ENTRIES.delete();
G_PNTL_RESP_VIO_LIST.delete();
G_UNEXCL_FUNC_ID_LIST.delete();
G_UNEXCL_GRP_CODE_LIST.delete();
SELECT PK1,PK2
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'RESP'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT AMW_USER_VIOLATION_S.NEXTVAL
FROM dual;
DELETE FROM AMW_VIOLAT_USER_ENTRIES
WHERE USER_VIOLATION_ID =l_user_violation_id;
UPDATE AMW_VIOLATION_USERS SET CORRECTED_FLAG = 'N'
WHERE USER_VIOLATION_ID = l_user_violation_id;
UPDATE AMW_VIOLATION_USERS SET WAIVED_FLAG = 'Y'
WHERE USER_VIOLATION_ID = l_user_violation_id;
AMW_VIOLATION_USERS_PKG.insert_row(x_rowid => l_row_id,
x_user_violation_id => l_user_violation_id,
x_violation_id => p_violation_id,
x_violated_by_id => l_party_id,
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_created_by => G_USER_ID,
x_creation_date => SYSDATE,
x_last_update_login => G_LOGIN_ID,
x_security_group_id => NULL,
x_waived_flag => l_is_user_waived);
INSERT INTO AMW_VIOLAT_USER_ENTRIES(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
USER_VIOLATION_ID,
RESPONSIBILITY_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
ROLE_NAME,
OBJECT_TYPE,
APPLICATION_ID,
PROGRAM_APPLICATION_ID)
VALUES (
SYSDATE, -- last_update_date
G_USER_ID, -- last_updated_by
G_LOGIN_ID, -- last_update_login
SYSDATE, -- creation_date
G_USER_ID, -- created_by
NULL, -- security_group_id
l_user_violation_id, -- user_violation_id
L_USERVIO_ENTRIES(k).Responsibility_id ,-- responsibility_id
L_USERVIO_ENTRIES(k).Menu_id, -- menu_id
L_USERVIO_ENTRIES(k).Function_Id , -- function_id
L_USERVIO_ENTRIES(k).Access_Given_Date, -- access_given_date
L_USERVIO_ENTRIES(k).Access_Given_By_Id,-- access_given_by_id
L_USERVIO_ENTRIES(k).Role_Name, -- role_name
L_USERVIO_ENTRIES(k).Object_Type, -- object_type
L_USERVIO_ENTRIES(k).application_id , -- application_id
L_USERVIO_ENTRIES(k).prog_appl_id ); -- program application id
SELECT AMW_USER_VIOLATION_S.NEXTVAL
FROM dual;
DELETE FROM AMW_VIOLAT_USER_ENTRIES
WHERE USER_VIOLATION_ID = l_user_violation_id;
UPDATE AMW_VIOLATION_USERS SET CORRECTED_FLAG = 'N'
WHERE USER_VIOLATION_ID = l_user_violation_id;
AMW_VIOLATION_USERS_PKG.insert_row(x_rowid => l_row_id,
x_user_violation_id => l_user_violation_id,
x_violation_id => p_violation_id,
x_violated_by_id => l_party_id,
x_last_updated_by => G_USER_ID,
x_last_update_date => SYSDATE,
x_created_by => G_USER_ID,
x_creation_date => SYSDATE,
x_last_update_login => G_LOGIN_ID,
x_security_group_id => NULL,
x_waived_flag => l_is_user_waived);
INSERT INTO AMW_VIOLAT_USER_ENTRIES(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
USER_VIOLATION_ID,
RESPONSIBILITY_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
ROLE_NAME,
OBJECT_TYPE,
APPLICATION_ID,
PROGRAM_APPLICATION_ID)
VALUES (
SYSDATE, -- last_update_date
G_USER_ID, -- last_updated_by
G_LOGIN_ID, -- last_update_login
SYSDATE, -- creation_date
G_USER_ID, -- created_by
NULL, -- security_group_id
l_user_violation_id, -- user_violation_id
L_USERVIO_ENTRIES(k).Responsibility_id ,-- responsibility_id
NULL, -- menu_id
NULL, -- function_id
L_USERVIO_ENTRIES(k).Access_Given_Date, -- access_given_date
L_USERVIO_ENTRIES(k).Access_Given_By_Id,-- access_given_by_id
NULL, -- role_name
'RESP', -- object_type
L_USERVIO_ENTRIES(k).application_id, -- application_id
NULL); -- program application id
SELECT function_id,object_type,group_code
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
SELECT AMW_VIOLATION_RESP_S.NEXTVAL
FROM dual;
SELECT PK1,PK2
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'RESP'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
L_RESPVIO_ENTRIES.DELETE();
L_FUNC_ID_LIST.DELETE();
INSERT INTO AMW_VIOLATION_RESP(
RESP_VIOLATION_ID,
VIOLATION_ID,
RESPONSIBILITY_ID,
APPLICATION_ID,
ROLE_NAME,
WAIVED_FLAG,
CORRECTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID
)
VALUES(
l_resp_violation_id,
P_VIOLATION_ID,
L_RESPVIO_ENTRIES(1).Responsibility_id,
L_RESPVIO_ENTRIES(1).application_id,
null,
l_is_resp_waived,
'N',
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
null
);
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
RESP_VIOLATION_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
l_resp_violation_id,
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id
);
INSERT INTO AMW_VIOLATION_RESP(
RESP_VIOLATION_ID,
VIOLATION_ID,
RESPONSIBILITY_ID,
APPLICATION_ID,
ROLE_NAME,
WAIVED_FLAG,
CORRECTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID
)
VALUES(
l_resp_violation_id,
P_VIOLATION_ID,
L_RESPVIO_ENTRIES(1).Responsibility_id,
L_RESPVIO_ENTRIES(1).application_id,
null,
l_is_resp_waived,
'N',
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
null
);
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
resp_violation_id,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
l_resp_violation_id,
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id );
L_AVAILABLE_GROUP_CODE_LIST.delete();
INSERT INTO AMW_VIOLATION_RESP(
RESP_VIOLATION_ID,
VIOLATION_ID,
RESPONSIBILITY_ID,
APPLICATION_ID,
ROLE_NAME,
WAIVED_FLAG,
CORRECTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID
)
VALUES(
l_resp_violation_id,
P_VIOLATION_ID,
L_RESPVIO_ENTRIES(1).Responsibility_id,
L_RESPVIO_ENTRIES(1).application_id,
null,
l_is_resp_waived,
'N',
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
null
);
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
RESP_VIOLATION_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
l_resp_violation_id,
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id
);
SELECT function_id,object_type,group_code
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
SELECT AMW_VIOLATION_RESP_S.NEXTVAL
FROM dual;
SELECT PK1,PK2
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'RESP'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT APPLICATION_ID,RESPONSIBILITY_ID,RESP_VIOLATION_ID
FROM AMW_VIOLATION_RESP
WHERE violation_id=l_violation_id;
UPDATE AMW_VIOLATION_RESP
SET CORRECTED_FLAG ='Y'
WHERE VIOLATION_ID=p_violation_id;
DELETE FROM AMW_VIOLAT_RESP_ENTRIES
WHERE RESP_VIOLATION_ID IN (SELECT RESP_VIOLATION_ID FROM AMW_VIOLATION_RESP WHERE VIOLATION_ID=p_violation_id);
DELETE FROM AMW_VIOLAT_RESP_ENTRIES
WHERE RESP_VIOLATION_ID =L_EXISTING_RESP_VIO_ID_LIST(i);
UPDATE AMW_VIOLATION_RESP
SET CORRECTED_FLAG ='Y'
WHERE VIOLATION_ID = p_violation_id
AND RESPONSIBILITY_ID = l_existing_resp_id_list(i)
AND APPLICATION_ID = l_existing_appl_id_list(i);
L_RESPVIO_ENTRIES.DELETE();
L_FUNC_ID_LIST.DELETE();
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
RESP_VIOLATION_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
L_EXISTING_RESP_VIO_ID_LIST(i),
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id
);
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
resp_violation_id,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
L_EXISTING_RESP_VIO_ID_LIST(i),
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id
);
L_AVAILABLE_GROUP_CODE_LIST.delete();
INSERT INTO AMW_VIOLAT_RESP_ENTRIES(
VIOLAT_RESP_ENTRY_ID,
RESP_VIOLATION_ID,
MENU_ID,
FUNCTION_ID,
ACCESS_GIVEN_DATE,
ACCESS_GIVEN_BY_ID,
OBJECT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SECURITY_GROUP_ID,
APPLICATION_ID
)
VALUES(
AMW_VIOLAT_RESP_ENTRIES_S.NEXTVAL,
L_EXISTING_RESP_VIO_ID_LIST(i),
L_RESPVIO_ENTRIES(k).Menu_Id,
L_RESPVIO_ENTRIES(k).Function_id,
L_RESPVIO_ENTRIES(k).Access_Given_Date,
L_RESPVIO_ENTRIES(k).Access_Given_By_Id,
L_RESPVIO_ENTRIES(k).Object_type,
sysdate,
G_USER_ID,
G_LOGIN_ID,
sysdate,
G_USER_ID,
NULL,
L_RESPVIO_ENTRIES(k).prog_appl_id
);
UPDATE AMW_VIOLATION_RESP
SET CORRECTED_FLAG = 'Y'
WHERE VIOLATION_ID = p_violation_id
AND RESPONSIBILITY_ID = l_existing_resp_id_list(i)
AND APPLICATION_ID = l_existing_appl_id_list(i);
UPDATE AMW_VIOLATION_RESP
SET CORRECTED_FLAG ='Y'
WHERE VIOLATION_ID = p_violation_id
AND RESPONSIBILITY_ID = l_existing_resp_id_list(i)
AND APPLICATION_ID = l_existing_appl_id_list(i);
UPDATE AMW_VIOLATION_RESP
SET CORRECTED_FLAG = 'Y'
WHERE VIOLATION_ID = p_violation_id
AND RESPONSIBILITY_ID = l_existing_resp_id_list(i)
AND APPLICATION_ID = l_existing_appl_id_list(i);
SELECT count(*)
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
l_user_access_func_list.DELETE();
l_user_access_func_nowaiv_list.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
l_user_access_func_list.DELETE();
l_user_access_func_nowaiv_list.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
l_user_access_grp_list.DELETE();
l_user_access_grp_nowaiv_list.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT count(*)
FROM amw_constraint_entries
WHERE constraint_rev_id=l_constraint_rev_id;
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT PK1
FROM amw_constraint_waivers_b
WHERE constraint_rev_id = l_constraint_rev_id
AND object_type = 'USER'
AND start_date <= sysdate AND (end_date >= sysdate or end_date is null);
SELECT violated_by_id,user_violation_id
FROM amw_violation_users
WHERE violation_id = l_violation_id;
G_User_Violation_Id_list.DELETE();
G_User_Waiver_List.DELETE();
l_user_access_grp_list.DELETE();
Update_Violation_User (
p_user_violation_id => NULL,
p_violation_id => p_violation_id,
p_violated_by_id => i,
p_corrected_flag => 'Y');
SELECT constraint_rev_id,
start_date,
end_date,
type_code
FROM amw_constraints_b
WHERE constraint_rev_id=l_constraint_rev_id;
Update_Violation(p_violation_id => l_violation_id,
p_constraint_rev_id => p_constraint_rev_id);
SELECT CONSTRAINT_REV_ID,
TYPE_CODE
FROM AMW_CONSTRAINTS_VL
WHERE START_DATE <= SYSDATE AND (END_DATE IS NULL OR END_DATE>=SYSDATE )
AND LOWER(CONSTRAINT_NAME) LIKE LOWER(p_constraint_name||'%');
l_stmt_str VARCHAR2(200) := 'SELECT USER_NAME FROM '||G_AMW_USER;
SELECT constraint_rev_id,
type_code
FROM amw_constraints_b
WHERE start_date <= sysdate AND (end_date IS NULL OR end_date >= sysdate);
SELECT a.constraint_rev_id,
a.type_code
FROM amw_constraints_b a,
amw_constraint_set_details cs
WHERE cs.constraint_set_code = p_constraint_set
AND cs.constraint_id = a.constraint_id;
SELECT cst.constraint_rev_id
,cst.type_code
FROM amw_constraints_b cst
,amw_violations v
WHERE v.violation_id = l_violation_id
AND v.constraint_rev_id = cst.constraint_rev_id;
UPDATE AMW_VIOLATION_USERS
SET CORRECTED_FLAG = 'Y'
WHERE VIOLATION_ID = p_violation_id;
Update_Violation(p_violation_id => p_violation_id,
p_constraint_rev_id => l_constraint.constraint_rev_id,
p_revalidate_flag => 'Y');
'SELECT responsibility_id, menu_id '
||' FROM '||G_AMW_RESPONSIBILITY
||' WHERE application_id = :1 ';
SELECT responsibility_id,
menu_id
from FND_RESPONSIBILITY
where application_id = l_appl_id;
SELECT responsibility_id,
menu_id
BULK COLLECT INTO l_resp_id_list,
l_menu_id_list
FROM FND_RESPONSIBILITY
WHERE application_id = p_appl_id;
'SELECT responsibility_id, menu_id '
||' FROM '||G_AMW_RESPONSIBILITY
||' WHERE application_id = :1 ';
SELECT responsibility_id,
menu_id
from FND_RESPONSIBILITY
where application_id = l_appl_id;
SELECT responsibility_id,
menu_id
BULK COLLECT INTO l_resp_id_list,
l_menu_id_list
FROM FND_RESPONSIBILITY
WHERE application_id = p_appl_id;
SELECT RULE_TYPE, ACTION_ID from fnd_resp_functions
where application_id = p_appl_id
and responsibility_id = p_resp_id;
'SELECT RULE_TYPE, ACTION_ID '
||' FROM '||G_AMW_RESP_FUNCTIONS
||' WHERE application_id = :1 '
||' AND responsibility_id = :2 ';
SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG
from fnd_menu_entries
where MENU_ID = l_sub_menu_id;
'SELECT MENU_ID, ENTRY_SEQUENCE, FUNCTION_ID, SUB_MENU_ID, GRANT_FLAG '
||' FROM '||G_AMW_MENU_ENTRIES
||' WHERE menu_id = :1 ';
/* Select the list of exclusion rules into our cache */
-- 11.12.2003 tsho: use dynamic sql for AMW
/*
for excl_rec in excl_c loop
EXCLUSIONS(excl_rec.action_id) := excl_rec.rule_type;
FND_FILE.put_line(fnd_file.log,'Passed-in Delete Option is: '||p_delopt);
FND_FILE.put_line(fnd_file.log,'will delete violation history prior(<) to : '||l_date);
FND_FILE.put_line(fnd_file.log,'Delete Constraint Violation Start');
DELETE FROM amw_violat_resp_entries
WHERE creation_date < l_date;
DELETE FROM amw_violation_resp
WHERE creation_date < l_date;
DELETE FROM amw_violat_user_entries
WHERE creation_date < l_date;
DELETE FROM amw_violation_users
WHERE creation_date < l_date;
DELETE FROM amw_violations
WHERE creation_date < l_date;
DELETE FROM amw_violat_resp_entries
WHERE resp_violation_id IN (
SELECT resp_violation_id
FROM amw_violation_resp
WHERE violation_id IN (
SELECT violation_id
FROM amw_violations
WHERE status_code = 'NA'
AND creation_date < l_date));
DELETE FROM amw_violation_resp
WHERE violation_id IN (
SELECT violation_id
FROM amw_violations
WHERE status_code = 'NA'
AND creation_date < l_date);
DELETE FROM amw_violat_user_entries
WHERE user_violation_id IN (
SELECT user_violation_id
FROM amw_violation_users
WHERE violation_id IN (
SELECT violation_id
FROM amw_violations
WHERE status_code = 'NA'
AND creation_date < l_date ));
DELETE FROM amw_violation_users
WHERE violation_id IN (
SELECT violation_id
FROM amw_violations
WHERE status_code = 'NA'
AND creation_date < l_date );
DELETE FROM amw_violations
WHERE status_code = 'NA'
AND creation_date < l_date ;
FND_FILE.put_line(fnd_file.log,'Delete Constraint Violation END');