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_MB_TRN_BKG_INT_V

Source

View Text - Preformatted


SELECT NVL( dbt.number_of_places, 1 ) number_of_places , TO_NUMBER( DECODE( bst.type , 'A' , DECODE( dbt.successful_attendance_flag, 'Y', 1, 0 ) , 0 ) ) attended_sccss_indicator , TO_NUMBER( DECODE( bst.type , 'A' , DECODE( dbt.successful_attendance_flag, 'Y', 0, 1 ) , 0 ) ) attended_unsccss_indicator , DECODE( bst.type, 'A', 1, 0 ) attended_indicator , DECODE( bst.type, 'C', 1, 0 ) cancelled_indicator , DECODE( bst.type, 'P', 1, 0 ) placed_indicator , DECODE( bst.type, 'R', 1, 0 ) requested_indicator , DECODE( bst.type, 'W', 1, 0 ) waitlisted_indicator , dbt.attendance_result dlgt_bkg_attendance_result , dbt.date_booking_placed dlgt_booking_placed_date , asg.effective_start_date asg_effective_start_date , asg.effective_end_date asg_effective_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.primary_flag primary_flag_code , asg.assignment_type assignment_type_code , asg.assignment_status_type_id assignment_status_type_id , ast.per_system_status per_system_status_code , ast.pay_system_status pay_system_status_code , asg.period_of_service_id period_of_service_id , asg.employment_category employment_category_code , asg.employee_category employee_category_code , dbt.event_id event_id , dbt.booking_id dlgt_booking_id , dbt.business_group_id dlgt_bkg_business_group_id , NVL( dbt.organization_id, -1 ) dlgt_bkg_organization_id , NVL( dbt.sponsor_person_id, -1 ) dlgt_bkg_sponsor_person_id , NVL( dbt.sponsor_assignment_id, -1 ) dlgt_bkg_sponsor_assignment_id , dbt.successful_attendance_flag sccssfl_attendance_flag_code , dbt.internal_booking_flag dlgt_bkg_internal_booking_flag , dbt.booking_priority dlgt_bkg_booking_priority_code , NVL( bst.booking_status_type_id, -1 ) booking_status_type_id , bst.type booking_status_type_code , bst.place_used_flag bst_place_used_flag_code , 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 , GREATEST( asg.last_update_date , ast.last_update_date , dbt.last_update_date , bst.last_update_date ) last_change_date FROM per_all_assignments_f asg , ota_delegate_bookings dbt , ota_booking_status_types bst , per_assignment_status_types ast WHERE 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 asg.assignment_status_type_id = ast.assignment_status_type_id AND dbt.date_booking_placed BETWEEN asg.effective_start_date AND asg.effective_end_date 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
, TO_NUMBER( DECODE( BST.TYPE
, 'A'
, DECODE( DBT.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 0 )
, 0 ) ) ATTENDED_SCCSS_INDICATOR
, TO_NUMBER( DECODE( BST.TYPE
, 'A'
, DECODE( DBT.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 0
, 1 )
, 0 ) ) ATTENDED_UNSCCSS_INDICATOR
, DECODE( BST.TYPE
, 'A'
, 1
, 0 ) ATTENDED_INDICATOR
, DECODE( BST.TYPE
, 'C'
, 1
, 0 ) CANCELLED_INDICATOR
, DECODE( BST.TYPE
, 'P'
, 1
, 0 ) PLACED_INDICATOR
, DECODE( BST.TYPE
, 'R'
, 1
, 0 ) REQUESTED_INDICATOR
, DECODE( BST.TYPE
, 'W'
, 1
, 0 ) WAITLISTED_INDICATOR
, DBT.ATTENDANCE_RESULT DLGT_BKG_ATTENDANCE_RESULT
, DBT.DATE_BOOKING_PLACED DLGT_BOOKING_PLACED_DATE
, ASG.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_EFFECTIVE_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.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, ASG.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_CODE
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASG.EMPLOYEE_CATEGORY EMPLOYEE_CATEGORY_CODE
, DBT.EVENT_ID EVENT_ID
, DBT.BOOKING_ID DLGT_BOOKING_ID
, DBT.BUSINESS_GROUP_ID DLGT_BKG_BUSINESS_GROUP_ID
, NVL( DBT.ORGANIZATION_ID
, -1 ) DLGT_BKG_ORGANIZATION_ID
, NVL( DBT.SPONSOR_PERSON_ID
, -1 ) DLGT_BKG_SPONSOR_PERSON_ID
, NVL( DBT.SPONSOR_ASSIGNMENT_ID
, -1 ) DLGT_BKG_SPONSOR_ASSIGNMENT_ID
, DBT.SUCCESSFUL_ATTENDANCE_FLAG SCCSSFL_ATTENDANCE_FLAG_CODE
, DBT.INTERNAL_BOOKING_FLAG DLGT_BKG_INTERNAL_BOOKING_FLAG
, DBT.BOOKING_PRIORITY DLGT_BKG_BOOKING_PRIORITY_CODE
, NVL( BST.BOOKING_STATUS_TYPE_ID
, -1 ) BOOKING_STATUS_TYPE_ID
, BST.TYPE BOOKING_STATUS_TYPE_CODE
, BST.PLACE_USED_FLAG BST_PLACE_USED_FLAG_CODE
, 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
, GREATEST( ASG.LAST_UPDATE_DATE
, AST.LAST_UPDATE_DATE
, DBT.LAST_UPDATE_DATE
, BST.LAST_UPDATE_DATE ) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, OTA_DELEGATE_BOOKINGS DBT
, OTA_BOOKING_STATUS_TYPES BST
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE 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 ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND DBT.DATE_BOOKING_PLACED BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND NOT EXISTS ( SELECT 'X'
FROM OTA_PROGRAM_MEMBERSHIPS PRG
WHERE DBT.EVENT_ID = PRG.EVENT_ID ) WITH READ ONLY