• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:November 30, 2021
  • Reading time:4 mins read
You are currently viewing SQL Query to fetch the Learner Course Progress Report
SQL Query to fetch the Learner Course Progress Report
- SQL Query to fetch the Learner Course Progress Report
Total Visits: 14

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.

If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.