[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_gsp_last_update_date DATE := NULL; -- variable added for Bug14826159
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);
l_last_update_date DATE := NULL; -- code added for Bug14826159
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;
SELECT Max(LAST_UPDATE_DATE)
INTO l_last_update_date
from ( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual union all
select role_name
from wf_user_roles wur,
(
select l_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 = l_user_name
and u1.person_party_id = incr1.orig_system_id
) incr2
where wur.user_name = incr2.name
) u2,
fnd_grants g
where g.grantee_key = U2.role_name
AND g.object_id = l_object_id
AND (l_function_id = -1 or
g.menu_id IN (select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = l_function_id));
AND ( g_gsp_last_update_date IS NOT NULL AND l_last_update_date IS NOT NULL
AND g_gsp_last_update_date >= l_last_update_date)
) then
x_predicate := g_gsp_predicate;
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||')';
g_gsp_last_update_date := l_last_update_date;
'SELECT '|| l_pk_orig_column_names ||
' FROM '|| l_db_object_name ||
' WHERE '||l_predicate||' ';
SELECT DISTINCT FU.USER_NAME, FURG.RESPONSIBILITY_ID,
FURG.RESPONSIBILITY_APPLICATION_ID, FURG.SECURITY_GROUP_ID
FROM FND_USER_RESP_GROUPS FURG, FND_USER FU
WHERE FU.USER_NAME =L_USER_NAME
AND FU.USER_ID = FURG.USER_ID
) ,
FMEV_EXT AS
(
select /*+materialize */ distinct menu_id,entry_sequence
from fnd_menu_entries_vl
where sub_menu_id is NOT NULL
and prompt is NULL
) ,
PROMPT1 AS
(
select /*+ leading(fmev) index(fmev) use_hash(base) */ base.menu_path, base.function_id, fmev.menu_id, base.prompt1 prompt
from (select sys_connect_by_path(menu_id,'/')||'/'||nvl(function_id,-1) menu_path, menu_id, entry_sequence, nvl(sub_menu_id,-1) sub_menu_id, nvl(function_id,-1) function_id, sys_connect_by_path(prompt,':->') prompt1
from (select * from fnd_menu_entries_vl where (menu_id,entry_sequence) not in (select /*+ HASH_FMEV_EXT */ menu_id, entry_sequence
from FMEV_EXT
where menu_id IS NOT NULL and entry_sequence IS NOT NULL))fmev
connect by prior SUB_MENU_ID = MENU_ID
start with MENU_ID in (select fr.menu_id from fnd_responsibility fr, fu_furg where fu_furg.responsibility_id=fr.responsibility_id and fu_furg.responsibility_application_id=fr.application_id)
) base,
fnd_menu_entries_tl fmev
where base.menu_id=fmev.menu_id
and base.entry_sequence=fmev.entry_sequence
and fmev.LANGUAGE=USERENV('LANG')
and upper(fmev.prompt) like upper(P_FUNCTION_PROMPT||'%')
and base.function_id <> -1
),
AJ AS
(
select /*+ materialize */
decode(EXCL.function_id, -1, fcmf1.function_id, EXCL.function_id) function_id,
EXCL.responsibility_id
from (select decode(rule_type, 'M', action_id, -1) MENU_ID,
decode(rule_type, 'F', action_id, -1) FUNCTION_ID,
responsibility_id
from (
select frf.rule_type,frf.action_id,frf.responsibility_id
from fnd_resp_functions frf, FU_FURG
where FRF.RESPONSIBILITY_ID = FU_FURG.RESPONSIBILITY_ID
and frf.application_id =fu_furg.responsibility_application_id)
) EXCL, fnd_compiled_menu_functions fcmf1
where EXCL.MENU_ID=fcmf1.menu_id(+)
),
NFT AS
(
select /*+ materialize */ function_id
from fnd_form_functions_vl
where type in ('WWW', 'WWK', 'SERVLET', 'JSP', 'FORM')
)
select
U.function_id,U.responsibility_name,U.responsibility_id, U.application_id, U.security_group_id, U.menu_id,U.prompt
from
(select /*+ use_hash(prompt1) swap_join_inputs(prompt1) */ fcmf.function_id,fr.responsibility_name,
fr.responsibility_id, fr.application_id, fu_furg.security_group_id, prompt1.menu_id, prompt1.prompt
from fnd_compiled_menu_functions fcmf, fnd_responsibility_vl fr,FU_FURG, PROMPT1,NFT
where fu_furg.responsibility_id=fr.responsibility_id
and fu_furg.responsibility_application_id=fr.application_id
and fcmf.menu_id=fr.menu_id
and fcmf.grant_flag='Y'
and (fcmf.function_id,fr.responsibility_id) not in
(select /*+ HASH_AJ */ function_id, responsibility_id
from AJ
where function_id IS NOT NULL and responsibility_id IS NOT NULL)
and prompt1.function_id=NFT.function_id
and prompt1.function_id = fcmf.function_id
and fr.menu_id=substr(prompt1.menu_path,2,instr(prompt1.menu_path,'/',1,2)-2)
union
select /*+ leading(GRANTS) */
FLAT.function_id, FLAT.RESPONSIBILITY_NAME,FLAT.RESPONSIBILITY_ID, FLAT.application_id, FLAT.security_group_id, prompt1.menu_id, prompt1.prompt
from
(select function_id, grants.ctx_secgrp_id, grants.ctx_resp_id, grants.ctx_resp_appl_id
from (select /*+ no_merge */ distinct g.menu_id, g.ctx_secgrp_id, g.ctx_resp_id, g.ctx_resp_appl_id from
( select /*+ NO_MERGE */ 'GLOBAL' role_name from dual
union all
select role_name
from wf_user_roles wur,
(
select L_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 = L_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 g.grantee_key = u2.role_name
AND ( g.end_date IS NULL OR g.end_date >= sysdate )
AND g.start_date <= sysdate
AND ( (g.instance_type = 'GLOBAL') AND (g.object_id = -1))
) GRANTS, fnd_compiled_menu_functions fcmf_in
where GRANTS.menu_id = fcmf_in.menu_id
) GRANTS_FN,
(select /*+ no_merge */ distinct
fcmf.function_id,fr.responsibility_name,fr.responsibility_id,fr.application_id, fu_furg.security_group_id, fr.menu_id
from fnd_compiled_menu_functions fcmf, fnd_responsibility_vl fr,FU_FURG
where fu_furg.responsibility_id=fr.responsibility_id
and fu_furg.responsibility_application_id=fr.application_id
and fcmf.menu_id=fr.menu_id
) FLAT, prompt1, NFT
WHERE GRANTS_FN.function_id = prompt1.function_id
and prompt1.function_id=NFT.function_id
and FLAT.menu_id=substr(prompt1.menu_path,2,instr(prompt1.menu_path,'/',1,2)-2)
and FLAT.function_id=prompt1.function_id
and decode(grants_fn.ctx_resp_id,
-1, flat.responsibility_id,
grants_fn.ctx_resp_id) = flat.responsibility_id
and decode(grants_fn.ctx_resp_appl_id,
-1, flat.application_id,
grants_fn.ctx_resp_appl_id) = flat.application_id
and decode(grants_fn.ctx_secgrp_id,
-1, flat.security_group_id,
grants_fn.ctx_secgrp_id) = flat.security_group_id
and (flat.function_id,flat.responsibility_id) not in
(select /*+ HASH_AJ */ function_id, responsibility_id
from AJ
where function_id IS NOT NULL and responsibility_id IS NOT NULL)
) U;