WLF_COMMUNITY_ASSIGNMENTS

Details

Schema: FUSION
Object owner: WLF
Object type: VIEW

Columns

Name
EVENT_ASSIGNMENT_ID LEARNING_ITEM_ID NAME ASSIGNMENT_START_DATE ASSIGNMENT_DUE_DATE EFFECTIVE_START_DATE EFFECTIVE_END_DATE ATTRIBUTION_ID OVERDUE_COUNT COMPLETED_COUNT INCOMPLETE_COUNT WITHDRAWN_COUNT LEARNING_ITEM_TYPE THUMBNAIL_ID ALL_COMPLETED_FLAG CANCEL_FLAG STATUS DYNAMIC_ASSIGNMENT_ENABLE DYNAMIC_ASSIGNMENT_DUE_DAYS DYNAMIC_ASSIGNMENT_STOP_DATE CREATION_DATE

Name

EVENT_ASSIGNMENT_ID

LEARNING_ITEM_ID

NAME

ASSIGNMENT_START_DATE

ASSIGNMENT_DUE_DATE

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

ATTRIBUTION_ID

OVERDUE_COUNT

COMPLETED_COUNT

INCOMPLETE_COUNT

WITHDRAWN_COUNT

LEARNING_ITEM_TYPE

THUMBNAIL_ID

ALL_COMPLETED_FLAG

CANCEL_FLAG

STATUS

DYNAMIC_ASSIGNMENT_ENABLE

DYNAMIC_ASSIGNMENT_DUE_DAYS

DYNAMIC_ASSIGNMENT_STOP_DATE

CREATION_DATE

Query

