eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRI_MD_TRN_BKG_INT_ATT_V

Source

View Text - Preformatted


SELECT NVL( dbt.number_of_places, 1 ) number_of_places , DECODE( bst.type, 'A', 1, 0 ) attended_indicator , TO_NUMBER( DECODE( bst.type , 'A' , DECODE( dbt.successful_attendance_flag, 'Y', 1, 0 ) , 0 ) ) attended_sccss_indicator , asg.effective_start_date asg_effective_start_date , asg.effective_end_date asg_effective_end_date , NVL( evt.course_start_date , prg_event.event_start_date ) event_start_date , NVL( evt.course_end_date , DECODE( evt.event_type, 'PROGRAMME', prg_event.event_end_date , 'SESSION' , evt.course_start_date ) ) event_end_date , asg.assignment_id assignment_id , asg.person_id person_id , asg.business_group_id business_group_id , asg.organization_id organization_id , NVL( asg.supervisor_id,-1) supervisor_person_id , NVL( asg.location_id, -1 ) location_id , NVL( asg.job_id, -1 ) job_id , NVL( asg.grade_id, -1 ) grade_id , NVL( asg.position_id, -1 ) position_id , asg.assignment_type assignment_type_code , NVL(ver.activity_id, -1) activity_id , NVL(evt.activity_version_id, -1) activity_version_id , evt.event_id event_id , dbt.booking_id dlgt_bkg_id , bst.booking_status_type_id dlgt_bkg_status_type_id , GREATEST(asg.last_update_date ,dbt.last_update_date ,bst.last_update_date ,evt.last_update_date) last_change_date , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD( 'PER_ALL_ASSIGNMENTS_F' , asg.assignment_id , asg.person_id , asg.assignment_type ) = 'TRUE' ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM per_all_assignments_f asg , ota_delegate_bookings dbt , ota_booking_status_types bst , ota_events evt , ota_activity_versions ver ,( 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_event WHERE dbt.event_id = evt.event_id AND evt.event_id = prg_event.event_id(+) AND evt.activity_version_id = ver.activity_version_id (+) AND bst.booking_status_type_id = dbt.booking_status_type_id AND asg.assignment_id = dbt.delegate_assignment_id AND asg.person_id = dbt.delegate_person_id AND dbt.date_booking_placed BETWEEN asg.effective_start_date AND asg.effective_end_date AND NVL( evt.course_end_date , DECODE( evt.event_type, 'PROGRAMME', prg_event.event_end_date , 'SESSION' , evt.course_start_date ) ) < TRUNC(sysdate) AND evt.event_type <> 'DEVELOPMENT' AND evt.offering_id IS NULL AND NVL(evt.event_status, 'NA_EDW') <> 'A' AND asg.assignment_type IN ('E','C') AND bst.type = 'A' AND NOT EXISTS ( SELECT 'x' FROM ota_program_memberships prg WHERE dbt.event_id = prg.event_id ) WITH READ ONLY

View Text - HTML Formatted

SELECT NVL( DBT.NUMBER_OF_PLACES
, 1 ) NUMBER_OF_PLACES
, DECODE( BST.TYPE
, 'A'
, 1
, 0 ) ATTENDED_INDICATOR
, TO_NUMBER( DECODE( BST.TYPE
, 'A'
, DECODE( DBT.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 0 )
, 0 ) ) ATTENDED_SCCSS_INDICATOR
, ASG.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, NVL( EVT.COURSE_START_DATE
, PRG_EVENT.EVENT_START_DATE ) EVENT_START_DATE
, NVL( EVT.COURSE_END_DATE
, DECODE( EVT.EVENT_TYPE
, 'PROGRAMME'
, PRG_EVENT.EVENT_END_DATE
, 'SESSION'
, EVT.COURSE_START_DATE ) ) EVENT_END_DATE
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.PERSON_ID PERSON_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL( ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, NVL( ASG.LOCATION_ID
, -1 ) LOCATION_ID
, NVL( ASG.JOB_ID
, -1 ) JOB_ID
, NVL( ASG.GRADE_ID
, -1 ) GRADE_ID
, NVL( ASG.POSITION_ID
, -1 ) POSITION_ID
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, NVL(VER.ACTIVITY_ID
, -1) ACTIVITY_ID
, NVL(EVT.ACTIVITY_VERSION_ID
, -1) ACTIVITY_VERSION_ID
, EVT.EVENT_ID EVENT_ID
, DBT.BOOKING_ID DLGT_BKG_ID
, BST.BOOKING_STATUS_TYPE_ID DLGT_BKG_STATUS_TYPE_ID
, GREATEST(ASG.LAST_UPDATE_DATE
, DBT.LAST_UPDATE_DATE
, BST.LAST_UPDATE_DATE
, EVT.LAST_UPDATE_DATE) LAST_CHANGE_DATE
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD( 'PER_ALL_ASSIGNMENTS_F'
, ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.ASSIGNMENT_TYPE ) = 'TRUE' ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_ALL_ASSIGNMENTS_F ASG
, OTA_DELEGATE_BOOKINGS DBT
, OTA_BOOKING_STATUS_TYPES BST
, OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS VER
, ( 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_EVENT
WHERE DBT.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = PRG_EVENT.EVENT_ID(+)
AND EVT.ACTIVITY_VERSION_ID = VER.ACTIVITY_VERSION_ID (+)
AND BST.BOOKING_STATUS_TYPE_ID = DBT.BOOKING_STATUS_TYPE_ID
AND ASG.ASSIGNMENT_ID = DBT.DELEGATE_ASSIGNMENT_ID
AND ASG.PERSON_ID = DBT.DELEGATE_PERSON_ID
AND DBT.DATE_BOOKING_PLACED BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND NVL( EVT.COURSE_END_DATE
, DECODE( EVT.EVENT_TYPE
, 'PROGRAMME'
, PRG_EVENT.EVENT_END_DATE
, 'SESSION'
, EVT.COURSE_START_DATE ) ) < TRUNC(SYSDATE)
AND EVT.EVENT_TYPE <> 'DEVELOPMENT'
AND EVT.OFFERING_ID IS NULL
AND NVL(EVT.EVENT_STATUS
, 'NA_EDW') <> 'A'
AND ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND BST.TYPE = 'A'
AND NOT EXISTS ( SELECT 'X'
FROM OTA_PROGRAM_MEMBERSHIPS PRG
WHERE DBT.EVENT_ID = PRG.EVENT_ID ) WITH READ ONLY