1. Tables and Views for HCM
  2. WLF_LRNR_COMPLETION_SUMMARY_V

WLF_LRNR_COMPLETION_SUMMARY_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

PARENT_LEARNING_ITEM_ID

PARENT_LEARNING_ITEM_TYPE

LEARNER_ID

ASSIGNMENT_RECORD_ID

PASSING_SCORE

EXPECTED_EFFORT

EFFORT_UNITS

EFFORT_UNITS_MEANING

COMPLETION_REQUIREMENT

COMPLETION_REQUIREMENT_UNITS

CMPLTN_REQRMNT_UNITS_MEANING

ACTUAL_SCORE

ACTUAL_EFFORT

COMPLETION_PROGRESS

STATUS

VIDEO_DURATION_UOM

VIDEO_DURATION

Query

SQL_Statement

SELECT /*WLF_LRNR_COMPLETION_SUMMARY_V*/ PARENT_LEARNING_ITEM_ID,

PARENT_LEARNING_ITEM_TYPE,

LEARNER_ID,

ASSIGNMENT_RECORD_ID,

PASSING_SCORE,

EXPECTED_EFFORT,

EFFORT_UNITS,

EFFORT_UNITS_MEANING,

COMPLETION_REQUIREMENT,

COMPLETION_REQUIREMENT_UNITS,

CMPLTN_REQRMNT_UNITS_MEANING,

ACTUAL_SCORE,

ACTUAL_EFFORT,

COMPLETION_PROGRESS,

STATUS,

VIDEO_DURATION_UOM,

VIDEO_DURATION

FROM

