View: OTFV_MISSING_REQ_RESOURCES

Product:	OTA - Learning Management
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.OTFV_MISSING_REQ_RESOURCES

View Text

SELECT DECODE(TSR.RESOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE)) RESOURCE_TYPE
, TSR.NAME RESOURCE_NAME
, TAV.VERSION_NAME ACTIVITY_NAME
, EVT.TITLE EVENT_TITLE
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, TSR.SUPPLIED_RESOURCE_ID SUPPLIED_RESOURCE_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_ACTIVITY_VERSIONS TAV
, OTA_RESOURCE_USAGES TRU
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TRU.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)
AND TRU.REQUIRED_FLAG = 'Y'
AND NOT EXISTS (SELECT 1
FROM OTA_RESOURCE_BOOKINGS TRB2
WHERE TRB2.EVENT_ID = EVT.EVENT_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TRB2.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID
AND TRB2.QUANTITY >= TRU.QUANTITY
AND TRB2.STATUS = 'C') UNION SELECT DECODE(TRU.RESOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TRU.RESOURCE_TYPE)) RESOURCE_TYPE
, TO_CHAR(NULL) RESOURCE_NAME
, TAV.VERSION_NAME ACTIVITY_NAME
, EVT.TITLE EVENT_TITLE
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, TO_NUMBER(NULL) SUPPLIED_RESOURCE_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_RESOURCE_USAGES TRU
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)
AND TRU.REQUIRED_FLAG = 'Y'
AND TRU.SUPPLIED_RESOURCE_ID IS NULL WITH READ ONLY

Columns