WLF_LI_ACTIVITY_DETAILS_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 ACTIVITY_SECTION_ID ACTIVITY_SECTION_NUMBER ACTIVITY_SECTION_TITLE ACTIVITY_SECTION_DESCRIPTION ACTIVITY_SECTION_POSITION ACTIVITY_SECTION_COMPL_REQ ACT_SECTION_TOTAL_ACTIVITIES ACTIVITY_ATTEMPT_SECTION_ID ACTIVITY_SECTION_STATUS ACTIVITY_SECTION_PROGRESS ACTIVITY_LEARNING_ITEM_ID ACTIVITY_ID ACTIVITY_NUMBER ACTIVITY_TYPE ACTIVITY_TITLE ACTIVITY_DESCRIPTION ACTIVITY_DESCRIPTION_LONG ACTIVITY_DESCRIPTION_SHORT ACTIVITY_POSITION ACTIVITY_PASSING_SCORE ACTIVITY_EXPECTED_EFFORT ACTIVITY_EXPECTED_EFFORT_UNITS ACTIVITY_COMPL_REQ LEARNER_CAN_MARK_COMPLETE ACTIVITY_DATE ACTIVITY_START_TIME ACTIVITY_END_TIME ACTIVITY_TIME_ZONE VIRTUAL_CLASSROOM_JOIN_LINK CONTENT_LEARNING_ITEM_ID LEARNING_CONTENT_ID LEARNING_CONTENT_NUMBER LEARNING_CONTENT_TYPE LEARNING_CONTENT_TITLE LEARNING_CONTENT_SHORT_DESC LEARNING_CONTENT_QSTNR_ID LEARNING_CONTENT_QSNTR_NUMBER CONTENT_ATTEMPTS_ALLOWED TIME_ALLOWED_PER_ATTEMPT TIME_ALLOWED_PER_ATTEMPT_UNITS MARK_COMPLETION_ON_OPEN STARTING_URL CONTENT_LOCATION CONTENT_THUMBNAIL_ID CONTENT_STATUS ACTIVITY_ATTEMPT_ID ACTIVITY_ATTEMPT_NUMBER ACTIVITY_ATTEMPT_STATUS ACTIVITY_TOTAL_ATTEMPTS ACTIVITY_ATTEMPT_STARTED_DATE ATTEMPT_COMPLETED_DATE ACTIVITY_ATTEMPT_EXEMPTED_DATE ATTEMPT_EXEMPTED_REASON_CODE ATTEMPT_ACTUAL_EFFORT ATTEMPT_ACTUAL_EFFORT_UNITS ACTIVITY_ATTEMPT_ACTUAL_SCORE OFFERING_LEARNING_ITEM_ID OFFERING_NAME OFFERING_NUMBER OFFERING_LI_EFFECTIVE_DATE OFFERING_ASSIGNMENT_RECORD_ID COURSE_LEARNING_ITEM_ID COURSE_ASSIGNMENT_RECORD_ID LI_EFFECTIVE_DATE ASSIGNMENT_TASK_ID MAX_PRICE MIN_PRICE CURRENCY_CODE HIDE_SCORE_LEARNER DISABLE_REVIEW PRIMARY_RELATIONSHIP_FLAG ACTIVITY_ASSIGNMENT_RECORD_ID ACT_ASG_EFFECTIVE_START_DATE ACT_ASG_EFFECTIVE_END_DATE

Query

SQL_Statement

