WLF_ALL_ASSIGNMENTS_V

Details

• Schema: FUSION

• Object owner: WLF

• Object type: VIEW

Columns

Name

LEARNING_ITEM_ID EVENT_SUB_TYPE EVENT_TYPE EVENT_ATTRIBUTION_TYPE EVENT_ATTRIBUTION_ID EVENT_ATTRIBUTION_LOOKUP_CODE EVENT_ASSIGNMENT_ID ASG_EFFECTIVE_START_DATE ASG_EFFECTIVE_END_DATE ASSIGNMENT_CREATION_DATE COMMENTS ASSIGNMENT_STATUS ASSIGNMENT_START_DATE ASSIGNMENT_RULE_ID ASSIGNMENT_RECORD_ID RECORD_EFFECTIVE_START_DATE RECORD_EFFECTIVE_END_DATE CALCULATED_DUE_DATE LEARNER_ID RECORD_STATUS COMPLETION_DATE CREATION_DATE RECORD_ASSIGNMENT_RULE_ID RECORD_LAST_UPDATE_DATE LEARNING_ITEM_TYPE LEARNING_ITEM_SUB_TYPE STATUS VISIBILITY DURATION DURATION_UOM THUMBNAIL_ID LI_CREATED_BY_ID LI_CREATION_DATE LI_EFFECTIVE_START_DATE LI_EFFECTIVE_END_DATE TRAILER_LI_ID LOCATION ITEM_ATTRIBUTION_ID ITEM_ATTRIBUTION_TYPE START_DATE NAME DESCRIPTION DESCRIPTION_SHORT ASSIGNED_ON_DATE LI_EFFECTIVE_DATE PRE_ASSIGNMENT_RECORD_ID SUB_STATUS TOTAL_ACTUAL_EFFORT EFFORT_UOM DATE_STATUS_CHANGED IS_PAST_RECORD

Query

SQL_Statement