SQL_Statement
WITH FutureAssignments AS ( SELECT Events.LEARNING_ITEM_ID, Events.EVENT_ID, Events.EVENT_TYPE, Events.ATTRIBUTION_ID, Events.ATTRIBUTION_TYPE, Translations.NAME, Items.LEARNING_ITEM_TYPE, ( CASE WHEN Items.LEARNING_ITEM_TYPE = 'ORA_CLASS' THEN (SELECT learningitems.thumbnail_id FROM wlf_learning_items_f learningitems, wlf_li_hierarchies_f hiers WHERE learningitems.learning_item_id= hiers.learning_item_id AND learningitems.learning_item_type = 'ORA_COURSE' AND learningitems.STATUS = 'ORA_COU_ACTIVE' AND hiers.child_learning_item_id = Items.learning_item_id AND trunc(SYSDATE) BETWEEN learningitems.EFFECTIVE_START_DATE AND learningitems.EFFECTIVE_END_DATE AND trunc(SYSDATE) BETWEEN hiers.EFFECTIVE_START_DATE AND hiers.EFFECTIVE_END_DATE ) ELSE Items.thumbnail_id END) THUMBNAIL_ID, Assignments.EVENT_ASSIGNMENT_ID, Assignments.ASSIGNMENT_START_DATE, Assignments.EFFECTIVE_START_DATE, Assignments.EFFECTIVE_END_DATE, Assignments.STATUS, Assignments.CREATION_DATE, CASE WHEN Records.CALCULATED_DUE_DATE IS NULL THEN AssignRules.INITIAL_DUE_DATE ELSE Records.CALCULATED_DUE_DATE END AS ASSIGNMENT_DUE_DATE, AssignRules.INITIAL_DUE_DATE_OPTION, AssignRules.EXPIRY_OPTION, AssignRules.DYN_ENABLED DYNAMIC_ASSIGNMENT_ENABLE, AssignRules.DYN_DUE_DATE_OPTION, AssignRules.DYN_DUE_DATE, AssignRules.DYN_DUE_IN_DAYS DYNAMIC_ASSIGNMENT_DUE_DAYS, AssignRules.DYN_STOP_NEW_DATE DYNAMIC_ASSIGNMENT_STOP_DATE FROM WLF_EVENTS Events, WLF_EVENT_ASSIGNMENTS_F Assignments,WLF_ASSIGNMENT_RECORDS_F Records, WLF_ASSIGNMENT_RULES AssignRules, WLF_LEARNING_ITEMS_F Items, WLF_LEARNING_ITEMS_F_TL Translations WHERE Events.EVENT_ID = Assignments.EVENT_ID AND Events.LEARNING_ITEM_ID = Items.LEARNING_ITEM_ID AND Items.LEARNING_ITEM_ID = Translations.LEARNING_ITEM_ID AND Translations.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG') AND (TRUNC(sysdate) BETWEEN Items.EFFECTIVE_START_DATE AND Items.EFFECTIVE_END_DATE) AND (TRUNC(sysdate) BETWEEN Translations.EFFECTIVE_START_DATE AND Translations.EFFECTIVE_END_DATE) AND Assignments.ASSIGNMENT_RULE_ID = AssignRules.ASSIGNMENT_RULE_ID(+) AND (Assignments.EVENT_ASSIGNMENT_ID = Records.EVENT_ASSIGNMENT_ID (+)) AND Events.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT' AND Events.ATTRIBUTION_TYPE = 'ORA_COMMUNITY' AND TRUNC(sysdate) < Assignments.ASSIGNMENT_START_DATE AND (TRUNC(sysdate) between Assignments.EFFECTIVE_START_DATE AND Assignments.EFFECTIVE_END_DATE) AND (TRUNC(sysdate) BETWEEN Records.EFFECTIVE_START_DATE(+) AND Records.EFFECTIVE_END_DATE(+)) ) , CurrentAssignments AS( SELECT Events.LEARNING_ITEM_ID, Events.EVENT_ID, Events.EVENT_TYPE, Events.ATTRIBUTION_ID, Events.ATTRIBUTION_TYPE, Translations.NAME, Items.LEARNING_ITEM_TYPE, ( CASE WHEN Items.LEARNING_ITEM_TYPE = 'ORA_CLASS' THEN (SELECT learningitems.thumbnail_id FROM wlf_learning_items_f learningitems, wlf_li_hierarchies_f hiers WHERE learningitems.learning_item_id= hiers.learning_item_id AND learningitems.learning_item_type = 'ORA_COURSE' AND learningitems.STATUS = 'ORA_COU_ACTIVE' AND hiers.child_learning_item_id = Items.learning_item_id AND trunc(SYSDATE) BETWEEN learningitems.EFFECTIVE_START_DATE AND learningitems.EFFECTIVE_END_DATE AND trunc(SYSDATE) BETWEEN hiers.EFFECTIVE_START_DATE AND hiers.EFFECTIVE_END_DATE ) ELSE Items.thumbnail_id END) THUMBNAIL_ID, Assignments.EVENT_ASSIGNMENT_ID, CASE WHEN Assignments.ASSIGNMENT_START_DATE IS NULL THEN Assignments.EFFECTIVE_START_DATE ELSE Assignments.ASSIGNMENT_START_DATE END AS ASSIGNMENT_START_DATE, Assignments.EFFECTIVE_START_DATE, Assignments.EFFECTIVE_END_DATE, Assignments.STATUS, Assignments.CREATION_DATE, CASE WHEN Records.CALCULATED_DUE_DATE IS NULL THEN AssignRules.INITIAL_DUE_DATE ELSE Records.CALCULATED_DUE_DATE END AS ASSIGNMENT_DUE_DATE, AssignRules.INITIAL_DUE_DATE_OPTION, AssignRules.EXPIRY_OPTION, AssignRules.DYN_ENABLED DYNAMIC_ASSIGNMENT_ENABLE, AssignRules.DYN_DUE_DATE_OPTION, AssignRules.DYN_DUE_DATE, AssignRules.DYN_DUE_IN_DAYS DYNAMIC_ASSIGNMENT_DUE_DAYS, AssignRules.DYN_STOP_NEW_DATE DYNAMIC_ASSIGNMENT_STOP_DATE, Records.LEARNER_ID, Records.ASSIGNMENT_RECORD_ID, CASE WHEN Records.STATUS IS NULL THEN NULL WHEN Records.STATUS = 'ORA_ASSN_REC_DELETED' THEN 'DELETED' WHEN Records.STATUS = 'ORA_ASSN_REC_WITHDRAWN' THEN 'WITHDRAWN' WHEN Records.STATUS = 'ORA_ASSN_REC_COMPLETE' THEN 'COMPLETED' WHEN ((Records.CALCULATED_DUE_DATE IS NULL AND (TRUNC(sysdate) > AssignRules.INITIAL_DUE_DATE)) OR TRUNC(sysdate) > Records.CALCULATED_DUE_DATE ) THEN 'OVERDUE' ELSE 'INCOMPLETE' END AS ASSN_STATUS, CASE WHEN AssignRules.DYN_ENABLED = 'Y' THEN AssignRules.DYN_STOP_NEW_DATE + AssignRules.DYN_DUE_IN_DAYS ELSE Records.CALCULATED_DUE_DATE END AS EVENT_ASSIGNMENT_DUE_DATE, CASE WHEN Records.ASSIGNMENT_RECORD_ID = (MAX(Records.ASSIGNMENT_RECORD_ID) OVER( PARTITION BY (Assignments.EVENT_ASSIGNMENT_ID))) THEN 'Y' WHEN Records.ASSIGNMENT_RECORD_ID IS NULL THEN 'Y' ELSE 'N' END AS LATEST_RECORD_FLAG FROM WLF_EVENTS Events, WLF_EVENT_ASSIGNMENTS_F Assignments, WLF_ASSIGNMENT_RECORDS_F Records, WLF_ASSIGNMENT_RULES AssignRules, WLF_LEARNING_ITEMS_F Items, WLF_LEARNING_ITEMS_F_TL Translations, WLF_ASSIGNMENT_RULES EventAssignRules WHERE Events.EVENT_ID = Assignments.EVENT_ID AND Events.LEARNING_ITEM_ID = Items.LEARNING_ITEM_ID AND Items.LEARNING_ITEM_ID = Translations.LEARNING_ITEM_ID AND Translations.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG') AND (TRUNC(sysdate) BETWEEN Items.EFFECTIVE_START_DATE AND Items.EFFECTIVE_END_DATE) AND (TRUNC(sysdate) BETWEEN Translations.EFFECTIVE_START_DATE AND Translations.EFFECTIVE_END_DATE) AND (Assignments.ASSIGNMENT_RULE_ID = EventAssignRules.ASSIGNMENT_RULE_ID(+)) AND (Assignments.EVENT_ASSIGNMENT_ID = Records.EVENT_ASSIGNMENT_ID (+)) AND (Assignments.ASSIGNMENT_RULE_ID = AssignRules.ASSIGNMENT_RULE_ID (+)) AND Events.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT' AND Events.ATTRIBUTION_TYPE = 'ORA_COMMUNITY' AND (TRUNC(sysdate) BETWEEN Records.EFFECTIVE_START_DATE(+) AND Records.EFFECTIVE_END_DATE(+)) AND (ASSIGNMENT_START_DATE IS NULL OR (TRUNC(sysdate) >= Assignments.ASSIGNMENT_START_DATE)) AND (TRUNC(sysdate) between Assignments.EFFECTIVE_START_DATE AND Assignments.EFFECTIVE_END_DATE) ) SELECT DISTINCT (EVENT_ASSIGNMENT_ID), LEARNING_ITEM_ID, Name, ASSIGNMENT_START_DATE, CASE WHEN ASSIGNMENT_DUE_DATE IS NULL THEN EVENT_ASSIGNMENT_DUE_DATE ELSE ASSIGNMENT_DUE_DATE END AS ASSIGNMENT_DUE_DATE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ATTRIBUTION_ID, (SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'OVERDUE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS OVERDUE_COUNT, (SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'COMPLETED' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS COMPLETED_COUNT, (SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'INCOMPLETE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS INCOMPLETE_COUNT, (SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'WITHDRAWN' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS WITHDRAWN_COUNT, LEARNING_ITEM_TYPE, THUMBNAIL_ID, CASE WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'OVERDUE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) > 0) THEN 'N' WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'INCOMPLETE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) > 0) THEN 'N' WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'COMPLETED' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) = 0) THEN 'N' WHEN (DYNAMIC_ASSIGNMENT_ENABLE = 'Y') AND (DYNAMIC_ASSIGNMENT_STOP_DATE IS NULL OR DYNAMIC_ASSIGNMENT_STOP_DATE + DYNAMIC_ASSIGNMENT_DUE_DAYS >= trunc(SYSDATE)) THEN 'N' ELSE 'Y' END AS ALL_COMPLETED_FLAG, CASE WHEN CA.EFFECTIVE_END_DATE < trunc(SYSDATE) AND CA.STATUS = 'ORA_ASSIGN_INACTIVE' THEN 'Y' ELSE 'N' END AS CANCEL_FLAG, STATUS, DYNAMIC_ASSIGNMENT_ENABLE, DYNAMIC_ASSIGNMENT_DUE_DAYS, DYNAMIC_ASSIGNMENT_STOP_DATE, CREATION_DATE FROM CurrentAssignments CA WHERE LATEST_RECORD_FLAG = 'Y' UNION SELECT DISTINCT (EVENT_ASSIGNMENT_ID), LEARNING_ITEM_ID, Name, ASSIGNMENT_START_DATE, ASSIGNMENT_DUE_DATE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ATTRIBUTION_ID, null,null,null, null, LEARNING_ITEM_TYPE, THUMBNAIL_ID, null, 'N' AS CANCEL_FLAG, STATUS, DYNAMIC_ASSIGNMENT_ENABLE, DYNAMIC_ASSIGNMENT_DUE_DAYS, DYNAMIC_ASSIGNMENT_STOP_DATE, CREATION_DATE FROM FutureAssignments FA ORDER BY LEARNING_ITEM_ID, ASSIGNMENT_START_DATE

