BIP – Query to extract Course details with offering and activitiy details

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