[Home] [Help]
263:
264:
265: CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
266: SELECT count(*)
267: FROM amw_constraint_entries
268: WHERE constraint_rev_id = l_constraint_rev_id;
269:
270: TYPE refCurTyp IS REF CURSOR;
271: func_acess_count_c refCurTyp;
316: SELECT resp.responsibility_name
317: FROM fnd_responsibility_vl resp
318: WHERE resp.responsibility_id in (
319: SELECT function_id
320: FROM amw_constraint_entries cons
321: WHERE constraint_rev_id = l_constraint_rev_id
322: AND cons.application_id = resp.application_id);
323:
324: l_cst_new_violation_sql VARCHAR2(5000) ;
441: ' select ur.role_orig_system_id '
442: ||' from '
443: || G_AMW_USER_ROLES||' ur '
444: ||' ,'||G_AMW_user||' u '
445: ||' ,amw_constraint_entries cst '
446: ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
447: ||' where u.user_id = :1 '
448: ||' and cst.constraint_rev_id = :2 '
449: ||' and u.user_name = ur.user_name '
459: ' select cst.group_code '
460: ||' from '
461: || G_AMW_USER_ROLES||' ur '
462: ||' ,'||G_AMW_user||' u '
463: ||' ,amw_constraint_entries cst '
464: ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
465: ||' where u.user_id = :1 '
466: ||' and cst.constraint_rev_id = :2 '
467: ||' and u.user_name = ur.user_name '
534: || l_resp_existing_sql
535: ||' UNION ALL '
536: ||' select distinct rle.orig_system_id '
537: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
538: ||' , amw_constraint_entries cst '
539: ||' where rle.orig_system = ''FND_RESP'' '
540: ||' and cst.constraint_rev_id = :3 '
541: ||' and cst.function_id = rle.orig_system_id '
542: ||' and rle.name in ( '||l_sub_role_names||' ) '
547: || l_resp_set_existing_sql
548: ||' UNION ALL '
549: ||' select cst.group_code '
550: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
551: ||' , amw_constraint_entries cst '
552: ||' where rle.orig_system = ''FND_RESP'' '
553: ||' and cst.constraint_rev_id = :3 '
554: ||' and cst.function_id = rle.orig_system_id '
555: ||' and rle.name in ( '||l_sub_role_names||' ) '
557:
558: l_violating_new_roles_sql :=
559: 'select resp.responsibility_name from fnd_responsibility_vl resp'
560: ||' where resp.responsibility_id in ( '
561: ||' select function_id from amw_constraint_entries cons, wf_roles rl '
562: ||' where constraint_rev_id = :1 '
563: ||' and cons.application_id = resp.application_id '
564: ||' and cons.function_id = rl.orig_system_id '
565: ||' and rl.orig_system = ''FND_RESP'' '
569:
570: l_violating_existing_roles_sql :=
571: 'select resp.responsibility_name from fnd_responsibility_vl resp'
572: ||' where resp.responsibility_id in ( '
573: ||' select function_id from amw_constraint_entries cons, wf_roles rl '
574: ||' where constraint_rev_id = :1 '
575: ||' and cons.application_id = resp.application_id '
576: ||' and cons.function_id = rl.orig_system_id '
577: ||' and rl.orig_system = ''FND_RESP'' '
594: -- even if the allready assigned violating roles exist.
595: /***
596: l_vio_exist_role_sql :='select count(distinct function_id)'
597: ||'from ('
598: ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
599: ||' MINUS '
600: ||'select distinct function_id from ( '
601: || l_func_existing_sql
602: ||') '
649: ||' where rcd.constraint_rev_id = :5 '
650: ||' and rcd.role_name in ( '||l_sub_role_names||' ) '
651: ||') '
652: ||' MINUS '
653: ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :6'
654: ||')';
655:
656:
657: OPEN func_acess_count_c FOR l_func_sql USING
839: -- Check to see if violation is due to the allready
840: -- assigned violating roles
841: l_vio_exist_resp_sql :='select count(distinct function_id)'
842: ||'from ('
843: ||' select function_id from amw_constraint_entries where constraint_rev_id = :1'
844: ||' MINUS '
845: ||'select distinct role_orig_system_id from ( '
846: || l_resp_existing_sql
847: ||') '
879: || l_resp_existing_sql
880: ||' UNION ALL '
881: ||' select distinct rle.orig_system_id as role_orig_system_id '
882: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
883: ||' , amw_constraint_entries cst '
884: ||' where rle.orig_system = ''FND_RESP'' '
885: ||' and cst.constraint_rev_id = :3 '
886: ||' and cst.function_id = rle.orig_system_id '
887: ||' and rle.name in ( '||l_sub_role_names||' ) '
885: ||' and cst.constraint_rev_id = :3 '
886: ||' and cst.function_id = rle.orig_system_id '
887: ||' and rle.name in ( '||l_sub_role_names||' ) '
888: ||' MINUS '
889: ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
890: ||')';
891:
892:
893:
963:
964: l_cst_new_violation_sql :=
965: ' select distinct rle.display_name '
966: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
967: ||' , amw_constraint_entries cst '
968: ||' where rle.orig_system = ''FND_RESP'' '
969: ||' and cst.constraint_rev_id = :1 '
970: ||' and cst.function_id = rle.orig_system_id '
971: ||' and rle.name in ( '||l_sub_role_names||' ) '
991: ' select distinct rle.display_name '
992: ||' from '
993: || G_AMW_USER_ROLES||' ur '
994: ||' ,'||G_AMW_user||' u '
995: ||' ,amw_constraint_entries cst '
996: ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
997: ||' ,'||G_AMW_ALL_ROLES_VL || ' rle '
998: ||' where u.user_id = :1 '
999: ||' and cst.constraint_rev_id = :2 '
1037: || l_resp_existing_sql
1038: ||' UNION ALL '
1039: ||' select distinct rle.orig_system_id as role_orig_system_id '
1040: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
1041: ||' , amw_constraint_entries cst '
1042: ||' where rle.orig_system = ''FND_RESP'' '
1043: ||' and cst.constraint_rev_id = :3 '
1044: ||' and cst.function_id = rle.orig_system_id '
1045: ||' and rle.name in ( '||l_sub_role_names||' ) '
1045: ||' and rle.name in ( '||l_sub_role_names||' ) '
1046: ||' and rle.owner_tag = (select application_short_name '
1047: ||' from fnd_application app where app.application_id = cst.application_id)'
1048: ||' MINUS '
1049: ||' select FUNCTION_ID from amw_constraint_entries where constraint_rev_id = :4'
1050: ||')';
1051:
1052:
1053: -- find the number of distinct constraint entries this user can access
1118:
1119: l_cst_new_violation_sql :=
1120: ' select distinct rle.display_name '
1121: ||' from ' || G_AMW_ALL_ROLES_VL || ' rle '
1122: ||' , amw_constraint_entries cst '
1123: ||' where rle.orig_system = ''FND_RESP'' '
1124: ||' and cst.constraint_rev_id = :1 '
1125: ||' and cst.function_id = rle.orig_system_id '
1126: ||' and rle.name in ( '||l_sub_role_names||' ) '
1146: ' select distinct rle.display_name '
1147: ||' from '
1148: || G_AMW_USER_ROLES||' ur '
1149: ||' ,'||G_AMW_user||' u '
1150: ||' ,amw_constraint_entries cst '
1151: ||' ,'||G_AMW_USER_ROLE_ASSIGNMENTS_V || ' uar '
1152: ||' ,'||G_AMW_ALL_ROLES_VL || ' rle '
1153: ||' where u.user_id = :1 '
1154: ||' and cst.constraint_rev_id = :2 '
1290: l_func_access_count NUMBER;
1291: l_group_access_count NUMBER;
1292: CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
1293: SELECT count(*)
1294: FROM amw_constraint_entries
1295: WHERE constraint_rev_id=l_constraint_rev_id;
1296:
1297: TYPE refCurTyp IS REF CURSOR;
1298: func_acess_count_c refCurTyp;
1619: -- 12.21.2004 tsho: set default NULL for p_access_given_date, p_access_given_by
1620: -- 12.21.2004 tsho: fix for performance bug 4036679
1621: -- History
1622: -- 05.24.2005 tsho: AMW.E Incompatible Sets,
1623: -- need to pass in amw_constraint_entries.group_code for each item
1624: -- ===============================================================
1625: FUNCTION PROCESS_MENU_TREE_DOWN_FOR_MN(
1626: p_menu_id in number,
1627: p_function_id in number,
1948: -- seperate the responsibility query from role
1949: -- 05.23.2006 dliao: remove the grant_flag
1950: l_role_dynamic_sql VARCHAR2(4000) :=
1951: '(SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
1952: ||' FROM AMW_CONSTRAINT_ENTRIES ce '
1953: ||' ,'||G_AMW_GRANTS ||' gra '
1954: ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
1955: ||' WHERE gra.menu_id = cmf.menu_id '
1956: ||' AND cmf.function_id = ce.function_id '
1961: ||' AND gra.GRANTEE_TYPE = ''GROUP'' '
1962: ||' AND gra.start_date <= sysdate AND (gra.end_date >= sysdate or gra.end_date is null) '
1963: ||' UNION '
1964: ||' SELECT gra.GRANTEE_KEY ROLE_NAME, gra.GRANTEE_ORIG_SYSTEM_ID as responsibility_id, gra.menu_id, ce.function_id, ce.object_type, ce.group_code '
1965: ||' FROM AMW_CONSTRAINT_ENTRIES ce '
1966: ||' ,'||G_AMW_GRANTS ||' gra '
1967: ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
1968: ||' WHERE gra.menu_id = cmf.menu_id '
1969: ||' AND cmf.function_id = ce.function_id '
1976: ||') UNION ALL '
1977: ||' SELECT to_char(null) role_name, resp.responsibility_id, resp.request_group_id menu_id, ce.function_id, ce.object_type, ce.group_code '
1978: ||' FROM '||G_AMW_RESPONSIBILITY ||' resp '
1979: ||' ,'||G_AMW_REQUEST_GROUP_UNITS ||' rgu '
1980: ||' ,AMW_CONSTRAINT_ENTRIES ce '
1981: ||' WHERE resp.request_group_id = rgu.request_group_id '
1982: ||' AND rgu.request_unit_type = ''P'' '
1983: ||' AND rgu.request_unit_id = ce.function_id AND ce.object_type = ''CP'' '
1984: ||' AND ce.CONSTRAINT_REV_ID = :3 '
2001: l_group_code_list G_VARCHAR2_CODE_TABLE;
2002: --17.12.06 psomanat : The FND creates a role for each responsibity.So the role name is added here.
2003: l_resp_dynamic_sql VARCHAR2(2000) :=
2004: ' SELECT war.name, resp.application_id, resp.responsibility_id, resp.menu_id, ce.function_id, ce.object_type, ce.group_code '
2005: ||' FROM AMW_CONSTRAINT_ENTRIES ce '
2006: ||' ,'||G_AMW_RESPONSIBILITY_VL ||' resp '
2007: ||' ,'||G_AMW_COMPILED_MENU_FUNCTIONS ||' cmf '
2008: ||' ,'||G_AMW_ALL_ROLES_VL ||' war '
2009: ||' WHERE resp.menu_id = cmf.menu_id '
2367: l_new_resp_dynamic_sql VARCHAR2(1500) :=
2368: ' SELECT distinct rv.display_name '
2369: ||' FROM fnd_responsibility_vl frv '
2370: ||' ,'||G_AMW_ALL_ROLES_VL||' rv ,'
2371: ||' amw_constraint_entries ace'
2372: ||' WHERE rv.name IN (:1)'
2373: ||' AND frv.responsibility_id = ace.function_id '
2374: ||' AND frv.APPLICATION_ID = ace.APPLICATION_ID '
2375: ||' AND ace.object_type = ''RESP'''
2856: ||' from amw_constraints_b cst '
2857: ||' where cst.type_code = ''ALL'' '
2858: ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
2859: ||' and (select count(*) '
2860: ||' from amw_constraint_entries ce '
2861: ||' where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
2862: ||' select count(distinct rcd.function_id) '
2863: ||' from amw_role_constraint_denorm rcd '
2864: ||' where rcd.constraint_rev_id = cst.constraint_rev_id '
3000: ||' from amw_constraints_b cst '
3001: ||' where cst.type_code = ''RESPALL'' '
3002: ||' and cst.start_date <= sysdate AND (cst.end_date IS NULL OR cst.end_date >= sysdate) '
3003: ||' and (select count(*) '
3004: ||' from amw_constraint_entries ce '
3005: ||' where ce.constraint_rev_id = cst.constraint_rev_id) = ( '
3006: || ' select count(distinct ur.role_orig_system_id) '
3007: ||' from '
3008: || G_AMW_USER_ROLES||' ur '
3006: || ' select count(distinct ur.role_orig_system_id) '
3007: ||' from '
3008: || G_AMW_USER_ROLES||' ur '
3009: ||' ,'||G_AMW_user||' u '
3010: ||' ,amw_constraint_entries ce '
3011: ||' where u.user_id = :21 '
3012: ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3013: ||' and u.user_name = ur.user_name '
3014: ||' and ur.role_orig_system_id = ce.function_id '
3032: || ' (select count(distinct ur.role_orig_system_id ) '
3033: ||' from '
3034: || G_AMW_USER_ROLES||' ur '
3035: ||' ,'||G_AMW_user||' u '
3036: ||' ,amw_constraint_entries ce '
3037: ||' where u.user_id = :25 '
3038: ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3039: ||' and u.user_name = ur.user_name '
3040: ||' and ur.role_orig_system_id = ce.function_id '
3059: || ' (select count(distinct ur.role_orig_system_id ) '
3060: ||' from '
3061: || G_AMW_USER_ROLES||' ur '
3062: ||' ,'||G_AMW_user||' u '
3063: ||' ,amw_constraint_entries ce '
3064: ||' where u.user_id = :29 '
3065: ||' and ce.constraint_rev_id = cst.constraint_rev_id '
3066: ||' and u.user_name = ur.user_name '
3067: ||' and ur.role_orig_system_id = ce.function_id '
3411: l_func_access_count NUMBER;
3412: l_group_access_count NUMBER;
3413: CURSOR c_constraint_entries_count (l_constraint_rev_id IN NUMBER) IS
3414: SELECT count(*)
3415: FROM amw_constraint_entries
3416: WHERE constraint_rev_id=l_constraint_rev_id;
3417:
3418:
3419: TYPE refCurTyp IS REF CURSOR;
3810: (END_DATE is null or END_DATE >= sysdate);
3811:
3812: cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
3813: select distinct FUNCTION_ID, GROUP_CODE
3814: from AMW_CONSTRAINT_ENTRIES
3815: where
3816: CONSTRAINT_REV_ID = p_constraint_rev_id and
3817: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3818:
3817: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3818:
3819: cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
3820: select distinct GROUP_CODE
3821: from AMW_CONSTRAINT_ENTRIES
3822: where
3823: CONSTRAINT_REV_ID = p_constraint_rev_id and
3824: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3825:
3989: (END_DATE is null or END_DATE >= sysdate);
3990:
3991: cursor CONSTRAINT_ENTRIES(p_constraint_rev_id NUMBER) is
3992: select distinct FUNCTION_ID, GROUP_CODE
3993: from AMW_CONSTRAINT_ENTRIES
3994: where
3995: CONSTRAINT_REV_ID = p_constraint_rev_id and
3996: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3997:
3996: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
3997:
3998: cursor CONSTRAINT_GROUP_CODES(p_constraint_rev_id NUMBER) is
3999: select distinct GROUP_CODE
4000: from AMW_CONSTRAINT_ENTRIES
4001: where
4002: CONSTRAINT_REV_ID = p_constraint_rev_id and
4003: (OBJECT_TYPE = 'FUNC' or OBJECT_TYPE is null);
4004: