WLF_LEARN_COMPLETION_DETAILS_V

Details

• Schema: FUSION

• Object owner: WLF

• Object type: VIEW

Columns

Name

LEARNING_ITEM_ID LEARNING_ITEM_NUMBER ACTIVITY_TYPE NAME TASK_POSITION SECTION_LI_ID ACTIVITYSECTIONNUMBER ACTIVITYSECTIONTITLE ACTIVITYSECTIONDISPLAYORDER ACTIVITYPASSINGSCORE ACTIVITYEXPECTEDEFFORT ACTIVITYEFFORTUNITS ELEARNQUESTIONNAIREID ELEARNQUESTIONNAIRENUMBER ELEARNATTEMPTSALLOWED ASSIGNMENT_TASK_ID ACTIVITYATTEMPTNUMBER ACTIVITY_ATTEMPT_STATUS ACTIVITYATTEMPTSTARTEDDATE COMPLETION_DATE EXEMPTED_ON_DATE REASON_CODE EFFORT EFFORT_UOM SCORE ACTIVITYTOTALATTEMPTS ASSIGNMENT_RECORD_ID COMP_DTLS_ASSIGN_REC_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE OBJECT_VERSION_NUMBER ACTIVITY_EFFECTIVE_START_DATE ACTIVITY_EFFECTIVE_END_DATE ACTIVITY_ACTIVITY_ID OBJECT_VERSION_NUMBER1 LI_LI_ID LI_EFFECTIVE_START_DATE LI_EFFECTIVE_END_DATE LI_TYPE LI_OBJECT_VERSION_NUMBER LIT_LI_ID LIT_EFFECTIVE_START_DATE LIT_EFFECTIVE_END_DATE LIT_OBJECT_VERSION_NUMBER LANGUAGE CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN LAST_UPDATE_DATE ACTIVITY_CREATED_BY ACTIVITY_CREATION_DATE ACTIVITY_LAST_UPDATE_LOGIN ACTIVITY_LAST_UPDATED_BY ACTIVITY_LAST_UPDATE_DATE LI_CREATED_BY LI_CREATION_DATE LI_LAST_UPDATE_DATE LI_LAST_UPDATE_LOGIN LI_LAST_UPDATED_BY LIT_CREATED_BY LIT_CREATION_DATE LIT_LAST_UPDATE_LOGIN LIT_LAST_UPDATED_BY LIT_LAST_UPDATE_DATE ACTIVITY_START_DATETIME ACTIVITY_END_DATETIME TASK_OWNER_ID SELF_COMPLETE_FLAG OFFERING_ASSIGNMENT_RECORD_ID ACT_ATTEMPT_LOCKED ACTIVITY_DESCRIPTION ACTIVITY_LEARNING_ITEM_ID ACTIVITY_SEC_COMPL_REQUIRED ACTIVITY_SECTION_PROGRESS ACT_SECT_ATTEMPT_LOCKED ACTIVITY_SECTION_DESCRIPTION ACTIVITY_DESCRIPTION_SHORT E_LEARN_COMPLETION_ON_OPEN TIME_ZONE HIDE_SCORE_LEARNER E_LEARN_ID E_LEARN_TIME_PER_ATTEMPT E_LEARN_TIME_PER_ATTEMPT_UNIT E_LEARN_TYPE OFFERING_LEARNING_ITEM_ID PARENT_LEARNING_ITEM_ID CONTENT_THUMBNAIL_LOCATION E_LEARN_CONTENT_STATUS STARTING_URL E_LEARN_CONTENT_THUMBNAIL_ID E_LEARN_CONTENT_LOCATION ACTIVITY_SEC_PREDECESSOR_ID COURSE_ASSIGNMENT_RECORD_ID ACTIVITY_ASSIGNMENT_RECORD_ID ACTIVITY_EVENT_ASSIGNMENT_ID PRIMARY_RELATIONSHIP_FLAG ACTIVITY_PREDECESSOR_ID ACTIVITY_SECTION_STATUS DISABLE_REVIEW E_LEARN_DISABLE_CONT_REVIEW VIRTUAL_CLASSROOM_JOIN_LINK ACTIVITYCOMPLREQUIREMENT ACTIVITY_DATE E_LEARN_DIS_REV_PASS_ATTEMPTS ACTIVITY_ATTEMPT_COMPL_DATE PARENT_LEARNING_ITEM_TYPE LI_EFFECTIVE_DATE E_LEARN_TITLE ACTIVITY_ATTEMPT_ID ACTIVITY_THUMBNAIL_LOCATION E_LEARN_NUMBER ACTIVITY_MAX_EXP_EFFORT_MINS ACTIVITY_MIN_EXP_EFFORT_MINS ACTIVITY_EXPECTED_EFFORT_UNITS ELEARN_SHOW_RESPONSE_FEEDBACK PARENT_LEARNING_ITEM_SUBTYPE SECTION_LI_ID_VALUE ACTIVITY_VILT_URL ATMPT_ASSIGNED_TO_NUMBER ATMPT_ASSIGNED_TO_DISP_NAME ACTVTY_ATMPT_ASSIGNED_TO_TYPE E_LEARN_QSTNR_SUBJECT_ID

Query

SQL_Statement

