[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_id,
obj_name,
database_object_name,
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
FROM fnd_objects
WHERE object_id = p_object_id;
l_statement := 'SELECT rowid, object_grant_group_id, grant_group_id, ' ||
' inherited_flag, inherited_from, inheritance_type ' ||
' FROM ibc_object_grant_groups ' ||
' WHERE object_id = :p_instance_object_id ' ||
' AND instance_pk1_value = :p_instance_pk1_value ';
SELECT object_id
FROM fnd_objects
WHERE obj_name = p_object_name;
SELECT permissions_lookup_type
FROM ibc_object_permissions
WHERE object_id = p_object_id;
SELECT object_id,
instance_pk1_value,
instance_pk2_value,
instance_pk3_value,
instance_pk4_value,
instance_pk5_value
FROM ibc_object_grant_groups
WHERE object_grant_group_id = p_object_grant_group_id;
SELECT *
FROM ibc_object_grant_groups
WHERE inherited_from = p_ogg_id;
DELETE FROM ibc_grants WHERE grant_group_id = l_grant_group_id;
DELETE FROM ibc_grant_groups WHERE grant_group_id = l_grant_group_id;
DELETE FROM ibc_object_grant_groups
WHERE ROWID = l_object_grant_group_rowid;
SELECT *
FROM ibc_object_grant_groups
WHERE inherited_from = p_ogg_id;
SELECT ibc_grant_groups_s1.nextval
INTO l_grant_group_id
FROM dual;
IBC_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_grant_group_id => l_grant_group_id
,p_object_version_number => 1
);
SELECT ibc_object_grant_groups_s1.nextval
INTO l_object_grant_group_id
FROM dual;
IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_object_grant_group_id => l_object_grant_group_id
,p_object_version_number => 1
,p_grant_group_id => l_grant_group_id
,p_object_id => p_instance_object_id
,p_inherited_flag => 'N'
,p_inherited_from => NULL
,p_instance_pk1_value => p_instance_pk1_value
,p_instance_pk2_value => p_instance_pk2_value
,p_instance_pk3_value => p_instance_pk3_value
,p_instance_pk4_value => p_instance_pk4_value
,p_instance_pk5_value => p_instance_pk5_value
,p_inheritance_type => NVL(p_inheritance_type, 'FOLDER')
);
SELECT ibc_object_grant_groups_s1.nextval
INTO l_object_grant_group_id
FROM dual;
IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_object_grant_group_id => l_object_grant_group_id
,p_object_version_number => 1
,p_grant_group_id => l_c_grant_group_id
,p_object_id => p_instance_object_id
,p_inherited_flag => 'Y'
,p_inherited_from => l_c_object_grant_group_id
,p_instance_pk1_value => p_instance_pk1_value
,p_instance_pk2_value => p_instance_pk2_value
,p_instance_pk3_value => p_instance_pk3_value
,p_instance_pk4_value => p_instance_pk4_value
,p_instance_pk5_value => p_instance_pk5_value
,p_inheritance_type => NVL(p_inheritance_type, l_default_inheritance_type)
);
Ibc_Object_Grant_Groups_Pkg.UPDATE_ROW (
P_OBJECT_GRANT_GROUP_ID => l_object_grant_group_id,
p_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM,
P_GRANT_GROUP_ID => l_grant_group_id,
P_OBJECT_ID => p_instance_object_id,
P_INHERITED_FLAG => l_inherited_flag,
P_INHERITED_FROM => l_inherited_from,
P_INSTANCE_PK1_VALUE => p_instance_pk1_value,
P_INSTANCE_PK2_VALUE => p_instance_pk2_value,
P_INSTANCE_PK3_VALUE => p_instance_pk3_value,
P_INSTANCE_PK4_VALUE => p_instance_pk4_value,
P_INSTANCE_PK5_VALUE => p_instance_pk5_value,
P_INHERITANCE_TYPE => p_inheritance_type
);
DELETE FROM ibc_grants
WHERE grant_group_id = l_grant_group_id;
DELETE FROM ibc_grant_groups
WHERE grant_group_id = l_grant_group_id;
UPDATE ibc_object_grant_groups
SET inherited_flag = 'Y',
grant_group_id = l_c_grant_group_id
WHERE object_grant_group_id
IN ( SELECT object_grant_group_id
FROM ibc_object_grant_groups
CONNECT BY PRIOR object_grant_group_id = inherited_from
START WITH inherited_from = l_object_grant_group_id);
* in the UI the user selects "Inherit"
*
* @param p_instance_object_id ID for object definition id found in FND_OBJECTS
* for this particular instance
* @param p_instance_pk1_value value 1 for instance's primary key
* @param p_instance_pk2_value value 2 for instance's primary key
* @param p_instance_pk3_value value 3 for instance's primary key
* @param p_instance_pk4_value value 4 for instance's primary key
* @param p_instance_pk5_value value 5 for instance's primary key
* @param p_commit Indicates whether to commit or not at the end
* of procedure
* @param p_api_version standard parm - API Version
* @param p_init_msg_list standard parm - Initialize message list
* @param x_return_status standard parm - Return Status
* @param x_msg_count standard parm - Message Count
* @param x_msg_data standard parm - Message Data
*
* @rep:displayname reset_permissions
*
*/
PROCEDURE reset_permissions(
p_instance_object_id IN NUMBER
,p_instance_pk1_value IN VARCHAR2
,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
,p_commit IN VARCHAR2 DEFAULT FND_API.g_false
,p_api_version IN NUMBER DEFAULT 1.0
,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) AS
l_rowid ROWID;
DELETE FROM ibc_grants
WHERE grant_group_id = l_grant_group_id;
DELETE FROM ibc_grant_groups
WHERE grant_group_id = l_grant_group_id;
UPDATE ibc_object_grant_groups
SET inherited_flag = 'Y',
grant_group_id = (SELECT grant_group_id
FROM ibc_object_grant_groups
WHERE object_grant_group_id = l_inherited_from)
WHERE object_grant_group_id
IN ( SELECT object_grant_group_id
FROM ibc_object_grant_groups
WHERE grant_group_id = l_grant_group_id);
SELECT object_id,
instance_pk1_value,
instance_pk2_value,
instance_pk3_value,
instance_pk4_value,
instance_pk5_value
FROM ibc_object_grant_groups
WHERE object_grant_group_id = p_object_grant_group_id;
CURSOR c_ogg_tree_update (p_object_grant_group_id NUMBER,
p_grant_group_id NUMBER) IS
SELECT ogg.*
FROM ibc_object_grant_groups ogg
WHERE grant_group_id = p_grant_group_id
AND inherited_flag = 'Y'
CONNECT BY PRIOR object_grant_group_id = inherited_from
START WITH inherited_from = p_object_grant_group_id;
SELECT ibc_grant_groups_s1.nextval
INTO l_grant_group_id
FROM dual;
IBC_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_grant_group_id => l_grant_group_id
,p_object_version_number => 1
);
FOR r_data IN (SELECT object_grant_group_id,
object_version_number,
object_id,
inherited_from,
instance_pk1_value,
instance_pk2_value,
instance_pk3_value,
instance_pk4_value,
instance_pk5_value,
inheritance_type
FROM ibc_object_grant_groups
WHERE ROWID = l_object_grant_group_rowid)
LOOP
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_data.object_grant_group_id
,p_object_version_number => r_data.object_version_number
,p_grant_group_id => l_grant_group_id
,p_object_id => r_data.object_id
,p_inherited_flag => 'N'
,p_inherited_from => r_data.inherited_from
,p_instance_pk1_value => r_data.instance_pk1_value
,p_instance_pk2_value => r_data.instance_pk2_value
,p_instance_pk3_value => r_data.instance_pk3_value
,p_instance_pk4_value => r_data.instance_pk4_value
,p_instance_pk5_value => r_data.instance_pk5_value
,p_inheritance_type => r_data.inheritance_type
);
FOR r_tree_ogg IN c_ogg_tree_update (l_object_grant_group_id,
l_old_grant_group_id)
LOOP
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_tree_ogg.object_grant_group_id
,p_object_version_number => r_tree_ogg.object_version_number
,p_grant_group_id => l_grant_group_id
,p_object_id => r_tree_ogg.object_id
,p_inherited_flag => r_tree_ogg.inherited_flag
,p_inherited_from => r_tree_ogg.inherited_from
,p_instance_pk1_value => r_tree_ogg.instance_pk1_value
,p_instance_pk2_value => r_tree_ogg.instance_pk2_value
,p_instance_pk3_value => r_tree_ogg.instance_pk3_value
,p_instance_pk4_value => r_tree_ogg.instance_pk4_value
,p_instance_pk5_value => r_tree_ogg.instance_pk5_value
,p_inheritance_type => r_tree_ogg.inheritance_type
);
FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
object_id, permission_code, grantee_user_id,
grantee_resource_id, grantee_resource_type,
l_grant_group_id grant_group_id,
action, grant_level + 1 grant_level, cascade_flag
FROM ibc_grants
WHERE grant_group_id = l_old_grant_group_id
AND cascade_flag = IBC_UTILITIES_PVT.g_true)
LOOP
IBC_GRANTS_PKG.insert_row(
PX_ROWID => l_rowid
,P_GRANT_ID => r_data.grant_id
,P_PERMISSION_CODE => r_data.permission_code
,P_GRANTEE_USER_ID => r_data.grantee_user_id
,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
,P_GRANT_GROUP_ID => r_data.grant_group_id
,P_ACTION => r_data.action
,P_GRANT_LEVEL => r_data.grant_level
,P_CASCADE_FLAG => r_data.cascade_flag
,P_OBJECT_VERSION_NUMBER => 1
,P_OBJECT_ID => r_data.object_id
);
SELECT LEVEL - 1 grant_level, ogg.*
FROM ibc_object_grant_groups ogg
CONNECT BY PRIOR object_grant_group_id = inherited_from
AND p_cascade_flag = IBC_UTILITIES_PVT.g_true
START WITH object_grant_group_id = p_object_grant_group_id;
CURSOR c_ogg_tree_update (p_object_grant_group_id NUMBER,
p_grant_group_id NUMBER) IS
SELECT ogg.*
FROM ibc_object_grant_groups ogg
WHERE grant_group_id = p_grant_group_id
AND inherited_flag = 'Y'
CONNECT BY PRIOR object_grant_group_id = inherited_from
AND p_cascade_flag = IBC_UTILITIES_PVT.g_true
START WITH inherited_from = p_object_grant_group_id;
SELECT 'X'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_permission_code
AND enabled_flag = 'Y'
AND language = USERENV('lang')
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE);
SELECT ibc_grant_groups_s1.nextval
INTO l_grant_group_id
FROM dual;
IBC_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_grant_group_id => l_grant_group_id
,p_object_version_number => 1
);
SELECT ibc_object_grant_groups_s1.nextval
INTO l_object_grant_group_id
FROM dual;
FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
object_id, permission_code, grantee_user_id,
grantee_resource_id, grantee_resource_type,
l_grant_group_id grant_group_id,
action, grant_level + 1 grant_level, cascade_flag
FROM ibc_grants
WHERE grant_group_id = l_c_grant_group_id)
LOOP
IBC_GRANTS_PKG.insert_row(
PX_ROWID => l_rowid
,P_GRANT_ID => r_data.grant_id
,P_PERMISSION_CODE => r_data.permission_code
,P_GRANTEE_USER_ID => r_data.grantee_user_id
,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
,P_GRANT_GROUP_ID => r_data.grant_group_id
,P_ACTION => r_data.action
,P_GRANT_LEVEL => r_data.grant_level
,P_CASCADE_FLAG => r_data.cascade_flag
,P_OBJECT_VERSION_NUMBER => 1
,P_OBJECT_ID => r_data.object_id
);
IBC_OBJECT_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_object_grant_group_id => l_object_grant_group_id
,p_object_version_number => 1
,p_grant_group_id => l_grant_group_id
,p_object_id => p_instance_object_id
,p_inherited_flag => 'N'
,p_inherited_from => l_c_object_grant_group_id
,p_instance_pk1_value => p_instance_pk1_value
,p_instance_pk2_value => p_instance_pk2_value
,p_instance_pk3_value => p_instance_pk3_value
,p_instance_pk4_value => p_instance_pk4_value
,p_instance_pk5_value => p_instance_pk5_value
,p_inheritance_type => l_c_inheritance_type
);
FOR r_data IN (SELECT object_grant_group_id,
object_version_number,
object_id,
inherited_from,
instance_pk1_value,
instance_pk2_value,
instance_pk3_value,
instance_pk4_value,
instance_pk5_value,
inheritance_type
FROM ibc_object_grant_groups
WHERE ROWID = l_object_grant_group_rowid)
LOOP
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_data.object_grant_group_id
,p_object_version_number => r_data.object_version_number
,p_grant_group_id => l_grant_group_id
,p_object_id => r_data.object_id
,p_inherited_flag => 'N'
,p_inherited_from => r_data.inherited_from
,p_instance_pk1_value => r_data.instance_pk1_value
,p_instance_pk2_value => r_data.instance_pk2_value
,p_instance_pk3_value => r_data.instance_pk3_value
,p_instance_pk4_value => r_data.instance_pk4_value
,p_instance_pk5_value => r_data.instance_pk5_value
,p_inheritance_type => r_data.inheritance_type
);
FOR r_ogg IN c_ogg_tree_update (l_object_grant_group_id,
l_old_grant_group_id)
LOOP
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_ogg.object_grant_group_id
,p_object_version_number => r_ogg.object_version_number
,p_grant_group_id => l_grant_group_id
,p_object_id => r_ogg.object_id
,p_inherited_flag => r_ogg.inherited_flag
,p_inherited_from => r_ogg.inherited_from
,p_instance_pk1_value => r_ogg.instance_pk1_value
,p_instance_pk2_value => r_ogg.instance_pk2_value
,p_instance_pk3_value => r_ogg.instance_pk3_value
,p_instance_pk4_value => r_ogg.instance_pk4_value
,p_instance_pk5_value => r_ogg.instance_pk5_value
,p_inheritance_type => r_ogg.inheritance_type
);
FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
object_id, permission_code, grantee_user_id,
grantee_resource_id, grantee_resource_type,
l_grant_group_id grant_group_id,
action, grant_level + 1 grant_level, cascade_flag
FROM ibc_grants
WHERE grant_group_id = l_old_grant_group_id
AND cascade_flag = IBC_UTILITIES_PVT.g_true)
LOOP
IBC_GRANTS_PKG.insert_row(
PX_ROWID => l_rowid
,P_GRANT_ID => r_data.grant_id
,P_PERMISSION_CODE => r_data.permission_code
,P_GRANTEE_USER_ID => r_data.grantee_user_id
,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
,P_GRANT_GROUP_ID => r_data.grant_group_id
,P_ACTION => r_data.action
,P_GRANT_LEVEL => r_data.grant_level
,P_CASCADE_FLAG => r_data.cascade_flag
,P_OBJECT_VERSION_NUMBER => 1
,P_OBJECT_ID => r_data.object_id
);
l_statement := ' SELECT ''X'' ' ||
' FROM ibc_grants ' ||
' WHERE object_id = :p_permission_object_id ' ||
' AND permission_code = :p_permission_code ' ||
' AND grant_group_id = :p_grant_group_id ' ||
' AND action = :p_action ' ||
' AND grant_level = 0 ';
SELECT ibc_grants_s1.nextval
INTO l_grant_id
FROM DUAL;
IBC_GRANTS_PKG.insert_row(
PX_ROWID => l_rowid
,P_GRANT_ID => l_grant_id
,P_PERMISSION_CODE => p_permission_code
,P_GRANTEE_USER_ID => p_grantee_user_id
,P_GRANTEE_RESOURCE_ID => p_grantee_resource_id
,P_GRANTEE_RESOURCE_TYPE => p_grantee_resource_type
,P_GRANT_GROUP_ID => r_object_grant_group.grant_group_id
,P_ACTION => p_action
,P_GRANT_LEVEL => r_object_grant_group.grant_level
,P_CASCADE_FLAG => p_cascade_flag
,P_OBJECT_VERSION_NUMBER => 1
,P_OBJECT_ID => p_permission_object_id
);
SELECT LEVEL - 1 grant_level, ogg.*
FROM ibc_object_grant_groups ogg
CONNECT BY PRIOR object_grant_group_id = inherited_from
START WITH object_grant_group_id = p_object_grant_group_id
ORDER BY 1 asc;
SELECT *
FROM ibc_object_grant_groups
WHERE object_grant_group_id = p_object_grant_group_id;
SELECT 'X'
FROM ibc_object_grant_groups
WHERE grant_group_id = p_grant_group_id;
SELECT *
FROM ibc_grants
WHERE grant_group_id = p_grant_group_id
AND (grant_level = 0 OR p_inheritance_type <> 'FULL');
l_statement := ' SELECT grant_id ' ||
' FROM ibc_grants ' ||
' WHERE object_id = :p_permission_object_id ' ||
' AND permission_code = :p_permission_code ' ||
' AND action = :p_action ';
SELECT ibc_grant_groups_s1.nextval
INTO l_new_grant_group_id
FROM dual;
IBC_GRANT_GROUPS_PKG.insert_row(
px_rowid => l_rowid
,p_grant_group_id => l_new_grant_group_id
,p_object_version_number => 1
);
FOR r_data IN (SELECT ibc_grants_s1.nextval grant_id,
object_id, permission_code, grantee_user_id,
grantee_resource_id, grantee_resource_type,
l_new_grant_group_id grant_group_id,
action, grant_level + 1 grant_level, cascade_flag
FROM ibc_grants
WHERE grant_group_id = l_grant_group_id)
LOOP
IBC_GRANTS_PKG.insert_row(
PX_ROWID => l_rowid
,P_GRANT_ID => r_data.grant_id
,P_PERMISSION_CODE => r_data.permission_code
,P_GRANTEE_USER_ID => r_data.grantee_user_id
,P_GRANTEE_RESOURCE_ID => r_data.grantee_resource_id
,P_GRANTEE_RESOURCE_TYPE => r_data.grantee_resource_type
,P_GRANT_GROUP_ID => r_data.grant_group_id
,P_ACTION => r_data.action
,P_GRANT_LEVEL => r_data.grant_level
,P_CASCADE_FLAG => r_data.cascade_flag
,P_OBJECT_VERSION_NUMBER => 1
,P_OBJECT_ID => r_data.object_id
);
FOR r_data IN (SELECT object_grant_group_id,
object_version_number,
object_id,
inherited_from,
instance_pk1_value,
instance_pk2_value,
instance_pk3_value,
instance_pk4_value,
instance_pk5_value,
inheritance_type
FROM ibc_object_grant_groups
WHERE ROWID = l_object_grant_group_rowid)
LOOP
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_data.object_grant_group_id
,p_object_version_number => r_data.object_version_number
,p_grant_group_id => l_new_grant_group_id
,p_object_id => r_data.object_id
,p_inherited_flag => 'N'
,p_inherited_from => r_data.inherited_from
,p_instance_pk1_value => r_data.instance_pk1_value
,p_instance_pk2_value => r_data.instance_pk2_value
,p_instance_pk3_value => r_data.instance_pk3_value
,p_instance_pk4_value => r_data.instance_pk4_value
,p_instance_pk5_value => r_data.instance_pk5_value
,p_inheritance_type => r_data.inheritance_type
);
IBC_GRANTS_PKG.delete_row(l_grant_id);
FOR r_data IN (SELECT grant_id
FROM ibc_grants
WHERE grant_group_id = r_object_grant_group.grant_group_id)
LOOP
IBC_GRANTS_PKG.delete_row(r_data.grant_id);
IBC_OBJECT_GRANT_GROUPS_PKG.delete_row(r_object_grant_group.object_grant_group_id);
-- Update grant object's grant group to inherit from container
SELECT grant_group_id
INTO l_grant_group_id
FROM ibc_object_grant_groups
WHERE object_grant_group_id = r_object_grant_group.inherited_from;
IBC_OBJECT_GRANT_GROUPS_PKG.update_row(
p_object_grant_group_id => r_object_grant_group.object_grant_group_id
,p_object_version_number => r_object_grant_group.object_version_number
,p_grant_group_id => l_grant_group_id
,p_object_id => r_object_grant_group.object_id
,p_inherited_flag => 'Y'
,p_inherited_from => r_object_grant_group.inherited_from
,p_instance_pk1_value => r_object_grant_group.instance_pk1_value
,p_instance_pk2_value => r_object_grant_group.instance_pk2_value
,p_instance_pk3_value => r_object_grant_group.instance_pk3_value
,p_instance_pk4_value => r_object_grant_group.instance_pk4_value
,p_instance_pk5_value => r_object_grant_group.instance_pk5_value
,p_inheritance_type => r_object_grant_group.inheritance_type
);
IBC_GRANT_GROUPS_PKG.delete_row(r_object_grant_group.grant_group_id);
SELECT action
FROM ibc_grants
WHERE object_id = p_instance_object_id
AND permission_code = p_permission_code
AND grant_group_id = p_grant_group_id
AND (p_inherited_flag = 'N'
OR cascade_flag = IBC_UTILITIES_PVT.g_true
)
AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
(IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
grantee_resource_id, grantee_resource_type,
p_current_user_id) = 'TRUE')
)
ORDER BY DECODE(p_inheritance_type, 'FOLDER', 0, 'HIDDEN-FOLDER', 0, grant_level) asc,
DECODE(grantee_resource_type,
'RESPONSIBILITY', 2,
'RS_GROUP', 2,
'GROUP', 2,
DECODE(grantee_user_id, NULL, 3, 1)),
action;
SELECT permission_code
FROM ibc_grants a0
WHERE object_id = p_instance_object_id
AND grant_group_id = p_grant_group_id
AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
(IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
grantee_resource_id, grantee_resource_type,
p_current_user_id) = 'TRUE')
)
AND action = 'ALLOW'
AND (p_inherited_flag = 'N'
OR cascade_flag = IBC_UTILITIES_PVT.g_true
)
AND NOT EXISTS (
SELECT 'x'
FROM ibc_grants a1
WHERE a1.grant_group_id = a0.grant_group_id
AND a1.object_id = a0.object_id
AND a1.permission_code = a0.permission_code
AND a1.action = 'RESTRICT'
-- Precedence User:1 Resp/Group:2 Global:3
-- Lowest takes precedence
AND DECODE(a1.grantee_resource_type,
'RESPONSIBILITY', 2,
'RS_GROUP', 2,
'GROUP', 2,
DECODE(a1.grantee_user_id, NULL, 3, 1))
<
DECODE(a0.grantee_resource_type,
'RESPONSIBILITY', 2,
'RS_GROUP', 2,
'GROUP', 2,
DECODE(a0.grantee_user_id, NULL, 3, 1))
AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
(IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
grantee_resource_id, grantee_resource_type,
p_current_user_id) = 'TRUE')
)
)
ORDER BY grant_level asc, grantee_resource_id asc;
FOR r_permission IN (SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = l_perms_lookup_type
AND enabled_flag = 'Y'
AND language = USERENV('lang')
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE))
LOOP
IF l_result IS NULL OR
INSTR(l_result, '[' || r_permission.lookup_code || ']') = 0
THEN
l_result := l_result || '[' || r_permission.lookup_code || ']';
SELECT permission_code
FROM ibc_grants a0
WHERE object_id = p_instance_object_id
AND grant_group_id = p_grant_group_id
AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
(IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
grantee_resource_id, grantee_resource_type,
p_current_user_id) = 'TRUE')
)
AND action = 'ALLOW'
AND (p_inherited_flag = 'N'
OR cascade_flag = IBC_UTILITIES_PVT.g_true
)
AND NOT EXISTS (
SELECT 'x'
FROM ibc_grants a1
WHERE a1.grant_group_id = a0.grant_group_id
AND a1.object_id = a0.object_id
AND a1.permission_code = a0.permission_code
AND a1.action = 'RESTRICT'
-- Precedence User:1 Resp/Group:2 Global:3
-- Lowest takes precedence
AND DECODE(a1.grantee_resource_type,
'RESPONSIBILITY', 2,
'RS_GROUP', 2,
'GROUP', 2,
DECODE(a1.grantee_user_id, NULL, 3, 1))
<
DECODE(a0.grantee_resource_type,
'RESPONSIBILITY', 2,
'RS_GROUP', 2,
'GROUP', 2,
DECODE(a0.grantee_user_id, NULL, 3, 1))
AND ((grantee_user_id IS NULL AND grantee_resource_id IS NULL) OR
(IBC_UTILITIES_PVT.Check_Current_User(grantee_user_id,
grantee_resource_id, grantee_resource_type,
p_current_user_id) = 'TRUE')
)
)
ORDER BY grant_level asc, grantee_resource_id asc;
FOR r_permission IN (SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = l_perms_lookup_type
AND enabled_flag = 'Y'
AND language = USERENV('lang')
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE))
LOOP
l_count := l_count + 1;
SELECT resource_type
FROM jtf_rs_all_resources_vl
WHERE resource_id = p_resource_id;
SELECT group_id group_id, resource_id group_resource_id, 'INDIVIDUAL' resource_type
FROM jtf_rs_group_members
WHERE group_id = p_resource_id
AND delete_flag = 'N'
UNION
SELECT rgm.group_id group_id, rgr.group_id group_resource_id, 'GROUP' resource_type
FROM jtf_rs_group_members rgm, jtf_rs_grp_relations rgr
WHERE rgm.group_id = rgr.related_group_id
AND rgm.group_id = p_resource_id
AND rgm.delete_flag = 'N'
AND rgr.delete_flag = 'N';
SELECT user_id
FROM fnd_user_resp_groups
WHERE responsibility_id = p_resp_id;
SELECT user_id
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id;
IS SELECT user_name
FROM fnd_user
WHERE user_id = p_user_id;
IS SELECT action,
permission_code,
grant_level,
grant_group_id,
grantee_user_id,
grantee_resource_id,
grantee_resource_type
FROM ibc_grants a0
WHERE object_id = p_object_id
AND grant_group_id = p_grant_group_id
AND permission_code = p_permission_code
AND action = 'ALLOW'
ORDER BY grant_level;
IS SELECT user_name
FROM fnd_user
WHERE user_id = p_user_id;
IS SELECT action,
permission_code,
grant_level,
grant_group_id,
grantee_user_id,
grantee_resource_id, grantee_resource_type
FROM ibc_grants a0
WHERE object_id = p_object_id
AND grant_group_id = p_grant_group_id
AND permission_code = p_permission_code
AND action = 'ALLOW'
ORDER BY grant_level;