WITH course as
(
SELECT
LearningItemDEO.LEARNING_ITEM_ID,
LearningItemDEO.EFFECTIVE_START_DATE,
LearningItemDEO.EFFECTIVE_END_DATE,
LearningItemDEO.LEARNING_ITEM_TYPE,
LearningItemDEO.LEARNING_ITEM_SUB_TYPE,
LearningItemDEO.LEARNING_ITEM_NUMBER,
LearningItemDEO.STATUS,
to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS LearningItemDEOCreationDate,
LearningItemTranslationDEO.NAME,
LearningItemTranslationDEO.DESCRIPTION,
LearningItemDEO.LI_START_DATE as LearningItemDEOLiStartDate,
LearningItemDEO.LI_END_DATE as LearningItemDEOLiEndDate
FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO
WHERE LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_COURSE')
AND LearningItemDEO.LEARNING_ITEM_ID = LearningItemTranslationDEO.LEARNING_ITEM_ID
AND TRUNC(SYSDATE) BETWEEN LearningItemTranslationDEO.EFFECTIVE_START_DATE AND LearningItemTranslationDEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LearningItemDEO.EFFECTIVE_START_DATE AND LearningItemDEO.EFFECTIVE_END_DATE
AND LearningItemTranslationDEO.LANGUAGE = USERENV('lang')
)
, offering as
(
select WLFI.LEARNING_ITEM_NUMBER
,WLFI.LEARNING_ITEM_ID Offering_LII
,TO_CHAR(WLFI.EFFECTIVE_END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLFI.EFFECTIVE_START_DATE,'YYYY/MM/DD')
,WLFT.DESCRIPTION
,WLFT.DESCRIPTION_LONG
,WLCF.ENABLE_CAPACITY
,WLCF.ENABLE_WAITLIST
,WLFI.LANGUAGE_CODE
,WLFI.STATUS
,WLCLF.MAXIMUM_ATTENDEES
,WLCLF.MINIMUM_ATTENDEES
,TO_CHAR(WLCLF.ENROLMENT_END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLCLF.ENROLMENT_START_DATE,'YYYY/MM/DD')
,WLCLF.DELIVERY_MODE
,TO_CHAR(WLFI.END_DATE,'YYYY/MM/DD')
,TO_CHAR(WLFI.START_DATE,'YYYY/MM/DD') offer_start_Date
,WLFT.NAME
,WLCLF.PRIMARY_CLASSROOM_ID
,WLCF.LEARNING_ITEM_ID WLCF_LEARNING_ITEM_ID
,(SELECT DISTINCT LOCATION_CODE FROM HR_LOCATIONS_ALL WHERE LOCATION_ID = WLCLF.LOCATION_ID
AND WLFI.START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) Offer_Venue
FROM WLF_LEARNING_ITEMS_F WLFI,
WLF_LI_COURSES_F WLCF, --LEARNING_ITEM_ID course
WLF_LI_CLASSES_F WLCLF,
WLF_LEARNING_ITEMS_F_TL WLFT
WHERE WLFI.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
AND WLFI.LEARNING_ITEM_ID = WLCLF.LEARNING_ITEM_ID
AND WLCLF.COURSE_LEARNING_ITEM_ID = WLCF.LEARNING_ITEM_ID
AND WLFT.LANGUAGE = USERENV('lang')
AND TRUNC(SYSDATE) BETWEEN WLFI.EFFECTIVE_START_DATE AND WLFI.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLCF.EFFECTIVE_START_DATE AND WLCF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLCLF.EFFECTIVE_START_DATE AND WLCLF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN WLFT.EFFECTIVE_START_DATE AND WLFT.EFFECTIVE_END_DATE
AND WLFI.LEARNING_ITEM_TYPE = 'ORA_CLASS'
)
SELECT course.name "Course Name"
,course.learning_item_number "Course Number"
,offering.name "Offering Name"
,offering.LEARNING_ITEM_NUMBER "Offering Number"
,offering.offer_start_Date "Offering Start Date"
,offering.DELIVERY_MODE "Offering Delivery Mode"
,offering.STATUS "Offering Status"
,offering.Offer_Venue "Offering Venue"
,offering.PRIMARY_CLASSROOM_ID
,WLFT.name "Activity Name"
,WLFI.LEARNING_ITEM_NUMBER "Activity Number"
,WLFI.start_date
,WLFI.end_date
,wlaf.virtual_classroom_url "VirtualClassroomUrl"
,wlaf.SELF_COMPLETE_FLAG "SelfCompleteFlag"
,wlaf.TIME_ZONE "TimeZone"
,TO_CHAR(CAST(WLFI.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') activity_start_time
,TO_CHAR(CAST(WLFI.end_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') activity_end_time
,(select wrt.name
from wlf_resources_tl wrt
where language = 'US'
and wrt.resource_id = wlb.BOOKED_ITEM_ID) "Classroom Locations"
FROM course
,offering
,WLF_LI_ACTIVITIES_F wlaf
,WLF_LEARNING_ITEMS_F_TL wlft
,WLF_LEARNING_ITEMS_F wlfi
,WLF_LI_BOOKINGS wlb
WHERE 1=1
AND course.LEARNING_ITEM_ID = offering.WLCF_LEARNING_ITEM_ID
AND offering.LEARNING_ITEM_NUMBER = 'OLC133462'
AND wlaf.PARENT_LEARNING_ITEM_ID = offering.Offering_LII
AND wlaf.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
AND WLFI.LEARNING_ITEM_ID = WLFT.LEARNING_ITEM_ID
AND wlb.learning_item_id (+) = wlaf.LEARNING_ITEM_ID
AND wlb.BOOKED_ITEM_TYPE (+) = 'ORA_RESOURCE'
AND WLFT.language = 'US'
AND TRUNC(SYSDATE) BETWEEN wlaf.effective_start_date AND wlaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN WLFT.effective_start_date AND WLFT.effective_end_date
AND TRUNC(SYSDATE) BETWEEN WLFI.effective_start_date AND WLFI.effective_end_date