View: OTFV_SCHEDULED_TRAINING_EVENTS

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, EVT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE EVENT_START_DATE
, EVT.COURSE_END_DATE EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, EVT.TIMEZONE TIME_ZONE
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, OTA_VIEWS_PKG.OTA_GET_PLACES_AVAILABLE(EVT.EVENT_ID) AVAILABLE_PLACES
, ORGT1.NAME TRAINING_CENTER
, EVT.ENROLMENT_START_DATE ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLMENT_END_DATE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) USER_STATUS
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.SECURE_EVENT_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'))) SECURE_EVENT_FLAG
, DECODE(EVT.PUBLIC_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.PUBLIC_EVENT_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'))) RESTRICTIONS_APPLY
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_STATUS
, 'F'
, 'Y'
, 'N'))) EVENT_FULL_FLAG
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.USER_STATUS
, 'PL'
, 'Y'
, 'N'))) EVENT_TENTATIVE_FLAG
, TAD.NAME ACTIVITY_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, ACI.ACTIVITY_CATEGORY) CATEGORY_NAME
, CUR.NAME CURRENCY_CODE
, LNG.DESCRIPTION LANGUAGE
, VEN.VENDOR_NAME SUPPLIER_NAME
, ORGT.NAME SPONSORING_ORGANIZATION
, NVL( SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, NVL( TDB.NUMBER_OF_PLACES
, 1 ) ) )
, 0 )INTERNAL_STUDENT_COUNT
, NVL( SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, NVL( TDB.NUMBER_OF_PLACES
, 1 ) ) )
, 0 )EXTERNAL_STUDENT_COUNT
, SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) SUCCESSFUL_INT_STUDENT_COUNT
, SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, DECODE( TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) SUCCESSFUL_EXT_STUDENT_COUNT
, SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) CONFIRMED_INT_STUDENT_COUNT
, SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) CONFIRMED_EXT_STUDENT_COUNT
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_BOOKING_STATUS_TYPES BST
, OTA_DELEGATE_BOOKINGS TDB
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, FND_LANGUAGES_VL LNG
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
WHERE EVT.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.EVENT_ID = TDB.EVENT_ID (+)
AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID (+)
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND EVT.VENDOR_ID = VEN.VENDOR_ID(+)
AND EVT.EVENT_TYPE = 'SCHEDULED'
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID (+)
AND ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID (+)
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE(+)
AND EVT.LANGUAGE_ID = LNG.LANGUAGE_ID(+)
AND EVT.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND BGR.ORGANIZATION_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) GROUP BY BGRT.NAME
, EVT.TITLE
, EVT.COMMENTS
, EVT.COURSE_START_DATE
, EVT.COURSE_END_DATE
, EVT.COURSE_START_TIME
, EVT.COURSE_END_TIME
, EVT.TIMEZONE
, EVT.DURATION
, EVT.DURATION_UNITS
, ORGT1.NAME
, EVT.ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE
, EVT.EVENT_STATUS
, EVT.USER_STATUS
, EVT.MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE
, EVT.SECURE_EVENT_FLAG
, EVT.PUBLIC_EVENT_FLAG
, EVT.EVENT_STATUS
, EVT.USER_STATUS
, TAD.NAME
, TAV.VERSION_NAME
, ACI.ACTIVITY_CATEGORY
, CUR.NAME
, LNG.DESCRIPTION
, VEN.VENDOR_NAME
, ORGT.NAME
, TAD.ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID
, EVT.BUSINESS_GROUP_ID
, EVT.EVENT_ID
, EVT.TRAINING_CENTER_ID
, TAV.RCO_ID
, EVT.OFFERING_ID WITH READ ONLY

Columns