WLF_LRNR_COMPLETION_DETAILS_V

Details

• Schema: FUSION

• Object owner: WLF

• Object type: VIEW

Columns

Name

ACTIVITY_ID ACTIVITY_NUMBER ACTIVITY_LEARNING_ITEM_ID ACTIVITY_TYPE ACTIVITY_TITLE ACTIVITY_DESCRIPTION ACTIVITY_DESCRIPTION_SHORT ACTIVITY_DISPLAY_ORDER ACTIVITY_SECTION_ID ACTIVITY_SECTION_NUMBER ACTIVITY_SECTION_TITLE ACTIVITY_SECTION_DESCRIPTION ACTIVITY_SECTION_DISPLAY_ORDER ACTIVITY_SEC_COMPL_REQUIRED ACT_SECTION_TOTAL_ACTIVITIES ACTIVITY_SECTION_STATUS ACTIVITY_SECTION_STATUS_MNG ACTIVITY_SECTION_PROGRESS ACTIVITY_PASSING_SCORE ACTIVITY_EXPECTED_EFFORT ACTIVITY_EXPECTED_EFFORT_UNITS ACTIVITY_COMPL_REQUIREMENT ACTIVITY_LEARNER_MARK_COMPL ACTIVITY_DATE ACTIVITY_START_DATETIME ACTIVITY_END_DATETIME ACTIVITY_START_TIME ACTIVITY_END_TIME ACTIVITY_TIME_ZONE VIRTUAL_CLASSROOM_JOIN_LINK VIRTUAL_CLASSROOM_INFORMATION ADD_TO_CALENDAR_LINK E_LEARN_ID E_LEARN_CONTENT_ID E_LEARN_NUMBER E_LEARN_TYPE E_LEARN_TITLE E_LEARN_SHORT_DESCRIPTION E_LEARN_RICH_MEDIA_LINK E_LEARN_QUESTIONNAIRE_ID E_LEARN_QUESTIONNAIRE_NUMBER E_LEARN_ATTEMPTS_ALLOWED E_LEARN_TIME_PER_ATTEMPT E_LEARN_TIME_PER_ATTEMPT_UNIT DISABLE_REVIEW HIDE_SCORE_LEARNER HIDE_CORRECT_ANSWERS E_LEARN_CONTENT_LOCATION STARTING_URL E_LEARN_CONTENT_THUMBNAIL_ID E_LEARN_CONTENT_STATUS E_LEARN_COMPLETION_ON_OPEN ACTIVITY_ATTEMPT_ID ACTIVITY_ATTEMPT_NUMBER ACTIVITY_ATTEMPT_STATUS ACTIVITY_ATTEMPT_NEXT_ACTION ACTIVITY_ATTEMPT_DEEP_LINK ACTIVITY_ATTEMPT_EMBED_LINK ACTIVITY_ATTEMPT_STARTED_DATE ACTIVITY_ATTEMPT_COMPL_DATE ACTIVITY_ATTEMPT_EXEMPTED_DATE ACTIVITY_ATTEMPT_EXEMPTED_RC ACTIVITY_ATTEMPT_ACTUAL_EFFORT ACTIVITY_ATTEMPT_AE_UNITS ACTIVITY_ATTEMPT_ACTUAL_SCORE ACTIVITY_TOTAL_ATTEMPTS PARENT_LEARNING_ITEM_ID PARENT_LEARNING_ITEM_TYPE ASSIGNMENT_RECORD_ID OFFERING_LEARNING_ITEM_ID OFFERING_LI_EFFECTIVE_DATE OFFERING_ASSIGNMENT_RECORD_ID COURSE_ASSIGNMENT_RECORD_ID LI_EFFECTIVE_DATE SHOW_JOIN ASSIGNMENT_TASK_ID SHOW_ADD_TO_CALENDAR ACTIVITY_LONG_DATE ACTIVITY_SHORT_DATE LEARNER_ID MAXIMUM_PRICE MINIMUM_PRICE CURRENCY_CODE PRIMARY_RELATIONSHIP_FLAG ACTIVITY_ASSIGNMENT_RECORD_ID CONTEXTLEARNINGITEMID DATA_SECURITY_PRIVILEGE ACT_ASG_EFFECTIVE_START_DATE ACT_ASG_EFFECTIVE_END_DATE ACTIVITY_THUMBNAIL_LOCATION CONTENT_THUMBNAIL_LOCATION ACTIVITY_ATTEMPT_SUB_STATUS ACTIVITY_EVENT_ASSIGNMENT_ID

Query

SQL_Statement

