VIEW: APPS.IBC_CITEM_PERMISSIONS_V

Source

View Text - Preformatted

SELECT IG.permission_code, CI.content_item_id, CI.item_reference_code, CI.content_type_code, CI.directory_node_id, CI.parent_item_id, CI.live_citem_version_id, CI.content_item_status, CI.locked_by_user_id, CI.wd_restricted_flag, CI.base_language, CI.translation_required_flag, CI.owner_resource_id, CI.owner_resource_type, CI.application_id, CI.created_by, CI.creation_date, CI.last_updated_by, CI.last_update_date, CI.last_update_login, CI.request_id, CI.program_update_date, CI.program_application_id, CI.program_id, CI.object_version_number, CI.security_group_id, CI.encrypt_flag, NVL(IG.grantee_user_id, FND_GLOBAL.user_id), 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_content_items CI , fnd_objects GRANTED_AT , ibc_grants IG , fnd_objects GRANTED_TO WHERE OGG.object_id = GRANTED_AT.object_id AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE' AND OGG.grant_group_id+0 = IG.grant_group_id AND CI.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.object_id = GRANTED_TO.object_id AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM' 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, CI.content_item_id, CI.item_reference_code, CI.content_type_code, CI.directory_node_id, CI.parent_item_id, CI.live_citem_version_id, CI.content_item_status, CI.locked_by_user_id, CI.wd_restricted_flag, CI.base_language, CI.translation_required_flag, CI.owner_resource_id, CI.owner_resource_type, CI.application_id, CI.created_by, CI.creation_date, CI.last_updated_by, CI.last_update_date, CI.last_update_login, CI.request_id, CI.program_update_date, CI.program_application_id, CI.program_id, CI.object_version_number, CI.security_group_id, CI.encrypt_flag, RSEXTN.user_id, 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_content_items CI , fnd_objects GRANTED_AT , ibc_grants IG , fnd_objects GRANTED_TO , jtf_rs_groups_denorm RSGROUP , jtf_rs_group_members RSMEMBER , jtf_rs_resource_extns RSEXTN WHERE OGG.object_id = GRANTED_AT.object_id AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE' AND OGG.grant_group_id+0 = IG.grant_group_id AND CI.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.object_id = GRANTED_TO.object_id AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM' 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, CI.content_item_id, CI.item_reference_code, CI.content_type_code, CI.directory_node_id, CI.parent_item_id, CI.live_citem_version_id, CI.content_item_status, CI.locked_by_user_id, CI.wd_restricted_flag, CI.base_language, CI.translation_required_flag, CI.owner_resource_id, CI.owner_resource_type, CI.application_id, CI.created_by, CI.creation_date, CI.last_updated_by, CI.last_update_date, CI.last_update_login, CI.request_id, CI.program_update_date, CI.program_application_id, CI.program_id, CI.object_version_number, CI.security_group_id, CI.encrypt_flag, FND_GLOBAL.user_id, 'Y' SECURITY_FLAG FROM ibc_object_grant_groups OGG , ibc_content_items CI , fnd_objects GRANTED_AT , ibc_grants IG , fnd_objects GRANTED_TO WHERE OGG.object_id = GRANTED_AT.object_id AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE' AND OGG.grant_group_id+0 = IG.grant_group_id AND CI.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.object_id = GRANTED_TO.object_id AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM' 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', CI.content_item_id, CI.item_reference_code, CI.content_type_code, CI.directory_node_id, CI.parent_item_id, CI.live_citem_version_id, CI.content_item_status, CI.locked_by_user_id, CI.wd_restricted_flag, CI.base_language, CI.translation_required_flag, CI.owner_resource_id, CI.owner_resource_type, CI.application_id, CI.created_by, CI.creation_date, CI.last_updated_by, CI.last_update_date, CI.last_update_login, CI.request_id, CI.program_update_date, CI.program_application_id, CI.program_id, CI.object_version_number, CI.security_group_id, CI.encrypt_flag, FND_GLOBAL.USER_ID, 'Y' SECURITY_FLAG FROM ibc_content_items CI, ibc_directory_nodes_b dirnodes WHERE CI.directory_node_id = dirnodes.directory_node_id AND dirnodes.hidden_flag IN ('Y', 'T') UNION ALL SELECT 'ALL_ALLOWED', CI.content_item_id, CI.item_reference_code, CI.content_type_code, CI.directory_node_id, CI.parent_item_id, CI.live_citem_version_id, CI.content_item_status, CI.locked_by_user_id, CI.wd_restricted_flag, CI.base_language, CI.translation_required_flag, CI.owner_resource_id, CI.owner_resource_type, CI.application_id, CI.created_by, CI.creation_date, CI.last_updated_by, CI.last_update_date, CI.last_update_login, CI.request_id, CI.program_update_date, CI.program_application_id, CI.program_id, CI.object_version_number, CI.security_group_id, CI.encrypt_flag, FND_GLOBAL.USER_ID , 'N' SECURITY_FLAG FROM ibc_content_items CI

