VIEW: APPS.OTFV_TOTAL_ENROLLMENTS

Source

View Text - Preformatted

SELECT ett.title event_title ,SUM(DECODE(bst.type,'A',tdb.number_of_places ,'P',tdb.number_of_places,0)) total_enrollments ,SUM(DECODE(bst.type,'A',tdb.number_of_places,0)) total_attended_enrollments ,SUM(DECODE(bst.type,'A',0,tdb.number_of_places)) total_non_attended_enrollments ,SUM(DECODE(bst.type,'C',tdb.number_of_places,0)) total_cancelled_enrollments ,SUM(DECODE(bst.type,'P',tdb.number_of_places,0)) total_placed_enrollments ,SUM(DECODE(bst.type,'R',tdb.number_of_places,0)) total_requested_enrollments ,SUM(DECODE(bst.type,'W',tdb.number_of_places,0)) total_waitlisted_enrollments ,SUM(DECODE(tdb.internal_booking_flag,'Y' ,DECODE(bst.type,'A',tdb.number_of_places ,'P',tdb.number_of_places,0) ,0)) total_internal_enrollments ,SUM(DECODE(tdb.internal_booking_flag,'N' ,DECODE(bst.type,'A',tdb.number_of_places ,'P',tdb.number_of_places,0) ,0)) total_external_enrollments ,evt.event_id event_id ,evt.parent_offering_id offering_id FROM ota_events evt ,ota_events_tl ett ,ota_delegate_bookings tdb ,ota_booking_status_types bst WHERE evt.event_id = tdb.event_id (+) AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') AND tdb.booking_status_type_id = bst.booking_status_type_id (+) AND evt.business_group_id = NVL(ota_general.get_business_group_id,evt.business_group_id) GROUP BY ett.title ,evt.event_id ,evt.parent_offering_id

View Text - HTML Formatted

SELECT ETT.TITLE EVENT_TITLE
, SUM(DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_ATTENDED_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'A'
, 0
, TDB.NUMBER_OF_PLACES)) TOTAL_NON_ATTENDED_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_CANCELLED_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_PLACED_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'R'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_REQUESTED_ENROLLMENTS
, SUM(DECODE(BST.TYPE
, 'W'
, TDB.NUMBER_OF_PLACES
, 0)) TOTAL_WAITLISTED_ENROLLMENTS
, SUM(DECODE(TDB.INTERNAL_BOOKING_FLAG
, 'Y'
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)
, 0)) TOTAL_INTERNAL_ENROLLMENTS
, SUM(DECODE(TDB.INTERNAL_BOOKING_FLAG
, 'N'
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)
, 0)) TOTAL_EXTERNAL_ENROLLMENTS
, EVT.EVENT_ID EVENT_ID
, EVT.PARENT_OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_DELEGATE_BOOKINGS TDB
, OTA_BOOKING_STATUS_TYPES BST
WHERE EVT.EVENT_ID = TDB.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID (+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) GROUP BY ETT.TITLE
, EVT.EVENT_ID
, EVT.PARENT_OFFERING_ID