VIEW: APPS.OTFV_MISSING_REQ_RESOURCES

Source

View Text - Preformatted

SELECT DECODE(tsr.resource_type,NULL,NULL, hr_bis.bis_decode_lookup('RESOURCE_TYPE',tsr.resource_type)) resource_type ,tst.name resource_name ,tvt.version_name activity_name ,ett.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 ,ofr.offering_id offering_id FROM ota_events evt ,ota_suppliable_resources tsr ,ota_activity_versions tav ,ota_events_tl ett ,ota_suppliable_resources_tl tst ,ota_activity_versions_tl tvt ,ota_resource_usages tru ,ota_offerings ofr WHERE tru.activity_version_id = tav.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = USERENV('LANG') AND tru.supplied_resource_id = tsr.supplied_resource_id AND tsr.supplied_resource_id = tst.supplied_resource_id AND tst.language = USERENV('LANG') AND evt.parent_offering_id = ofr.offering_id AND ofr.activity_version_id = tav.activity_version_id AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') 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 ,tvt.version_name activity_name ,ett.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 ,ofr.offering_id offering_id FROM ota_events evt ,ota_activity_versions tav ,ota_events_tl ett ,ota_activity_versions_tl tvt ,ota_resource_usages tru ,ota_offerings ofr WHERE tru.activity_version_id = tav.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = USERENV('LANG') AND ofr.activity_version_id = tav.activity_version_id AND evt.parent_offering_id = ofr.offering_id AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') 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

View Text - HTML Formatted

SELECT DECODE(TSR.RESOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE)) RESOURCE_TYPE
, TST.NAME RESOURCE_NAME
, TVT.VERSION_NAME ACTIVITY_NAME
, ETT.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
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS_TL ETT
, OTA_SUPPLIABLE_RESOURCES_TL TST
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_RESOURCE_USAGES TRU
, OTA_OFFERINGS OFR
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND TRU.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID
AND TSR.SUPPLIED_RESOURCE_ID = TST.SUPPLIED_RESOURCE_ID
AND TST.LANGUAGE = USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
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
, TVT.VERSION_NAME ACTIVITY_NAME
, ETT.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
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS_TL ETT
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_RESOURCE_USAGES TRU
, OTA_OFFERINGS OFR
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
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