Oracle Analytics and AI

Rosita Santosuosso

Summary:

Hello experts,

We are using WLF_ASSIGNMENT_TASKS_F to fetch the data from the learn tables

The

WLF_ASSIGNMENT_TASKS_F table, in the LAST_UPDATED_BY field, doesn't reflect who actually updates the task. I've attached the query used.

Can you please help me?

Content (please ensure you mask any confidential information):

CenterLargeAccessibilityDelete

what we see in the screenshot above is different from what we see in the screenshot below

CenterLargeAccessibilityDelete

Version (include the version you are using, if applicable):

26B 

Code Snippet (add any code snippets that support your topic, if applicable):

SELECT DISTINCT 'ASSIGNMENTS_GROUP' group_type-- ,ra.username COMMENTED -,--course assignment detailsac.assignment_record_id AS course_lr_id,ac.assignment_record_number AS course_lr_number,ac.pre_assignment_record_id AS course_pre_assignmnt_rec_id,ac.succ_assignment_record_id AS course_succ_assignmnt_rec_id,lc.learning_item_number AS course_li_number,lc.name AS course_title,ac.STATUS AS course_lr_status1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',ac.STATUS) AS course_lr_status -- ADDED -,ac.sub_status AS course_lr_sub_status1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',ac.sub_status) AS course_lr_sub_status -- ADDED -,ac.assigned_on_date AS course_lr_start_date,ac.completion_date AS course_lr_completion_date,ac.expiration_date AS course_lr_expiration_date,ac.calculated_due_date AS course_lr_due_date,ac.total_actual_effort AS course_lr_actual_effort1 -- COMMENTED -,LPAD(TRUNC(ac.total_actual_effort * 3600 / 3600), 2, '0') || ':' || LPAD(TRUNC(MOD(ac.total_actual_effort * 3600, 3600) / 60), 2, '0') || ':' || LPAD(MOD(TRUNC(ac.total_actual_effort * 3600), 60), 2, '0') AS course_lr_actual_effort -- ADDED -,ac.event_type AS course_lr_event_type1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_EVENT_TYPE',ac.event_type) AS course_lr_event_type -- ADDED -,ac.date_status_changed AS course_lr_date_status_changed,ac.creation_date AS course_lr_creation_date,ac.last_update_date AS course_lr_last_update_date,ac.created_by AS course_lr_created_by,ac.last_updated_by AS course_lr_last_udpated_by,--offering assignment detailsao.assignment_record_id AS offering_lr_id,ao.assignment_record_number AS offering_lr_number,lo.learning_item_number AS offering_li_number,lo.name AS offering_title,ao.assigned_on_date AS offering_lr_start_date,ao.completion_date AS offering_lr_completion_date,ao.expiration_date AS offering_lr_expiration_date,ao.calculated_due_date AS offering_lr_due_date,ao.total_actual_effort AS offering_lr_actual_effort1 -- COMMENTED -,LPAD(TRUNC(ao.total_actual_effort * 3600 / 3600), 2, '0') || ':' || LPAD(TRUNC(MOD(ao.total_actual_effort * 3600, 3600) / 60), 2, '0') || ':' || LPAD(MOD(TRUNC(ao.total_actual_effort * 3600), 60), 2, '0') AS offering_lr_actual_effort -- ADDED -,ao.STATUS AS offering_lr_status1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',ao.STATUS) AS offering_lr_status -- ADDED -,ao.sub_status AS offering_lr_sub_status1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_ASSIGN_RECORD_STATUS',ao.sub_status) AS offering_lr_sub_status -- ADDED -,ao.creation_date AS offering_lr_creation_date,ao.last_update_date AS offering_lr_last_update_date,ao.created_by AS offering_lr_created_by,ao.last_updated_by AS offering_lr_last_udpated_by,--assignment task detailsat.assignment_task_id AS task_id,at.last_update_date AS task_last_updated_date,at.completion_date AS task_completion_date,at.task_status AS task_status1 -- COMMENTED -,at.task_sub_status AS task_sub_status1 -- COMMENTED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_LI_ASSIGN_TASK_STATUS',at.task_status) AS task_status -- ADDED -,PER_EXTRACT_UTILITY.GET_DECODED_LOOKUP('ORA_WLF_LI_TASK_SUB_STATUS',at.task_sub_status) AS task_sub_status -- ADDED -,at.effort AS task_actual_effort1 -- COMMENTED -,LPAD(TRUNC(at.effort * 3600 / 3600), 2, '0') || ':' || LPAD(TRUNC(MOD(at.effort * 3600, 3600) / 60), 2, '0') || ':' || LPAD(MOD(TRUNC(at.effort * 3600), 60), 2, '0') AS task_actual_effort -- ADDED -,at.score AS task_score,at.creation_date AS task_creation_date,at.last_update_date AS task_last_update_date,at.created_by AS task_created_by,at.last_updated_by AS task_udpated_by,--offering activity detailsli_activity.learning_item_number AS activity_li_number,li_activity.name AS activity_title,ra.content_learning_item_number AS root_content_li_number,ra.content_learning_item_name AS root_content_learning_item_name,ra.playable_items_count,decode(ra.content_item_type, 'ORA_SCORM_2004', 'SCORM 2004', 'ORA_SCORM_12', 'SCORM 1.2', 'ORA_HACP', 'HACP', ra.content_item_type) AS content_type,--root attempt detailsra.root_attempt_id,ra.root_attempt_order,ra.root_learning_item_id,ra.root_attempt_creation_date,ra.root_attempt_last_update_date,ra.root_attempt_completion_date,ra.root_attempt_end_date,ra.root_attempt_completion_status,ra.root_event_id,ra.created_by AS root_attempt_created_by,ra.last_updated_by AS root_attempt_last_updated_by,ao.EFFORT_UOM ao_EFFORT_UOM,ac.EFFORT_UOM ac_EFFORT_UOMFROM (SELECT DENSE_RANK() OVER (ORDER BY root_attempt_id) AS root_attempt_order,root_attempt_id,learning_item_id AS root_learning_item_id,learning_item_number AS content_learning_item_number,name AS content_learning_item_name-- ,username COMMENTED -,content_item_type,user_learner_id,playable_items_count,root_attempt_creation_date,root_attempt_last_update_date,root_attempt_completion_date,root_attempt_end_date,root_attempt_completion_status,root_event_id,created_by,last_updated_byFROM (SELECT a.root_attempt_id,a.event_id root_event_id,a.creation_date AS root_attempt_creation_date,a.last_update_date AS root_attempt_last_update_date,a.completion_date AS root_attempt_completion_date,a.attempt_end_date AS root_attempt_end_date,decode(a.completion_status, 'C', 'Completed', 'P', 'Passed', 'I', 'Incomplete', 'F', 'Failed', 'U', 'Unknown', 'N', 'Not attempted', a.completion_status) AS root_attempt_completion_status,e.learning_item_id,l.learning_item_number,l.name--,upper(u.username) AS username COMMENTED -,lic.tracking_type AS content_item_type--,u.person_id AS user_learner_id COMMENTED -,papf.person_id AS user_learner_id,lic.playable_items_count,a.created_by,a.last_updated_byFROM fusion.wlf_events eINNER JOIN fusion.wlf_event_attempts a ON e.event_id = a.event_idINNER JOIN fusion.wlf_learning_items_f_vl l ON e.learning_item_id = l.learning_item_id--INNER JOIN fusion.per_users u ON e.created_by_id = u.person_id COMMENTED -INNER JOIN fusion.wlf_li_content_f lic ON l.learning_item_id = lic.learning_item_idINNER JOIN fusion.PER_ALL_PEOPLE_F papf ON e.created_by_id = papf.person_id -- ADDED --- INNER JOIN PER_ALL_ASSIGNMENTS_M PAAM ON e.created_by_id = PAAM.PERSON_ID -- ADDED --- INNER JOIN HR_ORGANIZATION_UNITS_F_TL HOUFT ON HOUFT.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID -- ADDED -WHERE a.event_attempt_id = a.root_attempt_id--condition to filter preview attemptsAND e.event_type = 'ORA_LI_ATTEMPT'AND trunc(sysdate) BETWEEN l.effective_start_dateAND l.effective_end_date-- AND upper(l.learning_item_number) = nvl(upper(:pcontentlearningitemnumber), upper(l.learning_item_number)) COMMENTED --- AND upper(u.username) = nvl(upper(:pusername), upper(u.username)) COMMENTED -/*ADDED STARTS - *//AND PAAM.PRIMARY_FLAG = 'Y'AND PAAM.ASSIGNMENT_TYPE = 'E'AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATEAND PAAM.EFFECTIVE_END_DATEAND HOUFT.LANGUAGE = 'US'AND TRUNC(SYSDATE) BETWEEN HOUFT.EFFECTIVE_START_DATEAND HOUFT.EFFECTIVE_END_DATE/AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATEAND PAPF.EFFECTIVE_END_DATEAND (PAPF.PERSON_NUMBER IN (:PERSON_NUMBER)OR 'All' IN (:PERSON_NUMBER || 'All'))AND e.created_by_id IN (SELECT PPNF.PERSON_IDFROM PER_PERSON_NAMES_F PPNFWHERE TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATEAND PPNF.EFFECTIVE_END_DATEAND PPNF.NAME_TYPE = 'GLOBAL'AND (PPNF.DISPLAY_NAME IN (:PERSON_NAME)OR 'All' IN (:PERSON_NAME || 'All')))AND e.created_by_id IN (SELECT PNI.PERSON_IDFROM PER_NATIONAL_IDENTIFIERS PNI,PER_ALL_PEOPLE_F PAPFWHERE PAPF.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_IDAND (PNI.NATIONAL_IDENTIFIER_NUMBER IN (:NID)OR 'All' IN (:NID || 'All')))/AND (houft.NAME IN (:LEGAL_ENTITY)OR 'All' IN (:LEGAL_ENTITY || 'All'))//*ADDED ENDS - */ORDER BY root_attempt_id ASC)) raINNER JOIN fusion.wlf_li_activities_f la ON ra.root_learning_item_id = la.related_content_idINNER JOIN fusion.wlf_learning_items_f_vl li_activity ON la.learning_item_id = li_activity.learning_item_idINNER JOIN fusion.wlf_assignment_tasks_f at ON li_activity.learning_item_id = at.learning_item_idAND at.source_object_id = ra.root_event_idINNER JOIN fusion.wlf_assignment_records_f ao ON at.assignment_record_id = ao.assignment_record_idINNER JOIN fusion.wlf_ar_relations_f ar ON ao.assignment_record_id = ar.assignment_record_idINNER JOIN fusion.wlf_assignment_records_f ac ON ar.related_object_id = ac.assignment_record_idAND ra.user_learner_id = ac.learner_idINNER JOIN fusion.wlf_learning_items_f_vl lc ON ac.learning_item_id = lc.learning_item_idINNER JOIN fusion.wlf_learning_items_f_vl lo ON ao.learning_item_id = lo.learning_item_idWHERE -- consider only content of type SCORMra.content_item_type IN ('ORA_SCORM_12','ORA_SCORM_2004','ORA_HACP')AND trunc(sysdate) BETWEEN at.effective_start_dateAND at.effective_end_dateAND trunc(sysdate) BETWEEN la.effective_start_dateAND la.effective_end_dateAND trunc(sysdate) BETWEEN li_activity.effective_start_dateAND li_activity.effective_end_dateAND trunc(sysdate) BETWEEN lo.effective_start_dateAND lo.effective_end_dateAND trunc(sysdate) BETWEEN lc.effective_start_dateAND lc.effective_end_dateAND trunc(sysdate) BETWEEN ar.effective_start_dateAND ar.effective_end_dateAND trunc(sysdate) BETWEEN ac.effective_start_dateAND ac.effective_end_dateAND trunc(sysdate) BETWEEN ao.effective_start_dateAND ao.effective_end_date/ADDED - STARTS/AND (/*li.name IN (:OFFERING_NAME)*/lo.name IN (:OFFERING_NAME)OR 'All' IN (:OFFERING_NAME || 'All'))AND (/*li.learning_item_number IN (:OFFERING_NUMBER)*/lo.learning_item_number IN (:OFFERING_NUMBER)OR 'All' IN (:OFFERING_NUMBER || 'All'))/ADDED - ENDS/ORDER BY root_attempt_order,ac.assignment_record_id,ao.assignment_record_id,at.assignment_task_id

MandeepGupta

Hi Rosita,

The screen shots are missing in your post. Last updates by is not the right field to get the data that you are looking for as the table can be updated by multiple people.

Please share appropriate navigation and screen shots.

Thanks.