SELECT AssignmentRecordDEO.LEARNING_ITEM_ID, AssignmentRecordDEO.EVENT_SUB_TYPE, AssignmentRecordDEO.EVENT_TYPE, AssignmentRecordDEO.ATTRIBUTION_TYPE AS EVENT_ATTRIBUTION_TYPE, AssignmentRecordDEO.ATTRIBUTION_ID AS EVENT_ATTRIBUTION_ID, AssignmentRecordDEO.ATTRIBUTION_LOOKUP_CODE AS EVENT_ATTRIBUTION_LOOKUP_CODE, EventAssignmentDEO.EVENT_ASSIGNMENT_ID, EventAssignmentDEO.EFFECTIVE_START_DATE AS ASG_EFFECTIVE_START_DATE, EventAssignmentDEO.EFFECTIVE_END_DATE AS ASG_EFFECTIVE_END_DATE, EventAssignmentDEO.CREATION_DATE AS ASSIGNMENT_CREATION_DATE, EventAssignmentTL.COMMENTS, EventAssignmentDEO.STATUS AS ASSIGNMENT_STATUS, EventAssignmentDEO.ASSIGNMENT_START_DATE, EventAssignmentDEO.ASSIGNMENT_RULE_ID, AssignmentRecordDEO.ASSIGNMENT_RECORD_ID, AssignmentRecordDEO.EFFECTIVE_START_DATE AS RECORD_EFFECTIVE_START_DATE, AssignmentRecordDEO.EFFECTIVE_END_DATE AS RECORD_EFFECTIVE_END_DATE, AssignmentRecordDEO.CALCULATED_DUE_DATE, AssignmentRecordDEO.LEARNER_ID, AssignmentRecordDEO.STATUS AS RECORD_STATUS, AssignmentRecordDEO.COMPLETION_DATE, AssignmentRecordDEO.CREATION_DATE, AssignmentRecordDEO.ASSIGNMENT_RULE_ID AS RECORD_ASSIGNMENT_RULE_ID, AssignmentRecordDEO.LAST_UPDATE_DATE AS RECORD_LAST_UPDATE_DATE, Items.LEARNING_ITEM_TYPE, Items.LEARNING_ITEM_SUB_TYPE, Items.STATUS, Items.VISIBILITY, Items.DURATION, Items.DURATION_UOM, Items.THUMBNAIL_ID, Items.CREATED_BY_ID AS LI_CREATED_BY_ID, Items.CREATION_DATE AS LI_CREATION_DATE, Items.EFFECTIVE_START_DATE AS LI_EFFECTIVE_START_DATE, Items.EFFECTIVE_END_DATE AS LI_EFFECTIVE_END_DATE, Items.TRAILER_LI_ID, Items.LOCATION, Items.ATTRIBUTION_ID AS ITEM_ATTRIBUTION_ID, Items.ATTRIBUTION_TYPE AS ITEM_ATTRIBUTION_TYPE, Items.START_DATE, Translations.NAME, Translations.DESCRIPTION, Translations.DESCRIPTION_SHORT, AssignmentRecordDEO.ASSIGNED_ON_DATE, AssignmentRecordDEO.LI_EFFECTIVE_DATE, AssignmentRecordDEO.PRE_ASSIGNMENT_RECORD_ID, AssignmentRecordDEO.SUB_STATUS, AssignmentRecordDEO.TOTAL_ACTUAL_EFFORT, AssignmentRecordDEO.EFFORT_UOM, AssignmentRecordDEO.DATE_STATUS_CHANGED, CASE WHEN ((trunc(nvl(assignmentrecorddeo.li_effective_date, SYSDATE))>=items.effective_start_date) AND (trunc(nvl(assignmentrecorddeo.li_effective_date, SYSDATE))<=items.effective_end_date)) THEN 'N' ELSE 'Y' END as IS_PAST_RECORD FROM WLF_EVENT_ASSIGNMENTS_F EventAssignmentDEO, WLF_ASSIGNMENT_RECORDS_F AssignmentRecordDEO, WLF_LEARNING_ITEMS_F Items, WLF_LEARNING_ITEMS_F_TL Translations, WLF_EVENT_ASSIGNMENTS_F_TL EventAssignmentTL Where (EventAssignmentDEO.EVENT_ASSIGNMENT_ID = AssignmentRecordDEO.EVENT_ASSIGNMENT_ID) AND (EventAssignmentDEO.EVENT_ASSIGNMENT_ID = EventAssignmentTL.EVENT_ASSIGNMENT_ID) AND (EventAssignmentDEO.effective_start_date = EventAssignmentTL.effective_start_date) AND (EventAssignmentDEO.effective_end_date = EventAssignmentTL.effective_end_date) AND (AssignmentRecordDEO.EVENT_TYPE = 'ORA_JOIN_ASSIGNMENT' OR AssignmentRecordDEO.EVENT_TYPE = 'ORA_RECOMMEND_ASSIGNMENT' OR AssignmentRecordDEO.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT' OR AssignmentRecordDEO.EVENT_TYPE='ORA_RECOMMEND_ASSIGNMENT' OR AssignmentRecordDEO.EVENT_TYPE = 'ORA_MGR_ASSIGNMENT') AND (AssignmentRecordDEO.LEARNING_ITEM_ID = Items.LEARNING_ITEM_ID) AND (Items.LEARNING_ITEM_ID = Translations.LEARNING_ITEM_ID) AND ( trunc(nvl(assignmentrecorddeo.li_effective_date, SYSDATE)) BETWEEN items.effective_start_date AND items.effective_end_date OR trunc(SYSDATE) BETWEEN items.effective_start_date AND items.effective_end_date AND NOT EXISTS (Select 1 from WLF_LEARNING_ITEMS_F LItems where AssignmentRecordDEO.LEARNING_ITEM_ID = LItems.LEARNING_ITEM_ID and TRUNC(NVL(AssignmentRecordDEO.LI_EFFECTIVE_DATE, SYSDATE)) BETWEEN LItems.EFFECTIVE_START_DATE AND LItems.EFFECTIVE_END_DATE) ) AND ( trunc(nvl(assignmentrecorddeo.li_effective_date, SYSDATE)) BETWEEN Translations.effective_start_date AND Translations.effective_end_date OR trunc(SYSDATE) BETWEEN Translations.effective_start_date AND Translations.effective_end_date AND NOT EXISTS (Select 1 from fusion.WLF_LEARNING_ITEMS_F_TL LItemsTL where AssignmentRecordDEO.LEARNING_ITEM_ID = LItemsTL.LEARNING_ITEM_ID and TRUNC(NVL(AssignmentRecordDEO.LI_EFFECTIVE_DATE, SYSDATE)) BETWEEN LItemsTL.EFFECTIVE_START_DATE AND LItemsTL.EFFECTIVE_END_DATE) ) /* These OR sections are here in case of data where LI_EFFECTIVE_DATE is out of range, so we take a current version of the record instead */ AND (SYS_CONTEXT('USERENV','LANG') = Translations.LANGUAGE) AND (TRUNC(SYSDATE) BETWEEN EventAssignmentDEO.EFFECTIVE_START_DATE AND EventAssignmentDEO.EFFECTIVE_END_DATE) AND (TRUNC(SYSDATE) BETWEEN AssignmentRecordDEO.EFFECTIVE_START_DATE AND AssignmentRecordDEO.EFFECTIVE_END_DATE) AND (SYS_CONTEXT('USERENV','LANG') = EventAssignmentTL.LANGUAGE)