WLF_LI_RATING

Details

Schema: FUSION
Object owner: WLF
Object type: VIEW

Columns

Name
LEARNING_ITEM_ID RATING_COUNT RATING_AVG

Name

LEARNING_ITEM_ID

RATING_COUNT

RATING_AVG

Query

SQL_Statement
select LEARNING_ITEM_ID, COUNT(RATING) AS RATING_COUNT, AVG(RATING) AS RATING_AVG from ( SELECT li.LEARNING_ITEM_ID, EventSocialEO.RATING, EventSocialEO.STATUS FROM WLF_EVENTS Event, WLF_EVENT_SOCIAL EventSocialEO, WLF_LEARNING_ITEMS_F li WHERE Event.EVENT_ID =EventSocialEO.EVENT_ID AND TRUNC(SYSDATE) BETWEEN li.EFFECTIVE_START_DATE(+) AND li.EFFECTIVE_END_DATE(+) AND Event.LEARNING_ITEM_ID = li.LEARNING_ITEM_ID AND li.LEARNING_ITEM_TYPE <> 'ORA_CLASS' AND (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_ACTIVE' or (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_INACTIVE' and rating_date is not null )) AND EVENT_TYPE ='ORA_LI_SOCIAL' AND Event.EVENT_SUB_TYPE ='ORA_EVT_SUBT_RATING' UNION all SELECT pli.LEARNING_ITEM_ID, EventSocialEO.RATING, EventSocialEO.STATUS FROM WLF_EVENTS Event, WLF_EVENT_SOCIAL EventSocialEO, WLF_LEARNING_ITEMS_F li, WLF_LI_HIERARCHIES_F lih, WLF_LEARNING_ITEMS_F pli WHERE Event.EVENT_ID =EventSocialEO.EVENT_ID AND TRUNC(SYSDATE) BETWEEN li.EFFECTIVE_START_DATE(+) AND li.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN pli.EFFECTIVE_START_DATE(+) AND pli.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN lih.EFFECTIVE_START_DATE(+) AND lih.EFFECTIVE_END_DATE(+) AND Event.LEARNING_ITEM_ID = li.LEARNING_ITEM_ID AND lih.CHILD_LEARNING_ITEM_ID = li.LEARNING_ITEM_ID and lih.LEARNING_ITEM_ID = pli.LEARNING_ITEM_ID and pli.LEARNING_ITEM_TYPE= 'ORA_COURSE' AND li.LEARNING_ITEM_TYPE = 'ORA_CLASS' AND (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_ACTIVE' or (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_INACTIVE' and rating_date is not null )) AND EVENT_TYPE ='ORA_LI_SOCIAL' AND Event.EVENT_SUB_TYPE ='ORA_EVT_SUBT_RATING') group by LEARNING_ITEM_ID

SQL_Statement

select LEARNING_ITEM_ID, COUNT(RATING) AS RATING_COUNT, AVG(RATING) AS RATING_AVG from ( SELECT

li.LEARNING_ITEM_ID,

EventSocialEO.RATING,

EventSocialEO.STATUS

FROM WLF_EVENTS Event,

WLF_EVENT_SOCIAL EventSocialEO,

WLF_LEARNING_ITEMS_F li

WHERE Event.EVENT_ID =EventSocialEO.EVENT_ID

AND TRUNC(SYSDATE) BETWEEN li.EFFECTIVE_START_DATE(+) AND li.EFFECTIVE_END_DATE(+)

AND Event.LEARNING_ITEM_ID = li.LEARNING_ITEM_ID

AND li.LEARNING_ITEM_TYPE <> 'ORA_CLASS'

AND (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_ACTIVE' or (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_INACTIVE' and rating_date is not null ))

AND EVENT_TYPE ='ORA_LI_SOCIAL'

AND Event.EVENT_SUB_TYPE ='ORA_EVT_SUBT_RATING'

UNION all

SELECT

pli.LEARNING_ITEM_ID,

EventSocialEO.RATING,

EventSocialEO.STATUS

FROM WLF_EVENTS Event,

WLF_EVENT_SOCIAL EventSocialEO,

WLF_LEARNING_ITEMS_F li, WLF_LI_HIERARCHIES_F lih, WLF_LEARNING_ITEMS_F pli

WHERE Event.EVENT_ID =EventSocialEO.EVENT_ID

AND TRUNC(SYSDATE) BETWEEN li.EFFECTIVE_START_DATE(+) AND li.EFFECTIVE_END_DATE(+)

AND TRUNC(SYSDATE) BETWEEN pli.EFFECTIVE_START_DATE(+) AND pli.EFFECTIVE_END_DATE(+)

AND TRUNC(SYSDATE) BETWEEN lih.EFFECTIVE_START_DATE(+) AND lih.EFFECTIVE_END_DATE(+)

AND Event.LEARNING_ITEM_ID = li.LEARNING_ITEM_ID

AND lih.CHILD_LEARNING_ITEM_ID = li.LEARNING_ITEM_ID and lih.LEARNING_ITEM_ID = pli.LEARNING_ITEM_ID and pli.LEARNING_ITEM_TYPE= 'ORA_COURSE'

AND li.LEARNING_ITEM_TYPE = 'ORA_CLASS'

AND (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_ACTIVE' or (EventSocialEO.STATUS = 'ORA_EVT_SOCIAL_INACTIVE' and rating_date is not null ))

AND EVENT_TYPE ='ORA_LI_SOCIAL'

AND Event.EVENT_SUB_TYPE ='ORA_EVT_SUBT_RATING') group by LEARNING_ITEM_ID