VIEW: APPS.OTV_SCHEDULED_EVENTS

Source

View Text - Preformatted

SELECT OAV.VERSION_NAME , EVT.TITLE , EVT.COURSE_START_DATE , EVT.COURSE_END_DATE , EVT.COURSE_START_TIME , EVT.COURSE_END_TIME , EVT.DURATION , HR_GENERAL.DECODE_LOOKUP('FREQUENCY',EVT.DURATION_UNITS) , OTA_VIEWS_PKG.OTA_GET_PLACES_AVAILABLE(EVT.EVENT_ID) , HR_GENERAL.DECODE_LOOKUP('TRAINING_CENTRE',EVT.CENTRE) , EVT.CENTRE , VEN.VENDOR_NAME , EVT.ENROLMENT_START_DATE , EVT.ENROLMENT_END_DATE , HR_GENERAL.DECODE_LOOKUP('TRAINING_EVENT_TYPE',EVT.EVENT_TYPE) , ota_utility.get_lang_name(ofv.language_code), HR_GENERAL.DECODE_LOOKUP('SCHEDULED_EVENT_STATUS', EVT.EVENT_STATUS) , HR_GENERAL.DECODE_LOOKUP('EVENT_USER_STATUS',EVT.USER_STATUS) , EVT.MINIMUM_ATTENDEES , EVT.MAXIMUM_ATTENDEES , EVT.STANDARD_PRICE , CUR.NAME , EVT.SECURE_EVENT_FLAG , HR_GENERAL.DECODE_LOOKUP('YES_NO',DECODE(EVT.PUBLIC_EVENT_FLAG, 'Y', 'N', 'N', 'Y')) , OAV.ACTIVITY_VERSION_ID , EVT.EVENT_ID ,EVT.BOOK_INDEPENDENT_FLAG , HR_GENERAL.DECODE_LOOKUP('YES_NO', DECODE(EVT.EVENT_STATUS, 'F', 'Y', 'N')) , HR_GENERAL.DECODE_LOOKUP('YES_NO', DECODE(EVT.USER_STATUS, 'PL', 'Y', 'N')) ,EVT.BUSINESS_GROUP_ID ,BGR.NAME, EVT.TRAINING_CENTER_ID, EVT.LOCATION_ID, EVT.LANGUAGE_ID FROM OTA_ACTIVITY_VERSIONS OAV ,OTA_EVENTS EVT ,PO_VENDORS VEN ,FND_CURRENCIES_VL CUR ,ota_offerings_vl ofv , HR_ALL_ORGANIZATION_UNITS BGR WHERE EVT.ACTIVITY_VERSION_ID = OAV.ACTIVITY_VERSION_ID AND EVT.VENDOR_ID = VEN.VENDOR_ID(+) AND EVT.EVENT_TYPE = 'SCHEDULED' AND TRUNC(SYSDATE) BETWEEN NVL( EVT.ENROLMENT_START_DATE, TRUNC( SYSDATE)) AND NVL( EVT.ENROLMENT_END_DATE, TRUNC( SYSDATE)) AND TRUNC(SYSDATE) <= NVL( EVT.COURSE_END_DATE, TRUNC(SYSDATE)) AND EVT.EVENT_STATUS IN ('N','P','F') AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE(+) AND ofv.offering_id(+) = evt.parent_offering_id AND EVT.BUSINESS_GROUP_ID=BGR.ORGANIZATION_ID AND EVT.BUSINESS_GROUP_ID=NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID, EVT.BUSINESS_GROUP_ID)

View Text - HTML Formatted

SELECT OAV.VERSION_NAME
, EVT.TITLE
, EVT.COURSE_START_DATE
, EVT.COURSE_END_DATE
, EVT.COURSE_START_TIME
, EVT.COURSE_END_TIME
, EVT.DURATION
, HR_GENERAL.DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)
, OTA_VIEWS_PKG.OTA_GET_PLACES_AVAILABLE(EVT.EVENT_ID)
, HR_GENERAL.DECODE_LOOKUP('TRAINING_CENTRE'
, EVT.CENTRE)
, EVT.CENTRE
, VEN.VENDOR_NAME
, EVT.ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE
, HR_GENERAL.DECODE_LOOKUP('TRAINING_EVENT_TYPE'
, EVT.EVENT_TYPE)
, OTA_UTILITY.GET_LANG_NAME(OFV.LANGUAGE_CODE)
, HR_GENERAL.DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)
, HR_GENERAL.DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)
, EVT.MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES
, EVT.STANDARD_PRICE
, CUR.NAME
, EVT.SECURE_EVENT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, DECODE(EVT.PUBLIC_EVENT_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'))
, OAV.ACTIVITY_VERSION_ID
, EVT.EVENT_ID
, EVT.BOOK_INDEPENDENT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_STATUS
, 'F'
, 'Y'
, 'N'))
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, DECODE(EVT.USER_STATUS
, 'PL'
, 'Y'
, 'N'))
, EVT.BUSINESS_GROUP_ID
, BGR.NAME
, EVT.TRAINING_CENTER_ID
, EVT.LOCATION_ID
, EVT.LANGUAGE_ID
FROM OTA_ACTIVITY_VERSIONS OAV
, OTA_EVENTS EVT
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, OTA_OFFERINGS_VL OFV
, HR_ALL_ORGANIZATION_UNITS BGR
WHERE EVT.ACTIVITY_VERSION_ID = OAV.ACTIVITY_VERSION_ID
AND EVT.VENDOR_ID = VEN.VENDOR_ID(+)
AND EVT.EVENT_TYPE = 'SCHEDULED'
AND TRUNC(SYSDATE) BETWEEN NVL( EVT.ENROLMENT_START_DATE
, TRUNC( SYSDATE))
AND NVL( EVT.ENROLMENT_END_DATE
, TRUNC( SYSDATE))
AND TRUNC(SYSDATE) <= NVL( EVT.COURSE_END_DATE
, TRUNC(SYSDATE))
AND EVT.EVENT_STATUS IN ('N'
, 'P'
, 'F')
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE(+)
AND OFV.OFFERING_ID(+) = EVT.PARENT_OFFERING_ID
AND EVT.BUSINESS_GROUP_ID=BGR.ORGANIZATION_ID
AND EVT.BUSINESS_GROUP_ID=NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)