OA Framework VO Extension in Oracle Learning Management

Create New VO Object - Step 5 - Query

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