/*WITH ATTEMPT AS( SELECT * FROM (SELECT RELATED_OBJECT_ID, LEARNER_ID, ATTEMPT_ID AS ACTIVITY_ATTEMPT_ID, STATUS AS ACTIVITY_ATTEMPT_STATUS, ATTEMPT_CREATION_DATE AS ACTIVITY_ATTEMPT_STARTED_DATE, SCORE, ROW_NUMBER() OVER (PARTITION BY RELATED_OBJECT_ID ORDER BY ATTEMPT_CREATION_DATE DESC) AS LATEST_ATTEMPT_ROW_NUM FROM WLF_ATTEMPT_RELATIONS ) WHERE LATEST_ATTEMPT_ROW_NUM = 1 )*/ SELECT COURSE_LI.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, /** Learning Activity Section Info **/ NULL AS ACTIVITY_SECTION_ID, NULL AS ACTIVITY_SECTION_NUMBER, NULL AS ACTIVITY_SECTION_TITLE, NULL AS ACTIVITY_SECTION_DESCRIPTION, NULL AS ACTIVITY_SECTION_POSITION, NULL AS ACTIVITY_SECTION_COMPL_REQ, NULL AS ACT_SECTION_TOTAL_ACTIVITIES, NULL AS ACTIVITY_ATTEMPT_SECTION_ID, NULL AS ACTIVITY_SECTION_STATUS, NULL AS ACTIVITY_SECTION_PROGRESS, /** Learning Activity Info **/ ACTIVITY_LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, ACTIVITY_LI.LEARNING_ITEM_ID AS ACTIVITY_ID, ACTIVITY_LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, ACTIVITY.ACTIVITY_TYPE, ACTIVITY_LI_TL.NAME AS ACTIVITY_TITLE, ACTIVITY_LI_TL.DESCRIPTION AS ACTIVITY_DESCRIPTION, ACTIVITY_LI_TL.DESCRIPTION_LONG AS ACTIVITY_DESCRIPTION_LONG, ACTIVITY_LI_TL.DESCRIPTION_SHORT AS ACTIVITY_DESCRIPTION_SHORT, CASE WHEN ACTIVITY.ACTIVITY_TYPE='ORA_DEF_QUESTIONNAIRE' THEN (SELECT MAX(HRCHY_SECTION.POSITION) + 1 FROM WLF_LI_HIERARCHIES_F HRCHY_OFFERING, WLF_LI_HIERARCHIES_F HRCHY_SECTION WHERE HRCHY_OFFERING.LEARNING_ITEM_ID = OFFERING_LI.LEARNING_ITEM_ID AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,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(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE GROUP BY OFFERING_LI.LEARNING_ITEM_ID ) ELSE (SELECT HRCHY_SECTION.POSITION FROM WLF_LI_HIERARCHIES_F HRCHY_OFFERING, WLF_LI_HIERARCHIES_F HRCHY_SECTION WHERE HRCHY_OFFERING.LEARNING_ITEM_ID = OFFERING_LI.LEARNING_ITEM_ID AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,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 HRCHY_SECTION.CHILD_LEARNING_ITEM_ID = ACTIVITY_LI.LEARNING_ITEM_ID AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE ) END AS ACTIVITY_POSITION, CONTENT.MASTERY_SCORE AS ACTIVITY_PASSING_SCORE, TO_CHAR(ACTIVITY_LI.DURATION) AS ACTIVITY_EXPECTED_EFFORT, ACTIVITY_LI.DURATION_UOM AS ACTIVITY_EXPECTED_EFFORT_UNITS, CASE WHEN ACTIVITY.ACTIVITY_TYPE='ORA_DEF_QUESTIONNAIRE' THEN CASE WHEN ACTIVITY.RELATED_CONTENT_ID = -1 THEN DECODE((SELECT RELATIONS.MANDATORY_FLAG FROM WLF_LI_RELATIONS_F RELATIONS,WLF_LI_CLASSES_F OFFERING WHERE OFFERING.LEARNING_ITEM_ID = ACTIVITY.PARENT_LEARNING_ITEM_ID AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN OFFERING.EFFECTIVE_START_DATE AND OFFERING.EFFECTIVE_END_DATE AND RELATIONS.LEARNING_ITEM_ID = -1 AND RELATIONS.TYPE_OF_USAGE = OFFERING.DELIVERY_MODE AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN RELATIONS.EFFECTIVE_START_DATE AND RELATIONS.EFFECTIVE_END_DATE), 'Y', 'ORA_REQUIRED', 'ORA_OPTIONAL') ELSE ACTIVITY.COMPLETION_TYPE END ELSE ACTIVITY.COMPLETION_TYPE END AS ACTIVITY_COMPL_REQ, ACTIVITY.SELF_COMPLETE_FLAG AS LEARNER_CAN_MARK_COMPLETE, /**ILT Activity Info**/ TRUNC(ACTIVITY_LI.START_DATE) AS ACTIVITY_DATE, ACTIVITY_LI.START_DATE AS ACTIVITY_START_TIME, ACTIVITY_LI.END_DATE AS ACTIVITY_END_TIME, ACTIVITY.TIME_ZONE AS ACTIVITY_TIME_ZONE, ACTIVITY.VIRTUAL_CLASSROOM_URL AS VIRTUAL_CLASSROOM_JOIN_LINK, /**SP Activity Info**/ CONTENT_LI.LEARNING_ITEM_ID AS CONTENT_LEARNING_ITEM_ID, CASE WHEN CONTENT_LI.LEARNING_ITEM_TYPE = 'ORA_CONTENT_VIDEO' THEN VIDEO.VIDEO_ID ELSE CONTENT.CONTENT_ID END AS LEARNING_CONTENT_ID, CONTENT_LI.LEARNING_ITEM_NUMBER AS LEARNING_CONTENT_NUMBER, CASE WHEN CONTENT_LI.LEARNING_ITEM_TYPE = 'ORA_CONTENT_VIDEO' THEN 'ORA_VIDEO' ELSE CONTENT.TRACKING_TYPE END AS LEARNING_CONTENT_TYPE, CONTENT_LI_TL.NAME AS LEARNING_CONTENT_TITLE, CONTENT_LI_TL.DESCRIPTION AS LEARNING_CONTENT_SHORT_DESC, CASE WHEN ACTIVITY.ACTIVITY_TYPE='ORA_DEF_QUESTIONNAIRE' THEN CASE WHEN ACTIVITY.RELATED_CONTENT_ID = -1 THEN (SELECT RELATIONS.SOURCE_ID FROM WLF_LI_RELATIONS_F RELATIONS,WLF_LI_CLASSES_F OFFERING WHERE OFFERING.LEARNING_ITEM_ID = OFFERING_LI.LEARNING_ITEM_ID AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN OFFERING.EFFECTIVE_START_DATE AND OFFERING.EFFECTIVE_END_DATE AND RELATIONS.LEARNING_ITEM_ID = -1 AND RELATIONS.TYPE_OF_USAGE = OFFERING.DELIVERY_MODE AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN RELATIONS.EFFECTIVE_START_DATE AND RELATIONS.EFFECTIVE_END_DATE) ELSE ACTIVITY.RELATED_CONTENT_ID END WHEN (CONTENT.TRACKING_TYPE = 'ORA_ASSESSMENT' AND CONTENT_LI.STATUS <> 'ORA_LI_DELETE') THEN CONTENT.RELATED_CONTENT_ID ELSE NULL END AS LEARNING_CONTENT_QSTNR_ID, NUll AS LEARNING_CONTENT_QSNTR_NUMBER, CONTENT.MAX_ATTEMPTS AS CONTENT_ATTEMPTS_ALLOWED, CONTENT.TIME_LIMIT AS TIME_ALLOWED_PER_ATTEMPT, CONTENT.TIME_LIMIT_UOM AS TIME_ALLOWED_PER_ATTEMPT_UNITS, CONTENT_ITEM_DETAILS.COMPLETION_ON_OPEN AS MARK_COMPLETION_ON_OPEN, CONTENT_ITEM_DETAILS.STARTING_URL AS STARTING_URL, CONTENT_LI.LOCATION AS CONTENT_LOCATION, CONTENT_LI.THUMBNAIL_ID AS CONTENT_THUMBNAIL_ID, CONTENT_LI.STATUS AS CONTENT_STATUS, /** Learner activity attempt info**/ ASG_TASKS.ASSIGNMENT_TASK_ID AS ACTIVITY_ATTEMPT_ID, NULL AS ACTIVITY_ATTEMPT_NUMBER, ASG_TASKS.TASK_STATUS AS ACTIVITY_ATTEMPT_STATUS, CASE WHEN (ACTIVITY_TYPE <> 'ORA_ONLINE_SP' AND ASG_TASKS.TASK_STATUS='ORA_ASSN_TASK_COMPLETED') THEN 1 ELSE (SELECT COUNT(*) FROM WLF_ATTEMPT_RELATIONS WHERE RELATED_OBJECT_ID = ASG_TASKS.ASSIGNMENT_TASK_ID and LEARNER_ID = ASG_TASKS.TASK_OWNER_ID) END AS ACTIVITY_TOTAL_ATTEMPTS, ASG_TASKS.CREATION_DATE AS ACTIVITY_ATTEMPT_STARTED_DATE, ASG_TASKS.COMPLETION_DATE AS ATTEMPT_COMPLETED_DATE, ASG_TASKS.EXEMPTED_ON_DATE AS ACTIVITY_ATTEMPT_EXEMPTED_DATE, ASG_TASKS.REASON_CODE AS ATTEMPT_EXEMPTED_REASON_CODE, ASG_TASKS.EFFORT AS ATTEMPT_ACTUAL_EFFORT, ASG_TASKS.EFFORT_UOM AS ATTEMPT_ACTUAL_EFFORT_UNITS, CASE WHEN ACTIVITY_TYPE = 'ORA_ONLINE_SP' THEN NULL ELSE ASG_TASKS.SCORE END AS ACTIVITY_ATTEMPT_ACTUAL_SCORE, OFFERING_LI.LEARNING_ITEM_ID AS OFFERING_LEARNING_ITEM_ID, OFFERING_LI_TL.NAME OFFERING_NAME, OFFERING_LI.LEARNING_ITEM_NUMBER AS OFFERING_NUMBER, OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE AS OFFERING_LI_EFFECTIVE_DATE, OFFERING_ASG_RECORDS.ASSIGNMENT_RECORD_ID AS OFFERING_ASSIGNMENT_RECORD_ID, COURSE_LI.LEARNING_ITEM_ID as COURSE_LEARNING_ITEM_ID, ASG_RECORDS.ASSIGNMENT_RECORD_ID AS COURSE_ASSIGNMENT_RECORD_ID, ASG_RECORDS.LI_EFFECTIVE_DATE AS LI_EFFECTIVE_DATE, ASG_TASKS.ASSIGNMENT_TASK_ID, NULL AS MAX_PRICE, NULL AS MIN_PRICE, NULL AS CURRENCY_CODE, CONTENT.HIDE_SCORE_LEARNER, CONTENT.DISABLE_REVIEW, CASE WHEN (OFFERING_ASG_RECORDS.STATUS IN ('ORA_ASSN_REC_ACTIVE','ORA_ASSN_REC_CONTENT_COMPLETE','ORA_ASSN_REC_COMPLETE','ORA_ASSN_REC_WITHDRAW_PENDING') AND AR_RELATIONS.PRIMARY_FLAG='Y') THEN 'Y' ELSE 'N' END AS PRIMARY_RELATIONSHIP_FLAG, OFFERING_ASG_RECORDS.ASSIGNMENT_RECORD_ID AS ACTIVITY_ASSIGNMENT_RECORD_ID, OFFERING_ASG_RECORDS.EFFECTIVE_START_DATE AS ACT_ASG_EFFECTIVE_START_DATE, OFFERING_ASG_RECORDS.EFFECTIVE_END_DATE AS ACT_ASG_EFFECTIVE_END_DATE FROM WLF_LEARNING_ITEMS_F COURSE_LI, WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS, WLF_AR_RELATIONS_F AR_RELATIONS, WLF_LEARNING_ITEMS_F OFFERING_LI, WLF_LEARNING_ITEMS_F_TL OFFERING_LI_TL, WLF_LEARNING_ITEMS_F ACTIVITY_LI, WLF_LEARNING_ITEMS_F_TL ACTIVITY_LI_TL, WLF_LI_ACTIVITIES_F ACTIVITY, WLF_ASSIGNMENT_TASKS_F ASG_TASKS, WLF_LI_CONTENT_F CONTENT, WLF_LI_VIDEOS_F VIDEO, WLF_LEARNING_ITEMS_F CONTENT_LI, WLF_LEARNING_ITEMS_F_TL CONTENT_LI_TL, WLF_ASSIGNMENT_RECORDS_F OFFERING_ASG_RECORDS, (SELECT CONTENT_HIERARCHY.LEARNING_ITEM_ID, CONTENT_ITEM.STARTING_URL, CONTENT_ITEM.COMPLETION_ON_OPEN FROM WLF_LI_CONTENT_F CONTENT_ROOT, WLF_LI_CONTENT_F CONTENT_ITEM , WLF_LI_HIERARCHIES_F CONTENT_HIERARCHY WHERE CONTENT_ROOT.TRACKING_TYPE = 'ORA_AUTO' AND TRUNC(SYSDATE) BETWEEN CONTENT_ROOT.EFFECTIVE_START_DATE AND CONTENT_ROOT.EFFECTIVE_END_DATE AND CONTENT_HIERARCHY.LEARNING_ITEM_ID = CONTENT_ROOT.LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN CONTENT_HIERARCHY.EFFECTIVE_START_DATE AND CONTENT_HIERARCHY.EFFECTIVE_END_DATE AND CONTENT_ITEM.LEARNING_ITEM_ID = CONTENT_HIERARCHY.CHILD_LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN CONTENT_ITEM.EFFECTIVE_START_DATE AND CONTENT_ITEM.EFFECTIVE_END_DATE) CONTENT_ITEM_DETAILS 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 AR_RELATIONS.RELATED_OBJECT_ID = ASG_RECORDS.ASSIGNMENT_RECORD_ID AND AR_RELATIONS.RELATION_TYPE = 'ORA_OFFERING_COURSE' AND AR_RELATIONS.RELATED_OBJECT_TYPE = 'ORA_COURSE' 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 OFFERING_ASG_RECORDS.LEARNER_ID = ASG_RECORDS.LEARNER_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_LI.LEARNING_ITEM_ID = OFFERING_ASG_RECORDS.LEARNING_ITEM_ID AND OFFERING_LI.LEARNING_ITEM_TYPE='ORA_CLASS' AND OFFERING_LI_TL.LEARNING_ITEM_ID = OFFERING_LI.LEARNING_ITEM_ID AND OFFERING_LI_TL.LANGUAGE = USERENV('LANG') AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN OFFERING_LI.EFFECTIVE_START_DATE AND OFFERING_LI.EFFECTIVE_END_DATE AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN OFFERING_LI_TL.EFFECTIVE_START_DATE AND OFFERING_LI_TL.EFFECTIVE_END_DATE AND ACTIVITY.PARENT_LEARNING_ITEM_ID = OFFERING_LI.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 ACTIVITY_LI_TL.LEARNING_ITEM_ID = ACTIVITY_LI.LEARNING_ITEM_ID AND ACTIVITY_LI_TL.LANGUAGE = SYS_CONTEXT('USERENV','LANG') AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN ACTIVITY_LI.EFFECTIVE_START_DATE AND ACTIVITY_LI.EFFECTIVE_END_DATE AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN ACTIVITY_LI_TL.EFFECTIVE_START_DATE AND ACTIVITY_LI_TL.EFFECTIVE_END_DATE 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(+) AND VIDEO.LEARNING_ITEM_ID(+) = ACTIVITY.RELATED_CONTENT_ID AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN VIDEO.EFFECTIVE_START_DATE(+) AND VIDEO.EFFECTIVE_END_DATE(+) AND CONTENT_LI.LEARNING_ITEM_ID(+) = NVL(CONTENT.LEARNING_ITEM_ID,VIDEO.LEARNING_ITEM_ID) AND CONTENT_LI_TL.LEARNING_ITEM_ID(+) = CONTENT_LI.LEARNING_ITEM_ID AND CONTENT_LI_TL.LANGUAGE(+) = SYS_CONTEXT('USERENV','LANG') AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN CONTENT_LI.EFFECTIVE_START_DATE(+) AND CONTENT_LI.EFFECTIVE_END_DATE(+) AND TRUNC(NVL(OFFERING_ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN CONTENT_LI_TL.EFFECTIVE_START_DATE(+) AND CONTENT_LI_TL.EFFECTIVE_END_DATE(+) AND CONTENT_ITEM_DETAILS.LEARNING_ITEM_ID (+) = CONTENT.LEARNING_ITEM_ID /*AND ATTEMPT.RELATED_OBJECT_ID(+) = ASG_TASKS.ASSIGNMENT_TASK_ID AND ATTEMPT.LEARNER_ID(+) = ASG_TASKS.TASK_OWNER_ID */ 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 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, SPEC_LI.LEARNING_ITEM_TYPE, ASG_RECORDS.EFFECTIVE_START_DATE, ASG_RECORDS.EFFECTIVE_END_DATE FROM WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS, WLF_LEARNING_ITEMS_F SPEC_LI WHERE ASG_RECORDS.LEARNING_ITEM_ID = SPEC_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 SPEC_LI.LEARNING_ITEM_TYPE = 'ORA_SPECIALIZATION' AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN SPEC_LI.EFFECTIVE_START_DATE AND SPEC_LI.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.SECTION_ID, SECTION.NO_OF_MANDATORY_ITEMS, HRCHY_SPEC.POSITION AS SECTION_POSITION, HRCHY_SECTION.CHILD_LEARNING_ITEM_ID AS COURSE_LEARNING_ITEM_ID, COURSE_ASG_RECORDS.ASSIGNMENT_RECORD_ID AS COURSE_ASG_RECORD_ID, COURSE_ASG_RECORDS.ASSIGNMENT_RECORD_NUMBER AS COURSE_ASG_RECORD_NUMBER, COURSE_ASG_RECORDS.EFFECTIVE_START_DATE AS COU_ASG_EFFECTIVE_START_DATE, COURSE_ASG_RECORDS.EFFECTIVE_END_DATE AS COU_ASG_EFFECTIVE_END_DATE, COURSE_ASG_RECORDS.STATUS AS COURSE_ASG_STATUS, COURSE_ASG_RECORDS.ASSIGNMENT_STARTED_ON_DATE AS COURSE_ASG_STARTED_ON_DATE, COURSE_ASG_RECORDS.ASSIGNED_ON_DATE AS COURSE_ASG_ASSIGNED_ON_DATE, COURSE_ASG_RECORDS.COMPLETION_DATE AS COURSE_ASG_COMPLETION_DATE, COURSE_ASG_RECORDS.TOTAL_ACTUAL_EFFORT AS COURSE_ASG_TOTAL_ACTUAL_EFFORT, COURSE_ASG_RECORDS.LI_EFFECTIVE_DATE AS COURSE_ASG_LI_EFFECTIVE_DATE, CASE WHEN COURSE_ASG_RECORDS.LEARNING_ITEM_ID IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID,HRCHY_SPEC.CHILD_LEARNING_ITEM_ID,HRCHY_SECTION.CHILD_LEARNING_ITEM_ID,COURSE_ASG_RECORDS.LEARNER_ID ORDER BY COURSE_ASG_RECORDS.EVENT_TYPE DESC) ELSE 1 END AS COURSE_ASG_FOR_SPEC FROM SPECIALIZATION_ASG, WLF_LI_HIERARCHIES_F HRCHY_SPEC, WLF_LI_SECTIONS_F SECTION, WLF_LI_HIERARCHIES_F HRCHY_SECTION, WLF_ASSIGNMENT_RECORDS_F COURSE_ASG_RECORDS WHERE HRCHY_SPEC.LEARNING_ITEM_ID = SPECIALIZATION_ASG.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 AND COURSE_ASG_RECORDS.LEARNING_ITEM_ID(+) = HRCHY_SECTION.CHILD_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(+) = SPECIALIZATION_ASG.LEARNER_ID AND COURSE_ASG_RECORDS.EVENT_TYPE(+) IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT') ), SECTION_DETAILS AS ( SELECT PARENT_LEARNING_ITEM_ID, ASSIGNMENT_RECORD_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_INFO WHERE COURSE_ASG_FOR_SPEC = 1 GROUP BY ASSIGNMENT_RECORD_ID,PARENT_LEARNING_ITEM_ID,SECTION_LEARNING_ITEM_ID,NO_OF_MANDATORY_ITEMS ) ,SECTION_COMPLETION_DETAILS AS( SELECT PARENT_LEARNING_ITEM_ID, ASSIGNMENT_RECORD_ID, SECTION_LEARNING_ITEM_ID, TOTAL_COURSES, 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 ASSIGNMENT_RECORD_ID,PARENT_LEARNING_ITEM_ID,SECTION_LEARNING_ITEM_ID,TOTAL_COURSES ) SELECT SPECIALIZATION_ASG.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID, SPECIALIZATION_ASG.LEARNING_ITEM_TYPE AS PARENT_LEARNING_ITEM_TYPE, SPECIALIZATION_ASG.LEARNER_ID, SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID, /** Learning Activity Section Info **/ SECTION_COURSE_INFO.SECTION_ID AS ACTIVITY_SECTION_ID, SECTION_LI.LEARNING_ITEM_NUMBER AS ACTIVITY_SECTION_NUMBER, SECTION_LI_TL.NAME AS ACTIVITY_SECTION_TITLE, SECTION_LI_TL.DESCRIPTION AS ACTIVITY_SECTION_DESCRIPTION, ROW_NUMBER() OVER (PARTITION BY SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID ORDER BY SECTION_COURSE_INFO.SECTION_POSITION) AS ACTIVITY_SECTION_POSITION, CASE WHEN SECTION_COURSE_INFO.NO_OF_MANDATORY_ITEMS = -1 THEN SECTION_COMPLETION_DETAILS.TOTAL_COURSES ELSE SECTION_COURSE_INFO.NO_OF_MANDATORY_ITEMS END AS ACTIVITY_SECTION_COMPL_REQ, SECTION_COMPLETION_DETAILS.TOTAL_COURSES AS ACT_SECTION_TOTAL_ACTIVITIES, SECTION_COURSE_INFO.SECTION_ID AS ACTIVITY_ATTEMPT_SECTION_ID, CASE WHEN SECTION_COMPLETION_DETAILS.MANDATORY_COURSES = SECTION_COMPLETION_DETAILS.COMPLETED_COURSES THEN 'ORA_ASSN_REC_COMPLETE' ELSE 'ORA_ASSN_REC_INCOMPLETE' END AS ACTIVITY_SECTION_STATUS, SECTION_COMPLETION_DETAILS.COMPLETED_COURSES AS ACTIVITY_SECTION_PROGRESS, /** Learning Activity Info **/ COURSE_LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, COURSE_LI.LEARNING_ITEM_ID AS ACTIVITY_ID, COURSE_LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, COURSE_LI.LEARNING_ITEM_TYPE AS ACTIVITY_TYPE, COURSE_LI_TL.NAME AS ACTIVITY_TITLE, COURSE_LI_TL.DESCRIPTION AS ACTIVITY_DESCRIPTION, COURSE_LI_TL.DESCRIPTION_LONG AS ACTIVITY_DESCRIPTION_LONG, COURSE_LI_TL.DESCRIPTION_SHORT AS ACTIVITY_DESCRIPTION_SHORT, NULL AS ACTIVITY_POSITION, NULL AS ACTIVITY_PASSING_SCORE, CASE WHEN (COURSE.MINIMUM_TRAINING_HOURS IS NOT NULL AND COURSE.MAXIMUM_TRAINING_HOURS IS NOT NULL) THEN (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 ACTIVITY_EXPECTED_EFFORT, 'ORA_DUR_HOUR' AS ACTIVITY_EXPECTED_EFFORT_UNITS, NULL AS ACTIVITY_COMPL_REQ, NULL AS LEARNER_CAN_MARK_COMPLETE, /**ILT Activity Info**/ TRUNC(COURSE_LI.START_DATE) AS ACTIVITY_DATE, COURSE_LI.START_DATE AS ACTIVITY_START_TIME, COURSE_LI.END_DATE AS ACTIVITY_END_TIME, NULL AS ACTIVITY_TIME_ZONE, NULL AS VIRTUAL_CLASSROOM_JOIN_LINK, /**SP Activity Info**/ NULL AS CONTENT_LEARNING_ITEM_ID, NULL AS LEARNING_CONTENT_ID, NULL AS LEARNING_CONTENT_NUMBER, NULL AS LEARNING_CONTENT_TYPE, NULL AS LEARNING_CONTENT_TITLE, NULL AS LEARNING_CONTENT_SHORT_DESC, NULL AS LEARNING_CONTENT_QSTNR_ID, NUll AS LEARNING_CONTENT_QSNTR_NUMBER, NULL AS CONTENT_ATTEMPTS_ALLOWED, NULL AS TIME_ALLOWED_PER_ATTEMPT, NULL AS TIME_ALLOWED_PER_ATTEMPT_UNITS, NULL AS MARK_COMPLETION_ON_OPEN, NULL AS STARTING_URL, NULL AS CONTENT_LOCATION, NULL AS CONTENT_THUMBNAIL_ID, NULL AS CONTENT_STATUS, /** Learner activity attempt info**/ SECTION_COURSE_INFO.COURSE_ASG_RECORD_ID AS ACTIVITY_ATTEMPT_ID, SECTION_COURSE_INFO.COURSE_ASG_RECORD_NUMBER AS ACTIVITY_ATTEMPT_NUMBER, SECTION_COURSE_INFO.COURSE_ASG_STATUS AS ACTIVITY_ATTEMPT_STATUS, NULL AS ACTIVITY_TOTAL_ATTEMPTS, NVL(SECTION_COURSE_INFO.COURSE_ASG_STARTED_ON_DATE,SECTION_COURSE_INFO.COURSE_ASG_ASSIGNED_ON_DATE) AS ACTIVITY_ATTEMPT_STARTED_DATE, SECTION_COURSE_INFO.COURSE_ASG_COMPLETION_DATE AS ATTEMPT_COMPLETED_DATE, NULL AS ACTIVITY_ATTEMPT_EXEMPTED_DATE, NULL AS ATTEMPT_EXEMPTED_REASON_CODE, SECTION_COURSE_INFO.COURSE_ASG_TOTAL_ACTUAL_EFFORT AS ATTEMPT_ACTUAL_EFFORT, 'ORA_DUR_HOUR' AS ATTEMPT_ACTUAL_EFFORT_UNITS, NULL AS ACTIVITY_ATTEMPT_ACTUAL_SCORE, NULL AS OFFERING_LEARNING_ITEM_ID, NULL AS OFFERING_NAME, NULL AS OFFERING_NUMBER, NULL AS OFFERING_LI_EFFECTIVE_DATE, NULL AS OFFERING_ASSIGNMENT_RECORD_ID, COURSE_LI.LEARNING_ITEM_ID AS COURSE_LEARNING_ITEM_ID, SECTION_COURSE_INFO.COURSE_ASG_RECORD_ID AS COURSE_ASSIGNMENT_RECORD_ID, SPECIALIZATION_ASG.LI_EFFECTIVE_DATE AS LI_EFFECTIVE_DATE, NULL AS ASSIGNMENT_TASK_ID, COURSE_LI.PRICE AS MAX_PRICE, COURSE_LI.MIN_PRICE AS MIN_PRICE, COURSE_LI.CURRENCY_CODE AS CURRENCY_CODE, NULL AS HIDE_SCORE_LEARNER, NULL AS DISABLE_REVIEW, 'Y' AS PRIMARY_RELATIONSHIP_FLAG, SECTION_COURSE_INFO.COURSE_ASG_RECORD_ID AS ACTIVITY_ASSIGNMENT_RECORD_ID, SECTION_COURSE_INFO.COU_ASG_EFFECTIVE_START_DATE AS ACT_ASG_EFFECTIVE_START_DATE, SECTION_COURSE_INFO.COU_ASG_EFFECTIVE_END_DATE AS ACT_ASG_EFFECTIVE_END_DATE FROM SPECIALIZATION_ASG, SECTION_COURSE_INFO, WLF_LEARNING_ITEMS_F SECTION_LI, WLF_LEARNING_ITEMS_F_TL SECTION_LI_TL, WLF_LEARNING_ITEMS_F COURSE_LI, WLF_LEARNING_ITEMS_F_TL COURSE_LI_TL, WLF_LI_COURSES_F COURSE, SECTION_COMPLETION_DETAILS WHERE SECTION_COURSE_INFO.ASSIGNMENT_RECORD_ID = SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID AND SECTION_COURSE_INFO.COURSE_ASG_FOR_SPEC = 1 AND SECTION_LI.LEARNING_ITEM_ID = SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN SECTION_LI.EFFECTIVE_START_DATE AND SECTION_LI.EFFECTIVE_END_DATE AND SECTION_LI_TL.LEARNING_ITEM_ID = SECTION_LI.LEARNING_ITEM_ID AND SECTION_LI_TL.LANGUAGE = USERENV('LANG') AND TRUNC(NVL(SPECIALIZATION_ASG.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN SECTION_LI_TL.EFFECTIVE_START_DATE AND SECTION_LI_TL.EFFECTIVE_END_DATE AND SECTION_COMPLETION_DETAILS.ASSIGNMENT_RECORD_ID = SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID AND SECTION_COMPLETION_DETAILS.PARENT_LEARNING_ITEM_ID = SPECIALIZATION_ASG.LEARNING_ITEM_ID AND SECTION_COMPLETION_DETAILS.SECTION_LEARNING_ITEM_ID = SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID AND COURSE_LI.LEARNING_ITEM_ID = SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID AND COURSE_LI_TL.LEARNING_ITEM_ID = COURSE_LI.LEARNING_ITEM_ID AND COURSE_LI_TL.LANGUAGE = USERENV('LANG') AND TRUNC(NVL(SECTION_COURSE_INFO.COURSE_ASG_LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE_LI.EFFECTIVE_START_DATE AND COURSE_LI.EFFECTIVE_END_DATE AND TRUNC(NVL(SECTION_COURSE_INFO.COURSE_ASG_LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE_LI_TL.EFFECTIVE_START_DATE AND COURSE_LI_TL.EFFECTIVE_END_DATE AND COURSE.LEARNING_ITEM_ID = COURSE_LI.LEARNING_ITEM_ID AND TRUNC(NVL(SECTION_COURSE_INFO.COURSE_ASG_LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE.EFFECTIVE_START_DATE AND COURSE.EFFECTIVE_END_DATE AND WLF_CAN_ACCESS.WLF_SS_CAN_ACCESS_LI(COURSE_LI.LEARNING_ITEM_ID) = 'T' ) UNION ALL SELECT LI.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, /* Learning Activity Section Info **/ NULL AS ACTIVITY_SECTION_ID, NULL AS ACTIVITY_SECTION_NUMBER, NULL AS ACTIVITY_SECTION_TITLE, NULL AS ACTIVITY_SECTION_DESCRIPTION, NULL AS ACTIVITY_SECTION_POSITION, NULL AS ACTIVITY_SECTION_COMPL_REQ, NULL AS ACT_SECTION_TOTAL_ACTIVITIES, NULL AS ACTIVITY_ATTEMPT_SECTION_ID, NULL AS ACTIVITY_SECTION_STATUS, NULL AS ACTIVITY_SECTION_PROGRESS, /** Learning Activity Info **/ LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, LI.LEARNING_ITEM_ID AS ACTIVITY_ID, LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, 'ORA_ONLINE_SP' AS ACTIVITY_TYPE, LI_TL.NAME AS ACTIVITY_TITLE, LI_TL.DESCRIPTION AS ACTIVITY_DESCRIPTION, LI_TL.DESCRIPTION_LONG AS ACTIVITY_DESCRIPTION_LONG, LI_TL.DESCRIPTION_SHORT AS ACTIVITY_DESCRIPTION_SHORT, NULL AS ACTIVITY_POSITION, NULL AS ACTIVITY_PASSING_SCORE, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN TO_CHAR(LI.DURATION) ELSE NULL END AS ACTIVITY_EXPECTED_EFFORT, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION_UOM ELSE NULL END AS ACTIVITY_EXPECTED_EFFORT_UNITS, 'ORA_REQUIRED' AS ACTIVITY_COMPL_REQ, NULL AS LEARNER_CAN_MARK_COMPLETE, /**ILT Activity Info**/ NULL AS ACTIVITY_DATE, NULL AS ACTIVITY_START_TIME, NULL AS ACTIVITY_END_TIME, NULL AS ACTIVITY_TIME_ZONE, NULL AS VIRTUAL_CLASSROOM_JOIN_LINK, /**SP Activity Info**/ LI.LEARNING_ITEM_ID AS CONTENT_LEARNING_ITEM_ID, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN VIDEO.VIDEO_ID ELSE NULL END AS LEARNING_CONTENT_ID, LI.LEARNING_ITEM_NUMBER AS LEARNING_CONTENT_NUMBER, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.LEARNING_ITEM_TYPE ELSE NULL END AS LEARNING_CONTENT_TYPE, LI_TL.NAME AS LEARNING_CONTENT_TITLE, LI_TL.DESCRIPTION AS LEARNING_CONTENT_SHORT_DESC, NULL AS LEARNING_CONTENT_QSTNR_ID, NUll AS LEARNING_CONTENT_QSNTR_NUMBER, NULL AS CONTENT_ATTEMPTS_ALLOWED, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION ELSE NULL END AS TIME_ALLOWED_PER_ATTEMPT, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION_UOM ELSE NULL END AS TIME_ALLOWED_PER_ATTEMPT_UNITS, NULL AS MARK_COMPLETION_ON_OPEN, NULL AS STARTING_URL, LI.LOCATION AS CONTENT_LOCATION, LI.THUMBNAIL_ID AS CONTENT_THUMBNAIL_ID, LI.STATUS AS CONTENT_STATUS, /** Learner activity attempt info **/ NULL AS ACTIVITY_ATTEMPT_ID, NULL AS ACTIVITY_ATTEMPT_NUMBER, NULL AS ACTIVITY_ATTEMPT_STATUS, NULL AS ACTIVITY_TOTAL_ATTEMPTS, NULL AS ACTIVITY_ATTEMPT_STARTED_DATE, NULL AS ATTEMPT_COMPLETED_DATE, NULL AS ACTIVITY_ATTEMPT_EXEMPTED_DATE, NULL AS ATTEMPT_EXEMPTED_REASON_CODE, NULL AS ATTEMPT_ACTUAL_EFFORT, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION_UOM ELSE NULL END AS ATTEMPT_ACTUAL_EFFORT_UNITS, NULL AS ACTIVITY_ATTEMPT_ACTUAL_SCORE, NULL AS OFFERING_LEARNING_ITEM_ID, NULL AS OFFERING_NAME, NULL AS OFFERING_NUMBER, NULL AS OFFERING_LI_EFFECTIVE_DATE, NULL AS OFFERING_ASSIGNMENT_RECORD_ID, NULL AS COURSE_LEARNING_ITEM_ID, NULL AS COURSE_ASSIGNMENT_RECORD_ID, ASG_RECORDS.LI_EFFECTIVE_DATE AS LI_EFFECTIVE_DATE, NULL AS ASSIGNMENT_TASK_ID, NULL AS MAX_PRICE, NULL AS MIN_PRICE, NULL AS CURRENCY_CODE, NULL AS HIDE_SCORE_LEARNER, NULL AS DISABLE_REVIEW, 'Y' AS PRIMARY_RELATIONSHIP_FLAG, ASG_RECORDS.ASSIGNMENT_RECORD_ID AS ACTIVITY_ASSIGNMENT_RECORD_ID, ASG_RECORDS.EFFECTIVE_START_DATE AS ACT_ASG_EFFECTIVE_START_DATE, ASG_RECORDS.EFFECTIVE_END_DATE AS ACT_ASG_EFFECTIVE_END_DATE FROM WLF_LEARNING_ITEMS_F LI, WLF_LEARNING_ITEMS_F_TL LI_TL, WLF_ASSIGNMENT_RECORDS_F ASG_RECORDS, WLF_LI_VIDEOS_F VIDEO 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') AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN LI.EFFECTIVE_START_DATE AND LI.EFFECTIVE_END_DATE AND LI_TL.LEARNING_ITEM_ID = LI.LEARNING_ITEM_ID AND LI_TL.LANGUAGE = USERENV('LANG') AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN LI_TL.EFFECTIVE_START_DATE AND LI_TL.EFFECTIVE_END_DATE AND VIDEO.LEARNING_ITEM_ID(+) = LI.LEARNING_ITEM_ID AND TRUNC(NVL(ASG_RECORDS.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN VIDEO.EFFECTIVE_START_DATE(+) AND VIDEO.EFFECTIVE_END_DATE(+)