VIEW: APPS.IBC_DIRNODE_PERMISSIONS_V

Source

View Text - Preformatted

SELECT IG.permission_code, DN.directory_node_id, DN.directory_node_code, DN.node_type, DN.node_status, DN.directory_path, DN.available_date, DN.expiration_date, DN.hidden_flag, DN.created_by, DN.creation_date, DN.last_updated_by, DN.last_update_date, DN.last_update_login, DN.object_version_number, DN.security_group_id, NVL(IG.grantee_user_id, FND_GLOBAL.user_id), 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_directory_nodes_b DN , ibc_grants IG WHERE OGG.grant_group_id = IG.grant_group_id AND TO_CHAR(DN.directory_node_id) = OGG.instance_pk1_value AND (OGG.inherited_flag = 'N' OR IG.cascade_flag = 'T') AND OGG.inheritance_type <> 'HIDDEN-FOLDER' AND IG.action = 'ALLOW' AND IG.grantee_resource_id IS NULL AND NOT EXISTS ( SELECT 'x' FROM ibc_grants a1 WHERE a1.grant_group_id = IG.grant_group_id AND a1.object_id = IG.object_id AND a1.permission_code = IG.permission_code AND a1.action = 'RESTRICT' AND DECODE(a1.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(a1.grantee_user_id, NULL, 3, 1)) < DECODE(IG.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(IG.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, null) = 'TRUE') ) ) UNION ALL SELECT IG.permission_code, DN.directory_node_id, DN.directory_node_code, DN.node_type, DN.node_status, DN.directory_path, DN.available_date, DN.expiration_date, DN.hidden_flag, DN.created_by, DN.creation_date, DN.last_updated_by, DN.last_update_date, DN.last_update_login, DN.object_version_number, DN.security_group_id, RSEXTN.user_id, 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_directory_nodes_b DN , ibc_grants IG , jtf_rs_groups_denorm RSGROUP , jtf_rs_group_members RSMEMBER , jtf_rs_resource_extns RSEXTN WHERE OGG.grant_group_id = IG.grant_group_id AND TO_CHAR(DN.directory_node_id) = OGG.instance_pk1_value AND (OGG.inherited_flag = 'N' OR IG.cascade_flag = 'T') AND OGG.inheritance_type <> 'HIDDEN-FOLDER' AND IG.action = 'ALLOW' AND IG.grantee_user_id IS NULL AND IG.grantee_resource_id IS NOT NULL AND IG.grantee_resource_type = 'RS_GROUP' AND IG.grantee_resource_id = RSGROUP.parent_group_id AND RSGROUP.group_id = RSMEMBER.group_id AND RSMEMBER.delete_flag = 'N' AND RSEXTN.resource_id = RSMEMBER.resource_id AND NOT EXISTS ( SELECT 'x' FROM ibc_grants a1 WHERE a1.grant_group_id = IG.grant_group_id AND a1.object_id = IG.object_id AND a1.permission_code = IG.permission_code AND a1.action = 'RESTRICT' AND DECODE(a1.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(a1.grantee_user_id, NULL, 3, 1)) < DECODE(IG.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(IG.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, null) = 'TRUE') ) ) UNION ALL SELECT IG.permission_code, DN.directory_node_id, DN.directory_node_code, DN.node_type, DN.node_status, DN.directory_path, DN.available_date, DN.expiration_date, DN.hidden_flag, DN.created_by, DN.creation_date, DN.last_updated_by, DN.last_update_date, DN.last_update_login, DN.object_version_number, DN.security_group_id, FND_GLOBAL.user_id, 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_directory_nodes_b DN , ibc_grants IG WHERE OGG.grant_group_id = IG.grant_group_id AND TO_CHAR(DN.directory_node_id) = OGG.instance_pk1_value AND (OGG.inherited_flag = 'N' OR IG.cascade_flag = 'T') AND OGG.inheritance_type <> 'HIDDEN-FOLDER' AND IG.action = 'ALLOW' AND IG.grantee_user_id IS NULL AND IG.grantee_resource_id = FND_GLOBAL.resp_id AND IG.grantee_resource_type = 'RESPONSIBILITY' AND NOT EXISTS ( SELECT 'x' FROM ibc_grants a1 WHERE a1.grant_group_id = IG.grant_group_id AND a1.object_id = IG.object_id AND a1.permission_code = IG.permission_code AND a1.action = 'RESTRICT' AND DECODE(a1.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(a1.grantee_user_id, NULL, 3, 1)) < DECODE(IG.grantee_resource_type, 'RESPONSIBILITY', 2, 'RS_GROUP', 2, 'GROUP', 2, DECODE(IG.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, null) = 'TRUE') ) ) UNION ALL SELECT 'ALL_ALLOWED', DN.directory_node_id, DN.directory_node_code, DN.node_type, DN.node_status, DN.directory_path, DN.available_date, DN.expiration_date, DN.hidden_flag, DN.created_by, DN.creation_date, DN.last_updated_by, DN.last_update_date, DN.last_update_login, DN.object_version_number, DN.security_group_id, FND_GLOBAL.USER_ID, 'Y' SECURITY_FLAG FROM ibc_directory_nodes_b DN WHERE DN.hidden_flag IN ('Y', 'T') UNION ALL SELECT 'ALL_ALLOWED', DN.directory_node_id, DN.directory_node_code, DN.node_type, DN.node_status, DN.directory_path, DN.available_date, DN.expiration_date, DN.hidden_flag, DN.created_by, DN.creation_date, DN.last_updated_by, DN.last_update_date, DN.last_update_login, DN.object_version_number, DN.security_group_id, FND_GLOBAL.USER_ID , 'N' SECURITY_FLAG FROM ibc_directory_nodes_b DN