SQL_Statement

WITH FutureAssignments AS (

SELECT Events.LEARNING_ITEM_ID,

Events.EVENT_ID,

Events.EVENT_TYPE,

Events.ATTRIBUTION_ID,

Events.ATTRIBUTION_TYPE,

Translations.NAME,

Items.LEARNING_ITEM_TYPE,

(

CASE

WHEN Items.LEARNING_ITEM_TYPE = 'ORA_CLASS' THEN

(SELECT learningitems.thumbnail_id

FROM wlf_learning_items_f learningitems,

wlf_li_hierarchies_f hiers

WHERE learningitems.learning_item_id= hiers.learning_item_id

AND learningitems.learning_item_type = 'ORA_COURSE'

AND learningitems.STATUS = 'ORA_COU_ACTIVE'

AND hiers.child_learning_item_id = Items.learning_item_id

AND trunc(SYSDATE) BETWEEN learningitems.EFFECTIVE_START_DATE AND learningitems.EFFECTIVE_END_DATE

AND trunc(SYSDATE) BETWEEN hiers.EFFECTIVE_START_DATE AND hiers.EFFECTIVE_END_DATE

)

ELSE

Items.thumbnail_id

END) THUMBNAIL_ID,

Assignments.EVENT_ASSIGNMENT_ID,

Assignments.ASSIGNMENT_START_DATE,

Assignments.EFFECTIVE_START_DATE,

Assignments.EFFECTIVE_END_DATE,

Assignments.STATUS,

Assignments.CREATION_DATE,

CASE WHEN Records.CALCULATED_DUE_DATE IS NULL THEN AssignRules.INITIAL_DUE_DATE

ELSE Records.CALCULATED_DUE_DATE END AS ASSIGNMENT_DUE_DATE,

AssignRules.INITIAL_DUE_DATE_OPTION,

AssignRules.EXPIRY_OPTION,

AssignRules.DYN_ENABLED DYNAMIC_ASSIGNMENT_ENABLE,

AssignRules.DYN_DUE_DATE_OPTION,

AssignRules.DYN_DUE_DATE,

AssignRules.DYN_DUE_IN_DAYS DYNAMIC_ASSIGNMENT_DUE_DAYS,

AssignRules.DYN_STOP_NEW_DATE DYNAMIC_ASSIGNMENT_STOP_DATE

FROM WLF_EVENTS Events, WLF_EVENT_ASSIGNMENTS_F Assignments,WLF_ASSIGNMENT_RECORDS_F Records, WLF_ASSIGNMENT_RULES AssignRules, WLF_LEARNING_ITEMS_F Items, WLF_LEARNING_ITEMS_F_TL Translations

WHERE Events.EVENT_ID = Assignments.EVENT_ID

AND Events.LEARNING_ITEM_ID = Items.LEARNING_ITEM_ID

AND Items.LEARNING_ITEM_ID = Translations.LEARNING_ITEM_ID

AND Translations.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

AND (TRUNC(sysdate) BETWEEN Items.EFFECTIVE_START_DATE AND Items.EFFECTIVE_END_DATE)

AND (TRUNC(sysdate) BETWEEN Translations.EFFECTIVE_START_DATE AND Translations.EFFECTIVE_END_DATE)

AND Assignments.ASSIGNMENT_RULE_ID = AssignRules.ASSIGNMENT_RULE_ID(+)

AND (Assignments.EVENT_ASSIGNMENT_ID = Records.EVENT_ASSIGNMENT_ID (+))

AND Events.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT'

AND Events.ATTRIBUTION_TYPE = 'ORA_COMMUNITY'

AND TRUNC(sysdate) < Assignments.ASSIGNMENT_START_DATE

AND (TRUNC(sysdate) between Assignments.EFFECTIVE_START_DATE AND Assignments.EFFECTIVE_END_DATE)

AND (TRUNC(sysdate) BETWEEN Records.EFFECTIVE_START_DATE(+) AND Records.EFFECTIVE_END_DATE(+))

)

