VIEW: APPS.OTFV_TRAINING_EVENT_SESSIONS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,ett.title session_name ,evt.course_start_date session_date ,evt.course_start_time session_start_time ,evt.course_end_time session_end_time ,ett2.title event_name ,evt2.course_start_date event_start_date ,evt2.course_start_time event_start_time ,evt2.timezone time_zone ,orgT1.name training_centre ,tdt.name activity_type ,tvt.version_name activity_name ,'_DF:OTA:OTA_EVENTS:evt' ,evt.business_group_id business_group_id ,tad.activity_id activity_id ,tav.activity_version_id activity_version_id ,evt.event_id event_id ,evt.parent_event_id parent_event_id ,evt.training_center_id training_center_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM hr_all_organization_units bgr ,hr_all_organization_units_tl bgrT ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,ota_events evt2 ,ota_events_tl ett2 ,ota_events evt ,ota_events_tl ett ,hr_all_organization_units_tl orgT1 ,ota_offerings ofr WHERE evt.event_type = 'SESSION' AND evt.training_center_id = orgT1.organization_id (+) AND DECODE(orgT1.language,null,'X',orgT1.language)= DECODE(orgT1.language,null,'X',userenv('LANG')) AND evt2.parent_offering_id = ofr.offering_id (+) AND ofr.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = userenv('LANG') AND tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') AND evt.parent_event_id = evt2.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND evt2.event_id = ett2.event_id AND ett2.language = userenv('LANG') AND evt2.event_type = 'SCHEDULED' 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) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE SESSION_NAME
, EVT.COURSE_START_DATE SESSION_DATE
, EVT.COURSE_START_TIME SESSION_START_TIME
, EVT.COURSE_END_TIME SESSION_END_TIME
, ETT2.TITLE EVENT_NAME
, EVT2.COURSE_START_DATE EVENT_START_DATE
, EVT2.COURSE_START_TIME EVENT_START_TIME
, EVT2.TIMEZONE TIME_ZONE
, ORGT1.NAME TRAINING_CENTRE
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, '_DF:OTA:OTA_EVENTS:EVT'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, EVT.PARENT_EVENT_ID PARENT_EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_EVENTS EVT2
, OTA_EVENTS_TL ETT2
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_OFFERINGS OFR
WHERE EVT.EVENT_TYPE = 'SESSION'
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND EVT2.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND EVT.PARENT_EVENT_ID = EVT2.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND EVT2.EVENT_ID = ETT2.EVENT_ID
AND ETT2.LANGUAGE = USERENV('LANG')
AND EVT2.EVENT_TYPE = 'SCHEDULED'
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) WITH READ ONLY