View Text - HTML Formatted

SELECT IG.PERMISSION_CODE
, DN.DIRECTORY_NODE_ID
, DN.DIRECTORY_NODE_CODE
, DN.NODE_TYPE
, DN.NODE_STATUS
, DN.DIRECTORY_PATH
, DN.AVAILABLE_DATE
, DN.EXPIRATION_DATE
, DN.HIDDEN_FLAG
, DN.CREATED_BY
, DN.CREATION_DATE
, DN.LAST_UPDATED_BY
, DN.LAST_UPDATE_DATE
, DN.LAST_UPDATE_LOGIN
, DN.OBJECT_VERSION_NUMBER
, DN.SECURITY_GROUP_ID
, NVL(IG.GRANTEE_USER_ID
, FND_GLOBAL.USER_ID)
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_DIRECTORY_NODES_B DN
, IBC_GRANTS IG
WHERE OGG.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND TO_CHAR(DN.DIRECTORY_NODE_ID) = OGG.INSTANCE_PK1_VALUE
AND (OGG.INHERITED_FLAG = 'N' OR IG.CASCADE_FLAG = 'T')
AND OGG.INHERITANCE_TYPE <> 'HIDDEN-FOLDER'
AND IG.ACTION = 'ALLOW'
AND IG.GRANTEE_RESOURCE_ID IS NULL
AND NOT EXISTS ( SELECT 'X'
FROM IBC_GRANTS A1
WHERE A1.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND A1.OBJECT_ID = IG.OBJECT_ID
AND A1.PERMISSION_CODE = IG.PERMISSION_CODE
AND A1.ACTION = 'RESTRICT'
AND DECODE(A1.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(A1.GRANTEE_USER_ID
, NULL
, 3
, 1)) < DECODE(IG.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(IG.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
, NULL) = 'TRUE') ) ) UNION ALL SELECT IG.PERMISSION_CODE
, DN.DIRECTORY_NODE_ID
, DN.DIRECTORY_NODE_CODE
, DN.NODE_TYPE
, DN.NODE_STATUS
, DN.DIRECTORY_PATH
, DN.AVAILABLE_DATE
, DN.EXPIRATION_DATE
, DN.HIDDEN_FLAG
, DN.CREATED_BY
, DN.CREATION_DATE
, DN.LAST_UPDATED_BY
, DN.LAST_UPDATE_DATE
, DN.LAST_UPDATE_LOGIN
, DN.OBJECT_VERSION_NUMBER
, DN.SECURITY_GROUP_ID
, RSEXTN.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_DIRECTORY_NODES_B DN
, IBC_GRANTS IG
, JTF_RS_GROUPS_DENORM RSGROUP
, JTF_RS_GROUP_MEMBERS RSMEMBER
, JTF_RS_RESOURCE_EXTNS RSEXTN
WHERE OGG.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND TO_CHAR(DN.DIRECTORY_NODE_ID) = OGG.INSTANCE_PK1_VALUE
AND (OGG.INHERITED_FLAG = 'N' OR IG.CASCADE_FLAG = 'T')
AND OGG.INHERITANCE_TYPE <> 'HIDDEN-FOLDER'
AND IG.ACTION = 'ALLOW'
AND IG.GRANTEE_USER_ID IS NULL
AND IG.GRANTEE_RESOURCE_ID IS NOT NULL
AND IG.GRANTEE_RESOURCE_TYPE = 'RS_GROUP'
AND IG.GRANTEE_RESOURCE_ID = RSGROUP.PARENT_GROUP_ID
AND RSGROUP.GROUP_ID = RSMEMBER.GROUP_ID
AND RSMEMBER.DELETE_FLAG = 'N'
AND RSEXTN.RESOURCE_ID = RSMEMBER.RESOURCE_ID
AND NOT EXISTS ( SELECT 'X'
FROM IBC_GRANTS A1
WHERE A1.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND A1.OBJECT_ID = IG.OBJECT_ID
AND A1.PERMISSION_CODE = IG.PERMISSION_CODE
AND A1.ACTION = 'RESTRICT'
AND DECODE(A1.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(A1.GRANTEE_USER_ID
, NULL
, 3
, 1)) < DECODE(IG.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(IG.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
, NULL) = 'TRUE') ) ) UNION ALL SELECT IG.PERMISSION_CODE
, DN.DIRECTORY_NODE_ID
, DN.DIRECTORY_NODE_CODE
, DN.NODE_TYPE
, DN.NODE_STATUS
, DN.DIRECTORY_PATH
, DN.AVAILABLE_DATE
, DN.EXPIRATION_DATE
, DN.HIDDEN_FLAG
, DN.CREATED_BY
, DN.CREATION_DATE
, DN.LAST_UPDATED_BY
, DN.LAST_UPDATE_DATE
, DN.LAST_UPDATE_LOGIN
, DN.OBJECT_VERSION_NUMBER
, DN.SECURITY_GROUP_ID
, FND_GLOBAL.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_DIRECTORY_NODES_B DN
, IBC_GRANTS IG
WHERE OGG.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND TO_CHAR(DN.DIRECTORY_NODE_ID) = OGG.INSTANCE_PK1_VALUE
AND (OGG.INHERITED_FLAG = 'N' OR IG.CASCADE_FLAG = 'T')
AND OGG.INHERITANCE_TYPE <> 'HIDDEN-FOLDER'
AND IG.ACTION = 'ALLOW'
AND IG.GRANTEE_USER_ID IS NULL
AND IG.GRANTEE_RESOURCE_ID = FND_GLOBAL.RESP_ID
AND IG.GRANTEE_RESOURCE_TYPE = 'RESPONSIBILITY'
AND NOT EXISTS ( SELECT 'X'
FROM IBC_GRANTS A1
WHERE A1.GRANT_GROUP_ID = IG.GRANT_GROUP_ID
AND A1.OBJECT_ID = IG.OBJECT_ID
AND A1.PERMISSION_CODE = IG.PERMISSION_CODE
AND A1.ACTION = 'RESTRICT'
AND DECODE(A1.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(A1.GRANTEE_USER_ID
, NULL
, 3
, 1)) < DECODE(IG.GRANTEE_RESOURCE_TYPE
, 'RESPONSIBILITY'
, 2
, 'RS_GROUP'
, 2
, 'GROUP'
, 2
, DECODE(IG.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
, NULL) = 'TRUE') ) ) UNION ALL SELECT 'ALL_ALLOWED'
, DN.DIRECTORY_NODE_ID
, DN.DIRECTORY_NODE_CODE
, DN.NODE_TYPE
, DN.NODE_STATUS
, DN.DIRECTORY_PATH
, DN.AVAILABLE_DATE
, DN.EXPIRATION_DATE
, DN.HIDDEN_FLAG
, DN.CREATED_BY
, DN.CREATION_DATE
, DN.LAST_UPDATED_BY
, DN.LAST_UPDATE_DATE
, DN.LAST_UPDATE_LOGIN
, DN.OBJECT_VERSION_NUMBER
, DN.SECURITY_GROUP_ID
, FND_GLOBAL.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_DIRECTORY_NODES_B DN
WHERE DN.HIDDEN_FLAG IN ('Y'
, 'T') UNION ALL SELECT 'ALL_ALLOWED'
, DN.DIRECTORY_NODE_ID
, DN.DIRECTORY_NODE_CODE
, DN.NODE_TYPE
, DN.NODE_STATUS
, DN.DIRECTORY_PATH
, DN.AVAILABLE_DATE
, DN.EXPIRATION_DATE
, DN.HIDDEN_FLAG
, DN.CREATED_BY
, DN.CREATION_DATE
, DN.LAST_UPDATED_BY
, DN.LAST_UPDATE_DATE
, DN.LAST_UPDATE_LOGIN
, DN.OBJECT_VERSION_NUMBER
, DN.SECURITY_GROUP_ID
, FND_GLOBAL.USER_ID
, 'N' SECURITY_FLAG
FROM IBC_DIRECTORY_NODES_B DN