[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT version
INTO l_version_string
FROM v$instance;
select 1
into l_dummy
from wf_user_roles
where user_name = p_user_name
and rownum = 1;
select object_id
into v_object_id
from fnd_objects
where obj_name=p_object_name;
select function_id
into v_function_id
from fnd_form_functions
where function_name=p_function_name;
SELECT pk1_column_name
,pk2_column_name
,pk3_column_name
,pk4_column_name
,pk5_column_name
,pk1_column_type
,pk2_column_type
,pk3_column_type
,pk4_column_type
,pk5_column_type
, database_object_name
FROM fnd_objects
WHERE obj_name=p_object_name ;
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
FROM
( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
union all
select role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g
WHERE rownum = 1
AND g.grantee_key = u2.role_name
AND g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)
AND( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND
( g.end_date IS NULL
OR g.end_date >= cp_sysdate )
AND
g.start_date <= cp_sysdate
AND
( (g.instance_type = 'GLOBAL')
AND (g.object_id = cp_object_id))
;
SELECT 'X'
FROM fnd_grants g
WHERE rownum = 1
AND(
( g.grantee_type = 'USER'
and g.grantee_key = cp_user_name)
OR (g.grantee_type = 'GROUP'
and (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id)))))
OR (g.grantee_type = 'GLOBAL'))
AND g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)
AND( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND
( g.end_date IS NULL
OR g.end_date >= cp_sysdate )
AND
g.start_date <= cp_sysdate
AND
( (g.instance_type = 'GLOBAL')
AND (g.object_id = cp_object_id));
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
FROM
( select /*+ NO_MERGE */ role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g
WHERE rownum = 1
AND g.grantee_key = u2.role_name
AND g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)
AND( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id = SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND
( g.end_date IS NULL
OR g.end_date >= cp_sysdate )
AND
g.start_date <= cp_sysdate
AND
( (g.instance_type = 'GLOBAL')
AND (g.object_id = cp_object_id))
;
'SELECT '|| '1' ||
' FROM '|| l_db_object_name || ' CKALIAS'||
' WHERE ROWNUM =1 AND ';
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
UNIQUE
instance_sets.predicate, instance_sets.instance_set_id
FROM
( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
union all
select role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND g.grantee_key = u2.role_name
AND g.object_id = cp_object_id
AND ( (cp_function_id = -1)
OR (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE );
SELECT UNIQUE
instance_sets.predicate, instance_sets.instance_set_id
FROM fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND (( g.grantee_type = 'USER'
AND g.grantee_key = cp_user_name)
OR ( g.grantee_type = 'GROUP'
AND (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id)))))
OR (g.grantee_type = 'GLOBAL'))
AND g.object_id = cp_object_id
AND ( (cp_function_id = -1)
OR (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE );
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
UNIQUE
instance_sets.predicate, instance_sets.instance_set_id
FROM
( select /*+ NO_MERGE */ role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND g.grantee_key = u2.role_name
AND g.object_id = cp_object_id
AND ( (cp_function_id = -1)
OR (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE );
'SELECT unique to_char(GNT.MENU_ID) '||
' FROM fnd_grants GNT, ' ||
l_db_object_name||' OBJTAB'||
' WHERE ';
'SELECT unique FF.FUNCTION_NAME '||
' FROM fnd_grants GNT, ' ||
l_db_object_name||' OBJTAB, '||
'fnd_compiled_menu_functions CMF, '||
'fnd_form_functions FF '||
' WHERE ';
' SELECT '|| l_pk_column_names ||
' FROM fnd_grants GNT';
' (select role_name '||
' from wf_user_roles wur '||
' where wur.user_name in '||
' ( (select '||
' '''||replace(l_user_name,'''','''''')||''' '||
' from dual) '||
' union all '||
' (select incrns.name from wf_local_roles incrns, '||
' fnd_user f '||
' where ''HZ_PARTY'' = incrns.orig_system '||
' and f.user_name = '||
' '''||replace(l_user_name,'''','''''')||''' '||
' and f.person_party_id = incrns.orig_system_id)))) '||
' OR (GNT.grantee_type = ''GLOBAL''))';
' (select role_name '||
' from wf_user_roles wur, '||
' ( select '||
' '''||replace(l_user_name,'''','''''')||''' '||
' name from dual '||
' union all '||
' (select incrns.name from wf_local_roles incrns, '||
' fnd_user f '||
' where ''HZ_PARTY'' = incrns.orig_system '||
' and f.user_name = '||
' '''||replace(l_user_name,'''','''''')||''' '||
' and f.person_party_id = incrns.orig_system_id '||
' and incrns.partition_id = 9 ) '||
' ) incr2 '||
' where wur.user_name = incr2.name '||
' union all '||
' select ''GLOBAL'' from dual))';
' (select cmf.menu_id'||
' from fnd_compiled_menu_functions cmf'||
' where cmf.function_id = '||l_function_id||')';
if (l_funclist_flag = FALSE) then /* Close off subselect parens */
l_aggregate_predicate := substrb( l_aggregate_predicate||')',
1, c_pred_buf_size);
select 1 from
dual
where exists
(
SELECT 1
FROM fnd_grants g
WHERE (g.grantee_type = 'GLOBAL')
AND g.object_id = cp_object_id
AND ( (cp_function_id = -1)
OR (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
AND g.instance_type = cp_instance_type
);
select 1
from dual
where exists
(
select 1
from fnd_grants g
where (( g.grantee_type = 'USER'
AND g.grantee_key = cp_user_name)
OR (g.grantee_type = 'GROUP'
AND (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id))))))
and g.object_id = cp_object_id
and ((cp_function_id = -1)
or (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
and ( g.ctx_secgrp_id = -1
or g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
and ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
and ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
and ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
and g.start_date <= SYSDATE
and ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
and g.instance_type = cp_instance_type
);
select 1 from
dual
where exists
(
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */ 'X'
FROM
( select /*+ NO_MERGE */ role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g
WHERE rownum = 1
AND g.grantee_key = u2.role_name
and g.object_id = cp_object_id
and ((cp_function_id = -1)
or (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)))
and ( g.ctx_secgrp_id = -1
or g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
and ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
and ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
and ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
and g.start_date <= SYSDATE
and ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
and g.instance_type = cp_instance_type
);
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM
( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
union all
select role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.grantee_key = u2.role_name
AND g.instance_type = 'SET'
AND g.object_id = cp_object_id
AND (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM
( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
union all
select role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.grantee_key = u2.role_name
AND g.object_id = cp_object_id
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND( (g.grantee_type = 'USER'
AND g.grantee_key = cp_user_name)
OR (g.grantee_type = 'GROUP'
AND (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id)))))
OR (g.grantee_type = 'GLOBAL'))
AND g.object_id = cp_object_id
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM
( select /*+ NO_MERGE */ role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.grantee_key = u2.role_name
AND g.object_id = cp_object_id
AND (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND ( (g.grantee_type = 'USER'
AND g.grantee_key = cp_user_name)
OR ( g.grantee_type = 'GROUP'
AND (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id))))))
AND g.object_id = cp_object_id
AND (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT /*+ leading(u2) use_nl(g) index(g,FND_GRANTS_N9) */
instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM
( select /*+ NO_MERGE */ role_name
from wf_user_roles wur,
(
select cp_user_name name from dual
union all
select incr1.name name
from wf_local_roles incr1, fnd_user u1
where 'HZ_PARTY' = incr1.orig_system
and u1.user_name = cp_user_name
and u1.person_party_id = incr1.orig_system_id
and incr1.partition_id = 9 /* HZ_PARTY */
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.grantee_key = u2.role_name
AND g.object_id = cp_object_id
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM fnd_grants g, fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND ( (g.grantee_type = 'USER'
AND g.grantee_key = cp_user_name)
OR (g.grantee_type = 'GROUP'
AND (g.grantee_key in
(select role_name
from wf_user_roles wur
where wur.user_name in
( (select cp_user_name from dual)
union all
(select incrns.name from wf_local_roles incrns, fnd_user f
where 'HZ_PARTY' = incrns.orig_system
and f.user_name = cp_user_name
and f.person_party_id = incrns.orig_system_id))))))
AND g.object_id = cp_object_id
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND (g.grantee_type = 'GLOBAL')
AND g.object_id = cp_object_id
AND (g.menu_id in
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id))
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
SELECT instance_sets.predicate, instance_sets.instance_set_id,
g.grant_guid
FROM fnd_grants g,
fnd_object_instance_sets instance_sets
WHERE g.instance_type = 'SET'
AND (g.grantee_type = 'GLOBAL')
AND g.object_id = cp_object_id
AND g.instance_set_id = instance_sets.instance_set_id
AND ( g.ctx_secgrp_id = -1
OR g.ctx_secgrp_id =
SYS_CONTEXT('FND','SECURITY_GROUP_ID'))
AND ( g.ctx_resp_id = -1
OR g.ctx_resp_id = SYS_CONTEXT('FND','RESP_ID'))
AND ( g.ctx_resp_appl_id = -1
OR g.ctx_resp_appl_id = SYS_CONTEXT('FND','RESP_APPL_ID'))
AND ( g.ctx_org_id = -1
OR g.ctx_org_id = SYS_CONTEXT('FND', 'ORG_ID'))
AND g.start_date <= SYSDATE
AND ( g.end_date IS NULL
OR g.end_date >= SYSDATE )
ORDER BY instance_sets.predicate,
instance_sets.instance_set_id desc;
else /* Has references to grant table so we subselect */
/* against the grants table */
l_aggregate_predicate :=
substrb( l_aggregate_predicate ||
' exists (select null'||
' from fnd_grants gnt'||
' where gnt.grant_guid in (',
1, c_pred_buf_size);
' exists (select null'||
' from fnd_grants gnt';
' (select role_name '||
' from wf_user_roles wur '||
' where wur.user_name in '||
' ( (select '||l_user_name_bind ||' from dual) '||
' union all '||
' (select incrns.name from wf_local_roles incrns, '||
' fnd_user f '||
' where ''HZ_PARTY'' = incrns.orig_system '||
' and f.user_name = '||l_user_name_bind ||
' and f.person_party_id = incrns.orig_system_id)))))';
' (select role_name '||
' from wf_user_roles wur, '||
' ( select '||l_user_name_bind ||' name from dual '||
' union all '||
' (select incrns.name from wf_local_roles incrns, '||
' fnd_user f '||
' where ''HZ_PARTY'' = incrns.orig_system '||
' and f.user_name = '||l_user_name_bind ||
' and f.person_party_id = incrns.orig_system_id '||
' and incrns.partition_id = 9 ) '||
' ) incr2 '||
' where wur.user_name = incr2.name ';
' select ''GLOBAL'' from dual ';
' (select cmf.menu_id'||
' from fnd_compiled_menu_functions cmf'||
' where cmf.function_id = :FUNCTION_ID_BIND )';
' (select cmf.menu_id'||
' from fnd_compiled_menu_functions cmf'||
' where cmf.function_id = '||l_function_id||')';
'SELECT '|| l_pk_orig_column_names ||
' FROM '|| l_db_object_name ||
' WHERE '||l_predicate||' ';