, CurrentAssignments AS(

SELECT Events.LEARNING_ITEM_ID,

Events.EVENT_ID,

Events.EVENT_TYPE,

Events.ATTRIBUTION_ID,

Events.ATTRIBUTION_TYPE,

Translations.NAME,

Items.LEARNING_ITEM_TYPE,

(

CASE

WHEN Items.LEARNING_ITEM_TYPE = 'ORA_CLASS' THEN

(SELECT learningitems.thumbnail_id

FROM wlf_learning_items_f learningitems,

wlf_li_hierarchies_f hiers

WHERE learningitems.learning_item_id= hiers.learning_item_id

AND learningitems.learning_item_type = 'ORA_COURSE'

AND learningitems.STATUS = 'ORA_COU_ACTIVE'

AND hiers.child_learning_item_id = Items.learning_item_id

AND trunc(SYSDATE) BETWEEN learningitems.EFFECTIVE_START_DATE AND learningitems.EFFECTIVE_END_DATE

AND trunc(SYSDATE) BETWEEN hiers.EFFECTIVE_START_DATE AND hiers.EFFECTIVE_END_DATE

)

ELSE

Items.thumbnail_id

END) THUMBNAIL_ID,

Assignments.EVENT_ASSIGNMENT_ID,

CASE WHEN Assignments.ASSIGNMENT_START_DATE IS NULL THEN Assignments.EFFECTIVE_START_DATE

ELSE Assignments.ASSIGNMENT_START_DATE END

AS ASSIGNMENT_START_DATE,

Assignments.EFFECTIVE_START_DATE,

Assignments.EFFECTIVE_END_DATE,

Assignments.STATUS,

Assignments.CREATION_DATE,

CASE WHEN Records.CALCULATED_DUE_DATE IS NULL THEN AssignRules.INITIAL_DUE_DATE

ELSE Records.CALCULATED_DUE_DATE END AS ASSIGNMENT_DUE_DATE,

AssignRules.INITIAL_DUE_DATE_OPTION,

AssignRules.EXPIRY_OPTION,

AssignRules.DYN_ENABLED DYNAMIC_ASSIGNMENT_ENABLE,

AssignRules.DYN_DUE_DATE_OPTION,

AssignRules.DYN_DUE_DATE,

AssignRules.DYN_DUE_IN_DAYS DYNAMIC_ASSIGNMENT_DUE_DAYS,

AssignRules.DYN_STOP_NEW_DATE DYNAMIC_ASSIGNMENT_STOP_DATE,

Records.LEARNER_ID,

Records.ASSIGNMENT_RECORD_ID,

CASE WHEN Records.STATUS IS NULL THEN NULL

WHEN Records.STATUS = 'ORA_ASSN_REC_DELETED' THEN 'DELETED'

WHEN Records.STATUS = 'ORA_ASSN_REC_WITHDRAWN' THEN 'WITHDRAWN'

WHEN Records.STATUS = 'ORA_ASSN_REC_COMPLETE' THEN 'COMPLETED'

WHEN ((Records.CALCULATED_DUE_DATE IS NULL AND (TRUNC(sysdate) > AssignRules.INITIAL_DUE_DATE))

OR TRUNC(sysdate) > Records.CALCULATED_DUE_DATE ) THEN 'OVERDUE'

ELSE 'INCOMPLETE'

END AS ASSN_STATUS,

CASE WHEN AssignRules.DYN_ENABLED = 'Y' THEN AssignRules.DYN_STOP_NEW_DATE + AssignRules.DYN_DUE_IN_DAYS

ELSE Records.CALCULATED_DUE_DATE

END AS EVENT_ASSIGNMENT_DUE_DATE,

CASE WHEN Records.ASSIGNMENT_RECORD_ID = (MAX(Records.ASSIGNMENT_RECORD_ID) OVER( PARTITION BY (Assignments.EVENT_ASSIGNMENT_ID))) THEN 'Y'

WHEN Records.ASSIGNMENT_RECORD_ID IS NULL THEN 'Y'

ELSE 'N' END AS LATEST_RECORD_FLAG

FROM WLF_EVENTS Events, WLF_EVENT_ASSIGNMENTS_F Assignments, WLF_ASSIGNMENT_RECORDS_F Records, WLF_ASSIGNMENT_RULES AssignRules, WLF_LEARNING_ITEMS_F Items, WLF_LEARNING_ITEMS_F_TL Translations, WLF_ASSIGNMENT_RULES EventAssignRules

WHERE Events.EVENT_ID = Assignments.EVENT_ID

AND Events.LEARNING_ITEM_ID = Items.LEARNING_ITEM_ID

AND Items.LEARNING_ITEM_ID = Translations.LEARNING_ITEM_ID

AND Translations.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

AND (TRUNC(sysdate) BETWEEN Items.EFFECTIVE_START_DATE AND Items.EFFECTIVE_END_DATE)

AND (TRUNC(sysdate) BETWEEN Translations.EFFECTIVE_START_DATE AND Translations.EFFECTIVE_END_DATE)

AND (Assignments.ASSIGNMENT_RULE_ID = EventAssignRules.ASSIGNMENT_RULE_ID(+))

AND (Assignments.EVENT_ASSIGNMENT_ID = Records.EVENT_ASSIGNMENT_ID (+))

AND (Assignments.ASSIGNMENT_RULE_ID = AssignRules.ASSIGNMENT_RULE_ID (+))

AND Events.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT'

AND Events.ATTRIBUTION_TYPE = 'ORA_COMMUNITY'

AND (TRUNC(sysdate) BETWEEN Records.EFFECTIVE_START_DATE(+) AND Records.EFFECTIVE_END_DATE(+))

AND (ASSIGNMENT_START_DATE IS NULL OR (TRUNC(sysdate) >= Assignments.ASSIGNMENT_START_DATE))

AND (TRUNC(sysdate) between Assignments.EFFECTIVE_START_DATE AND Assignments.EFFECTIVE_END_DATE)

)

