BIP – Specialization Details

In Oracle Learning Cloud, a specialization can be created as a combination of multiple sections with each section comprising of multiple courses.

A learning admin can setup the specialization by navigating to My Client Groups -> Learning -> Learning Catalog -> Specializations:

Example:

Use the below query to get the above details:

WITH specialization as
(SELECT
      LearningItemDEO.LEARNING_ITEM_ID,              
      LearningItemDEO.EFFECTIVE_START_DATE SPESD,         
      LearningItemDEO.EFFECTIVE_END_DATE SPEED,           
      LearningItemDEO.LEARNING_ITEM_TYPE SPLIT,
      LearningItemDEO.LEARNING_ITEM_SUB_TYPE SPLIST,          
      LearningItemDEO.LEARNING_ITEM_NUMBER,         
      LearningItemDEO.STATUS SP_STATUS,                    
      LearningItemDEO.START_DATE SPSD,                   
      LearningItemDEO.END_DATE SPED,                               
      to_date(TO_CHAR( LearningItemDEO.CREATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS SPCreationDate,                
      LearningItemTranslationDEO.NAME,
      LearningItemDEO.LI_START_DATE as SP_LiStartDate,
      LearningItemDEO.LI_END_DATE as SP_LiEndDate
 FROM WLF_LEARNING_ITEMS_F LearningItemDEO,
      WLF_LEARNING_ITEMS_F_TL LearningItemTranslationDEO
WHERE LearningItemDEO.LEARNING_ITEM_TYPE IN ('ORA_SPECIALIZATION')
  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')
  --AND LearningItemTranslationDEO.NAME LIKE ('Working From Home Guidelines and Tips')
 )
, Section 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,                    
        LearningItemDEO.START_DATE,                   
        LearningItemDEO.END_DATE,                                
        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_SPECL_SECTION')
    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')
 )
, 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
      ,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')
      ,WLFT.NAME
	  ,WLCF.LEARNING_ITEM_ID WLCF_LEARNING_ITEM_ID
 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 specialization.name 						"Specialization Name"
      ,specialization.LEARNING_ITEM_NUMBER		"Specialization Number"
	  ,Section.name								"Section Name"
	  ,Section.LEARNING_ITEM_NUMBER				"Section Number"
	  ,WLF_LI_HIERARCHIES_F_SS.position			"Section Position"
	  ,course.name								"Course Name"
	  ,course.learning_item_number				"Course Number"
	  ,WLF_LI_HIERARCHIES_F_SC.HIERARCHY_NUMBER	"Activity Number"
	  ,WLF_LI_HIERARCHIES_F_SC.position			"Course Position"
      ,offering.name							"Offering Name"
      ,offering.LEARNING_ITEM_NUMBER			"Offering Number"
      ,offering.DELIVERY_MODE					"Offering Delivery Mode"
      ,offering.STATUS							"Offering Status"
  FROM WLF_LI_HIERARCHIES_F WLF_LI_HIERARCHIES_F_SS, 
	   WLF_LI_HIERARCHIES_F WLF_LI_HIERARCHIES_F_SC, 
       specialization, 
	   section,
	   course,
	   offering
 WHERE specialization.name =  'Essential Worker'
   AND WLF_LI_HIERARCHIES_F_SS.LEARNING_ITEM_ID = specialization.LEARNING_ITEM_ID
   AND WLF_LI_HIERARCHIES_F_SS.CHILD_LEARNING_ITEM_ID = section.LEARNING_ITEM_ID
   AND TRUNC(SYSDATE) BETWEEN WLF_LI_HIERARCHIES_F_SS.EFFECTIVE_START_DATE AND WLF_LI_HIERARCHIES_F_SS.EFFECTIVE_END_DATE
   AND WLF_LI_HIERARCHIES_F_SC.LEARNING_ITEM_ID = section.LEARNING_ITEM_ID
   AND WLF_LI_HIERARCHIES_F_SC.CHILD_LEARNING_ITEM_ID = course.LEARNING_ITEM_ID
   AND TRUNC(SYSDATE) BETWEEN WLF_LI_HIERARCHIES_F_SC.EFFECTIVE_START_DATE AND WLF_LI_HIERARCHIES_F_SC.EFFECTIVE_END_DATE
   AND course.LEARNING_ITEM_ID = offering.WLCF_LEARNING_ITEM_ID
ORDER BY specialization.name,WLF_LI_HIERARCHIES_F_SS.position,WLF_LI_HIERARCHIES_F_SC.position