VIEW: APPS.HRI_CS_TRNEVTACT_B_V

Source

View Text - Preformatted

SELECT NVL( act.activity_id, -1 ) activity_id , NVL( ver.activity_version_id, -1 ) activity_version_id , evt.event_id event_id , evt.enrolment_start_date event_enrolment_start_date , evt.enrolment_end_date event_enrolment_end_date , NVL( evt.course_start_date , prg_date_ref.event_start_date ) event_start_date , NVL( evt.course_end_date , DECODE( evt.event_type, 'PROGRAMME', prg_date_ref.event_end_date , 'SESSION' , evt.course_start_date ) ) event_end_date , evt.course_start_time event_start_time , evt.course_end_time event_end_time , NVL( act.business_group_id, evt.business_group_id ) actvt_def_business_group_id , act.name actvt_def_name , act.comments actvt_def_comments , act.description actvt_def_description , act.multiple_con_versions_flag actvt_def_multi_con_ver_flag , NVL( ver.organization_id, -1 ) actvt_vrsn_organization_id , ver.user_status actvt_vrsn_user_status_code , evt.business_group_id event_business_group_id , evt.event_type event_type_code , NVL( evt.vendor_id, -1 ) event_vendor_id , evt.public_event_flag event_public_event_flag , evt.development_event_type development_event_type_code , evt.offering_id event_offering_id , NVL( evt.event_status, 'X' ) event_status_code , GREATEST( ver.last_update_date , evt.last_update_date ) last_change_date FROM ota_activity_definitions act , ota_activity_versions ver , ota_events evt , ( SELECT prg.program_event_id event_id , MIN( evt.course_start_date ) event_start_date , MAX( evt.course_end_date ) event_end_date FROM ota_events evt , ota_program_memberships prg WHERE prg.event_id = evt.event_id GROUP BY prg.program_event_id ) prg_date_ref WHERE act.activity_id(+) = ver.activity_id AND ver.activity_version_id(+) = evt.activity_version_id AND evt.event_id = prg_date_ref.event_id(+) AND evt.business_group_id = NVL( ota_general.get_business_group_id , evt.business_group_id ) AND ( ( NOT EXISTS ( SELECT 'x' FROM ota_program_memberships prg WHERE evt.event_id = prg.event_id OR evt.event_id = prg.program_event_id ) ) OR ( EXISTS ( SELECT 'x' FROM ota_program_memberships prg WHERE evt.event_id = prg.program_event_id ) ) ) WITH READ ONLY

View Text - HTML Formatted

SELECT NVL( ACT.ACTIVITY_ID
, -1 ) ACTIVITY_ID
, NVL( VER.ACTIVITY_VERSION_ID
, -1 ) ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, EVT.ENROLMENT_START_DATE EVENT_ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE EVENT_ENROLMENT_END_DATE
, NVL( EVT.COURSE_START_DATE
, PRG_DATE_REF.EVENT_START_DATE ) EVENT_START_DATE
, NVL( EVT.COURSE_END_DATE
, DECODE( EVT.EVENT_TYPE
, 'PROGRAMME'
, PRG_DATE_REF.EVENT_END_DATE
, 'SESSION'
, EVT.COURSE_START_DATE ) ) EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, NVL( ACT.BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID ) ACTVT_DEF_BUSINESS_GROUP_ID
, ACT.NAME ACTVT_DEF_NAME
, ACT.COMMENTS ACTVT_DEF_COMMENTS
, ACT.DESCRIPTION ACTVT_DEF_DESCRIPTION
, ACT.MULTIPLE_CON_VERSIONS_FLAG ACTVT_DEF_MULTI_CON_VER_FLAG
, NVL( VER.ORGANIZATION_ID
, -1 ) ACTVT_VRSN_ORGANIZATION_ID
, VER.USER_STATUS ACTVT_VRSN_USER_STATUS_CODE
, EVT.BUSINESS_GROUP_ID EVENT_BUSINESS_GROUP_ID
, EVT.EVENT_TYPE EVENT_TYPE_CODE
, NVL( EVT.VENDOR_ID
, -1 ) EVENT_VENDOR_ID
, EVT.PUBLIC_EVENT_FLAG EVENT_PUBLIC_EVENT_FLAG
, EVT.DEVELOPMENT_EVENT_TYPE DEVELOPMENT_EVENT_TYPE_CODE
, EVT.OFFERING_ID EVENT_OFFERING_ID
, NVL( EVT.EVENT_STATUS
, 'X' ) EVENT_STATUS_CODE
, GREATEST( VER.LAST_UPDATE_DATE
, EVT.LAST_UPDATE_DATE ) LAST_CHANGE_DATE
FROM OTA_ACTIVITY_DEFINITIONS ACT
, OTA_ACTIVITY_VERSIONS VER
, OTA_EVENTS EVT
, ( SELECT PRG.PROGRAM_EVENT_ID EVENT_ID
, MIN( EVT.COURSE_START_DATE ) EVENT_START_DATE
, MAX( EVT.COURSE_END_DATE ) EVENT_END_DATE
FROM OTA_EVENTS EVT
, OTA_PROGRAM_MEMBERSHIPS PRG
WHERE PRG.EVENT_ID = EVT.EVENT_ID GROUP BY PRG.PROGRAM_EVENT_ID ) PRG_DATE_REF
WHERE ACT.ACTIVITY_ID(+) = VER.ACTIVITY_ID
AND VER.ACTIVITY_VERSION_ID(+) = EVT.ACTIVITY_VERSION_ID
AND EVT.EVENT_ID = PRG_DATE_REF.EVENT_ID(+)
AND EVT.BUSINESS_GROUP_ID = NVL( OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID )
AND ( ( NOT EXISTS ( SELECT 'X'
FROM OTA_PROGRAM_MEMBERSHIPS PRG
WHERE EVT.EVENT_ID = PRG.EVENT_ID OR EVT.EVENT_ID = PRG.PROGRAM_EVENT_ID ) ) OR ( EXISTS ( SELECT 'X'
FROM OTA_PROGRAM_MEMBERSHIPS PRG
WHERE EVT.EVENT_ID = PRG.PROGRAM_EVENT_ID ) ) ) WITH READ ONLY