View: OTA_CATEGORY_USAGES_V

Product:	OTA - Learning Management
Description:	View to list all the uses of a Category
Implementation/DBA Data:	APPS.OTA_CATEGORY_USAGES_V

View Text

SELECT TCUC.BUSINESS_GROUP_ID
, TCUC.CATEGORY
, LK1.MEANING
, 'Y'
, TCUC.CATEGORY_USAGE_ID
, TCUC.ROWID
, TCUC.OBJECT_VERSION_NUMBER
, 'Y'
, TCUP.CATEGORY_USAGE_ID
, TCUP.ROWID
, TCUP.OBJECT_VERSION_NUMBER
, 'Y'
, TCUD.CATEGORY_USAGE_ID
, TCUD.ROWID
, TCUD.OBJECT_VERSION_NUMBER
FROM OTA_CATEGORY_USAGES TCUC
, OTA_CATEGORY_USAGES TCUP
, OTA_CATEGORY_USAGES TCUD
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUC.CATEGORY = LK1.LOOKUP_CODE
AND TCUC.TYPE = 'C'
AND TCUP.CATEGORY = LK1.LOOKUP_CODE
AND TCUP.TYPE = 'P'
AND TCUD.CATEGORY = LK1.LOOKUP_CODE
AND TCUD.TYPE = 'D'
AND TCUC.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID
AND TCUP.BUSINESS_GROUP_ID = TCUD.BUSINESS_GROUP_ID UNION SELECT TCUC.BUSINESS_GROUP_ID
, TCUC.CATEGORY
, LK1.MEANING
, 'Y'
, TCUC.CATEGORY_USAGE_ID
, TCUC.ROWID
, TCUC.OBJECT_VERSION_NUMBER
, 'Y'
, TCUP.CATEGORY_USAGE_ID
, TCUP.ROWID
, TCUP.OBJECT_VERSION_NUMBER
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
FROM OTA_CATEGORY_USAGES TCUC
, OTA_CATEGORY_USAGES TCUP
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUC.CATEGORY = LK1.LOOKUP_CODE
AND TCUC.TYPE = 'C'
AND TCUP.CATEGORY = LK1.LOOKUP_CODE
AND TCUP.TYPE = 'P'
AND TCUC.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'D'
AND TCU.BUSINESS_GROUP_ID = TCUC.BUSINESS_GROUP_ID) UNION SELECT TCUP.BUSINESS_GROUP_ID
, TCUP.CATEGORY
, LK1.MEANING
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'Y'
, TCUP.CATEGORY_USAGE_ID
, TCUP.ROWID
, TCUP.OBJECT_VERSION_NUMBER
, 'Y'
, TCUD.CATEGORY_USAGE_ID
, TCUD.ROWID
, TCUD.OBJECT_VERSION_NUMBER
FROM OTA_CATEGORY_USAGES TCUD
, OTA_CATEGORY_USAGES TCUP
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUD.CATEGORY = LK1.LOOKUP_CODE
AND TCUD.TYPE = 'D'
AND TCUP.CATEGORY = LK1.LOOKUP_CODE
AND TCUP.TYPE = 'P'
AND TCUD.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'C'
AND TCU.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID) UNION SELECT TCUC.BUSINESS_GROUP_ID
, TCUC.CATEGORY
, LK1.MEANING
, 'Y'
, TCUC.CATEGORY_USAGE_ID
, TCUC.ROWID
, TCUC.OBJECT_VERSION_NUMBER
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'Y'
, TCUD.CATEGORY_USAGE_ID
, TCUD.ROWID
, TCUD.OBJECT_VERSION_NUMBER
FROM OTA_CATEGORY_USAGES TCUC
, OTA_CATEGORY_USAGES TCUD
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUC.CATEGORY = LK1.LOOKUP_CODE
AND TCUC.TYPE = 'C'
AND TCUD.CATEGORY = LK1.LOOKUP_CODE
AND TCUD.TYPE = 'D'
AND TCUC.BUSINESS_GROUP_ID = TCUD.BUSINESS_GROUP_ID
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'P'
AND TCU.BUSINESS_GROUP_ID = TCUC.BUSINESS_GROUP_ID) UNION SELECT TCUC.BUSINESS_GROUP_ID
, TCUC.CATEGORY
, LK1.MEANING
, 'Y'
, TCUC.CATEGORY_USAGE_ID
, TCUC.ROWID
, TCUC.OBJECT_VERSION_NUMBER
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
FROM OTA_CATEGORY_USAGES TCUC
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUC.CATEGORY = LK1.LOOKUP_CODE
AND TCUC.TYPE = 'C'
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'P'
AND TCU.BUSINESS_GROUP_ID = TCUC.BUSINESS_GROUP_ID)
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'D'
AND TCU.BUSINESS_GROUP_ID = TCUC.BUSINESS_GROUP_ID) UNION SELECT TCUP.BUSINESS_GROUP_ID
, TCUP.CATEGORY
, LK1.MEANING
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'Y'
, TCUP.CATEGORY_USAGE_ID
, TCUP.ROWID
, TCUP.OBJECT_VERSION_NUMBER
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
FROM OTA_CATEGORY_USAGES TCUP
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUP.CATEGORY = LK1.LOOKUP_CODE
AND TCUP.TYPE = 'P'
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'C'
AND TCU.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID)
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'D'
AND TCU.BUSINESS_GROUP_ID = TCUP.BUSINESS_GROUP_ID) UNION SELECT TCUD.BUSINESS_GROUP_ID
, TCUD.CATEGORY
, LK1.MEANING
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'N'
, TO_NUMBER(NULL)
, CHARTOROWID(NULL)
, TO_NUMBER(NULL)
, 'Y'
, TCUD.CATEGORY_USAGE_ID
, TCUD.ROWID
, TCUD.OBJECT_VERSION_NUMBER
FROM OTA_CATEGORY_USAGES TCUD
, HR_LOOKUPS LK1
WHERE LK1.LOOKUP_TYPE = 'ACTIVITY_CATEGORY'
AND TCUD.CATEGORY = LK1.LOOKUP_CODE
AND TCUD.TYPE = 'D'
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'C'
AND TCU.BUSINESS_GROUP_ID = TCUD.BUSINESS_GROUP_ID)
AND NOT EXISTS (SELECT NULL
FROM OTA_CATEGORY_USAGES TCU
WHERE TCU.CATEGORY = LK1.LOOKUP_CODE
AND TCU.TYPE = 'P'
AND TCU.BUSINESS_GROUP_ID = TCUD.BUSINESS_GROUP_ID)

Columns