WLF_CI_ACTIVITY_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_LONG_DESCRIPTION ACTIVITY_SHORT_DESCRIPTION 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_PASSING_SCORE ELEARN_HIDE_ACTUAL_SCORE ELEARN_DISABLE_REVIEW ELEARN_SHOW_RESPONSE_FEEDBACK 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_RICH_MEDIA_AUTH_TOKEN E_LEARN_QUESTIONNAIRE_ID E_LEARN_QUESTIONNAIRE_NUMBER E_LEARN_ATTEMPTS_ALLOWED E_LEARN_TIME_PER_ATTEMPT E_LEARN_TIME_PER_ATTEMPT_UNIT E_LEARN_COMPLETION_ON_OPEN E_LEARN_CONTENT_LOCATION STARTING_URL E_LEARN_CONTENT_THUMBNAIL_ID PARENT_LEARNING_ITEM_ID PARENT_LEARNING_ITEM_TYPE OFFERING_START_DATE OFFERING_END_DATE MAXIMUM_PRICE MINIMUM_PRICE CURRENCY_CODE PRIMARY_CLASSROOM_ID PRIMARY_INSTRUCTOR_ID ACTIVITY_CALCULATED_RATING ACTIVITY_THUMBNAIL_ID ACTIVITY_THUMBNAIL_LOCATION CONTENT_THUMBNAIL_LOCATION

Query

SQL_Statement