SELECT DISTINCT (EVENT_ASSIGNMENT_ID),

LEARNING_ITEM_ID,

Name,

ASSIGNMENT_START_DATE,

CASE WHEN ASSIGNMENT_DUE_DATE IS NULL THEN EVENT_ASSIGNMENT_DUE_DATE

ELSE ASSIGNMENT_DUE_DATE END AS ASSIGNMENT_DUE_DATE,

EFFECTIVE_START_DATE,

EFFECTIVE_END_DATE,

ATTRIBUTION_ID,

(SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'OVERDUE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS OVERDUE_COUNT,

(SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'COMPLETED' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS COMPLETED_COUNT,

(SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'INCOMPLETE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS INCOMPLETE_COUNT,

(SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'WITHDRAWN' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) AS WITHDRAWN_COUNT,

LEARNING_ITEM_TYPE,

THUMBNAIL_ID,

CASE WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'OVERDUE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) > 0) THEN 'N'

WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'INCOMPLETE' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) > 0) THEN 'N'

WHEN ((SELECT COUNT(DISTINCT CA1.LEARNER_ID) FROM CurrentAssignments CA1 WHERE ASSN_STATUS = 'COMPLETED' AND CA1.EVENT_ASSIGNMENT_ID = CA.EVENT_ASSIGNMENT_ID) = 0) THEN 'N'

WHEN (DYNAMIC_ASSIGNMENT_ENABLE = 'Y') AND (DYNAMIC_ASSIGNMENT_STOP_DATE IS NULL OR DYNAMIC_ASSIGNMENT_STOP_DATE + DYNAMIC_ASSIGNMENT_DUE_DAYS >= trunc(SYSDATE)) THEN 'N'

ELSE 'Y' END AS ALL_COMPLETED_FLAG,

CASE WHEN CA.EFFECTIVE_END_DATE < trunc(SYSDATE) AND CA.STATUS = 'ORA_ASSIGN_INACTIVE' THEN 'Y'

ELSE 'N' END AS CANCEL_FLAG,

STATUS,

DYNAMIC_ASSIGNMENT_ENABLE,

DYNAMIC_ASSIGNMENT_DUE_DAYS,

DYNAMIC_ASSIGNMENT_STOP_DATE,

CREATION_DATE

FROM CurrentAssignments CA

WHERE LATEST_RECORD_FLAG = 'Y'

UNION

SELECT DISTINCT (EVENT_ASSIGNMENT_ID),

LEARNING_ITEM_ID,

Name,

ASSIGNMENT_START_DATE,

ASSIGNMENT_DUE_DATE,

EFFECTIVE_START_DATE,

EFFECTIVE_END_DATE,

ATTRIBUTION_ID,

null,null,null, null,

LEARNING_ITEM_TYPE,

THUMBNAIL_ID,

null,

'N' AS CANCEL_FLAG,

STATUS,

DYNAMIC_ASSIGNMENT_ENABLE,

DYNAMIC_ASSIGNMENT_DUE_DAYS,

DYNAMIC_ASSIGNMENT_STOP_DATE,

CREATION_DATE

FROM FutureAssignments FA

ORDER BY LEARNING_ITEM_ID, ASSIGNMENT_START_DATE