SQL Query to fetch the Learner Course Progress Report

In this article, we will look into a query to fetch the Learner course completion details in Oracle Learning Cloud.

SQL Query:

SELECT distinct 
       papf.PERSON_NUMBER "Learner Number"
	   ,ppnf_ler.full_name "Learner Name"
	   ,ppnf_mgr.full_name "Manager Name"
	   ,wlif_tl.name "Course Name"
	   ,(case when wlif.LEARNING_ITEM_TYPE = 'ORA_COURSE' then 'Course'
	         when wlif.LEARNING_ITEM_TYPE = 'ORA_CLASS' then 'Offering'
			 else wlif.LEARNING_ITEM_TYPE END) "Course Type"
,(case when
	   PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.STATUS) like 'Pending%' then 
	   PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.STATUS)
	   else PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',WARF.SUB_STATUS)
	   end) 	"Current Status"   
	   ,pjftl.job_family_name "Job Family"
	   ,per_extract_utility.get_decoded_lookup('JOB_FUNCTION_CODE', pjf.JOB_FUNCTION_CODE) "Job Function"
       ,TO_CHAR(warf.CALCULATED_DUE_DATE,'YYYY/MM/DD') "Due Date"	   
       ,TO_CHAR(warf.COMPLETION_DATE,'YYYY/MM/DD') "Completion Date"	   
       ,warf.ACTUAL_SCORE "Actual Score"
	   ,TO_CHAR(warf.EFFECTIVE_START_DATE,'YYYY/MM/DD') "Learning Record StartDate"
	   ,TO_CHAR(warf.creation_date,'YYYY/MM/DD') "Registration Date"
	   ,NVL(ppnf.full_name,pu.username) "Registered By"
  FROM WLF_LEARNING_ITEMS_F wlif,
       WLF_LEARNING_ITEMS_F_TL wlif_tl,
       WLF_ASSIGNMENT_RECORDS_F warf,
       PER_ALL_PEOPLE_F papf,
       per_users pu,
       per_person_names_f ppnf,
	   per_person_names_f ppnf_ler,
	   per_person_names_f ppnf_mgr,
	   PER_ALL_ASSIGNMENTS_M PAAM,
	   PER_ASSIGNMENT_SUPERVISORS_F PASF,
	   per_jobs_f pjf,
	   per_job_family_f_tl pjftl
 WHERE to_char(warf.LEARNING_ITEM_ID) = to_char(wlif.LEARNING_ITEM_ID)
 and wlif_tl.LEARNING_ITEM_ID = wlif.LEARNING_ITEM_ID
 AND wlif_tl.LANGUAGE = USERENV('lang')
   AND sysdate BETWEEN wlif_tl.EFFECTIVE_START_DATE AND wlif_tl.EFFECTIVE_END_DATE
   AND sysdate BETWEEN warf.EFFECTIVE_START_DATE AND warf.EFFECTIVE_END_DATE
   AND sysdate BETWEEN wlif.EFFECTIVE_START_DATE AND wlif.EFFECTIVE_END_DATE
   AND warf.learner_id = papf.person_id
   AND sysdate BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   and pu.username(+) = warf.created_by
   and pu.person_id = ppnf.person_id(+)
   and ppnf.name_type(+) = 'GLOBAL'
   and sysdate BETWEEN ppnf.EFFECTIVE_START_DATE(+) AND ppnf.EFFECTIVE_END_DATE(+)
   and papf.person_id = ppnf_ler.person_id
   and ppnf_ler.name_type = 'GLOBAL'
   and sysdate BETWEEN ppnf_ler.EFFECTIVE_START_DATE AND ppnf_ler.EFFECTIVE_END_DATE
   and paam.person_id = papf.person_id
   and paam.primary_flag = 'Y'
   and paam.effective_latest_change = 'Y'
   and paam.assignment_type = 'E'
   and sysdate between paam.effective_start_date and paam.effective_end_date
   and pjf.job_id(+) = paam.job_id
   and sysdate between pjf.effective_start_date(+) and pjf.effective_end_date(+)
   AND pjftl.job_family_id( + ) = pjf.job_family_id
   AND pjftl.language( + ) = 'US'
   AND sysdate BETWEEN pjftl.effective_start_date( + ) AND pjftl.effective_end_date( + )   
   and pasf.person_id(+) = papf.person_id
   and PASF.MANAGER_TYPE(+) = 'LINE_MANAGER'
   AND sysdate BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)
   and PASF.MANAGER_ID = ppnf_mgr.person_id(+)
   and ppnf_mgr.name_type(+) = 'GLOBAL'
   and sysdate BETWEEN ppnf_mgr.EFFECTIVE_START_DATE(+) AND ppnf_mgr.EFFECTIVE_END_DATE(+)
order by papf.PERSON_NUMBER

The above query pulls the Learner Info, course/offering details, manager details, course registration details and finally the assignment details.