[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 1
into l_dummy
from wf_user_roles
where user_name = p_user_name
and rownum = 1;
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 party_type
FROM hz_parties
WHERE party_id=cp_party_id;
SELECT 'X'
FROM fnd_grants grants,
fnd_objects obj,
fnd_menus menus
WHERE grants.grantee_key=cp_grantee_key
AND grants.grantee_type=cp_grantee_type
AND grants.menu_id=menus.menu_id
AND menus.menu_name=cp_menu_name
AND grants.object_id = obj.object_id
AND obj.obj_name=cp_object_name
AND grants.instance_type=cp_instance_type
AND ((grants.instance_pk1_value=cp_instance_pk1_value )
OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
AND ((grants.instance_pk2_value=cp_instance_pk2_value )
OR((grants.instance_pk2_value = '*NULL*') AND (cp_instance_pk2_value IS NULL)))
AND ((grants.instance_pk3_value=cp_instance_pk3_value )
OR((grants.instance_pk3_value = '*NULL*') AND (cp_instance_pk3_value IS NULL)))
AND ((grants.instance_pk4_value=cp_instance_pk4_value )
OR((grants.instance_pk4_value = '*NULL*') AND (cp_instance_pk4_value IS NULL)))
AND ((grants.instance_pk5_value=cp_instance_pk5_value )
OR((grants.instance_pk5_value = '*NULL*') AND (cp_instance_pk5_value IS NULL)))
AND ((grants.instance_set_id=cp_instance_set_id )
OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
AND (((grants.start_date<=cp_start_date )
AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
OR ((grants.start_date >= cp_start_date )
AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
SELECT grant_guid
FROM fnd_grants
WHERE grant_guid=HEXTORAW(cp_grant_id);
SELECT g1.grant_guid
FROM fnd_grants g1, fnd_grants g2
WHERE g1.grant_guid=HEXTORAW(cp_grant_id)
AND g2.grant_guid<>HEXTORAW(cp_grant_id)
AND g1.object_id=g2.object_id
AND g1.menu_id=g2.menu_id
AND g1.instance_type=g2.instance_type
AND g1.instance_pk1_value=g2.instance_pk1_value
AND g1.grantee_type=g2.grantee_type
AND g1.grantee_key=g2.grantee_key
AND (
((g2.start_date<=cp_start_date )
AND (( g2.end_date IS NULL) OR (cp_start_date<=g2.end_date )))
OR ((g2.start_date >= cp_start_date )
AND (( cp_end_date IS NULL) OR (cp_end_date>=g2.start_date)))
);
fnd_grants_pkg.update_grant (
p_api_version => p_api_version,
p_grant_guid => HEXTORAW(p_grant_guid),
p_start_date => p_start_date,
p_end_date => p_end_date,
x_success => x_return_status
);
AMW_SECURITY_UTILS_PVT.update_dependant_grants(
p_grant_guid => HEXTORAW(p_grant_guid),
p_new_start_date => p_start_date,
p_new_end_date => p_end_date,
x_success => x_return_status);
select 1 from
dual
where exists
(
SELECT /*+ INDEX(g, FND_GRANTS_N1) */ 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 /*+ 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 */ 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 = '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;
else /* Has references to grant table so we subselect */
/* against the grants table */
-- abedajna begin
top := ' SELECT g.grant_guid ' ||
' FROM fnd_grants g, fnd_compiled_menu_functions cmf ' ||
' WHERE g.instance_type = '||''''||'SET'||'''' ||
' and g.instance_set_id = ' || d_instance_set_id ||
' AND g.menu_id = cmf.menu_id AND cmf.function_id = '|| l_function_id;
' (select role_name ' ||
' from wf_user_roles ' ||
' where user_name in ' ||
' (select incrns.name ' ||
' from wf_local_roles src, ' ||
' wf_local_roles incrns ' ||
' where src.name = '||''''||l_user_name||'''' ||
' and src.parent_orig_system = incrns.parent_orig_system ' ||
' and src.parent_orig_system_id = incrns.parent_orig_system_id) ' ||
' )) ' ||
' OR (g.grantee_type = '||''''|| 'GLOBAL'|| '''' || ')) ';
' (select role_name ' ||
' from wf_user_roles ' ||
' where user_name in ' ||
' (select incrns.name ' ||
' from wf_local_roles src ' ||
' ,wf_local_roles incrns ' ||
' where src.name = '||''''||l_user_name||'''' ||
' and src.parent_orig_system = ' ||
' incrns.parent_orig_system ' ||
' and src.parent_orig_system_id = ' ||
' incrns.parent_orig_system_id) ' ||
' )) ';
' exists (select null'||
' from fnd_grants gnt'||
' where gnt.grant_guid in ' || guid_subquery,
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 incrns.name '||
' from wf_local_roles src '||
' ,wf_local_roles incrns '||
' where src.name = ' ||
l_user_name_bind ||
' and src.parent_orig_system '||
' = incrns.parent_orig_system '||
' and src.parent_orig_system_id '||
' = incrns.parent_orig_system_id)))';
' (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||')';