Reports (BIP) – Query to extract default Access Group data for a Course/Offering

In Oracle Learning cloud, Learning Admin can define the Default access at Course or offering level. Course.dat can be used to load this information.

In order to extract this information from DB in HDL format, please use below query:

SELECT 'MERGE' 	                                           "METADATA"       
      ,'CourseDefaultAccess' 		                   "CourseDefaultAccess"                  
      ,to_char(wapf.effective_start_date,'RRRR/MM/DD')     "EffectiveStartDate"           
      ,to_char(wapf.effective_end_date,'RRRR/MM/DD')       "EffectiveEndDate"       
      ,wapf.access_permission_number                       "DefaultAccessNumber"       
      ,wlif.learning_item_number                           "CourseNumber"       
      ,wapf.follow_spec 	                           "FollowSpecialization"       
      ,wapf.assign_as_manager                		   "MgrInitialStatus"       
      ,wapf.assign_for_self                                "SelfInitialStatus"        
      ,wlif.ss_view_mode                                   "SelfViewMode"    
 FROM WLF_ACCESS_PERMISSIONS_F wapf      
     ,WLF_LEARNING_ITEMS_F wlif 
WHERE 1=1   
  AND wapf.access_permission_id= wlif.access_permission_id   
--AND trunc(sysdate) between wapf.effective_start_date and wapf.effective_end_date   
  AND trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date   
  AND wlif.learning_item_number = 'OLC251051'

Also, learners can be assigned using Access Groups.

To extract this information below SQL can be used:

SELECT wlif.learning_item_id
      ,wlif.learning_item_number course_Number
      ,wlif_tl.name course_Name
      ,wlif_tl.description_short description
      ,wlif_ag.learning_item_number access_group_number
      ,wlif_tl_ag.name access_group_name
  FROM wlf_learning_items_f 	wlif
      ,wlf_learning_items_f_tl 	wlif_tl
      ,wlf_learning_items_f 	wlif_ag
      ,wlf_learning_items_f_tl 	wlif_tl_ag
      ,wlf_li_ag_relations_f 	wlarf
 WHERE 1=1
   AND wlif_tl.learning_item_id 		= wlif.learning_item_id
   AND wlif_tl.name 				= 'Test 1234'
   AND wlif_ag.learning_item_type 		= 'ORA_ACCESS_GROUP'
   AND wlif_tl_ag.learning_item_id 		= wlif_ag.learning_item_id
   AND wlarf.access_learning_item_id 	= wlif_ag.learning_item_id
   AND wlarf.catalog_learning_item_id 	= wlif.learning_item_id   
   AND TRUNC(SYSDATE) BETWEEN wlif_tl_ag.effective_start_date AND wlif_tl_ag.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif_ag.effective_start_date AND wlif_ag.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif_tl.effective_start_date AND wlif_tl.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date AND wlif.effective_end_date