View Text - HTML Formatted

SELECT IG.PERMISSION_CODE
, CI.CONTENT_ITEM_ID
, CI.ITEM_REFERENCE_CODE
, CI.CONTENT_TYPE_CODE
, CI.DIRECTORY_NODE_ID
, CI.PARENT_ITEM_ID
, CI.LIVE_CITEM_VERSION_ID
, CI.CONTENT_ITEM_STATUS
, CI.LOCKED_BY_USER_ID
, CI.WD_RESTRICTED_FLAG
, CI.BASE_LANGUAGE
, CI.TRANSLATION_REQUIRED_FLAG
, CI.OWNER_RESOURCE_ID
, CI.OWNER_RESOURCE_TYPE
, CI.APPLICATION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.REQUEST_ID
, CI.PROGRAM_UPDATE_DATE
, CI.PROGRAM_APPLICATION_ID
, CI.PROGRAM_ID
, CI.OBJECT_VERSION_NUMBER
, CI.SECURITY_GROUP_ID
, CI.ENCRYPT_FLAG
, NVL(IG.GRANTEE_USER_ID
, FND_GLOBAL.USER_ID)
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_CONTENT_ITEMS CI
, FND_OBJECTS GRANTED_AT
, IBC_GRANTS IG
, FND_OBJECTS GRANTED_TO
WHERE OGG.OBJECT_ID = GRANTED_AT.OBJECT_ID
AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE'
AND OGG.GRANT_GROUP_ID+0 = IG.GRANT_GROUP_ID
AND CI.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.OBJECT_ID = GRANTED_TO.OBJECT_ID
AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM'
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
, CI.CONTENT_ITEM_ID
, CI.ITEM_REFERENCE_CODE
, CI.CONTENT_TYPE_CODE
, CI.DIRECTORY_NODE_ID
, CI.PARENT_ITEM_ID
, CI.LIVE_CITEM_VERSION_ID
, CI.CONTENT_ITEM_STATUS
, CI.LOCKED_BY_USER_ID
, CI.WD_RESTRICTED_FLAG
, CI.BASE_LANGUAGE
, CI.TRANSLATION_REQUIRED_FLAG
, CI.OWNER_RESOURCE_ID
, CI.OWNER_RESOURCE_TYPE
, CI.APPLICATION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.REQUEST_ID
, CI.PROGRAM_UPDATE_DATE
, CI.PROGRAM_APPLICATION_ID
, CI.PROGRAM_ID
, CI.OBJECT_VERSION_NUMBER
, CI.SECURITY_GROUP_ID
, CI.ENCRYPT_FLAG
, RSEXTN.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_CONTENT_ITEMS CI
, FND_OBJECTS GRANTED_AT
, IBC_GRANTS IG
, FND_OBJECTS GRANTED_TO
, JTF_RS_GROUPS_DENORM RSGROUP
, JTF_RS_GROUP_MEMBERS RSMEMBER
, JTF_RS_RESOURCE_EXTNS RSEXTN
WHERE OGG.OBJECT_ID = GRANTED_AT.OBJECT_ID
AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE'
AND OGG.GRANT_GROUP_ID+0 = IG.GRANT_GROUP_ID
AND CI.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.OBJECT_ID = GRANTED_TO.OBJECT_ID
AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM'
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
, CI.CONTENT_ITEM_ID
, CI.ITEM_REFERENCE_CODE
, CI.CONTENT_TYPE_CODE
, CI.DIRECTORY_NODE_ID
, CI.PARENT_ITEM_ID
, CI.LIVE_CITEM_VERSION_ID
, CI.CONTENT_ITEM_STATUS
, CI.LOCKED_BY_USER_ID
, CI.WD_RESTRICTED_FLAG
, CI.BASE_LANGUAGE
, CI.TRANSLATION_REQUIRED_FLAG
, CI.OWNER_RESOURCE_ID
, CI.OWNER_RESOURCE_TYPE
, CI.APPLICATION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.REQUEST_ID
, CI.PROGRAM_UPDATE_DATE
, CI.PROGRAM_APPLICATION_ID
, CI.PROGRAM_ID
, CI.OBJECT_VERSION_NUMBER
, CI.SECURITY_GROUP_ID
, CI.ENCRYPT_FLAG
, FND_GLOBAL.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_OBJECT_GRANT_GROUPS OGG
, IBC_CONTENT_ITEMS CI
, FND_OBJECTS GRANTED_AT
, IBC_GRANTS IG
, FND_OBJECTS GRANTED_TO
WHERE OGG.OBJECT_ID = GRANTED_AT.OBJECT_ID
AND GRANTED_AT.OBJ_NAME = 'IBC_DIRECTORY_NODE'
AND OGG.GRANT_GROUP_ID+0 = IG.GRANT_GROUP_ID
AND CI.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.OBJECT_ID = GRANTED_TO.OBJECT_ID
AND GRANTED_TO.OBJ_NAME = 'IBC_CONTENT_ITEM'
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'
, CI.CONTENT_ITEM_ID
, CI.ITEM_REFERENCE_CODE
, CI.CONTENT_TYPE_CODE
, CI.DIRECTORY_NODE_ID
, CI.PARENT_ITEM_ID
, CI.LIVE_CITEM_VERSION_ID
, CI.CONTENT_ITEM_STATUS
, CI.LOCKED_BY_USER_ID
, CI.WD_RESTRICTED_FLAG
, CI.BASE_LANGUAGE
, CI.TRANSLATION_REQUIRED_FLAG
, CI.OWNER_RESOURCE_ID
, CI.OWNER_RESOURCE_TYPE
, CI.APPLICATION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.REQUEST_ID
, CI.PROGRAM_UPDATE_DATE
, CI.PROGRAM_APPLICATION_ID
, CI.PROGRAM_ID
, CI.OBJECT_VERSION_NUMBER
, CI.SECURITY_GROUP_ID
, CI.ENCRYPT_FLAG
, FND_GLOBAL.USER_ID
, 'Y' SECURITY_FLAG
FROM IBC_CONTENT_ITEMS CI
, IBC_DIRECTORY_NODES_B DIRNODES
WHERE CI.DIRECTORY_NODE_ID = DIRNODES.DIRECTORY_NODE_ID
AND DIRNODES.HIDDEN_FLAG IN ('Y'
, 'T') UNION ALL SELECT 'ALL_ALLOWED'
, CI.CONTENT_ITEM_ID
, CI.ITEM_REFERENCE_CODE
, CI.CONTENT_TYPE_CODE
, CI.DIRECTORY_NODE_ID
, CI.PARENT_ITEM_ID
, CI.LIVE_CITEM_VERSION_ID
, CI.CONTENT_ITEM_STATUS
, CI.LOCKED_BY_USER_ID
, CI.WD_RESTRICTED_FLAG
, CI.BASE_LANGUAGE
, CI.TRANSLATION_REQUIRED_FLAG
, CI.OWNER_RESOURCE_ID
, CI.OWNER_RESOURCE_TYPE
, CI.APPLICATION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.REQUEST_ID
, CI.PROGRAM_UPDATE_DATE
, CI.PROGRAM_APPLICATION_ID
, CI.PROGRAM_ID
, CI.OBJECT_VERSION_NUMBER
, CI.SECURITY_GROUP_ID
, CI.ENCRYPT_FLAG
, FND_GLOBAL.USER_ID
, 'N' SECURITY_FLAG
FROM IBC_CONTENT_ITEMS CI