View: OTFV_TOTAL_ENROLLMENTS

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

View Text

SELECT EVT.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.OFFERING_ID OFFERING_ID
FROM OTA_EVENTS EVT
, OTA_DELEGATE_BOOKINGS TDB
, OTA_BOOKING_STATUS_TYPES BST
WHERE EVT.EVENT_ID = TDB.EVENT_ID (+)
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 EVT.TITLE
, EVT.EVENT_ID
, EVT.OFFERING_ID

Columns