In this article, we will look into a query to fetch the Learner course completion details in Oracle Learning Cloud.
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 LinkedIn, Facebook, and Twitter to get updated with the latest content.