SELECT /* WLF_LRNR_COMPLETION_DETAILS_V */ /*Learning activity info*/ ACTIVITY_ID, ACTIVITY_NUMBER, ACTIVITY_LEARNING_ITEM_ID, ACTIVITY_TYPE, ACTIVITY_TITLE, ACTIVITY_DESCRIPTION, ACTIVITY_DESCRIPTION_SHORT, ACTIVITY_POSITION as ACTIVITY_DISPLAY_ORDER, /*Learning activity section info*/ ACTIVITY_SECTION_ID, ACTIVITY_SECTION_NUMBER, ACTIVITY_SECTION_TITLE, ACTIVITY_SECTION_DESCRIPTION, ACTIVITY_SECTION_POSITION as ACTIVITY_SECTION_DISPLAY_ORDER, ACTIVITY_SECTION_COMPL_REQ AS ACTIVITY_SEC_COMPL_REQUIRED, ACT_SECTION_TOTAL_ACTIVITIES, ACTIVITY_SECTION_STATUS, NULL AS ACTIVITY_SECTION_STATUS_MNG, ACTIVITY_SECTION_PROGRESS, ACTIVITY_PASSING_SCORE, ACTIVITY_EXPECTED_EFFORT, ACTIVITY_EXPECTED_EFFORT_UNITS, ACTIVITY_COMPL_REQ AS ACTIVITY_COMPL_REQUIREMENT, LEARNER_CAN_MARK_COMPLETE AS ACTIVITY_LEARNER_MARK_COMPL, /*ILT activity info*/ ACTIVITY_DATE as ACTIVITY_DATE, ACTIVITY_START_TIME as ACTIVITY_START_DATETIME, ACTIVITY_END_TIME as ACTIVITY_END_DATETIME, '' as ACTIVITY_START_TIME, '' as ACTIVITY_END_TIME, ACTIVITY_TIME_ZONE, CASE WHEN VIRTUAL_CLASSROOM_JOIN_LINK IS NULL THEN (SELECT VILT_URL FROM WLF_ASSIGNMENT_TASKS_F assignment_tasks WHERE assignment_tasks.ASSIGNMENT_RECORD_ID = activities.OFFERING_ASSIGNMENT_RECORD_ID AND activities.ACTIVITY_LEARNING_ITEM_ID = assignment_tasks.learning_item_id and TRUNC(SYSDATE) BETWEEN assignment_tasks.effective_start_date and assignment_tasks.effective_end_date) ELSE VIRTUAL_CLASSROOM_JOIN_LINK END AS VIRTUAL_CLASSROOM_JOIN_LINK, VIRTUAL_CLASSROOM_JOIN_LINK AS VIRTUAL_CLASSROOM_INFORMATION, '' AS ADD_TO_CALENDAR_LINK, /*Self paced activity info*/ CONTENT_LEARNING_ITEM_ID as E_LEARN_ID, LEARNING_CONTENT_ID as E_LEARN_CONTENT_ID, LEARNING_CONTENT_NUMBER AS E_LEARN_NUMBER, LEARNING_CONTENT_TYPE AS E_LEARN_TYPE, LEARNING_CONTENT_TITLE AS E_LEARN_TITLE, LEARNING_CONTENT_SHORT_DESC AS E_LEARN_SHORT_DESCRIPTION, '' AS E_LEARN_RICH_MEDIA_LINK, LEARNING_CONTENT_QSTNR_ID AS E_LEARN_QUESTIONNAIRE_ID, '' AS E_LEARN_QUESTIONNAIRE_NUMBER, CONTENT_ATTEMPTS_ALLOWED AS E_LEARN_ATTEMPTS_ALLOWED, TIME_ALLOWED_PER_ATTEMPT AS E_LEARN_TIME_PER_ATTEMPT, TIME_ALLOWED_PER_ATTEMPT_UNITS AS E_LEARN_TIME_PER_ATTEMPT_UNIT, DISABLE_REVIEW, HIDE_SCORE_LEARNER, HIDE_CORRECT_ANSWERS, CONTENT_LOCATION AS E_LEARN_CONTENT_LOCATION, STARTING_URL AS STARTING_URL, CONTENT_THUMBNAIL_ID AS E_LEARN_CONTENT_THUMBNAIL_ID, CONTENT_STATUS AS E_LEARN_CONTENT_STATUS, MARK_COMPLETION_ON_OPEN AS E_LEARN_COMPLETION_ON_OPEN, /*Learner activity attempt info*/ ACTIVITY_ATTEMPT_ID, ACTIVITY_ATTEMPT_NUMBER, ACTIVITY_ATTEMPT_STATUS, NULL as ACTIVITY_ATTEMPT_NEXT_ACTION, NULL AS ACTIVITY_ATTEMPT_DEEP_LINK, NULL AS ACTIVITY_ATTEMPT_EMBED_LINK, ACTIVITY_ATTEMPT_STARTED_DATE, ATTEMPT_COMPLETED_DATE AS ACTIVITY_ATTEMPT_COMPL_DATE, ACTIVITY_ATTEMPT_EXEMPTED_DATE, ATTEMPT_EXEMPTED_REASON_CODE as ACTIVITY_ATTEMPT_EXEMPTED_RC, ATTEMPT_ACTUAL_EFFORT as ACTIVITY_ATTEMPT_ACTUAL_EFFORT, ATTEMPT_ACTUAL_EFFORT_UNITS as ACTIVITY_ATTEMPT_AE_UNITS, ACTIVITY_ATTEMPT_ACTUAL_SCORE, ACTIVITY_TOTAL_ATTEMPTS, PARENT_LEARNING_ITEM_ID, PARENT_LEARNING_ITEM_TYPE, ASSIGNMENT_RECORD_ID, OFFERING_LEARNING_ITEM_ID, OFFERING_LI_EFFECTIVE_DATE, OFFERING_ASSIGNMENT_RECORD_ID, COURSE_ASSIGNMENT_RECORD_ID, LI_EFFECTIVE_DATE, NULL AS SHOW_JOIN, ACTIVITIES.ASSIGNMENT_TASK_ID, NULL AS SHOW_ADD_TO_CALENDAR, NULL AS ACTIVITY_LONG_DATE, NULL AS ACTIVITY_SHORT_DATE, LEARNER_ID, MAX_PRICE AS MAXIMUM_PRICE, MIN_PRICE AS MINIMUM_PRICE, CURRENCY_CODE, PRIMARY_RELATIONSHIP_FLAG, ACTIVITY_ASSIGNMENT_RECORD_ID, NULL AS contextLearningItemId, 'ORA_LEARNER' AS DATA_SECURITY_PRIVILEGE, ACT_ASG_EFFECTIVE_START_DATE, ACT_ASG_EFFECTIVE_END_DATE, ACTIVITY_THUMBNAIL_LOCATION, CONTENT_THUMBNAIL_LOCATION, ACTIVITY_ATTEMPT_SUB_STATUS, ACTIVITY_EVENT_ASSIGNMENT_ID FROM ( 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_STATUS_MNG, NULL AS ACTIVITY_SECTION_PROGRESS, /** Learning Activity Info **/ ACTIVITY_LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, ACTIVITY.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_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.DISABLE_REVIEW, CONTENT.HIDE_SCORE_LEARNER, CASE WHEN CONTENT.LEARNING_ITEM_ID IS NULL THEN NULL WHEN (CONTENT.HIDE_CORRECT_ANSWERS IS NULL OR CONTENT.HIDE_CORRECT_ANSWERS='N') THEN 'Y' ELSE 'N' END AS HIDE_CORRECT_ANSWERS, 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, 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, ACTIVITY.EFFECTIVE_START_DATE AS ACT_EFFECTIVE_START_DATE, ACTIVITY.EFFECTIVE_END_DATE AS ACT_EFFECTIVE_END_DATE, CASE WHEN ACTIVITY_LI.THUMBNAIL_ID IS NOT NULL THEN (SELECT ACTIVITY_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS ACTIVITY_THUMBNAILS WHERE ACTIVITY_THUMBNAILS.THUMBNAIL_ID = ACTIVITY_LI.THUMBNAIL_ID) ELSE NULL END AS ACTIVITY_THUMBNAIL_LOCATION, CASE WHEN CONTENT_LI.THUMBNAIL_ID IS NOT NULL THEN (SELECT CONTENT_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS CONTENT_THUMBNAILS WHERE CONTENT_THUMBNAILS.THUMBNAIL_ID = CONTENT_LI.THUMBNAIL_ID) ELSE NULL END AS CONTENT_THUMBNAIL_LOCATION, NULL AS ACTIVITY_ATTEMPT_SUB_STATUS, ASG_RECORDS.EVENT_ASSIGNMENT_ID AS ACTIVITY_EVENT_ASSIGNMENT_ID 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, ASG_RECORDS.COMPLETION_DATE AS SPEC_COMPLETION_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, SPECIALIZATION_ASG.SPEC_COMPLETION_DATE FROM SPECIALIZATION_ASG, WLF_LI_HIERARCHIES_F HRCHY_SPEC, WLF_LI_SECTIONS_F SECTION, WLF_LI_HIERARCHIES_F HRCHY_SECTION 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 ), COURSE_ASG_INFO AS ( SELECT 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.SUB_STATUS AS COURSE_ASG_SUB_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, 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, COURSE_ASG_RECORDS.EVENT_ASSIGNMENT_ID AS COURSE_EVENT_ASSIGNMENT_ID 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_RECORD_NUMBER, COURSE_ASG_INFO.COU_ASG_EFFECTIVE_START_DATE, COURSE_ASG_INFO.COU_ASG_EFFECTIVE_END_DATE, COURSE_ASG_INFO.COURSE_ASG_STATUS, COURSE_ASG_INFO.COURSE_ASG_SUB_STATUS, COURSE_ASG_INFO.COURSE_ASG_STARTED_ON_DATE, COURSE_ASG_INFO.COURSE_ASG_ASSIGNED_ON_DATE, COURSE_ASG_INFO.COURSE_ASG_COMPLETION_DATE, COURSE_ASG_INFO.COURSE_ASG_TOTAL_ACTUAL_EFFORT, COURSE_ASG_INFO.COURSE_ASG_LI_EFFECTIVE_DATE, COURSE_ASG_INFO.COURSE_LEARNING_ITEM_ID, COURSE_ASG_INFO.COURSE_EVENT_ASSIGNMENT_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.LEARNER_ID, SECTION_COURSE_INFO.ASSIGNMENT_RECORD_ID, SECTION_COURSE_INFO.SECTION_LEARNING_ITEM_ID, SECTION_COURSE_INFO.SECTION_ID, SECTION_COURSE_INFO.NO_OF_MANDATORY_ITEMS, SECTION_COURSE_INFO.SECTION_POSITION, SECTION_COURSE_INFO.COURSE_LEARNING_ITEM_ID, LATEST_COURSE_ASG_INFO.COURSE_ASG_RECORD_ID, LATEST_COURSE_ASG_INFO.COURSE_ASG_RECORD_NUMBER, LATEST_COURSE_ASG_INFO.COU_ASG_EFFECTIVE_START_DATE, LATEST_COURSE_ASG_INFO.COU_ASG_EFFECTIVE_END_DATE, LATEST_COURSE_ASG_INFO.COURSE_ASG_STATUS, LATEST_COURSE_ASG_INFO.COURSE_ASG_SUB_STATUS, LATEST_COURSE_ASG_INFO.COURSE_ASG_STARTED_ON_DATE, LATEST_COURSE_ASG_INFO.COURSE_ASG_ASSIGNED_ON_DATE, LATEST_COURSE_ASG_INFO.COURSE_ASG_COMPLETION_DATE, LATEST_COURSE_ASG_INFO.COURSE_ASG_TOTAL_ACTUAL_EFFORT, LATEST_COURSE_ASG_INFO.COURSE_ASG_LI_EFFECTIVE_DATE, LATEST_COURSE_ASG_INFO.COURSE_EVENT_ASSIGNMENT_ID 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, 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_ASG_INFO 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_ASG_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_ASG_INFO.SECTION_POSITION) AS ACTIVITY_SECTION_POSITION, CASE WHEN SECTION_COURSE_ASG_INFO.NO_OF_MANDATORY_ITEMS = -1 THEN SECTION_COMPLETION_DETAILS.TOTAL_COURSES ELSE SECTION_COURSE_ASG_INFO.NO_OF_MANDATORY_ITEMS END AS ACTIVITY_SECTION_COMPL_REQ, SECTION_COMPLETION_DETAILS.TOTAL_COURSES AS ACT_SECTION_TOTAL_ACTIVITIES, SECTION_COURSE_ASG_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, NULL AS ACTIVITY_SECTION_STATUS_MNG, 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_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 DISABLE_REVIEW, NULL AS HIDE_SCORE_LEARNER, NULL AS HIDE_CORRECT_ANSWERS, 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_ASG_INFO.COURSE_ASG_RECORD_ID AS ACTIVITY_ATTEMPT_ID, SECTION_COURSE_ASG_INFO.COURSE_ASG_RECORD_NUMBER AS ACTIVITY_ATTEMPT_NUMBER, SECTION_COURSE_ASG_INFO.COURSE_ASG_STATUS AS ACTIVITY_ATTEMPT_STATUS, NULL AS ACTIVITY_TOTAL_ATTEMPTS, NVL(SECTION_COURSE_ASG_INFO.COURSE_ASG_STARTED_ON_DATE,SECTION_COURSE_ASG_INFO.COURSE_ASG_ASSIGNED_ON_DATE) AS ACTIVITY_ATTEMPT_STARTED_DATE, SECTION_COURSE_ASG_INFO.COURSE_ASG_COMPLETION_DATE AS ATTEMPT_COMPLETED_DATE, NULL AS ACTIVITY_ATTEMPT_EXEMPTED_DATE, NULL AS ATTEMPT_EXEMPTED_REASON_CODE, SECTION_COURSE_ASG_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_ASG_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, 'Y' AS PRIMARY_RELATIONSHIP_FLAG, SECTION_COURSE_ASG_INFO.COURSE_ASG_RECORD_ID AS ACTIVITY_ASSIGNMENT_RECORD_ID, SECTION_COURSE_ASG_INFO.COU_ASG_EFFECTIVE_START_DATE AS ACT_ASG_EFFECTIVE_START_DATE, SECTION_COURSE_ASG_INFO.COU_ASG_EFFECTIVE_END_DATE AS ACT_ASG_EFFECTIVE_END_DATE, COURSE_LI.EFFECTIVE_START_DATE AS ACT_EFFECTIVE_START_DATE, COURSE_LI.EFFECTIVE_END_DATE AS ACT_EFFECTIVE_END_DATE, CASE WHEN COURSE_LI.THUMBNAIL_ID IS NOT NULL THEN (SELECT ACTIVITY_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS ACTIVITY_THUMBNAILS WHERE ACTIVITY_THUMBNAILS.THUMBNAIL_ID = COURSE_LI.THUMBNAIL_ID) ELSE NULL END AS ACTIVITY_THUMBNAIL_LOCATION, NULL AS CONTENT_THUMBNAIL_LOCATION, SECTION_COURSE_ASG_INFO.COURSE_ASG_SUB_STATUS AS ACTIVITY_ATTEMPT_SUB_STATUS, SECTION_COURSE_ASG_INFO.COURSE_EVENT_ASSIGNMENT_ID AS ACTIVITY_EVENT_ASSIGNMENT_ID FROM SPECIALIZATION_ASG, SECTION_COURSE_ASG_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, WLF_ACCESS_PERMISSIONS_F COURSE_ACCESS, SECTION_COMPLETION_DETAILS WHERE SECTION_COURSE_ASG_INFO.ASSIGNMENT_RECORD_ID = SPECIALIZATION_ASG.ASSIGNMENT_RECORD_ID AND SECTION_LI.LEARNING_ITEM_ID = SECTION_COURSE_ASG_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_ASG_INFO.SECTION_LEARNING_ITEM_ID AND COURSE_LI.LEARNING_ITEM_ID = SECTION_COURSE_ASG_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_ASG_INFO.COURSE_ASG_LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE_LI.EFFECTIVE_START_DATE AND COURSE_LI.EFFECTIVE_END_DATE AND TRUNC(NVL(SECTION_COURSE_ASG_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_ASG_INFO.COURSE_ASG_LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN COURSE.EFFECTIVE_START_DATE AND COURSE.EFFECTIVE_END_DATE AND COURSE_LI.ACCESS_PERMISSION_ID = COURSE_ACCESS.ACCESS_PERMISSION_ID AND TRUNC(SYSDATE) BETWEEN COURSE_ACCESS.EFFECTIVE_START_DATE AND COURSE_ACCESS.EFFECTIVE_END_DATE AND (COURSE_ACCESS.FOLLOW_SPEC='Y' OR (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_STATUS_MNG, 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_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 DISABLE_REVIEW, NULL AS HIDE_SCORE_LEARNER, NULL AS HIDE_CORRECT_ANSWERS, 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, '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, LI.EFFECTIVE_START_DATE AS ACT_EFFECTIVE_START_DATE, LI.EFFECTIVE_END_DATE AS ACT_EFFECTIVE_END_DATE, CASE WHEN LI.THUMBNAIL_ID IS NOT NULL THEN (SELECT ACTIVITY_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS ACTIVITY_THUMBNAILS WHERE ACTIVITY_THUMBNAILS.THUMBNAIL_ID = LI.THUMBNAIL_ID) ELSE NULL END AS ACTIVITY_THUMBNAIL_LOCATION, CASE WHEN LI.THUMBNAIL_ID IS NOT NULL THEN (SELECT CONTENT_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS CONTENT_THUMBNAILS WHERE CONTENT_THUMBNAILS.THUMBNAIL_ID = LI.THUMBNAIL_ID) ELSE NULL END AS CONTENT_THUMBNAIL_LOCATION, NULL AS ACTIVITY_ATTEMPT_SUB_STATUS, ASG_RECORDS.EVENT_ASSIGNMENT_ID AS ACTIVITY_EVENT_ASSIGNMENT_ID 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(+) ) ACTIVITIES