In Step 5 enter the new query.
SELECT avt.version_name Course_Name , oft.name Offering_Name , ctt.category Delivery_Name , ota_utility.get_lang_name(ofs.language_code) Language , ent.title Class_Name , lkp.meaning Status , evt.course_start_date StartDate , evt.course_end_date EndDate , nvl(evt.course_start_time,'00:00') StartTime , nvl(evt.course_end_time,'23:59') EndTime , ftl.name Timezone , ota_general.get_location_code(evt.location_id) Location_Name , evt.actual_cost || ' ' || ota_general.fnd_currency_name(evt.budget_currency_code) cost , evt.event_type event_type , // New Code CR123 ======================== (select substr((osr.supplied_resource_name ||' | ' ||internal_address_line ||' | ' ||special_instruction) ,0 ,254 ) XXOLM_RESOURCE_VENUE_DETAILS from OTA_SUPPLIABLE_RESOURCES_V osr, ota_resource_bookings orb where 1=1 and osr.supplied_resource_id = orb.supplied_resource_id and osr.resource_type_meaning in ('Venue') and orb.display_to_learner_flag = 'Y' and orb.primary_venue_flag = 'Y' and orb.event_id = evt.event_id ) XXOLMVenue // End of New Code CR123 ================= FROM ota_events evt , ota_events_tl ent , ota_activity_versions_tl avt , ota_offerings ofs , ota_offerings_tl oft , ota_category_usages_tl ctt , hr_lookups lkp , fnd_timezones_tl ftl WHERE evt.event_id = ent.event_id AND ent.language = userenv('LANG') AND evt.activity_version_id = avt.activity_version_id AND avt.language = userenv('LANG') AND ofs.offering_id = evt.parent_offering_id AND ofs.offering_id = oft.offering_id AND oft.language = userenv('LANG') AND ofs.delivery_mode_id = ctt.category_usage_id AND ctt.language = userenv('LANG') AND lkp.lookup_type = 'SCHEDULED_EVENT_STATUS' AND lkp.lookup_code = evt.event_status AND ftl.timezone_code = evt.timezone AND ftl.language = userenv('LANG') AND evt.event_id = :1
Fig 28 - Enter New SQL |
Fig 29 - Test New SQL |