SELECT /* WLF_LEARN_COMPLETION_DETAILS_V */ assignmenttaskdeo.learning_item_id, learningitemdeo.learning_item_number, activitydeo.activity_type, CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN (SELECT courselitl.name from wlf_learning_items_f_tl courselitl where courselitl.learning_item_id = activitydeo.related_content_id AND courselitl.language = sys_context('USERENV', 'LANG') AND trunc(sysdate) BETWEEN courselitl.effective_start_date AND courselitl.effective_end_date AND ROWNUM=1) when ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN parentlearningitemtransdeo.name ELSE learningitemtranslationdeo.name END AS NAME, assignmenttaskdeo.task_position, ( SELECT section.learning_item_id FROM wlf_li_sections_f section WHERE assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN section.effective_start_date AND section.effective_end_date AND ROWNUM=1 ) as section_li_id, ( SELECT l.learning_item_number AS activity_section_number FROM wlf_learning_items_f l,wlf_li_sections_f section WHERE assignmenttaskdeo.section_li_id = l.learning_item_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN l.effective_start_date AND l.effective_end_date AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN section.effective_start_date AND section.effective_end_date AND ROWNUM=1 ) AS activitysectionnumber, ( SELECT lit.name AS activity_section_title FROM wlf_learning_items_f_tl lit,wlf_li_sections_f section WHERE assignmenttaskdeo.section_li_id = lit.learning_item_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND lit.language = sys_context('USERENV', 'LANG') AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN lit.effective_start_date AND lit.effective_end_date AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN section.effective_start_date AND section.effective_end_date AND ROWNUM=1 ) AS activitysectiontitle, ( SELECT t.task_position AS activity_section_position FROM wlf_assignment_tasks_f t,wlf_li_sections_f section WHERE assignmenttaskdeo.section_li_id = t.learning_item_id AND assignmenttaskdeo.assignment_record_id = t.assignment_record_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN t.effective_start_date AND t.effective_end_date AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN section.effective_start_date AND section.effective_end_date AND ROWNUM=1 ) AS activitysectiondisplayorder, CASE WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS' AND activitydeo.related_content_id IS NOT NULL) THEN ( SELECT content.mastery_score FROM wlf_li_content_f content WHERE content.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content.effective_start_date AND content.effective_end_date AND ROWNUM=1 ) ELSE NULL END as activityPassingScore, ( CASE WHEN ( activitydeo.activity_type = 'ORA_COURSE' AND assignmenttaskdeo.source_object_id IS NOT NULL ) THEN ( SELECT 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 FROM wlf_li_courses_f course, wlf_assignment_records_f course_latest_asg WHERE course_latest_asg.event_type IN ( 'ORA_REQUIRE_ASSIGNMENT', 'ORA_JOIN_ASSIGNMENT' ) AND course_latest_asg.status NOT IN ( 'ORA_ASSN_REC_WITHDRAWN', 'ORA_ASSN_REC_REQ_REJECTED', 'ORA_ASSN_REC_DELETED' ) AND course.learning_item_id = course_latest_asg.learning_item_id AND assignmenttaskdeo.source_object_id = course_latest_asg.assignment_record_id AND assignmenttaskdeo.learning_item_type = 'ORA_ACTIVITY' AND trunc(nvl(course_latest_asg.li_effective_date, sysdate)) BETWEEN course.effective_start_date AND course .effective_end_date AND trunc(sysdate) BETWEEN course_latest_asg.effective_start_date AND course_latest_asg.effective_end_date AND (trunc(course_latest_asg.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) WHEN ( activitydeo.activity_type = 'ORA_COURSE' AND assignmenttaskdeo.source_object_id IS NULL ) THEN ( SELECT 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 FROM wlf_learning_items_f course_li, wlf_li_courses_f course, wlf_access_permissions_f course_access, wlf_li_activities_f course_activity WHERE ( course_access.follow_spec = 'Y' OR wlf_can_access.wlf_ss_can_access_li(course.learning_item_id) = 'T' ) AND course.learning_item_id = course_li.learning_item_id AND course_activity.related_content_id = course.learning_item_id AND course_li.access_permission_id = course_access.access_permission_id AND assignmenttaskdeo.learning_item_id = course_activity.learning_item_id AND assignmenttaskdeo.learning_item_type = 'ORA_ACTIVITY' AND trunc(sysdate) BETWEEN course.effective_start_date AND course.effective_end_date AND trunc(sysdate) BETWEEN course_li.effective_start_date AND course_li.effective_end_date AND trunc(sysdate) BETWEEN course_access.effective_start_date AND course_access.effective_end_date AND trunc(sysdate) BETWEEN course_activity.effective_start_date AND course_activity.effective_end_date AND ROWNUM=1 ) ELSE ( to_char(learningitemdeo.duration) ) END ) AS activityexpectedeffort, ( CASE WHEN ( activitydeo.activity_type = 'ORA_COURSE' ) THEN 'ORA_DUR_HOUR' ELSE NVL( learningitemdeo.duration_uom, 'ORA_DUR_HOUR') END ) AS activityeffortunits, ( SELECT CASE WHEN activitydeo.activity_type = 'ORA_DEF_QUESTIONNAIRE' THEN CASE WHEN activitydeo.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_asg_records.learning_item_id 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 offering.effective_start_date AND offering.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN relations.effective_start_date AND relations.effective_end_date AND ROWNUM=1 ) ELSE activitydeo.related_content_id END ELSE ( SELECT content.related_content_id FROM wlf_learning_items_f content_li, wlf_li_content_f content WHERE content.tracking_type IN ('ORA_ASSESSMENT','ORA_OBSERVATION_CHECKLIST') AND content_li.status != 'ORA_LI_DELETE' AND content.learning_item_id = activitydeo.related_content_id AND content_li.learning_item_id = activitydeo.related_content_id AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN content.effective_start_date AND content.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN content_li.effective_start_date AND content_li.effective_end_date AND ROWNUM=1 ) END AS learning_content_qstnr_id FROM wlf_assignment_records_f offering_asg_records WHERE offering_asg_records.event_type IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT' ) AND assignmenttaskdeo.learning_item_id = activitydeo.learning_item_id AND assignmenttaskdeo.assignment_record_id = offering_asg_records.assignment_record_id AND trunc(sysdate) BETWEEN offering_asg_records.effective_start_date AND offering_asg_records.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN activitydeo.effective_start_date AND activitydeo .effective_end_date AND (trunc(offering_asg_records.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) AS elearnquestionnaireid, ( CASE WHEN (activitydeo.activity_type = 'ORA_DEF_QUESTIONNAIRE' AND activitydeo.related_content_id = - 1) THEN ( SELECT questionnairepeo.questionnaire_code FROM hrq_questionnaires_b questionnairepeo, wlf_li_relations_f relations, wlf_li_classes_f offering, wlf_assignment_records_f offering_asg_records WHERE offering.learning_item_id = offering_asg_records.learning_item_id AND relations.learning_item_id = - 1 AND questionnairepeo.questionnaire_id = relations.source_id AND relations.type_of_usage = offering.delivery_mode AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN offering.effective_start_date AND offering.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN relations.effective_start_date AND relations.effective_end_date AND assignmenttaskdeo.learning_item_id = activitydeo.learning_item_id AND assignmenttaskdeo.assignment_record_id = offering_asg_records.assignment_record_id AND trunc(sysdate) BETWEEN offering_asg_records.effective_start_date AND offering_asg_records.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date AND ( trunc(offering_asg_records.assigned_on_date) <= trunc(sysdate) ) AND offering_asg_records.event_type IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT' ) AND ROWNUM = 1 ) WHEN (activitydeo.activity_type = 'ORA_DEF_QUESTIONNAIRE' AND activitydeo.related_content_id != - 1) THEN ( SELECT questionnairepeo.questionnaire_code FROM hrq_questionnaires_b questionnairepeo, wlf_assignment_records_f offering_asg_records WHERE activitydeo.parent_learning_item_id = offering_asg_records.learning_item_id AND questionnairepeo.questionnaire_id = activitydeo.related_content_id AND assignmenttaskdeo.learning_item_id = activitydeo.learning_item_id AND assignmenttaskdeo.assignment_record_id = offering_asg_records.assignment_record_id AND trunc(sysdate) BETWEEN offering_asg_records.effective_start_date AND offering_asg_records.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date AND ( trunc(offering_asg_records.assigned_on_date) <= trunc(sysdate) ) AND offering_asg_records.event_type IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT' ) AND ROWNUM = 1) WHEN ((activitydeo.elearning_type = 'ORA_ASSESSMENT') OR (activitydeo.elearning_type = 'ORA_OBSERVATION_CHECKLIST')) THEN (SELECT questionnairepeo.questionnaire_code FROM hrq_questionnaires_b questionnairepeo, wlf_learning_items_f content_li, wlf_li_content_f content, wlf_assignment_records_f offering_asg_records WHERE activitydeo.parent_learning_item_id = offering_asg_records.learning_item_id AND questionnairepeo.questionnaire_id = content.related_content_id AND assignmenttaskdeo.learning_item_id = activitydeo.learning_item_id AND assignmenttaskdeo.assignment_record_id = offering_asg_records.assignment_record_id AND trunc(sysdate) BETWEEN offering_asg_records.effective_start_date AND offering_asg_records.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date AND ( trunc(offering_asg_records.assigned_on_date) <= trunc(sysdate) ) AND content.tracking_type IN ('ORA_ASSESSMENT','ORA_OBSERVATION_CHECKLIST') AND content_li.status != 'ORA_LI_DELETE' AND content.learning_item_id = activitydeo.related_content_id AND content_li.learning_item_id = activitydeo.related_content_id AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN content.effective_start_date AND content.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN content_li.effective_start_date AND content_li.effective_end_date AND offering_asg_records.event_type IN ( 'ORA_JOIN_ASSIGNMENT', 'ORA_REQUIRE_ASSIGNMENT' ) AND ROWNUM = 1) ELSE NULL END ) AS elearnquestionnairenumber, ( SELECT CASE WHEN assignmenttaskdeo.TASK_OWNER_TYPE = 'ORA_LEARNER' and content.tracking_type = 'ORA_OBSERVATION_CHECKLIST' and content.ENABLE_SELF_ASSESS_CHECKLIST = 'Y' THEN 1 WHEN content.recorded_attempts = 'ORA_WLF_ARA_UNLTD' THEN - 1 ELSE content.max_attempts END FROM wlf_li_content_f content, wlf_assignment_records_f offering_asg_records WHERE content.learning_item_id = activitydeo.related_content_id AND offering_asg_records.assignment_record_id = assignmenttaskdeo.assignment_record_id AND trunc(sysdate) BETWEEN offering_asg_records.effective_start_date AND offering_asg_records.effective_end_date AND trunc(nvl(offering_asg_records.li_effective_date, sysdate)) BETWEEN content.effective_start_date (+) AND content. effective_end_date (+) AND (trunc(offering_asg_records.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) AS elearnattemptsallowed, assignmenttaskdeo.assignment_task_id AS assignment_task_id, ( SELECT ar.assignment_record_number FROM wlf_assignment_records_f ar WHERE ar.assignment_record_id = assignmenttaskdeo.assignment_record_id AND trunc(sysdate) BETWEEN ar.effective_start_date AND ar.effective_end_date AND (trunc(ar.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) AS activityattemptnumber, assignmenttaskdeo.task_sub_status as activity_attempt_status, assignmenttaskdeo.creation_date AS activityattemptstarteddate, assignmenttaskdeo.completion_date, assignmenttaskdeo.exempted_on_date, assignmenttaskdeo.reason_code, assignmenttaskdeo.effort, assignmenttaskdeo.effort_uom, assignmenttaskdeo.score, ( CASE WHEN ( activity_type != 'ORA_ONLINE_SP' AND assignmenttaskdeo.task_status = 'ORA_ASSN_TASK_COMPLETED' ) THEN 1 ELSE ( SELECT COUNT(1) FROM wlf_attempt_relations WHERE related_object_id = assignmenttaskdeo.assignment_task_id AND learner_id = assignmenttaskdeo.task_owner_id ) END ) AS activitytotalattempts, CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT arrelations.related_object_id FROM wlf_ar_relations_f arrelations WHERE arrelations.assignment_record_id = assignmenttaskdeo.assignment_record_id AND trunc(sysdate) BETWEEN arrelations.effective_start_date AND arrelations.effective_end_date AND ROWNUM=1 ) ELSE assignmenttaskdeo.assignment_record_id END AS assignment_record_id, assignmenttaskdeo.assignment_record_id AS comp_dtls_assign_rec_id, assignmenttaskdeo.effective_start_date, assignmenttaskdeo.effective_end_date, assignmenttaskdeo.object_version_number, activitydeo.effective_start_date AS activity_effective_start_date, activitydeo.effective_end_date AS activity_effective_end_date, activitydeo.activity_id AS activity_activity_id, activitydeo.object_version_number AS object_version_number1, learningitemdeo.learning_item_id AS li_li_id, learningitemdeo.effective_start_date AS li_effective_start_date, learningitemdeo.effective_end_date AS li_effective_end_date, learningitemdeo.learning_item_type AS li_type, learningitemdeo.object_version_number AS li_object_version_number, learningitemtranslationdeo.learning_item_id AS lit_li_id, learningitemtranslationdeo.effective_start_date AS lit_effective_start_date, learningitemtranslationdeo.effective_end_date AS lit_effective_end_date, learningitemtranslationdeo.object_version_number AS lit_object_version_number, learningitemtranslationdeo.language, assignmenttaskdeo.created_by, assignmenttaskdeo.creation_date, assignmenttaskdeo.last_updated_by, assignmenttaskdeo.last_update_login, assignmenttaskdeo.last_update_date, activitydeo.created_by AS activity_created_by, activitydeo.creation_date AS activity_creation_date, activitydeo.last_update_login AS activity_last_update_login, activitydeo.last_updated_by AS activity_last_updated_by, activitydeo.last_update_date AS activity_last_update_date, learningitemdeo.created_by AS li_created_by, learningitemdeo.creation_date AS li_creation_date, learningitemdeo.last_update_date AS li_last_update_date, learningitemdeo.last_update_login AS li_last_update_login, learningitemdeo.last_updated_by AS li_last_updated_by, learningitemtranslationdeo.created_by AS lit_created_by, learningitemtranslationdeo.creation_date AS lit_creation_date, learningitemtranslationdeo.last_update_login AS lit_last_update_login, learningitemtranslationdeo.last_updated_by AS lit_last_updated_by, learningitemtranslationdeo.last_update_date AS lit_last_update_date, (CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN parentlearningitemdeo.start_date ELSE learningitemdeo.start_date END) AS activity_start_datetime, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN parentlearningitemdeo.end_date ELSE learningitemdeo.end_date END ) AS activity_end_datetime, assignmenttaskdeo.task_owner_id, activitydeo.self_complete_flag, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN assignmenttaskdeo.assignment_record_id ELSE NULL END ) AS offering_assignment_record_id, ( decode(assignmenttaskdeo.is_open, 'Y', 'N', 'Y') ) AS act_attempt_locked, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN ( SELECT description FROM wlf_learning_items_f_tl litl WHERE litl.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN litl.effective_start_date AND litl.effective_end_date AND litl.language = userenv('LANG') AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN parentlearningitemtransdeo.description ELSE learningitemtranslationdeo.description END ) AS activity_description, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING') THEN activitydeo.related_content_id ELSE activitydeo.learning_item_id END ) AS activity_learning_item_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT section_tasks.req_act_completion_count FROM wlf_assignment_tasks_f section_tasks,wlf_li_sections_f section WHERE section_tasks.assignment_task_id = assignmenttaskdeo.parent_task_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND trunc(sysdate) BETWEEN section.effective_start_date AND section.effective_end_date AND trunc(sysdate) BETWEEN section_tasks.effective_start_date AND section_tasks.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS activity_sec_compl_required, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT section_tasks.completed_activity_count FROM wlf_assignment_tasks_f section_tasks,wlf_li_sections_f section WHERE section_tasks.assignment_task_id = assignmenttaskdeo.parent_task_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND trunc(sysdate) BETWEEN section.effective_start_date AND section.effective_end_date AND trunc(sysdate) BETWEEN section_tasks.effective_start_date AND section_tasks.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS activity_section_progress, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT decode(section_tasks.is_open, 'Y', 'N', 'Y') FROM wlf_assignment_tasks_f section_tasks,wlf_li_sections_f section WHERE section_tasks.assignment_task_id = assignmenttaskdeo.parent_task_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND trunc(sysdate) BETWEEN section.effective_start_date AND section.effective_end_date AND trunc(sysdate) BETWEEN section_tasks.effective_start_date AND section_tasks.effective_end_date AND ROWNUM=1 ) ELSE 'N' END ) AS act_sect_attempt_locked, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT section_litl.description FROM wlf_learning_items_f_tl section_litl,wlf_li_sections_f section WHERE section_litl.learning_item_id = assignmenttaskdeo.section_li_id AND assignmenttaskdeo.section_li_id = section.learning_item_id AND (section.is_hidden is null or section.is_hidden = 'N') AND trunc(sysdate) BETWEEN section.effective_start_date AND section.effective_end_date AND trunc(sysdate) BETWEEN section_litl.effective_start_date AND section_litl.effective_end_date AND section_litl.language = userenv('LANG') AND ROWNUM=1 ) ELSE NULL END ) AS activity_section_description, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN ( SELECT description_short FROM wlf_learning_items_f_tl litl WHERE litl.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN litl.effective_start_date AND litl.effective_end_date AND litl.language = userenv('LANG') AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN ( parentlearningitemtransdeo.description_short ) ELSE learningitemtranslationdeo.description_short END ) AS activity_description_short, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null and activitydeo.activity_type = 'ORA_ONLINE_SP') THEN ( SELECT 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 IN ( 'ORA_AUTO' ) AND content_root.learning_item_id = activitydeo.related_content_id 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 AND ROWNUM = 1 ) ELSE NULL END ) AS e_learn_completion_on_open, activitydeo.time_zone, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null) THEN ( SELECT nvl(content_root.hide_score_learner, 'N') FROM wlf_li_content_f content_root WHERE content_root.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root.effective_start_date AND content_root.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS hide_score_learner, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING') THEN activitydeo.parent_learning_item_id ELSE activitydeo.related_content_id END ) AS e_learn_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING') THEN ( SELECT content_root_li.duration FROM wlf_learning_items_f content_root_li WHERE content_root_li.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root_li.effective_start_date AND content_root_li.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT content_root.time_limit FROM wlf_li_content_f content_root WHERE content_root.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root.effective_start_date AND content_root.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS e_learn_time_per_attempt, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' and parentlearningitemdeo.learning_item_sub_type = 'ORA_VIDEO') THEN ( SELECT content_root_li.duration_uom FROM wlf_learning_items_f content_root_li WHERE content_root_li.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root_li.effective_start_date AND content_root_li.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT content_root.time_limit_uom FROM wlf_li_content_f content_root WHERE content_root.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root.effective_start_date AND content_root.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS e_learn_time_per_attempt_unit, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' and parentlearningitemdeo.learning_item_sub_type = 'ORA_SS_VIDEO') THEN 'ORA_VIDEO' WHEN ( 1 = ( SELECT 1 FROM wlf_learning_items_f li, wlf_li_content_f content WHERE activitydeo.parent_learning_item_id = li.learning_item_id AND li.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id = content.learning_item_id AND trunc(sysdate) BETWEEN li.effective_start_date AND li.effective_end_date AND trunc(sysdate) BETWEEN content.effective_start_date AND content.effective_end_date AND ROWNUM=1 ) ) THEN ( SELECT content_root.tracking_type FROM wlf_li_content_f content_root WHERE content_root.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content_root.effective_start_date AND content_root.effective_end_date AND ROWNUM=1 ) WHEN ( 1 = ( SELECT 1 FROM wlf_learning_items_f li, wlf_li_videos_f videos WHERE activitydeo.parent_learning_item_id = li.learning_item_id AND li.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id = videos.learning_item_id AND trunc(sysdate) BETWEEN li.effective_start_date AND li.effective_end_date AND trunc(sysdate) BETWEEN videos.effective_start_date AND videos.effective_end_date AND ROWNUM=1 ) ) THEN 'ORA_VIDEO' ELSE NULL END ) AS e_learn_type, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN activitydeo.parent_learning_item_id ELSE NULL END ) AS offering_learning_item_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN activitydeo.parent_learning_item_id WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT classes.course_learning_item_id FROM wlf_li_classes_f classes WHERE classes.learning_item_id = activitydeo.parent_learning_item_id AND trunc(sysdate) BETWEEN classes.effective_start_date AND classes.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS parent_learning_item_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN ( SELECT thumbnailli.location FROM wlf_learning_items_f thumbnailli, wlf_learning_items_f elearningcontentrootli WHERE thumbnailli.learning_item_id = elearningcontentrootli.thumbnail_id AND elearningcontentrootli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN thumbnailli.effective_start_date AND thumbnailli.effective_end_date AND trunc(sysdate) BETWEEN elearningcontentrootli.effective_start_date AND elearningcontentrootli.effective_end_date AND ROWNUM=1 ) WHEN parentlearningitemdeo.learning_item_type='ORA_CLASS' THEN (SELECT ACTIVITY_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS ACTIVITY_THUMBNAILS WHERE ACTIVITY_THUMBNAILS.THUMBNAIL_ID = (select THUMBNAIL_ID from wlf_learning_items_f where learning_item_id = activitydeo.RELATED_CONTENT_ID and trunc(sysdate) between effective_start_date and effective_end_date and learning_item_type ='ORA_CONTENT_VIDEO') ) ELSE NULL END ) AS content_thumbnail_location, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT contentli.status FROM wlf_learning_items_f contentli WHERE contentli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN contentli.effective_start_date AND contentli.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN ( parentlearningitemdeo.status ) ELSE NULL END ) AS e_learn_content_status, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null and activitydeo.activity_type = 'ORA_ONLINE_SP') THEN ( SELECT content_item.starting_url FROM wlf_li_content_f content_root, wlf_li_content_f content_item, wlf_li_hierarchies_f content_hierarchy WHERE content_root.tracking_type IN ( 'ORA_AUTO' ) AND content_root.learning_item_id = activitydeo.related_content_id 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 AND ROWNUM = 1 ) ELSE NULL END ) AS starting_url, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR (parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null and activitydeo.activity_type = 'ORA_ONLINE_SP')) THEN ( SELECT contentli.thumbnail_id FROM wlf_learning_items_f contentli WHERE contentli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN contentli.effective_start_date AND contentli.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN ( SELECT elearningcontentrootli.thumbnail_id FROM wlf_learning_items_f elearningcontentrootli WHERE elearningcontentrootli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN elearningcontentrootli.effective_start_date AND elearningcontentrootli.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS e_learn_content_thumbnail_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN NULL WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null and activitydeo.activity_type = 'ORA_ONLINE_SP') THEN ( SELECT contentli.location FROM wlf_learning_items_f contentli WHERE contentli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN contentli.effective_start_date AND contentli.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING' ) THEN ( SELECT elearningcontentrootli.location FROM wlf_learning_items_f elearningcontentrootli WHERE elearningcontentrootli.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN elearningcontentrootli.effective_start_date AND elearningcontentrootli.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS e_learn_content_location, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT section_tasks.required_predecessor FROM wlf_assignment_tasks_f section_tasks WHERE section_tasks.assignment_task_id = assignmenttaskdeo.parent_task_id AND trunc(sysdate) BETWEEN section_tasks.effective_start_date AND section_tasks.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS activity_sec_predecessor_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN (select assignment_record_id from wlf_assignment_records_f where assignment_record_id = assignmenttaskdeo.source_object_id and trunc(assigned_on_date) <= trunc(sysdate) and trunc(sysdate) between effective_start_date and effective_end_date) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT arrelations.related_object_id FROM wlf_ar_relations_f arrelations WHERE arrelations.assignment_record_id = assignmenttaskdeo.assignment_record_id AND trunc(sysdate) BETWEEN arrelations.effective_start_date AND arrelations.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) AS course_assignment_record_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN (select assignment_record_id from wlf_assignment_records_f where assignment_record_id = assignmenttaskdeo.source_object_id and trunc(assigned_on_date) <= trunc(sysdate) and trunc(sysdate) between effective_start_date and effective_end_date) ELSE assignmenttaskdeo.assignment_record_id END ) AS activity_assignment_record_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' ) THEN ( SELECT assignments.event_assignment_id FROM wlf_assignment_records_f assignments WHERE assignments.assignment_record_id = assignmenttaskdeo.source_object_id AND trunc(sysdate) BETWEEN assignments.effective_start_date AND assignments.effective_end_date AND (trunc(assignments.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT course_assignments.event_assignment_id FROM wlf_ar_relations_f arrelations, wlf_assignment_records_f course_assignments WHERE arrelations.assignment_record_id = assignmenttaskdeo.assignment_record_id AND arrelations.related_object_id = course_assignments.assignment_record_id AND trunc(sysdate) BETWEEN arrelations.effective_start_date AND arrelations.effective_end_date AND trunc(sysdate) BETWEEN course_assignments.effective_start_date AND course_assignments.effective_end_date AND (trunc(course_assignments.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) ELSE NULL END ) AS activity_event_assignment_id, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_ELEARNING') THEN 'Y' WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' ) THEN ( SELECT CASE WHEN ( assignments.status = 'ORA_ASSN_REC_ACTIVE' OR assignments.status = 'ORA_ASSN_REC_CONTENT_COMPLETE' OR assignments.status = 'ORA_ASSN_REC_COMPLETE' OR assignments.status = 'ORA_ASSN_REC_WITHDRAW_PENDING' ) AND ar_relations.primary_flag = 'Y' THEN 'Y' ELSE 'N' END AS primary_flag FROM wlf_ar_relations_f ar_relations, wlf_assignment_records_f assignments WHERE ar_relations.assignment_record_id = assignmenttaskdeo.assignment_record_id AND assignments.assignment_record_id = ar_relations.assignment_record_id AND trunc(sysdate) BETWEEN assignments.effective_start_date AND assignments.effective_end_date AND trunc(sysdate) BETWEEN ar_relations.effective_start_date AND ar_relations.effective_end_date AND (trunc(assignments.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) ELSE 'Y' END ) AS primary_relationship_flag, assignmenttaskdeo.required_predecessor as ACTIVITY_PREDECESSOR_ID, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION' OR parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN ( SELECT section_tasks.task_status FROM wlf_assignment_tasks_f section_tasks WHERE section_tasks.assignment_task_id = assignmenttaskdeo.parent_task_id AND trunc(sysdate) BETWEEN section_tasks.effective_start_date AND section_tasks.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) as ACTIVITY_SECTION_STATUS, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null) THEN ( SELECT DECODE (CONTENT.RECORDED_ATTEMPT_REVIEW , 'ORA_WLF_ARAR_DONT_ENABLE' , 'Y' , 'N') FROM WLF_LI_CONTENT_F CONTENT WHERE CONTENT.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN CONTENT.effective_start_date AND CONTENT.effective_end_date AND ROWNUM=1 ) ELSE NULL END ) as DISABLE_REVIEW, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null) THEN ( SELECT CASE WHEN (CONTENT.TRACKING_TYPE in ('ORA_PDF','ORA_AUTO','ORA_SCORM_12','ORA_SCORM_2004','ORA_HACP') OR CONTENT_LI.LEARNING_ITEM_TYPE = 'ORA_CONTENT_VIDEO') THEN 'N' ELSE 'Y' END AS DISABLE_CONT_REVIEW FROM WLF_LI_CONTENT_F CONTENT,WLF_LEARNING_ITEMS_F CONTENT_LI WHERE CONTENT.learning_item_id = activitydeo.related_content_id AND CONTENT_LI.LEARNING_ITEM_ID = activitydeo.related_content_id AND trunc(sysdate) BETWEEN CONTENT.effective_start_date AND CONTENT.effective_end_date AND trunc(sysdate) BETWEEN CONTENT_LI.effective_start_date AND CONTENT_LI.effective_end_date AND ROWNUM=1 ) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION') THEN NULL ELSE 'N' END ) as E_LEARN_DISABLE_CONT_REVIEW, NVL(activitydeo.VIRTUAL_CLASSROOM_URL,assignmenttaskdeo.VILT_URL) AS VIRTUAL_CLASSROOM_JOIN_LINK, ( CASE WHEN ( assignmenttaskdeo.task_completion_rule is not null ) THEN assignmenttaskdeo.task_completion_rule ELSE 'ORA_DEFINED_BY_SECTION' END ) AS ACTIVITYCOMPLREQUIREMENT, (CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS') THEN (learningitemdeo.START_DATE) WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_SPECIALIZATION') THEN (SELECT course_li.start_date from wlf_learning_items_f course_li where trunc(sysdate) between course_li.effective_start_date and course_li.effective_end_date and rownum=1) ELSE NULL END) AS ACTIVITY_DATE, ( CASE WHEN ( parentlearningitemdeo.learning_item_type = 'ORA_CLASS' AND activitydeo.related_content_id is not null) THEN (SELECT CASE WHEN CONTENT.LEARNING_ITEM_ID IS NULL THEN NULL WHEN CONTENT.RECORDED_ATTEMPT_REVIEW= 'ORA_WLF_ARAR_DONT_ENABLE' THEN 'Y' WHEN CONTENT.RECORDED_ATTEMPT_REVIEW= 'ORA_WLF_ARAR_LAST_ATTEMPT' AND (CONTENT.DISABLE_REVIEW='N' OR CONTENT.DISABLE_REVIEW IS NULL) THEN 'N' ELSE 'Y' END AS PASS_ATTEMPT FROM wlf_li_content_f content where CONTENT.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN CONTENT.effective_start_date AND CONTENT.effective_end_date AND ROWNUM=1) ELSE NULL END ) AS E_LEARN_DIS_REV_PASS_ATTEMPTS, assignmenttaskdeo.Completion_Date as ACTIVITY_ATTEMPT_COMPL_DATE, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_CLASS' THEN 'ORA_COURSE' ELSE parentlearningitemdeo.learning_item_type END AS PARENT_LEARNING_ITEM_TYPE, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_CLASS' THEN ( SELECT LI_EFFECTIVE_DATE FROM wlf_ar_relations_f ar_relations, wlf_assignment_records_f assignments WHERE ar_relations.assignment_record_id = assignmenttaskdeo.assignment_record_id AND assignments.assignment_record_id = ar_relations.related_object_id AND trunc(sysdate) BETWEEN assignments.effective_start_date AND assignments.effective_end_date AND trunc(sysdate) BETWEEN ar_relations.effective_start_date AND ar_relations.effective_end_date AND (trunc(assignments.assigned_on_date) <= trunc(sysdate)) AND ROWNUM=1 ) ELSE assignmentrecorddeo.LI_EFFECTIVE_DATE END AS LI_EFFECTIVE_DATE, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN NULL WHEN parentlearningitemdeo.learning_item_type='ORA_ELEARNING' THEN parentlearningitemtransdeo.NAME WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS' AND activitydeo.related_content_id IS NOT NULL) THEN ( SELECT CONTENT_LI_TL.NAME FROM WLF_LEARNING_ITEMS_F_TL CONTENT_LI_TL WHERE LEARNING_ITEM_ID = activitydeo.related_content_id AND TRUNC(SYSDATE) BETWEEN CONTENT_LI_TL.EFFECTIVE_START_DATE AND CONTENT_LI_TL.EFFECTIVE_END_DATE AND ROWNUM=1 ) ELSE NULL END AS E_LEARN_TITLE, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN (select assignment_record_id from wlf_assignment_records_f where assignment_record_id = assignmenttaskdeo.source_object_id and trunc(assigned_on_date) <= trunc(sysdate) and trunc(sysdate) between effective_start_date and effective_end_date) WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS') THEN assignmenttaskdeo.assignment_task_id ELSE NULL END AS ACTIVITY_ATTEMPT_ID, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN ( SELECT ca_content_root.location from wlf_learning_items_f ca_content_root, wlf_learning_items_f course_li WHERE course_li.learning_item_id = activitydeo.RELATED_CONTENT_ID and ca_content_root.learning_item_id = course_li.thumbnail_id AND trunc(sysdate) between course_li.effective_start_date and course_li.effective_end_date AND trunc(sysdate) between ca_content_root.effective_start_date and ca_content_root.effective_end_date ) WHEN parentlearningitemdeo.learning_item_type='ORA_CLASS' THEN (SELECT ACTIVITY_THUMBNAILS.LOCATION FROM WLF_THUMBNAILS ACTIVITY_THUMBNAILS WHERE ACTIVITY_THUMBNAILS.THUMBNAIL_ID = (select THUMBNAIL_ID from wlf_learning_items_f where learning_item_id = activitydeo.RELATED_CONTENT_ID and trunc(sysdate) between effective_start_date and effective_end_date and learning_item_type <>'ORA_CONTENT_VIDEO') ) WHEN parentlearningitemdeo.learning_item_type='ORA_ELEARNING' THEN (SELECT THUMBNAIL_CONTENT_ROOT.LOCATION FROM WLF_LEARNING_ITEMS_F THUMBNAIL_CONTENT_ROOT , WLF_LEARNING_ITEMS_F ELEARN_CONTENT_ROOT WHERE THUMBNAIL_CONTENT_ROOT.LEARNING_ITEM_ID = ELEARN_CONTENT_ROOT.THUMBNAIL_ID AND ELEARN_CONTENT_ROOT.LEARNING_ITEM_ID = activitydeo.RELATED_CONTENT_ID AND TRUNC(SYSDATE) BETWEEN THUMBNAIL_CONTENT_ROOT.EFFECTIVE_START_DATE AND THUMBNAIL_CONTENT_ROOT.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN ELEARN_CONTENT_ROOT.EFFECTIVE_START_DATE AND ELEARN_CONTENT_ROOT.EFFECTIVE_END_DATE AND ROWNUM=1) ELSE NULL END AS ACTIVITY_THUMBNAIL_LOCATION, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN NULL WHEN parentlearningitemdeo.learning_item_type='ORA_ELEARNING' THEN parentlearningitemdeo.LEARNING_ITEM_NUMBER WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS' AND activitydeo.related_content_id IS NOT NULL) THEN ( SELECT CONTENT_LI.LEARNING_ITEM_NUMBER FROM WLF_LEARNING_ITEMS_F CONTENT_LI WHERE LEARNING_ITEM_ID = activitydeo.related_content_id AND TRUNC(SYSDATE) BETWEEN CONTENT_LI.EFFECTIVE_START_DATE AND CONTENT_LI.EFFECTIVE_END_DATE AND ROWNUM=1 ) ELSE NULL END AS E_LEARN_NUMBER, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN ( SELECT COURSE.MAXIMUM_TRAINING_HOURS * 60 FROM WLF_LI_COURSES_F COURSE WHERE COURSE.LEARNING_ITEM_ID = activitydeo.related_content_id AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date ) AND COURSE.MAXIMUM_TRAINING_HOURS IS NOT NULL AND ROWNUM=1 ) WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS' AND learningitemdeo.DURATION IS NOT NULL) THEN ( SELECT CASE WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_MONTH' THEN learningitemdeo.DURATION * 30 * 24 * 60 WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_WEEK' THEN learningitemdeo.DURATION * 7 * 24 * 60 WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_DAY' THEN learningitemdeo.DURATION * 24 * 60 WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_HOUR' THEN learningitemdeo.DURATION * 60 WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_MIN' THEN learningitemdeo.DURATION WHEN learningitemdeo.DURATION_UOM = 'ORA_DUR_SEC' THEN learningitemdeo.DURATION / 60 ELSE NULL END AS OFFERING_ACT_DURATION FROM DUAL ) WHEN (parentlearningitemdeo.learning_item_type='ORA_ELEARNING' AND parentlearningitemdeo.learning_item_sub_type = 'ORA_SS_VIDEO') THEN ( SELECT CASE WHEN root_content_li.DURATION_UOM = 'ORA_DUR_MONTH' THEN root_content_li.DURATION * 30 * 24 * 60 WHEN root_content_li.DURATION_UOM = 'ORA_DUR_WEEK' THEN root_content_li.DURATION * 7 * 24 * 60 WHEN root_content_li.DURATION_UOM = 'ORA_DUR_DAY' THEN root_content_li.DURATION * 24 * 60 WHEN root_content_li.DURATION_UOM = 'ORA_DUR_HOUR' THEN root_content_li.DURATION * 60 WHEN root_content_li.DURATION_UOM = 'ORA_DUR_MIN' THEN root_content_li.DURATION WHEN root_content_li.DURATION_UOM = 'ORA_DUR_SEC' THEN root_content_li.DURATION / 60 ELSE NULL END AS ELEARN_ACT_DURATION FROM wlf_learning_items_f root_content_li WHERE root_content_li.LEARNING_ITEM_ID = activitydeo.related_content_id AND (SYSDATE) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date AND root_content_li.DURATION IS NOT NULL AND ROWNUM=1 ) ELSE NULL END AS ACTIVITY_MAX_EXP_EFFORT_MINS, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN ( SELECT COURSE.MAXIMUM_TRAINING_HOURS * 60 FROM WLF_LI_COURSES_F COURSE WHERE COURSE.LEARNING_ITEM_ID = activitydeo.related_content_id AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date ) AND COURSE.MAXIMUM_TRAINING_HOURS IS NOT NULL AND ROWNUM=1 ) ELSE NULL END AS ACTIVITY_MIN_EXP_EFFORT_MINS, CASE WHEN parentlearningitemdeo.learning_item_type='ORA_SPECIALIZATION' THEN ( 'ORA_DUR_HOUR' ) WHEN parentlearningitemdeo.learning_item_type='ORA_CLASS' THEN ( NVL(learningitemdeo.DURATION_UOM,'ORA_DUR_HOUR') ) WHEN (parentlearningitemdeo.learning_item_type='ORA_ELEARNING' AND parentlearningitemdeo.learning_item_sub_type = 'ORA_SS_VIDEO') THEN ( SELECT root_content_li.DURATION_UOM FROM wlf_learning_items_f root_content_li WHERE root_content_li.LEARNING_ITEM_ID = activitydeo.related_content_id AND (SYSDATE) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date AND ROWNUM=1 ) ELSE NULL END AS ACTIVITY_EXPECTED_EFFORT_UNITS, CASE WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS' AND activitydeo.related_content_id IS NOT NULL) THEN ( SELECT CASE WHEN (CONTENT.HIDE_CORRECT_ANSWERS IS NULL OR CONTENT.HIDE_CORRECT_ANSWERS='N') THEN 'Y' ELSE 'N' END FROM wlf_li_content_f content WHERE content.learning_item_id = activitydeo.related_content_id AND trunc(sysdate) BETWEEN content.effective_start_date AND content.effective_end_date AND ROWNUM=1 ) ELSE NULL END as ELEARN_SHOW_RESPONSE_FEEDBACK, CASE WHEN (parentlearningitemdeo.learning_item_type='ORA_CLASS') THEN ( SELECT course_li.learning_item_sub_type FROM wlf_li_classes_f classes, wlf_learning_items_f course_li WHERE classes.learning_item_id = activitydeo.parent_learning_item_id AND course_li.learning_item_id = classes.course_learning_item_id AND trunc(sysdate) BETWEEN classes.effective_start_date AND classes.effective_end_date AND trunc(sysdate) BETWEEN course_li.effective_start_date AND course_li.effective_end_date AND ROWNUM=1 ) ELSE parentlearningitemdeo.learning_item_sub_type END AS PARENT_LEARNING_ITEM_SUBTYPE, ( SELECT section.learning_item_id FROM wlf_li_sections_f section WHERE assignmenttaskdeo.section_li_id = section.learning_item_id AND TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN section.effective_start_date AND section.effective_end_date AND ROWNUM=1 ) as section_li_id_value, activitydeo.VIRTUAL_CLASSROOM_URL AS ACTIVITY_VILT_URL, CASE WHEN assignmenttaskdeo.TASK_OWNER_ID IS NOT NULL THEN (SELECT ASSIGNED_TO_PEOPLE.PERSON_NUMBER FROM PER_ALL_PEOPLE_F ASSIGNED_TO_PEOPLE WHERE ASSIGNED_TO_PEOPLE.PERSON_ID = assignmenttaskdeo.TASK_OWNER_ID AND (TRUNC(SYSDATE) BETWEEN ASSIGNED_TO_PEOPLE.EFFECTIVE_START_DATE AND ASSIGNED_TO_PEOPLE.EFFECTIVE_END_DATE)) ELSE NULL END AS ATMPT_ASSIGNED_TO_NUMBER , CASE WHEN assignmenttaskdeo.TASK_OWNER_ID IS NOT NULL THEN (SELECT PERSON_NAMES.DISPLAY_NAME FROM PER_PERSON_NAMES_F_V PERSON_NAMES WHERE PERSON_NAMES.PERSON_ID = assignmenttaskdeo.TASK_OWNER_ID AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ) ELSE NULL END AS ATMPT_ASSIGNED_TO_DISP_NAME, assignmenttaskdeo.TASK_OWNER_TYPE AS ACTVTY_ATMPT_ASSIGNED_TO_TYPE, CASE WHEN assignmenttaskdeo.LEARNING_ITEM_TYPE = 'ORA_ACTIVITY' and assignmenttaskdeo.SOURCE_OBJECT_TYPE = 'ORA_EVENT_ATTEMPTS' and assignmenttaskdeo.source_object_id is not null THEN (SELECT TO_CHAR(EVENT_ATTEMPT_ID) FROM WLF_EVENT_ATTEMPTS WHERE EVENT_ID = assignmenttaskdeo.source_object_id) WHEN assignmenttaskdeo.LEARNING_ITEM_TYPE = 'ORA_ACTIVITY' and (assignmenttaskdeo.source_object_id is null OR assignmenttaskdeo.SOURCE_OBJECT_TYPE <> 'ORA_EVENT_ATTEMPTS') THEN TO_CHAR(assignmenttaskdeo.assignment_record_id) ELSE NULL END AS E_LEARN_QSTNR_SUBJECT_ID FROM wlf_assignment_tasks_f assignmenttaskdeo, wlf_li_activities_f activitydeo, wlf_learning_items_f learningitemdeo, wlf_learning_items_f_tl learningitemtranslationdeo, wlf_learning_items_f parentlearningitemdeo, wlf_learning_items_f_tl parentlearningitemtransdeo, wlf_assignment_records_f assignmentrecorddeo WHERE assignmenttaskdeo.learning_item_id = activitydeo.learning_item_id AND assignmenttaskdeo.assignment_record_id = assignmentrecorddeo.assignment_record_id AND activitydeo.parent_learning_item_id = parentlearningitemdeo.learning_item_id AND parentlearningitemdeo.learning_item_id = parentlearningitemtransdeo.learning_item_id AND assignmenttaskdeo.learning_item_id = learningitemdeo.learning_item_id AND learningitemdeo.learning_item_id = learningitemtranslationdeo.learning_item_id AND TRUNC(SYSDATE) BETWEEN assignmenttaskdeo.effective_start_date AND assignmenttaskdeo.effective_end_date AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN activitydeo.effective_start_date AND activitydeo.effective_end_date ) AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN learningitemdeo.effective_start_date AND learningitemdeo.effective_end_date ) AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN learningitemtranslationdeo.effective_start_date AND learningitemtranslationdeo.effective_end_date) AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN parentlearningitemdeo.effective_start_date AND parentlearningitemdeo.effective_end_date ) AND ( TRUNC(NVL(assignmentrecorddeo.LI_EFFECTIVE_DATE,SYSDATE)) BETWEEN parentlearningitemtransdeo.effective_start_date AND parentlearningitemtransdeo.effective_end_date) AND ( TRUNC(SYSDATE) BETWEEN assignmentrecorddeo.effective_start_date AND assignmentrecorddeo.effective_end_date) AND (trunc(assignmentrecorddeo.assigned_on_date) <= trunc(sysdate)) AND learningitemtranslationdeo.language=USERENV('lang') AND parentlearningitemtransdeo.language=USERENV('lang') AND (1 = ( CASE WHEN parentlearningitemdeo.LEARNING_ITEM_TYPE = 'ORA_SPECIALIZATION' THEN ( SELECT 1 FROM WLF_LEARNING_ITEMS_F COURSE_LI,WLF_ACCESS_PERMISSIONS_F CRS_ACCESS WHERE COURSE_LI.LEARNING_ITEM_ID = activitydeo.RELATED_CONTENT_ID AND COURSE_LI.ACCESS_PERMISSION_ID = CRS_ACCESS.ACCESS_PERMISSION_ID AND (CRS_ACCESS.FOLLOW_SPEC='Y' OR (WLF_CAN_ACCESS.WLF_SS_CAN_ACCESS_LI(COURSE_LI.LEARNING_ITEM_ID) = 'T')) AND TRUNC(SYSDATE) BETWEEN COURSE_LI.EFFECTIVE_START_DATE AND COURSE_LI.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN CRS_ACCESS.EFFECTIVE_START_DATE AND CRS_ACCESS.EFFECTIVE_END_DATE) ELSE 1 END ) )