[Home] [Help]
153: -- Fix for bug 4308825: This code was preventing global grantee type
154: -- grants from working in the PER case, so it's commented out.
155: -- select 1
156: -- into l_dummy
157: -- from fnd_grants
158: -- where rownum = 1
159: -- and grantee_type = 'USER'
160: -- and grantee_key = p_user_name;
161: null;
727: cp_function_id NUMBER,
728: cp_sysdate DATE,
729: cp_object_id in NUMBER
730: ) IS
731: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
732: FROM
733: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
734: union all
735: select role_name
745: and incr1.partition_id = 9 /* HZ_PARTY */
746: ) incr2
747: where wur.user_name = incr2.name
748: ) u2,
749: fnd_grants g
750: WHERE rownum = 1
751: AND g.grantee_key = u2.role_name
752: AND g.menu_id in
753: (select cmf.menu_id
784: cp_object_id in NUMBER
785: )
786: IS
787: SELECT 'X'
788: FROM fnd_grants g
789: WHERE rownum = 1
790: AND(
791: ( g.grantee_type = 'USER'
792: and g.grantee_key = cp_user_name)
832: cp_sysdate DATE,
833: cp_object_id in NUMBER
834: )
835: IS
836: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
837: FROM
838: ( select /*+ NO_MERGE */ role_name
839: from wf_user_roles wur,
840: (
848: and incr1.partition_id = 9 /* HZ_PARTY */
849: ) incr2
850: where wur.user_name = incr2.name
851: ) u2,
852: fnd_grants g
853: WHERE rownum = 1
854: AND g.grantee_key = u2.role_name
855: AND g.menu_id in
856: (select cmf.menu_id
2158: CURSOR instance_set_grants_c (cp_user_name varchar2,
2159: cp_function_id NUMBER,
2160: cp_object_id VARCHAR2)
2161: IS
2162: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2163: UNIQUE
2164: instance_sets.predicate, instance_sets.instance_set_id
2165: FROM
2166: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
2178: and incr1.partition_id = 9 /* HZ_PARTY */
2179: ) incr2
2180: where wur.user_name = incr2.name
2181: ) u2,
2182: fnd_grants g,
2183: fnd_object_instance_sets instance_sets
2184: WHERE g.instance_type = 'SET'
2185: AND g.grantee_key = u2.role_name
2186: AND g.object_id = cp_object_id
2215: cp_object_id VARCHAR2)
2216: IS
2217: SELECT UNIQUE
2218: instance_sets.predicate, instance_sets.instance_set_id
2219: FROM fnd_grants g,
2220: fnd_object_instance_sets instance_sets
2221: WHERE g.instance_type = 'SET'
2222: AND (( g.grantee_type = 'USER'
2223: AND g.grantee_key = cp_user_name)
2256: CURSOR instance_set_grants_guest_c (cp_user_name varchar2,
2257: cp_function_id NUMBER,
2258: cp_object_id VARCHAR2)
2259: IS
2260: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
2261: UNIQUE
2262: instance_sets.predicate, instance_sets.instance_set_id
2263: FROM
2264: ( select /*+ NO_MERGE */ role_name
2274: and incr1.partition_id = 9 /* HZ_PARTY */
2275: ) incr2
2276: where wur.user_name = incr2.name
2277: ) u2,
2278: fnd_grants g,
2279: fnd_object_instance_sets instance_sets
2280: WHERE g.instance_type = 'SET'
2281: AND g.grantee_key = u2.role_name
2282: AND g.object_id = cp_object_id
2671:
2672: IF (l_menulist_flag = TRUE) then
2673: l_aggregate_predicate := l_aggregate_predicate ||
2674: 'SELECT unique to_char(GNT.MENU_ID) '||
2675: ' FROM fnd_grants GNT, ' ||
2676: l_db_object_name||' OBJTAB'||
2677: ' WHERE ';
2678: ELSIF(l_funclist_flag = TRUE) THEN
2679: l_aggregate_predicate := l_aggregate_predicate ||
2677: ' WHERE ';
2678: ELSIF(l_funclist_flag = TRUE) THEN
2679: l_aggregate_predicate := l_aggregate_predicate ||
2680: 'SELECT unique FF.FUNCTION_NAME '||
2681: ' FROM fnd_grants GNT, ' ||
2682: l_db_object_name||' OBJTAB, '||
2683: 'fnd_compiled_menu_functions CMF, '||
2684: 'fnd_form_functions FF '||
2685: ' WHERE ';
2687: NULL;
2688: ELSE
2689: l_aggregate_predicate := l_aggregate_predicate ||
2690: ' SELECT '|| l_pk_column_names ||
2691: ' FROM fnd_grants GNT';
2692: IF ((l_instance_flag = TRUE) OR (l_instance_set_flag = TRUE)) then
2693: l_aggregate_predicate := l_aggregate_predicate ||
2694: ', '||l_db_object_name||' OBJTAB';
2695: END IF;
3193: dual
3194: where exists
3195: (
3196: SELECT 1
3197: FROM fnd_grants g
3198: WHERE (g.grantee_type = 'GLOBAL')
3199: AND g.object_id = cp_object_id
3200: AND ( (cp_function_id = -1)
3201: OR (g.menu_id in
3234: from dual
3235: where exists
3236: (
3237: select 1
3238: from fnd_grants g
3239: where (( g.grantee_type = 'USER'
3240: AND g.grantee_key = cp_user_name)
3241: OR (g.grantee_type = 'GROUP'
3242: AND (g.grantee_key in
3281: select 1 from
3282: dual
3283: where exists
3284: (
3285: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
3286: FROM
3287: ( select /*+ NO_MERGE */ role_name
3288: from wf_user_roles wur,
3289: (
3297: and incr1.partition_id = 9 /* HZ_PARTY */
3298: ) incr2
3299: where wur.user_name = incr2.name
3300: ) u2,
3301: fnd_grants g
3302: WHERE rownum = 1
3303: AND g.grantee_key = u2.role_name
3304: and g.object_id = cp_object_id
3305: and ((cp_function_id = -1)
3336: CURSOR isg_grp_glob_fn_c (cp_user_name varchar2,
3337: cp_function_id NUMBER,
3338: cp_object_id VARCHAR2)
3339: IS
3340: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3341: instance_sets.predicate, instance_sets.instance_set_id,
3342: g.grant_guid
3343: FROM
3344: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3356: and incr1.partition_id = 9 /* HZ_PARTY */
3357: ) incr2
3358: where wur.user_name = incr2.name
3359: ) u2,
3360: fnd_grants g,
3361: fnd_object_instance_sets instance_sets
3362: WHERE g.grantee_key = u2.role_name
3363: AND g.instance_type = 'SET'
3364: AND g.object_id = cp_object_id
3385: /* Which instance sets are granted for any function? */
3386: CURSOR isg_grp_glob_nofn_c (cp_user_name varchar2,
3387: cp_object_id VARCHAR2)
3388: IS
3389: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3390: instance_sets.predicate, instance_sets.instance_set_id,
3391: g.grant_guid
3392: FROM
3393: ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
3405: and incr1.partition_id = 9 /* HZ_PARTY */
3406: ) incr2
3407: where wur.user_name = incr2.name
3408: ) u2,
3409: fnd_grants g,
3410: fnd_object_instance_sets instance_sets
3411: WHERE g.grantee_key = u2.role_name
3412: AND g.object_id = cp_object_id
3413: AND g.instance_set_id = instance_sets.instance_set_id
3437: cp_object_id VARCHAR2)
3438: IS
3439: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3440: g.grant_guid
3441: FROM fnd_grants g,
3442: fnd_object_instance_sets instance_sets
3443: WHERE g.instance_type = 'SET'
3444: AND( (g.grantee_type = 'USER'
3445: AND g.grantee_key = cp_user_name)
3476: CURSOR isg_grp_fn_c (cp_user_name varchar2,
3477: cp_function_id NUMBER,
3478: cp_object_id VARCHAR2)
3479: IS
3480: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3481: instance_sets.predicate, instance_sets.instance_set_id,
3482: g.grant_guid
3483: FROM
3484: ( select /*+ NO_MERGE */ role_name
3494: and incr1.partition_id = 9 /* HZ_PARTY */
3495: ) incr2
3496: where wur.user_name = incr2.name
3497: ) u2,
3498: fnd_grants g,
3499: fnd_object_instance_sets instance_sets
3500: WHERE g.grantee_key = u2.role_name
3501: AND g.object_id = cp_object_id
3502: AND (g.menu_id in
3531: cp_object_id VARCHAR2)
3532: IS
3533: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3534: g.grant_guid
3535: FROM fnd_grants g,
3536: fnd_object_instance_sets instance_sets
3537: WHERE g.instance_type = 'SET'
3538: AND ( (g.grantee_type = 'USER'
3539: AND g.grantee_key = cp_user_name)
3573: /* Which instance sets are granted for any function? */
3574: CURSOR isg_grp_nofn_c (cp_user_name varchar2,
3575: cp_object_id VARCHAR2)
3576: IS
3577: SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
3578: instance_sets.predicate, instance_sets.instance_set_id,
3579: g.grant_guid
3580: FROM
3581: ( select /*+ NO_MERGE */ role_name
3591: and incr1.partition_id = 9 /* HZ_PARTY */
3592: ) incr2
3593: where wur.user_name = incr2.name
3594: ) u2,
3595: fnd_grants g,
3596: fnd_object_instance_sets instance_sets
3597: WHERE g.grantee_key = u2.role_name
3598: AND g.object_id = cp_object_id
3599: AND g.instance_set_id = instance_sets.instance_set_id
3622: cp_object_id VARCHAR2)
3623: IS
3624: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3625: g.grant_guid
3626: FROM fnd_grants g, fnd_object_instance_sets instance_sets
3627: WHERE g.instance_type = 'SET'
3628: AND ( (g.grantee_type = 'USER'
3629: AND g.grantee_key = cp_user_name)
3630: OR (g.grantee_type = 'GROUP'
3662: cp_object_id VARCHAR2)
3663: IS
3664: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3665: g.grant_guid
3666: FROM fnd_grants g,
3667: fnd_object_instance_sets instance_sets
3668: WHERE g.instance_type = 'SET'
3669: AND (g.grantee_type = 'GLOBAL')
3670: AND g.object_id = cp_object_id
3693: cp_object_id VARCHAR2)
3694: IS
3695: SELECT instance_sets.predicate, instance_sets.instance_set_id,
3696: g.grant_guid
3697: FROM fnd_grants g,
3698: fnd_object_instance_sets instance_sets
3699: WHERE g.instance_type = 'SET'
3700: AND (g.grantee_type = 'GLOBAL')
3701: AND g.object_id = cp_object_id
4883: /* against the grants table */
4884: l_aggregate_predicate :=
4885: substrb( l_aggregate_predicate ||
4886: ' exists (select null'||
4887: ' from fnd_grants gnt'||
4888: ' where gnt.grant_guid in (',
4889: 1, c_pred_buf_size);
4890: l_need_to_close_pred := TRUE;
4891: l_refers_to_grants := TRUE;
4981: if (l_inst_instance_type) then
4982: l_instance_predicate :=
4983: l_instance_predicate ||
4984: ' exists (select null'||
4985: ' from fnd_grants gnt';
4986: if (p_with_binds = 'Y') then
4987: l_instance_predicate :=
4988: l_instance_predicate ||
4989: ' where (GNT.object_id = :OBJECT_ID_BIND'||
5929: /*
5930: ** substitute_pred-
5931: **
5932: ** an internal-only routine that substitutes in the object table alias
5933: ** and the grant table alias in the fnd_grants table.
5934: **
5935: **
5936: */
5937: FUNCTION substitute_predicate(in_pred in varchar2,