(WITH

PRIMARY_OFFERING_ASG AS(

SELECT

AR_RELATIONS.RELATED_OBJECT_ID,

SUM(CONTENT.MASTERY_SCORE) AS PASSING_SCORE,

SUM(CASE WHEN CONTENT.MASTERY_SCORE IS NOT NULL

THEN 1

ELSE 0

END) AS TOTAL_SCORED_ACTIVITIES,

SUM(ACTIVITY_LI.DURATION) AS EXPECTED_EFFORT,

COUNT(

CASE WHEN ACTIVITY.ACTIVITY_TYPE = 'ORA_DEF_QUESTIONNAIRE'

THEN CASE WHEN ACTIVITY.RELATED_CONTENT_ID = -1

THEN DECODE((SELECT MANDATORY_FLAG FROM WLF_LI_RELATIONS_F

WHERE LEARNING_ITEM_ID = -1 AND TYPE_OF_USAGE = OFFERING.DELIVERY_MODE

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) , 'Y', 1 , NULL)

ELSE DECODE(ACTIVITY.COMPLETION_TYPE , 'ORA_REQUIRED', 1 , null)

END

ELSE DECODE(ACTIVITY.COMPLETION_TYPE , 'ORA_REQUIRED', 1 , null)

END) AS COMPLETION_REQUIREMENT,

SUM ( CASE WHEN ACTIVITY_TYPE = 'ORA_ONLINE_SP'

THEN (SELECT NVL(SCORE,0) FROM

(SELECT SCORE,

ROW_NUMBER() OVER (PARTITION BY RELATED_OBJECT_ID ORDER BY ATTEMPT_COMPLETION_DATE DESC) AS LATEST_ATTEMPT_ROW_NUM

FROM WLF_ATTEMPT_RELATIONS ATTEMPT

WHERE ATTEMPT.RELATED_OBJECT_ID = ASG_TASKS.ASSIGNMENT_TASK_ID

AND ATTEMPT.LEARNER_ID = ASG_TASKS.TASK_OWNER_ID)

WHERE LATEST_ATTEMPT_ROW_NUM = 1

)

ELSE ASG_TASKS.SCORE END) AS ACTUAL_SCORE,

SUM(ASG_TASKS.EFFORT) AS ACTUAL_EFFORT,

COUNT(

CASE WHEN ASG_TASKS.TASK_STATUS = 'ORA_ASSN_TASK_COMPLETED'

THEN (CASE WHEN ACTIVITY.ACTIVITY_TYPE = 'ORA_DEF_QUESTIONNAIRE'

THEN CASE WHEN ACTIVITY.RELATED_CONTENT_ID = -1

THEN DECODE((SELECT MANDATORY_FLAG FROM WLF_LI_RELATIONS_F

WHERE LEARNING_ITEM_ID = -1 AND TYPE_OF_USAGE = OFFERING.DELIVERY_MODE

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) , 'Y', 1 , NULL)

ELSE DECODE(ACTIVITY.COMPLETION_TYPE , 'ORA_REQUIRED', 1 , null)

END

ELSE 1

END)

WHEN ASG_TASKS.TASK_STATUS = 'ORA_ASSN_TASK_EXEMPTED'

THEN 1

ELSE NULL

END) AS COMPLETION_PROGRESS

FROM

WLF_AR_RELATIONS_F AR_RELATIONS,

WLF_ASSIGNMENT_RECORDS_F OFFERING_ASG_RECORDS,

WLF_LI_CLASSES_F OFFERING,

WLF_LI_ACTIVITIES_F ACTIVITY,

WLF_LEARNING_ITEMS_F ACTIVITY_LI,

WLF_ASSIGNMENT_TASKS_F ASG_TASKS,

WLF_LI_CONTENT_F CONTENT

WHERE

AR_RELATIONS.RELATED_OBJECT_TYPE = 'ORA_COURSE'

AND AR_RELATIONS.RELATION_TYPE = 'ORA_OFFERING_COURSE'

AND AR_RELATIONS.PRIMARY_FLAG = 'Y'

AND TRUNC(SYSDATE) BETWEEN AR_RELATIONS.EFFECTIVE_START_DATE AND AR_RELATIONS.EFFECTIVE_END_DATE

AND OFFERING_ASG_RECORDS.ASSIGNMENT_RECORD_ID = AR_RELATIONS.ASSIGNMENT_RECORD_ID

AND TRUNC(SYSDATE) BETWEEN OFFERING_ASG_RECORDS.EFFECTIVE_START_DATE AND OFFERING_ASG_RECORDS.EFFECTIVE_END_DATE

AND OFFERING_ASG_RECORDS.EVENT_TYPE IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT')

AND OFFERING.LEARNING_ITEM_ID = OFFERING_ASG_RECORDS.LEARNING_ITEM_ID

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN OFFERING.EFFECTIVE_START_DATE AND OFFERING.EFFECTIVE_END_DATE

/*

AND HRCHY_OFFERING.LEARNING_ITEM_ID = TO_FILTER_OFFERING_ASG.LEARNING_ITEM_ID

AND TRUNC(SYSDATE) BETWEEN HRCHY_OFFERING.EFFECTIVE_START_DATE AND HRCHY_OFFERING.EFFECTIVE_END_DATE

AND HRCHY_SECTION.LEARNING_ITEM_ID = HRCHY_OFFERING.CHILD_LEARNING_ITEM_ID

AND TRUNC(SYSDATE) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE */

AND ACTIVITY.PARENT_LEARNING_ITEM_ID = OFFERING.LEARNING_ITEM_ID

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN ACTIVITY.EFFECTIVE_START_DATE AND ACTIVITY.EFFECTIVE_END_DATE

AND ACTIVITY_LI.LEARNING_ITEM_ID = ACTIVITY.LEARNING_ITEM_ID

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN ACTIVITY_LI.EFFECTIVE_START_DATE AND ACTIVITY_LI.EFFECTIVE_END_DATE

AND ASG_TASKS.LEARNING_ITEM_ID(+) = ACTIVITY.LEARNING_ITEM_ID

AND TRUNC(SYSDATE) BETWEEN ASG_TASKS.EFFECTIVE_START_DATE(+) AND ASG_TASKS.EFFECTIVE_END_DATE(+)

AND ASG_TASKS.ASSIGNMENT_RECORD_ID(+) = OFFERING_ASG_RECORDS.ASSIGNMENT_RECORD_ID

AND CONTENT.LEARNING_ITEM_ID(+) = ACTIVITY.RELATED_CONTENT_ID

AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN CONTENT.EFFECTIVE_START_DATE(+) AND CONTENT.EFFECTIVE_END_DATE(+)

GROUP BY AR_RELATIONS.RELATED_OBJECT_ID

)

SELECT

ASG_RECORDS.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID,

COURSE_LI.LEARNING_ITEM_TYPE AS PARENT_LEARNING_ITEM_TYPE,

ASG_RECORDS.LEARNER_ID,

ASG_RECORDS.ASSIGNMENT_RECORD_ID,

CASE WHEN PRIMARY_OFFERING_ASG.TOTAL_SCORED_ACTIVITIES = 1

THEN PRIMARY_OFFERING_ASG.PASSING_SCORE

ELSE NULL

END AS PASSING_SCORE,

CASE

WHEN (COURSE.MINIMUM_TRAINING_HOURS IS NOT NULL

AND COURSE.MAXIMUM_TRAINING_HOURS IS NOT NULL)

THEN DECODE(COURSE.MINIMUM_TRAINING_HOURS, COURSE.MAXIMUM_TRAINING_HOURS,

TO_CHAR(COURSE.MINIMUM_TRAINING_HOURS),

(COURSE.MINIMUM_TRAINING_HOURS

|| ' - '

|| COURSE.MAXIMUM_TRAINING_HOURS))

WHEN (COURSE.MINIMUM_TRAINING_HOURS IS NOT NULL

AND COURSE.MAXIMUM_TRAINING_HOURS IS NULL)

THEN TO_CHAR(COURSE.MINIMUM_TRAINING_HOURS)

WHEN (COURSE.MINIMUM_TRAINING_HOURS IS NULL

AND COURSE.MAXIMUM_TRAINING_HOURS IS NOT NULL)

THEN TO_CHAR(COURSE.MAXIMUM_TRAINING_HOURS)

ELSE NULL

END AS EXPECTED_EFFORT,

'ORA_DUR_HOUR' AS EFFORT_UNITS,

NULL AS EFFORT_UNITS_MEANING,

PRIMARY_OFFERING_ASG.COMPLETION_REQUIREMENT,

CASE

WHEN PRIMARY_OFFERING_ASG.RELATED_OBJECT_ID IS NOT NULL

THEN 'ORA_REQUIRED_ACTIVITIES'

ELSE NULL

END COMPLETION_REQUIREMENT_UNITS,

NULL AS CMPLTN_REQRMNT_UNITS_MEANING,

PRIMARY_OFFERING_ASG.ACTUAL_SCORE,

PRIMARY_OFFERING_ASG.ACTUAL_EFFORT,

CASE

WHEN PRIMARY_OFFERING_ASG.RELATED_OBJECT_ID IS NOT NULL

THEN PRIMARY_OFFERING_ASG.COMPLETION_PROGRESS

ELSE NULL

END COMPLETION_PROGRESS,

ASG_RECORDS.STATUS,

NULL AS VIDEO_DURATION_UOM,

NULL AS VIDEO_DURATION

FROM

WLF_LEARNING_ITEMS_F COURSE_LI,

WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS,

WLF_LI_COURSES_F COURSE,

PRIMARY_OFFERING_ASG

WHERE

ASG_RECORDS.LEARNING_ITEM_ID = COURSE_LI.LEARNING_ITEM_ID

AND ASG_RECORDS.EVENT_TYPE IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT')

AND TRUNC(SYSDATE) BETWEEN ASG_RECORDS.EFFECTIVE_START_DATE AND ASG_RECORDS.EFFECTIVE_END_DATE

AND COURSE_LI.LEARNING_ITEM_TYPE = 'ORA_COURSE'

AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE_LI.EFFECTIVE_START_DATE AND COURSE_LI.EFFECTIVE_END_DATE

AND COURSE.LEARNING_ITEM_ID = ASG_RECORDS.LEARNING_ITEM_ID

AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE.EFFECTIVE_START_DATE AND COURSE.EFFECTIVE_END_DATE

AND PRIMARY_OFFERING_ASG.RELATED_OBJECT_ID(+) = ASG_RECORDS.ASSIGNMENT_RECORD_ID

UNION ALL

SELECT * FROM

(WITH SPECIALIZATION_ASG AS (

SELECT ASG_RECORDS.LEARNING_ITEM_ID,

ASG_RECORDS.LEARNER_ID,

ASG_RECORDS.ASSIGNMENT_RECORD_ID,

ASG_RECORDS.LI_EFFECTIVE_DATE,

ASG_RECORDS.STATUS,

ASG_RECORDS.COMPLETION_DATE AS SPEC_COMPLETION_DATE

FROM WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS

WHERE ASG_RECORDS.EVENT_TYPE IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT')

AND TRUNC(SYSDATE) BETWEEN ASG_RECORDS.EFFECTIVE_START_DATE AND ASG_RECORDS.EFFECTIVE_END_DATE

),

SECTION_COURSE_INFO AS (

SELECT

SPECIALIZATION_ASG.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID,

SPECIALIZATION_ASG.LEARNER_ID,

SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID,

HRCHY_SPEC.CHILD_LEARNING_ITEM_ID AS SECTION_LEARNING_ITEM_ID,

SECTION.NO_OF_MANDATORY_ITEMS,

HRCHY_SECTION.CHILD_LEARNING_ITEM_ID AS COURSE_LEARNING_ITEM_ID,

SPECIALIZATION_ASG.SPEC_COMPLETION_DATE

FROM

SPECIALIZATION_ASG,

WLF_LEARNING_ITEMS_F SPEC_LI,

WLF_LI_HIERARCHIES_F HRCHY_SPEC,

WLF_LI_SECTIONS_F SECTION,

WLF_LI_HIERARCHIES_F HRCHY_SECTION

WHERE

SPEC_LI.LEARNING_ITEM_TYPE = 'ORA_SPECIALIZATION'

AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN SPEC_LI.EFFECTIVE_START_DATE AND SPEC_LI.EFFECTIVE_END_DATE

AND HRCHY_SPEC.LEARNING_ITEM_ID = SPEC_LI.LEARNING_ITEM_ID

AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN HRCHY_SPEC.EFFECTIVE_START_DATE AND HRCHY_SPEC.EFFECTIVE_END_DATE

AND SECTION.LEARNING_ITEM_ID = HRCHY_SPEC.CHILD_LEARNING_ITEM_ID

AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN SECTION.EFFECTIVE_START_DATE AND SECTION.EFFECTIVE_END_DATE

AND HRCHY_SECTION.LEARNING_ITEM_ID = HRCHY_SPEC.CHILD_LEARNING_ITEM_ID

AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE

),

COURSE_ASG_INFO AS (

SELECT

COURSE_ASG_RECORDS.ASSIGNMENT_RECORD_ID AS COURSE_ASG_RECORD_ID,

COURSE_ASG_RECORDS.STATUS AS COURSE_ASG_STATUS,

COURSE_ASG_RECORDS.LEARNING_ITEM_ID AS COURSE_LEARNING_ITEM_ID,

CASE WHEN COURSE_ASG_RECORDS.STATUS ='ORA_ASSN_REC_COMPLETE'

THEN ROW_NUMBER() OVER (PARTITION BY SECTION_COURSE_INFO.ASSIGNMENT_RECORD_ID,SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID,SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID,COURSE_ASG_RECORDS.LEARNER_ID ORDER BY COURSE_ASG_RECORDS.ASSIGNED_ON_DATE DESC)

ELSE NULL

END AS COMPLETED_COURSE_ASG_FOR_SPEC,

CASE WHEN COURSE_ASG_RECORDS.STATUS <> 'ORA_ASSN_REC_COMPLETE'

THEN ROW_NUMBER() OVER (PARTITION BY SECTION_COURSE_INFO.ASSIGNMENT_RECORD_ID,SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID,SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID,COURSE_ASG_RECORDS.LEARNER_ID ORDER BY COURSE_ASG_RECORDS.ASSIGNED_ON_DATE DESC)

ELSE NULL

END AS INCOMPLETE_COURSE_ASG_FOR_SPEC

FROM

SECTION_COURSE_INFO,

WLF_ASSIGNMENT_RECORDS_F COURSE_ASG_RECORDS

WHERE

COURSE_ASG_RECORDS.LEARNING_ITEM_ID = SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID

AND TRUNC(SYSDATE) BETWEEN COURSE_ASG_RECORDS.EFFECTIVE_START_DATE AND COURSE_ASG_RECORDS.EFFECTIVE_END_DATE

AND COURSE_ASG_RECORDS.LEARNER_ID = SECTION_COURSE_INFO.LEARNER_ID

AND COURSE_ASG_RECORDS.EVENT_TYPE IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT')

AND TRUNC(COURSE_ASG_RECORDS.ASSIGNED_ON_DATE) <= TRUNC(NVL(SECTION_COURSE_INFO.SPEC_COMPLETION_DATE,SYSDATE))

AND TRUNC(NVL(COURSE_ASG_RECORDS.COMPLETION_DATE,SYSDATE)) <= TRUNC(NVL(SECTION_COURSE_INFO.SPEC_COMPLETION_DATE,SYSDATE))

AND TRUNC(NVL(COURSE_ASG_RECORDS.EXPIRATION_DATE,SYSDATE)) >= TRUNC(NVL(SECTION_COURSE_INFO.SPEC_COMPLETION_DATE,SYSDATE))

AND COURSE_ASG_RECORDS.EVENT_TYPE IN ('ORA_REQUIRE_ASSIGNMENT','ORA_JOIN_ASSIGNMENT')

AND COURSE_ASG_RECORDS.STATUS <> 'ORA_ASSN_REC_WITHDRAWN' AND COURSE_ASG_RECORDS.STATUS <> 'ORA_ASSN_REC_REQ_REJECTED'

AND COURSE_ASG_RECORDS.STATUS <> 'ORA_ASSN_REC_DELETED'

AND (SECTION_COURSE_INFO.SPEC_COMPLETION_DATE IS NULL

OR ( SECTION_COURSE_INFO.SPEC_COMPLETION_DATE IS NOT NULL AND COURSE_ASG_RECORDS.COMPLETION_DATE IS NOT NULL )

OR ( SECTION_COURSE_INFO.SPEC_COMPLETION_DATE IS NOT NULL AND COURSE_ASG_RECORDS.COMPLETION_DATE IS NULL AND COURSE_ASG_RECORDS.ASSIGNMENT_RECORD_ID = -1)

)

),

LATEST_COURSE_ASG_INFO AS (

SELECT

COURSE_ASG_INFO.COURSE_ASG_RECORD_ID,

COURSE_ASG_INFO.COURSE_ASG_STATUS,

COURSE_ASG_INFO.COURSE_LEARNING_ITEM_ID

FROM

COURSE_ASG_INFO

WHERE

( (COMPLETED_COURSE_ASG_FOR_SPEC IS NOT NULL AND COMPLETED_COURSE_ASG_FOR_SPEC = 1)

OR ( COMPLETED_COURSE_ASG_FOR_SPEC IS NULL AND INCOMPLETE_COURSE_ASG_FOR_SPEC IS NOT NULL AND INCOMPLETE_COURSE_ASG_FOR_SPEC=1 )

)

),

SECTION_COURSE_ASG_INFO AS (

SELECT SECTION_COURSE_INFO.PARENT_LEARNING_ITEM_ID,

SECTION_COURSE_INFO.ASSIGNMENT_RECORD_ID,

SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID,

SECTION_COURSE_INFO.NO_OF_MANDATORY_ITEMS,

SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID,

LATEST_COURSE_ASG_INFO.COURSE_ASG_RECORD_ID,

LATEST_COURSE_ASG_INFO.COURSE_ASG_STATUS

FROM

SECTION_COURSE_INFO,

LATEST_COURSE_ASG_INFO

WHERE LATEST_COURSE_ASG_INFO.COURSE_LEARNING_ITEM_ID(+) = SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID

),

SECTION_DETAILS AS

(

SELECT

PARENT_LEARNING_ITEM_ID,

SECTION_LEARNING_ITEM_ID,

COUNT(COURSE_LEARNING_ITEM_ID) TOTAL_COURSES,

CASE WHEN (NO_OF_MANDATORY_ITEMS = -1)

THEN COUNT(COURSE_LEARNING_ITEM_ID)

ELSE NO_OF_MANDATORY_ITEMS

END AS MANDATORY_COURSES,

SUM(CASE WHEN COURSE_ASG_STATUS = 'ORA_ASSN_REC_COMPLETE' THEN 1 ELSE 0 END) COMPLETED_COURSES

FROM

SECTION_COURSE_ASG_INFO

GROUP BY PARENT_LEARNING_ITEM_ID,SECTION_LEARNING_ITEM_ID,NO_OF_MANDATORY_ITEMS

)

,SECTION_COMPLETION_DETAILS AS(

SELECT

PARENT_LEARNING_ITEM_ID,

SECTION_LEARNING_ITEM_ID,

SUM(MANDATORY_COURSES) AS MANDATORY_COURSES,

SUM( CASE WHEN ( COMPLETED_COURSES > MANDATORY_COURSES)

THEN MANDATORY_COURSES

ELSE COMPLETED_COURSES

END ) AS COMPLETED_COURSES

FROM

SECTION_DETAILS

GROUP BY PARENT_LEARNING_ITEM_ID,SECTION_LEARNING_ITEM_ID

),

SPECILIZATION_PROGRESS AS(

SELECT PARENT_LEARNING_ITEM_ID,

SUM(MANDATORY_COURSES) MANDATORY_COURSES,

SUM(COMPLETED_COURSES) COMPLETED_COURSES

FROM SECTION_COMPLETION_DETAILS

GROUP BY PARENT_LEARNING_ITEM_ID

)

SELECT

SPECIALIZATION_ASG.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID,

'ORA_SPECIALIZATION' AS PARENT_LEARNING_ITEM_TYPE,

SPECIALIZATION_ASG.LEARNER_ID,

SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID AS ASSIGNMENT_RECORD_ID,

NULL AS PASSING_SCORE,

CASE

WHEN (SPEC.MINIMUM_TRAINING_HOURS IS NOT NULL

AND SPEC.MAXIMUM_TRAINING_HOURS IS NOT NULL)

THEN DECODE(SPEC.MINIMUM_TRAINING_HOURS, SPEC.MAXIMUM_TRAINING_HOURS,

TO_CHAR(SPEC.MINIMUM_TRAINING_HOURS),

(SPEC.MINIMUM_TRAINING_HOURS

|| ' - '

|| SPEC.MAXIMUM_TRAINING_HOURS))

WHEN (SPEC.MINIMUM_TRAINING_HOURS IS NOT NULL

AND SPEC.MAXIMUM_TRAINING_HOURS IS NULL)

THEN TO_CHAR(SPEC.MINIMUM_TRAINING_HOURS)

WHEN (SPEC.MINIMUM_TRAINING_HOURS IS NULL

AND SPEC.MAXIMUM_TRAINING_HOURS IS NOT NULL)

THEN TO_CHAR(SPEC.MAXIMUM_TRAINING_HOURS)

ELSE NULL

END AS EXPECTED_EFFORT,

'ORA_DUR_HOUR' AS EFFORT_UNITS,

NULL AS EFFORT_UNITS_MEANING,

SPECILIZATION_PROGRESS.MANDATORY_COURSES AS COMPLETION_REQUIREMENT,

'ORA_REQUIRED_ACTIVITIES' AS COMPLETION_REQUIREMENT_UNITS,

NULL AS CMPLTN_REQRMNT_UNITS_MEANING,

NULL AS ACTUAL_SCORE ,

NULL AS ACTUAL_EFFORT,

SPECILIZATION_PROGRESS.COMPLETED_COURSES AS COMPLETION_PROGRESS,

SPECIALIZATION_ASG.STATUS,

NULL AS VIDEO_DURATION_UOM,

NULL AS VIDEO_DURATION

FROM SPECILIZATION_PROGRESS,

SPECIALIZATION_ASG,

WLF_LI_SPECIALIZATIONS_F SPEC

WHERE SPECILIZATION_PROGRESS.PARENT_LEARNING_ITEM_ID = SPECIALIZATION_ASG.LEARNING_ITEM_ID

AND SPEC.LEARNING_ITEM_ID = SPECIALIZATION_ASG.LEARNING_ITEM_ID

AND TRUNC(SYSDATE) BETWEEN SPEC.EFFECTIVE_START_DATE AND SPEC.EFFECTIVE_END_DATE

)

UNION ALL

SELECT

ASG_RECORDS.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID,

LI.LEARNING_ITEM_TYPE AS PARENT_LEARNING_ITEM_TYPE,

ASG_RECORDS.LEARNER_ID,

ASG_RECORDS.ASSIGNMENT_RECORD_ID,

NULL AS PASSING_SCORE,

CASE WHEN LI.LEARNING_ITEM_TYPE in ('ORA_VIDEO','ORA_NON_CATALOG') THEN TO_CHAR(LI.DURATION) ELSE NULL END AS EXPECTED_EFFORT,

CASE WHEN LI.LEARNING_ITEM_TYPE = 'ORA_VIDEO' THEN 'ORA_DUR_HOUR'

WHEN LI.LEARNING_ITEM_TYPE = 'ORA_LEGACY' THEN ASG_RECORDS.EFFORT_UOM

WHEN LI.LEARNING_ITEM_TYPE = 'ORA_NON_CATALOG' THEN LI.DURATION_UOM

ELSE NULL

END AS EFFORT_UNITS,

NULL AS EFFORT_UNITS_MEANING,

CASE WHEN LI.LEARNING_ITEM_TYPE in ('ORA_VIDEO','ORA_NON_CATALOG') THEN LI.DURATION ELSE

(SELECT

COUNT(CHILD_LEARNING_ITEM_ID) AS CHILD_LEARNING_ITEMS_COUNT

FROM WLF_LI_HIERARCHIES_F TUTORIAL_HRCHY,

WLF_LEARNING_ITEMS_F CHAPTER_LI

WHERE TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN TUTORIAL_HRCHY.EFFECTIVE_START_DATE AND TUTORIAL_HRCHY.EFFECTIVE_END_DATE

AND TUTORIAL_HRCHY.LEARNING_ITEM_ID = ASG_RECORDS.LEARNING_ITEM_ID

AND TUTORIAL_HRCHY.CHILD_LEARNING_ITEM_ID = CHAPTER_LI.LEARNING_ITEM_ID

AND CHAPTER_LI.STATUS NOT LIKE '%_INACTIVE'

AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN CHAPTER_LI.EFFECTIVE_START_DATE AND CHAPTER_LI.EFFECTIVE_END_DATE

GROUP BY TUTORIAL_HRCHY.LEARNING_ITEM_ID

) END AS COMPLETION_REQUIREMENT,

CASE WHEN LI.LEARNING_ITEM_TYPE in ('ORA_VIDEO','ORA_NON_CATALOG') THEN 'ORA_DUR_HOUR'

WHEN LI.LEARNING_ITEM_TYPE = 'ORA_TUTORIAL' THEN 'ORA_CHAPTERS'

ELSE NULL

END AS COMPLETION_REQUIREMENT_UNITS,

NULL AS CMPLTN_REQRMNT_UNITS_MEANING,

CASE WHEN LI.LEARNING_ITEM_TYPE = 'ORA_NON_CATALOG' THEN ASG_RECORDS.ACTUAL_SCORE

ELSE NULL

END AS ACTUAL_SCORE,

CASE WHEN LI.LEARNING_ITEM_TYPE in ('ORA_LEGACY','ORA_NON_CATALOG') THEN ASG_RECORDS.TOTAL_ACTUAL_EFFORT

ELSE NULL

END AS ACTUAL_EFFORT,

CASE WHEN LI.LEARNING_ITEM_TYPE = 'ORA_VIDEO' THEN

(SELECT CASE WHEN (EVENT_ATTEMPTS.LAST_UPDATE_DATE > (SELECT EVENT_ASG.CREATION_DATE FROM WLF_EVENT_ASSIGNMENTS_F EVENT_ASG

WHERE

EVENT_ASG.EVENT_ASSIGNMENT_ID = ASG_RECORDS.EVENT_ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN EVENT_ASG.EFFECTIVE_START_DATE AND EVENT_ASG.EFFECTIVE_END_DATE )

) THEN EVENT_ATTEMPTS.PROGRESS_MEASURE ELSE 0 END AS PROGRESS_MEASURE

FROM

WLF_EVENT_ATTEMPTS EVENT_ATTEMPTS

WHERE

EVENT_ATTEMPTS.EVENT_ID = (SELECT MAX(EVENTS.EVENT_ID) AS EVENT_ID

FROM WLF_EVENTS EVENTS

WHERE EVENTS.EVENT_TYPE = 'ORA_LI_ATTEMPT'

AND EVENTS.CREATED_BY_ID = ASG_RECORDS.LEARNER_ID

AND EVENTS.LEARNING_ITEM_ID = ASG_RECORDS.LEARNING_ITEM_ID )

)

WHEN LI.LEARNING_ITEM_TYPE = 'ORA_TUTORIAL' THEN

(SELECT count(e.learning_item_id)

FROM wlf_event_attempts ea,

wlf_events e

WHERE e.event_id = ea.event_id

AND e.created_by_id = NVL(ASG_RECORDS.LEARNER_ID, HRC_SESSION_UTIL.GET_USER_PERSONID)

AND ea.completion_status ='C'

AND ea.completion_date IS NOT NULL

AND ea.root_attempt_id =

(SELECT event_attempt_id

FROM wlf_events event,

wlf_event_attempts attempt

WHERE event.event_id = attempt.event_id

AND event.event_type ='ORA_LI_ATTEMPT'

AND event.learning_item_id = LI.LEARNING_ITEM_ID

AND event.created_by_id = NVL(ASG_RECORDS.LEARNER_ID, HRC_SESSION_UTIL.GET_USER_PERSONID)

AND NVL(attempt.completion_status, 'I') = 'I'

AND attempt.completion_date IS NULL

AND attempt_End_date IS NULL

))

WHEN LI.LEARNING_ITEM_TYPE = 'ORA_NON_CATALOG' THEN LI.DURATION

ELSE NULL

END AS COMPLETION_PROGRESS,

ASG_RECORDS.STATUS,

CASE WHEN LI.LEARNING_ITEM_TYPE = 'ORA_VIDEO' THEN LI.DURATION_UOM ELSE NULL END AS VIDEO_DURATION_UOM,

CASE WHEN LI.LEARNING_ITEM_TYPE = 'ORA_VIDEO' THEN LI.DURATION ELSE NULL END AS VIDEO_DURATION

FROM

WLF_LEARNING_ITEMS_F LI,

WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS

WHERE

ASG_RECORDS.LEARNING_ITEM_ID = LI.LEARNING_ITEM_ID

AND ASG_RECORDS.EVENT_TYPE IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT')

AND TRUNC(SYSDATE) BETWEEN ASG_RECORDS.EFFECTIVE_START_DATE AND ASG_RECORDS.EFFECTIVE_END_DATE

AND ((LI.LEARNING_ITEM_TYPE IN ('ORA_TUTORIAL','ORA_VIDEO','ORA_LEGACY') OR

LI.LEARNING_ITEM_TYPE = 'ORA_NON_CATALOG' AND ASG_RECORDS.STATUS = 'ORA_ASSN_REC_COMPLETE'))

AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN LI.EFFECTIVE_START_DATE AND LI.EFFECTIVE_END_DATE

/*end LearnerLearningCompletionSummaryVO*/

) CompletionSummary