“Completed by” field in our Learning completion audit report is not returning any records for bypass completions.
Request from team was to update report with correct value for ‘completed by’ field, oracle support told us to utilize WLF_LI_ACTIVITY_DETAILS_V table however we are not getting any records returned now due to there being no link between offering and activity table.
In our testing this is what we found.
Is there a table where we can retrieve who completed the course?
The safety meeting course has no offering/activity information or completions at the activity level it only has bypass completions for the course, but the phishing course does have offering/activity information and completions at the activity level. So, this means the report is not returning any Bypass Completion status at the course level when that table is used.
is there a table that returns both completion and bypass completion information?
Sql below:
/*****************************************************************
OBJECT NAME: Learning Completion Audit Report
DESCRIPTION: Report will return all employees and course details which are completed between dates given at run time.
Version Name Date Description
---------------------------------------------------------------------------------------------------------------------
<1.0> Neminath 28-06-2022 Initial Creation
**********************************************************************************************************************/
SELECT PAPF.PERSON_NUMBER "PERSON_NUMBER"
, PPNF.LAST_NAME "LAST_NAME"
, PPNF.FIRST_NAME "FIRST_NAME"
, PPNF.MIDDLE_NAMES "MIDDLE_NAMES"
, HOUF.NAME "BUSINESS_UNIT"
, HOUF_LE.NAME "LE_NAME"
, LEAR_TYPE.MEANING "LEARNING_ITEM_TYPE"
, WLIF.LEARNING_ITEM_NUMBER "LEARNING_ITEM_NUMBER"
, WLST.NAME "LEARNING_ITEM_NAME"
, WEAF.NAME "INITIATIVE_NAME"
, TO_CHAR(WARF.ASSIGNED_ON_DATE,'YYYY/MM/DD') "ASSIGNED_ON_DATE"
, TO_CHAR(WARF.CALCULATED_DUE_DATE,'YYYY/MM/DD') "DUE_DATE"
, LEAR_ASSIGN_SUB_TYPE.MEANING "ASSIGNMENTSTATUSSUBSTATUS"
, TO_CHAR(WARF.CREATION_DATE,'YYYY/MM/DD') "ASSIGNMENT RECORD CREATION DATE"
, TO_CHAR(WARF.COMPLETION_DATE,'YYYY/MM/DD') "COMPLETIONDATE"
, WARF.STATUS_CHANGE_COMMENT
, COMPL_REASON.MEANING "COMPLETION_REASON"
, COMPL_TYPE.MEANING "COMPLETION_TYPE"
, WARF.LAST_UPDATED_BY "COMPLETED_BY"
, TO_CHAR(WARF.VALIDITY_DATE ,'YYYY/MM/DD') "VALIDITYPERIODSTARTDATE"
, TO_CHAR(WARF.EXPIRATION_DATE,'YYYY/MM/DD') "EXPIRATIONDATE"
, PPNF_ASS_RUNAS.DISPLAY_NAME "ASSIGNMENTRUNAS"
, WARF.REASON_CODE "REASON_CODE"
, WARF.STATUS_CHANGE_TYPE "STATUS_CHANGE_TYPE"
, PPNF_ASS_PER.DISPLAY_NAME "ASSIGNEDBYPERSON"
, PAPF1.PERSON_NUMBER "ASSIGNEDBYPERSONNUMBER"
, WLIF.LEARNING_ITEM_TYPE "LEARN_ITEM_TYPE_CODE"
, WARF.EVENT_TYPE "LEARNING ASSIGNMENT TYPE CODE"
, WARF.SUB_STATUS "ASSIGNMENTSTATUSSUBSTATUS CODE"
, WARF.STATUS "STATUS"
, WARF.ASSIGNMENT_RECORD_ID "ASSIGNMENT_RECORD_ID"
, WARF.ASSIGNMENT_RECORD_NUMBER "ASSIGNMENT_RECORD_NUMBER"
, WLIF.LEARNING_ITEM_NUMBER "LEARNING_RECORD_NUMBER"
,WAD.learner_id
FROM
WLF_ASSIGNMENT_RECORDS_F WARF,
WLF_LI_ACTIVITY_DETAILS_V WAD,
--PER_PERSON_SECURED_LIST_V
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
WLF_LEARNING_ITEMS_F_TL WLST,
WLF_LEARNING_ITEMS_F WLIF,
WLF_EVENT_ASSIGNMENTS_F_TL WEAF,
PER_PERSON_NAMES_F PPNF,
PER_PERSON_NAMES_F PPNF_ASS_RUNAS,
PER_PERSON_NAMES_F PPNF_ASS_PER,
PER_ALL_PEOPLE_F PAPF1,
HR_ORGANIZATION_UNITS_F_TL HOUF,
HR_ORG_UNIT_CLASSIFICATIONS_F HOUC,
HR_ORGANIZATION_UNITS_F_TL HOUF_LE,
HR_ORG_UNIT_CLASSIFICATIONS_F HOUC_LE,
WLF_EVENTS WE,
WLF_EVENT_ASSIGNMENTS_F WEF,
PER_JOBS_F_TL JOB,
(SELECT LOOKUP2.MEANING, LOOKUP2.LOOKUP_CODE
FROM FND_LOOKUP_VALUES_TL LOOKUP2
WHERE LOOKUP2.LOOKUP_TYPE = 'ORA_WLF_COMPLT_RSN_SPEC'
AND LOOKUP2.LANGUAGE='US') COMPL_REASON,
(SELECT LOOKUP3.MEANING, LOOKUP3.LOOKUP_CODE
FROM FND_LOOKUP_VALUES_TL LOOKUP3
WHERE LOOKUP3.LOOKUP_TYPE = 'ORA_WLF_LEARNING_ITEM_TYPE'
AND LOOKUP3.LANGUAGE='US') LEAR_TYPE,
(SELECT LOOKUP4.MEANING, LOOKUP4.LOOKUP_CODE
FROM FND_LOOKUP_VALUES_TL LOOKUP4
WHERE LOOKUP4.LOOKUP_TYPE = 'ORA_WLF_REC_STATUS_CHANGE_TYPE'
AND LOOKUP4.LANGUAGE='US') COMPL_TYPE,
(SELECT LOOKUP5.MEANING, LOOKUP5.LOOKUP_CODE
FROM FND_LOOKUP_VALUES_TL LOOKUP5
WHERE LOOKUP5.LOOKUP_TYPE = 'ORA_WLF_ASSIGN_RECORD_STATUS'
AND LOOKUP5.LANGUAGE='US') LEAR_ASSIGN_SUB_TYPE
WHERE
PAPF.PERSON_ID=WARF.LEARNER_ID
AND ((COALESCE(NULL, :P_PERSON_NUMBER) IS NULL) OR (PAPF.PERSON_NUMBER IN (:P_PERSON_NUMBER)))
AND ((COALESCE(NULL, :P_PERSON_NAME) IS NULL) OR (PPNF.DISPLAY_NAME IN (:P_PERSON_NAME)))
AND ((COALESCE(NULL, :P_COURSE_NAME) IS NULL) OR (WLST.NAME IN (:P_COURSE_NAME)))
AND ((COALESCE(NULL, :P_COURSE_NUMBER) IS NULL) OR (WLIF.LEARNING_ITEM_NUMBER IN (:P_COURSE_NUMBER)))
AND ((COALESCE(NULL, :P_COURSE_TYPE) IS NULL) OR (LEAR_TYPE.MEANING IN (:P_COURSE_TYPE)))
AND WARF.COMPLETION_DATE BETWEEN NVL(:P_START_DATE,'1951/01/01') AND NVL(:P_END_DATE,'4712/12/31')
AND PAPF.PERSON_ID=PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND WARF.LEARNING_ITEM_ID=WLIF.LEARNING_ITEM_ID
AND WARF.LEARNING_ITEM_ID=WLST.LEARNING_ITEM_ID
AND WARF.EVENT_ASSIGNMENT_ID=WEAF.EVENT_ASSIGNMENT_ID
AND WEAF.LANGUAGE='US'
AND PAAM.JOB_ID = JOB.JOB_ID
AND JOB.LANGUAGE = 'US'
AND PAAM.SYSTEM_PERSON_TYPE = 'EMP'
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 SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN HOUC.EFFECTIVE_START_DATE(+) AND HOUC.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN HOUC_LE.EFFECTIVE_START_DATE(+) AND HOUC_LE.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN WEF.EFFECTIVE_START_DATE AND WEF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNF_ASS_RUNAS.EFFECTIVE_START_DATE(+) AND PPNF_ASS_RUNAS.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN PPNF_ASS_PER.EFFECTIVE_START_DATE(+) AND PPNF_ASS_PER.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN JOB.EFFECTIVE_START_DATE AND JOB.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN WEF.EFFECTIVE_START_DATE AND WEF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE(+) AND PAPF1.EFFECTIVE_END_DATE(+)
AND SYSDATE BETWEEN WEAF.EFFECTIVE_START_DATE AND WEAF.EFFECTIVE_END_DATE
AND WLST.LAST_UPDATE_DATE BETWEEN WLST.EFFECTIVE_START_DATE AND WLST.effective_end_date
AND WLST.LANGUAGE = 'US'
AND WLST.SOURCE_LANG='US'
AND PAPF.PERSON_ID=PPNF.PERSON_ID
AND PPNF.NAME_TYPE='GLOBAL'
AND PAAM.BUSINESS_UNIT_ID = HOUF.ORGANIZATION_ID (+)
AND HOUF.ORGANIZATION_ID=HOUC.ORGANIZATION_ID(+)
AND HOUF.LANGUAGE(+)='US'
AND HOUC.CLASSIFICATION_CODE(+) = 'FUN_BUSINESS_UNIT'
AND PAAM.LEGAL_ENTITY_ID = HOUF_LE.ORGANIZATION_ID(+)
AND HOUF_LE.ORGANIZATION_ID=HOUC_LE.ORGANIZATION_ID(+)
AND HOUF_LE.LANGUAGE(+)='US'
AND HOUC_LE.CLASSIFICATION_CODE(+) = 'HCM_LEMP'
AND WARF.REASON_CODE = COMPL_REASON.LOOKUP_CODE(+)
AND WEF.RUN_AS_ID = PPNF_ASS_RUNAS.PERSON_ID(+)
AND PPNF_ASS_RUNAS.NAME_TYPE(+) ='GLOBAL'
AND WARF.LEARNING_ITEM_ID=WE.LEARNING_ITEM_ID
AND WEF.EVENT_ASSIGNMENT_ID=WARF.EVENT_ASSIGNMENT_ID
AND WEF.EVENT_ID=WE.EVENT_ID
AND WE.ATTRIBUTION_ID = PPNF_ASS_PER.PERSON_ID(+)
AND PPNF_ASS_PER.NAME_TYPE(+)='GLOBAL'
AND WE.ATTRIBUTION_ID = PAPF1.PERSON_ID(+)
AND WLIF.LEARNING_ITEM_TYPE = LEAR_TYPE.LOOKUP_CODE(+)
AND WARF.STATUS_CHANGE_TYPE = COMPL_TYPE.LOOKUP_CODE(+)
AND WARF.SUB_STATUS = LEAR_ASSIGN_SUB_TYPE.LOOKUP_CODE(+)
-- AND WLST.NAME = 'Q2 Quarterly Hands-On Driving Module'
-- AND PAPF.PERSON_NUMBER = '3003155'
AND WAD.assignment_record_id=WARF.assignment_record_id
ORDER BY PPNF.LAST_NAME,PPNF.FIRST_NAME,WLST.NAME,WARF.COMPLETION_DATE