/**** Select clause for Offerings***/ SELECT /*Learning activity info*/ ACTIVITY_LI.LEARNING_ITEM_ID AS ACTIVITY_ID, ACTIVITY_LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, ACTIVITY_LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, ACTIVITY.ACTIVITY_TYPE, (CASE WHEN ACTIVITY.ACTIVITY_TYPE = 'ORA_DEF_QUESTIONNAIRE' THEN 'Evaluation' ELSE ACTIVITY_LI_TL.NAME END) AS ACTIVITY_TITLE, ACTIVITY_LI_TL.DESCRIPTION AS ACTIVITY_DESCRIPTION, ACTIVITY_LI_TL.DESCRIPTION_LONG AS ACTIVITY_LONG_DESCRIPTION, ACTIVITY_LI_TL.DESCRIPTION_SHORT AS ACTIVITY_SHORT_DESCRIPTION, 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 = ACTIVITY.PARENT_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 GROUP BY ACTIVITY.PARENT_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 = ACTIVITY.PARENT_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 HRCHY_SECTION.CHILD_LEARNING_ITEM_ID = ACTIVITY_LI.LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE ) END AS ACTIVITY_DISPLAY_ORDER, /*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_DISPLAY_ORDER, NULL AS ACTIVITY_SEC_COMPL_REQUIRED, NULL AS ACT_SECTION_TOTAL_ACTIVITIES, CONTENT.MASTERY_SCORE AS ACTIVITY_PASSING_SCORE, CONTENT.HIDE_SCORE_LEARNER AS ELEARN_HIDE_ACTUAL_SCORE, CONTENT.DISABLE_REVIEW AS ELEARN_DISABLE_REVIEW, 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 ELEARN_SHOW_RESPONSE_FEEDBACK, 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(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(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_REQUIREMENT, ACTIVITY.SELF_COMPLETE_FLAG AS ACTIVITY_LEARNER_MARK_COMPL, /*ILT activity info*/ TRUNC(ACTIVITY_LI.START_DATE) AS ACTIVITY_DATE, ACTIVITY_LI.START_DATE AS ACTIVITY_START_DATETIME, ACTIVITY_LI.END_DATE AS ACTIVITY_END_DATETIME, '' AS ACTIVITY_START_TIME, '' AS ACTIVITY_END_TIME, ACTIVITY.TIME_ZONE AS ACTIVITY_TIME_ZONE, ACTIVITY.VIRTUAL_CLASSROOM_URL AS VIRTUAL_CLASSROOM_JOIN_LINK, '' AS VIRTUAL_CLASSROOM_INFORMATION, '' AS ADD_TO_CALENDAR_LINK, /*Self paced activity info*/ CONTENT_LI.LEARNING_ITEM_ID AS E_LEARN_ID, CASE WHEN CONTENT_LI.LEARNING_ITEM_TYPE = 'ORA_CONTENT_VIDEO' THEN VIDEO.VIDEO_ID ELSE CONTENT.CONTENT_ID END AS E_LEARN_CONTENT_ID, CONTENT_LI.LEARNING_ITEM_NUMBER AS E_LEARN_NUMBER, CASE WHEN CONTENT_LI.LEARNING_ITEM_TYPE = 'ORA_CONTENT_VIDEO' THEN 'ORA_VIDEO' ELSE CONTENT.TRACKING_TYPE END AS E_LEARN_TYPE, CONTENT_LI_TL.NAME AS E_LEARN_TITLE, CONTENT_LI_TL.DESCRIPTION AS E_LEARN_SHORT_DESCRIPTION, '' AS E_LEARN_RICH_MEDIA_LINK, '' AS E_LEARN_RICH_MEDIA_AUTH_TOKEN, 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 = ACTIVITY.PARENT_LEARNING_ITEM_ID AND TRUNC(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(SYSDATE) BETWEEN RELATIONS.EFFECTIVE_START_DATE AND RELATIONS.EFFECTIVE_END_DATE) ELSE ACTIVITY.RELATED_CONTENT_ID END ELSE NULL END AS E_LEARN_QUESTIONNAIRE_ID, '' AS E_LEARN_QUESTIONNAIRE_NUMBER, CONTENT.MAX_ATTEMPTS AS E_LEARN_ATTEMPTS_ALLOWED, CONTENT.TIME_LIMIT AS E_LEARN_TIME_PER_ATTEMPT, CONTENT.TIME_LIMIT_UOM AS E_LEARN_TIME_PER_ATTEMPT_UNIT, CONTENT_ITEM_DETAILS.COMPLETION_ON_OPEN AS E_LEARN_COMPLETION_ON_OPEN, CONTENT_LI.LOCATION AS E_LEARN_CONTENT_LOCATION, CONTENT_ITEM_DETAILS.STARTING_URL AS STARTING_URL, CONTENT_LI.THUMBNAIL_ID AS E_LEARN_CONTENT_THUMBNAIL_ID, ACTIVITY.PARENT_LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID, 'ORA_CLASS' AS PARENT_LEARNING_ITEM_TYPE, /** Course Activity Details **/ NULL AS OFFERING_START_DATE, NULL AS OFFERING_END_DATE, NULL AS MAXIMUM_PRICE, NULL AS MINIMUM_PRICE, NULL AS CURRENCY_CODE, ACTIVITY_OFFERING.PRIMARY_CLASSROOM_ID, ACTIVITY_OFFERING.PRIMARY_INSTRUCTOR_ID, NULL AS ACTIVITY_CALCULATED_RATING, ACTIVITY_LI.THUMBNAIL_ID AS ACTIVITY_THUMBNAIL_ID, 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 FROM WLF_LEARNING_ITEMS_F ACTIVITY_LI, WLF_LEARNING_ITEMS_F_TL ACTIVITY_LI_TL, WLF_LI_ACTIVITIES_F ACTIVITY, 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_LI_CLASSES_F ACTIVITY_OFFERING, (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 TRUNC(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(SYSDATE) BETWEEN ACTIVITY_LI.EFFECTIVE_START_DATE AND ACTIVITY_LI.EFFECTIVE_END_DATE AND TRUNC(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(SYSDATE) BETWEEN CONTENT.EFFECTIVE_START_DATE(+) AND CONTENT.EFFECTIVE_END_DATE(+) AND VIDEO.LEARNING_ITEM_ID(+) = ACTIVITY.RELATED_CONTENT_ID AND TRUNC(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(SYSDATE) BETWEEN CONTENT_LI.EFFECTIVE_START_DATE(+) AND CONTENT_LI.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN CONTENT_LI_TL.EFFECTIVE_START_DATE(+) AND CONTENT_LI_TL.EFFECTIVE_END_DATE(+) AND ACTIVITY_OFFERING.LEARNING_ITEM_ID = ACTIVITY.PARENT_LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN ACTIVITY_OFFERING.EFFECTIVE_START_DATE AND ACTIVITY_OFFERING.EFFECTIVE_END_DATE AND CONTENT_ITEM_DETAILS.LEARNING_ITEM_ID (+) = CONTENT.LEARNING_ITEM_ID UNION ALL /**** Select clause for Specialization***/ SELECT /*Learning activity info*/ COURSE_LI.LEARNING_ITEM_ID AS ACTIVITY_ID, COURSE_LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, COURSE_LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, 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_LONG_DESCRIPTION, COURSE_LI_TL.DESCRIPTION_SHORT AS ACTIVITY_SHORT_DESCRIPTION, ROW_NUMBER() OVER (PARTITION BY SECTION_LI.LEARNING_ITEM_ID ORDER BY HRCHY_SECTION.POSITION) AS ACTIVITY_DISPLAY_ORDER, /*Learning activity section info*/ SECTION.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, DENSE_RANK() OVER (PARTITION BY SPEC_LI.LEARNING_ITEM_ID ORDER BY HRCHY_SPEC.POSITION) AS ACTIVITY_SECTION_DISPLAY_ORDER, SECTION.NO_OF_MANDATORY_ITEMS AS ACTIVITY_SEC_COMPL_REQUIRED, (SELECT COUNT(CHILD_LEARNING_ITEM_ID) FROM WLF_LI_HIERARCHIES_F SECTION_HIERARCHY WHERE SECTION_HIERARCHY.LEARNING_ITEM_ID = SECTION_LI.LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN SECTION_HIERARCHY.EFFECTIVE_START_DATE AND SECTION_HIERARCHY.EFFECTIVE_END_DATE GROUP BY SECTION_HIERARCHY.LEARNING_ITEM_ID )AS ACT_SECTION_TOTAL_ACTIVITIES, NULL AS ACTIVITY_PASSING_SCORE, NULL AS ELEARN_HIDE_ACTUAL_SCORE, NULL AS ELEARN_DISABLE_REVIEW, NULL AS ELEARN_SHOW_RESPONSE_FEEDBACK, 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_REQUIREMENT, NULL AS ACTIVITY_LEARNER_MARK_COMPL, /*ILT activity info*/ TRUNC(COURSE_LI.START_DATE) AS ACTIVITY_DATE, COURSE_LI.START_DATE AS ACTIVITY_START_DATETIME, COURSE_LI.END_DATE AS ACTIVITY_END_DATETIME, '' as ACTIVITY_START_TIME, '' as ACTIVITY_END_TIME, NULL AS ACTIVITY_TIME_ZONE, '' AS VIRTUAL_CLASSROOM_JOIN_LINK, '' AS VIRTUAL_CLASSROOM_JOIN_INFO, '' AS ADD_TO_CALENDER_LINK, /*Self paced activity info*/ NULL AS E_LEARN_ID, NULL AS E_LEARN_CONTENT_ID, NULL AS E_LEARN_NUMBER, NULL AS E_LEARN_TYPE, NULL AS E_LEARN_TITLE, NULL AS E_LEARN_SHORT_DESCRIPTION, '' AS E_LEARN_RICH_MEDIA_LINK, '' AS E_LEARN_RICH_MEDIA_AUTH_TOKEN, NULL AS E_LEARN_QUESTIONNAIRE_ID, '' AS E_LEARN_QUESTIONNAIRE_NUMBER, NULL AS E_LEARN_ATTEMPTS_ALLOWED, NULL AS E_LEARN_TIME_PER_ATTEMPT, NULL AS E_LEARN_TIME_PER_ATTEMPT_UNIT, NULL AS E_LEARN_COMPLETION_ON_OPEN, NULL AS E_LEARN_CONTENT_LOCATION, NULL AS STARTING_URL, NULL AS E_LEARN_CONTENT_THUMBNAIL_ID, SPEC_LI.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID, SPEC_LI.LEARNING_ITEM_TYPE AS PARENT_LEARNING_ITEM_TYPE, /** Course Activity Details **/ null AS OFFERING_START_DATE, null AS OFFERING_END_DATE, COURSE_LI.PRICE AS MAXIMUM_PRICE, COURSE_LI.MIN_PRICE AS MINIMUM_PRICE, COURSE_LI.CURRENCY_CODE AS CURRENCY_CODE, NULL AS PRIMARY_CLASSROOM_ID, NULL AS PRIMARY_INSTRUCTOR_ID, COURSE_LI.CALCULATED_RATING AS ACTIVITY_CALCULATED_RATING, COURSE_LI.THUMBNAIL_ID AS ACTIVITY_THUMBNAIL_ID, 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 FROM WLF_LEARNING_ITEMS_F SPEC_LI, WLF_LI_HIERARCHIES_F HRCHY_SPEC, WLF_LI_HIERARCHIES_F HRCHY_SECTION, WLF_LI_SECTIONS_F SECTION, 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 WHERE SPEC_LI.LEARNING_ITEM_TYPE = 'ORA_SPECIALIZATION' AND TRUNC(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(SYSDATE) BETWEEN HRCHY_SPEC.EFFECTIVE_START_DATE AND HRCHY_SPEC.EFFECTIVE_END_DATE AND SECTION_LI.LEARNING_ITEM_ID = HRCHY_SPEC.CHILD_LEARNING_ITEM_ID AND TRUNC(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(SYSDATE) BETWEEN SECTION_LI_TL.EFFECTIVE_START_DATE AND SECTION_LI_TL.EFFECTIVE_END_DATE AND SECTION.LEARNING_ITEM_ID = HRCHY_SPEC.CHILD_LEARNING_ITEM_ID AND TRUNC(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(SYSDATE) BETWEEN HRCHY_SECTION.EFFECTIVE_START_DATE AND HRCHY_SECTION.EFFECTIVE_END_DATE AND COURSE_LI.LEARNING_ITEM_ID = HRCHY_SECTION.CHILD_LEARNING_ITEM_ID AND COURSE_LI_TL.LEARNING_ITEM_ID = COURSE_LI.LEARNING_ITEM_ID AND COURSE_LI_TL.LANGUAGE = USERENV('LANG') AND TRUNC(SYSDATE) BETWEEN COURSE_LI.EFFECTIVE_START_DATE AND COURSE_LI.EFFECTIVE_END_DATE AND TRUNC(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(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 clause for Video and Tutorial***/ SELECT /*Learning activity info*/ LI.LEARNING_ITEM_ID AS ACTIVITY_ID, LI.LEARNING_ITEM_NUMBER AS ACTIVITY_NUMBER, LI.LEARNING_ITEM_ID AS ACTIVITY_LEARNING_ITEM_ID, 'ORA_ONLINE_SP' AS ACTIVITY_TYPE, LI_TL.NAME AS ACTIVITY_TITLE, LI_TL.DESCRIPTION AS ACTIVITY_DESCRIPTION, LI_TL.DESCRIPTION_LONG AS ACTIVITY_LONG_DESCRIPTION, LI_TL.DESCRIPTION_SHORT AS ACTIVITY_SHORT_DESCRIPTION, NULL AS ACTIVITY_DISPLAY_ORDER, /*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_DISPLAY_ORDER, NULL AS ACTIVITY_SEC_COMPL_REQUIRED, NULL AS ACT_SECTION_TOTAL_ACTIVITIES, /*Learning activity info*/ NULL AS ACTIVITY_PASSING_SCORE, NULL AS ELEARN_HIDE_ACTUAL_SCORE, NULL AS ELEARN_DISABLE_REVIEW, NULL AS ELEARN_SHOW_RESPONSE_FEEDBACK, 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_REQUIREMENT, NULL AS ACTIVITY_LEARNER_MARK_COMPL, /*ILT activity info*/ NULL AS ACTIVITY_DATE, NULL AS ACTIVITY_START_DATETIME, NULL AS ACTIVITY_END_DATETIME, '' as ACTIVITY_START_TIME, '' as ACTIVITY_END_TIME, NULL AS ACTIVITY_TIME_ZONE, NULL AS VIRTUAL_CLASSROOM_JOIN_LINK, '' AS VIRTUAL_CLASSROOM_INFORMATION, '' AS ADD_TO_CALENDAR_LINK, /*Self paced activity info*/ LI.LEARNING_ITEM_ID AS E_LEARN_ID, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN VIDEO.VIDEO_ID ELSE NULL END AS E_LEARN_CONTENT_ID, LI.LEARNING_ITEM_NUMBER AS E_LEARN_NUMBER, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.LEARNING_ITEM_TYPE ELSE NULL END AS E_LEARN_TYPE, LI_TL.NAME AS E_LEARN_TITLE, LI_TL.DESCRIPTION_SHORT AS E_LEARN_SHORT_DESCRIPTION, '' AS E_LEARN_RICH_MEDIA_LINK, '' AS E_LEARN_RICH_MEDIA_AUTH_TOKEN, NULL AS E_LEARN_QUESTIONNAIRE_ID, '' AS E_LEARN_QUESTIONNAIRE_NUMBER, NULL AS E_LEARN_ATTEMPTS_ALLOWED, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION ELSE NULL END AS E_LEARN_TIME_PER_ATTEMPT, CASE WHEN (LI.LEARNING_ITEM_TYPE ='ORA_VIDEO') THEN LI.DURATION_UOM ELSE NULL END AS E_LEARN_TIME_PER_ATTEMPT_UNIT, NULL AS E_LEARN_COMPLETION_ON_OPEN, LI.LOCATION AS E_LEARN_CONTENT_LOCATION, NULL AS STARTING_URL, LI.THUMBNAIL_ID AS E_LEARN_CONTENT_THUMBNAIL_ID, LI.LEARNING_ITEM_ID AS PARENT_LEARNING_ITEM_ID, LI.LEARNING_ITEM_TYPE AS PARENT_LEARNING_ITEM_TYPE, /** Course Activity Details **/ NULL AS OFFERING_START_DATE, NULL AS OFFERING_END_DATE, NULL AS MAXIMUM_PRICE, NULL AS MINIMUM_PRICE, NULL AS CURRENCY_CODE, NULL AS PRIMARY_CLASSROOM_ID, NULL AS PRIMARY_INSTRUCTOR_ID, LI.CALCULATED_RATING AS ACTIVITY_CALCULATED_RATING, LI.THUMBNAIL_ID AS ACTIVITY_THUMBNAIL_ID, 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 FROM WLF_LEARNING_ITEMS_F LI, WLF_LEARNING_ITEMS_F_TL LI_TL, WLF_LI_VIDEOS_F VIDEO WHERE LI.LEARNING_ITEM_TYPE IN ('ORA_VIDEO','ORA_TUTORIAL') AND TRUNC(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(SYSDATE) BETWEEN LI_TL.EFFECTIVE_START_DATE AND LI_TL.EFFECTIVE_END_DATE AND VIDEO.LEARNING_ITEM_ID(+) = LI.LEARNING_ITEM_ID AND TRUNC(SYSDATE) BETWEEN VIDEO.EFFECTIVE_START_DATE(+) AND VIDEO.EFFECTIVE_END_DATE(+)