BIP – Query to Extract Learner Completion Details

Use below query to extract the learner completion details. The same query can be used to generate the Learning Record HDL format with small changes.

SELECT TO_CHAR(warf.EFFECTIVE_START_DATE,'RRRR/MM/DD') "Learning Record EffectiveStartDate"
	   ,warf.ASSIGNMENT_RECORD_NUMBER "Learning Record Number"
	   ,weaf.ASSIGNMENT_PROFILE_NUMBER "Assignment Number"
	   ,wlif.LEARNING_ITEM_TYPE "Learning ItemType"
	   ,wlif.LEARNING_ITEM_NUMBER "Learning ItemNumber"
	   ,warf.EVENT_TYPE "Assignment Type"
	   ,warf.EVENT_SUB_TYPE "Assignment SubType"
	   ,warf.ATTRIBUTION_TYPE "Assignment AttributionType"
	   ,warf.ATTRIBUTION_LOOKUP_CODE "Assignment AttributionCode"
       ,papf.PERSON_NUMBER "Learner Number"
	   ,warf.STATUS "Learning RecordStatus"
       ,TO_CHAR(NVL(warf.ASSIGNMENT_STARTED_ON_DATE,warf.EFFECTIVE_START_DATE),'RRRR/MM/DD') "Learning Record StartDate"
       ,TO_CHAR(warf.CALCULATED_DUE_DATE,'RRRR/MM/DD') "Due Date"	   
       ,TO_CHAR(warf.COMPLETION_DATE,'RRRR/MM/DD') "Completion Date"	   
       ,warf.ACTUAL_SCORE "Actual Score"
  FROM WLF_LEARNING_ITEMS_F wlif,
       WLF_ASSIGNMENT_RECORDS_F warf,
       WLF_EVENT_ASSIGNMENTS_F weaf,
       PER_ALL_PEOPLE_F papf	   
 WHERE to_char(warf.LEARNING_ITEM_ID) = to_char(wlif.LEARNING_ITEM_ID)
   AND warf.STATUS = 'ORA_ASSN_REC_COMPLETE'
   AND TRUNC(SYSDATE) BETWEEN warf.EFFECTIVE_START_DATE AND warf.EFFECTIVE_END_DATE
   AND TRUNC(SYSDATE) BETWEEN wlif.EFFECTIVE_START_DATE AND wlif.EFFECTIVE_END_DATE
   AND warf.learner_id = papf.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   AND weaf.EVENT_ASSIGNMENT_ID = warf.EVENT_ASSIGNMENT_ID