Query: Events and Booking

select
    hr.meaning                                              "Type",
    pe.date_start                                           "Date:Start",
    pe.date_end                                             "Date: End",
    pe.time_start                                           "Time:Start",
    pe.time_end                                             "Time:End",
    hotl.name                                               "Org Run By",
    hltl.location_code                                      "Location",
    hl.timezone_code                                        "TimeZone",
    pe.external_contact                                     "External Contact: Name",
    pe.contact_telephone_number                             "External contact:Telephone",
    pap.full_name                                           "Internal Contact:Name",
    nvl (pap.employee_number, pap.npw_number)               "Internal Contact:Number",
    (select full_name
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Bookings Name",
    (select employee_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Worker Number",
    (select applicant_number
        from per_all_people_f
        where person_id = pb.person_id
        and sysdate between effective_start_date and effective_end_date)        "Applicant Number",
    --
    pe.event_id,
    pe.business_group_id,
    pe.assignment_id
    --
from
    per_events pe,
    per_bookings pb,
    per_all_people_f pap,
    hr_lookups hr,
    hr_locations_all_tl hltl,
    hr_locations_no_join hl,
    hr_all_organization_units ho,
    hr_all_organization_units_tl hotl
where  
    1=1
and ho.organization_id = hotl.organization_id(+)
and pe.organization_run_by_id = ho.organization_id(+)
and pe.event_or_interview = 'E'
and pe.type = hr.lookup_code
and pe.event_id=pb.event_id
and hr.lookup_type =decode (pe.emp_or_apl, 'E', 'EMP_EVENT_TYPE','A', 'APL_EVENT_TYPE')
and pe.location_id = hl.location_id(+)
and pe.internal_contact_person_id = pap.person_id(+)
and pe.date_start between nvl (pap.effective_start_date, pe.date_start)
and nvl (pap.effective_end_date, pe.date_start)
and decode (hotl.organization_id, null, '1', hotl.language) = decode (hotl.organization_id, null, '1', userenv ('LANG'))
and hl.location_id = hltl.location_id(+)
and decode (hltl.location_id, null, '1', hltl.language) =decode (hltl.location_id, null, '1', userenv ('LANG'))
--
and hr.meaning = 'Orientation Day'
order by
    1,2,3,4,5