I have created the below query to get the course title , the assigned date, completion date, and the Completion score BUT no data is coming in the completion score column.
Can anyone help pls ?
SELECT DISTINCT PU.full_name "Employee Name",
papf.person_number,
WLIFT.NAME "Course Title",
LR.assigned_on_date "Assignment Date",
LR.completion_date "Competetion Date",
LR.status,
hl.meaning,
(select
distinct attempt.COMPLETION_SCORE
from fusion.WLF_EVENTS event,
fusion.WLF_EVENT_ATTEMPTS attempt
where
event.event_id = attempt.event_id
AND event.LEARNING_ITEM_ID =LI.LEARNING_ITEM_ID
AND event.created_by_id = papf.person_id) COMPLETION_SCORE
FROM wlf_assignment_records_f LR,
wlf_learning_items_f LI,
per_person_names_f PU,
wlf_learning_items_f_tl WLIFT,
hr_lookups hl,
per_all_people_f papf ,
per_all_assignments_m paam
WHERE LR.learning_item_id = LI.learning_item_id
AND PU.person_id = LR.learner_id
AND papf.person_id = PU.person_id
and paam.person_id = papf.person_id
and paam.assignment_status_type = 'ACTIVE'
and PAAM.ASSIGNMENT_TYPE NOT LIKE '%T%'
AND name_type = 'GLOBAL'
AND li.learning_item_type = 'ORA_COURSE'
AND LI.learning_item_id = WLIFT.learning_item_id
AND Trunc(sysdate) BETWEEN WLIFT.effective_start_date AND
WLIFT.effective_end_date
AND Trunc(sysdate) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(sysdate) BETWEEN paam.effective_start_date AND
paam.effective_end_date
AND Trunc(sysdate) BETWEEN pu.effective_start_date AND
pu.effective_end_date
AND Trunc(sysdate) BETWEEN lr.effective_start_date AND
lr.effective_end_date
AND Trunc(sysdate) BETWEEN li.effective_start_date AND
li.effective_end_date
AND hl.lookup_code = Lr.sub_status
AND lookup_type = 'ORA_WLF_ASSIGN_RECORD_STATUS'
ORDER BY papf.person_number