[Home] [Help]
154: -- Fix for bug 4308825: This code was preventing global grantee type
155: -- grants from working in the PER case, so it's commented out.
156: -- select 1
157: -- into l_dummy
158: -- from fnd_grants
159: -- where rownum = 1
160: -- and grantee_type = 'USER'
161: -- and grantee_key = p_user_name;
162: null;
728: cp_function_id NUMBER,
729: cp_sysdate DATE,
730: cp_object_id in NUMBER
731: ) IS
732: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
733: FROM
734: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
735: union all
736: select role_name
746: and incr1.partition_id = 9 /* HZ_PARTY */
747: ) incr2
748: where wur.user_name = incr2.name
749: ) u2,
750: fnd_grants g
751: WHERE rownum = 1
752: AND g.grantee_key = u2.role_name
753: AND g.menu_id in
754: (select cmf.menu_id
785: cp_object_id in NUMBER
786: )
787: IS
788: SELECT 'X'
789: FROM fnd_grants g
790: WHERE rownum = 1
791: AND(
792: ( g.grantee_type = 'USER'
793: and g.grantee_key = cp_user_name)
833: cp_sysdate DATE,
834: cp_object_id in NUMBER
835: )
836: IS
837: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
838: FROM
839: ( select /*+ NO_MERGE */ role_name
840: from wf_user_roles wur,
841: (
849: and incr1.partition_id = 9 /* HZ_PARTY */
850: ) incr2
851: where wur.user_name = incr2.name
852: ) u2,
853: fnd_grants g
854: WHERE rownum = 1
855: AND g.grantee_key = u2.role_name
856: AND g.menu_id in
857: (select cmf.menu_id
2159: CURSOR instance_set_grants_c (cp_user_name varchar2,
2160: cp_function_id NUMBER,
2161: cp_object_id VARCHAR2)
2162: IS
2163: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2164: UNIQUE
2165: instance_sets.predicate, instance_sets.instance_set_id
2166: FROM
2167: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
2179: and incr1.partition_id = 9 /* HZ_PARTY */
2180: ) incr2
2181: where wur.user_name = incr2.name
2182: ) u2,
2183: fnd_grants g,
2184: fnd_object_instance_sets instance_sets
2185: WHERE g.instance_type = 'SET'
2186: AND g.grantee_key = u2.role_name
2187: AND g.object_id = cp_object_id
2216: cp_object_id VARCHAR2)
2217: IS
2218: SELECT UNIQUE
2219: instance_sets.predicate, instance_sets.instance_set_id
2220: FROM fnd_grants g,
2221: fnd_object_instance_sets instance_sets
2222: WHERE g.instance_type = 'SET'
2223: AND (( g.grantee_type = 'USER'
2224: AND g.grantee_key = cp_user_name)
2257: CURSOR instance_set_grants_guest_c (cp_user_name varchar2,
2258: cp_function_id NUMBER,
2259: cp_object_id VARCHAR2)
2260: IS
2261: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2262: UNIQUE
2263: instance_sets.predicate, instance_sets.instance_set_id
2264: FROM
2265: ( select /*+ NO_MERGE */ role_name
2275: and incr1.partition_id = 9 /* HZ_PARTY */
2276: ) incr2
2277: where wur.user_name = incr2.name
2278: ) u2,
2279: fnd_grants g,
2280: fnd_object_instance_sets instance_sets
2281: WHERE g.instance_type = 'SET'
2282: AND g.grantee_key = u2.role_name
2283: AND g.object_id = cp_object_id
2672:
2673: IF (l_menulist_flag = TRUE) then
2674: l_aggregate_predicate := l_aggregate_predicate ||
2675: 'SELECT unique to_char(GNT.MENU_ID) '||
2676: ' FROM fnd_grants GNT, ' ||
2677: l_db_object_name||' OBJTAB'||
2678: ' WHERE ';
2679: ELSIF(l_funclist_flag = TRUE) THEN
2680: l_aggregate_predicate := l_aggregate_predicate ||
2678: ' WHERE ';
2679: ELSIF(l_funclist_flag = TRUE) THEN
2680: l_aggregate_predicate := l_aggregate_predicate ||
2681: 'SELECT unique FF.FUNCTION_NAME '||
2682: ' FROM fnd_grants GNT, ' ||
2683: l_db_object_name||' OBJTAB, '||
2684: 'fnd_compiled_menu_functions CMF, '||
2685: 'fnd_form_functions FF '||
2686: ' WHERE ';
2688: NULL;
2689: ELSE
2690: l_aggregate_predicate := l_aggregate_predicate ||
2691: ' SELECT '|| l_pk_column_names ||
2692: ' FROM fnd_grants GNT';
2693: IF ((l_instance_flag = TRUE) OR (l_instance_set_flag = TRUE)) then
2694: l_aggregate_predicate := l_aggregate_predicate ||
2695: ', '||l_db_object_name||' OBJTAB';
2696: END IF;
3195: dual
3196: where exists
3197: (
3198: SELECT 1
3199: FROM fnd_grants g
3200: WHERE (g.grantee_type = 'GLOBAL')
3201: AND g.object_id = cp_object_id
3202: AND ( (cp_function_id = -1)
3203: OR (g.menu_id in
3236: from dual
3237: where exists
3238: (
3239: select 1
3240: from fnd_grants g
3241: where (( g.grantee_type = 'USER'
3242: AND g.grantee_key = cp_user_name)
3243: OR (g.grantee_type = 'GROUP'
3244: AND (g.grantee_key in
3283: select 1 from
3284: dual
3285: where exists
3286: (
3287: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
3288: FROM
3289: ( select /*+ NO_MERGE */ role_name
3290: from wf_user_roles wur,
3291: (
3299: and incr1.partition_id = 9 /* HZ_PARTY */
3300: ) incr2
3301: where wur.user_name = incr2.name
3302: ) u2,
3303: fnd_grants g
3304: WHERE rownum = 1
3305: AND g.grantee_key = u2.role_name
3306: and g.object_id = cp_object_id
3307: and ((cp_function_id = -1)
3338: CURSOR isg_grp_glob_fn_c (cp_user_name varchar2,
3339: cp_function_id NUMBER,
3340: cp_object_id VARCHAR2)
3341: IS
3342: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3343: instance_sets.predicate, instance_sets.instance_set_id,
3344: g.grant_guid
3345: FROM
3346: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3358: and incr1.partition_id = 9 /* HZ_PARTY */
3359: ) incr2
3360: where wur.user_name = incr2.name
3361: ) u2,
3362: fnd_grants g,
3363: fnd_object_instance_sets instance_sets
3364: WHERE g.grantee_key = u2.role_name
3365: AND g.instance_type = 'SET'
3366: AND g.object_id = cp_object_id
3387: /* Which instance sets are granted for any function? */
3388: CURSOR isg_grp_glob_nofn_c (cp_user_name varchar2,
3389: cp_object_id VARCHAR2)
3390: IS
3391: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3392: instance_sets.predicate, instance_sets.instance_set_id,
3393: g.grant_guid
3394: FROM
3395: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3407: and incr1.partition_id = 9 /* HZ_PARTY */
3408: ) incr2
3409: where wur.user_name = incr2.name
3410: ) u2,
3411: fnd_grants g,
3412: fnd_object_instance_sets instance_sets
3413: WHERE g.grantee_key = u2.role_name
3414: AND g.object_id = cp_object_id
3415: AND g.instance_set_id = instance_sets.instance_set_id
3439: cp_object_id VARCHAR2)
3440: IS
3441: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3442: g.grant_guid
3443: FROM fnd_grants g,
3444: fnd_object_instance_sets instance_sets
3445: WHERE g.instance_type = 'SET'
3446: AND( (g.grantee_type = 'USER'
3447: AND g.grantee_key = cp_user_name)
3478: CURSOR isg_grp_fn_c (cp_user_name varchar2,
3479: cp_function_id NUMBER,
3480: cp_object_id VARCHAR2)
3481: IS
3482: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3483: instance_sets.predicate, instance_sets.instance_set_id,
3484: g.grant_guid
3485: FROM
3486: ( select /*+ NO_MERGE */ role_name
3496: and incr1.partition_id = 9 /* HZ_PARTY */
3497: ) incr2
3498: where wur.user_name = incr2.name
3499: ) u2,
3500: fnd_grants g,
3501: fnd_object_instance_sets instance_sets
3502: WHERE g.grantee_key = u2.role_name
3503: AND g.object_id = cp_object_id
3504: AND (g.menu_id in
3533: cp_object_id VARCHAR2)
3534: IS
3535: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3536: g.grant_guid
3537: FROM fnd_grants g,
3538: fnd_object_instance_sets instance_sets
3539: WHERE g.instance_type = 'SET'
3540: AND ( (g.grantee_type = 'USER'
3541: AND g.grantee_key = cp_user_name)
3575: /* Which instance sets are granted for any function? */
3576: CURSOR isg_grp_nofn_c (cp_user_name varchar2,
3577: cp_object_id VARCHAR2)
3578: IS
3579: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3580: instance_sets.predicate, instance_sets.instance_set_id,
3581: g.grant_guid
3582: FROM
3583: ( select /*+ NO_MERGE */ role_name
3593: and incr1.partition_id = 9 /* HZ_PARTY */
3594: ) incr2
3595: where wur.user_name = incr2.name
3596: ) u2,
3597: fnd_grants g,
3598: fnd_object_instance_sets instance_sets
3599: WHERE g.grantee_key = u2.role_name
3600: AND g.object_id = cp_object_id
3601: AND g.instance_set_id = instance_sets.instance_set_id
3624: cp_object_id VARCHAR2)
3625: IS
3626: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3627: g.grant_guid
3628: FROM fnd_grants g, fnd_object_instance_sets instance_sets
3629: WHERE g.instance_type = 'SET'
3630: AND ( (g.grantee_type = 'USER'
3631: AND g.grantee_key = cp_user_name)
3632: OR (g.grantee_type = 'GROUP'
3664: cp_object_id VARCHAR2)
3665: IS
3666: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3667: g.grant_guid
3668: FROM fnd_grants g,
3669: fnd_object_instance_sets instance_sets
3670: WHERE g.instance_type = 'SET'
3671: AND (g.grantee_type = 'GLOBAL')
3672: AND g.object_id = cp_object_id
3695: cp_object_id VARCHAR2)
3696: IS
3697: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3698: g.grant_guid
3699: FROM fnd_grants g,
3700: fnd_object_instance_sets instance_sets
3701: WHERE g.instance_type = 'SET'
3702: AND (g.grantee_type = 'GLOBAL')
3703: AND g.object_id = cp_object_id
3939: and u1.person_party_id = incr1.orig_system_id
3940: ) incr2
3941: where wur.user_name = incr2.name
3942: ) u2,
3943: fnd_grants g
3944: where g.grantee_key = U2.role_name
3945: AND g.object_id = l_object_id
3946: AND (l_function_id = -1 or
3947: g.menu_id IN (select cmf.menu_id
4923: /* against the grants table */
4924: l_aggregate_predicate :=
4925: substrb( l_aggregate_predicate ||
4926: ' exists (select null'||
4927: ' from fnd_grants gnt'||
4928: ' where gnt.grant_guid in (',
4929: 1, c_pred_buf_size);
4930: l_need_to_close_pred := TRUE;
4931: l_refers_to_grants := TRUE;
5021: if (l_inst_instance_type) then
5022: l_instance_predicate :=
5023: l_instance_predicate ||
5024: ' exists (select null'||
5025: ' from fnd_grants gnt';
5026: if (p_with_binds = 'Y') then
5027: l_instance_predicate :=
5028: l_instance_predicate ||
5029: ' where (GNT.object_id = :OBJECT_ID_BIND'||
5970: /*
5971: ** substitute_pred-
5972: **
5973: ** an internal-only routine that substitutes in the object table alias
5974: ** and the grant table alias in the fnd_grants table.
5975: **
5976: **
5977: */
5978: FUNCTION substitute_predicate(in_pred in varchar2,
6223: and incr1.partition_id = 9 /* HZ_PARTY */
6224: ) incr2
6225: where wur.user_name = incr2.name
6226: ) u2,
6227: fnd_grants g
6228: where g.grantee_key = u2.role_name
6229: AND ( g.end_date IS NULL OR g.end_date >= sysdate )
6230: AND g.start_date <= sysdate
6231: AND ( (g.instance_type = 'GLOBAL') AND (g.object_id = -1))