1. Tables and Views for HCM
  2. WLF_VISIBILITY_V

WLF_VISIBILITY_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

LEARNING_ITEM_ID

EVENT_TYPE

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement

WITH assigned_items AS

(SELECT asg.learning_item_id,

asg.event_type,

asg.learner_id,

asg.event_sub_type

FROM wlf_assignment_records_f asg

WHERE TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date

AND learner_id = HRC_SESSION_UTIL.GET_USER_PERSONID

AND (asg.event_type IN ('ORA_REQUIRE_ASSIGNMENT','ORA_RECOMMEND_ASSIGNMENT','ORA_JOIN_ASSIGNMENT')

AND asg.status IN ('ORA_ASSN_REC_COMPLETE','ORA_ASSN_REC_CONTENT_COMPLETE','ORA_ASSN_REC_ACTIVE')

)

)

SELECT itm.learning_item_id,

event_type,

itm.effective_start_date,

itm.effective_end_date

FROM wlf_learning_items_f itm,

assigned_items asg_itm

WHERE

itm.learning_item_id = asg_itm.learning_item_id (+)

AND (

asg_itm.learning_item_id IS NOT NULL

OR (

itm.learning_item_type in ('ORA_VIDEO','ORA_TUTORIAL','ORA_COMMUNITY') and

itm.visibility in ('PUBLIC','ORA_PUBLIC','ORA_CLOSED')

)

OR EXISTS (

SELECT 1 FROM WLF_LI_AG_RELATIONS_F relation,

WLF_LI_ACCESS_GROUPS_F accessli,

WLF_ASSIGNMENT_RECORDS_F asgRec

WHERE

relation.ACCESS_LEARNING_ITEM_ID= asgRec.LEARNING_ITEM_ID

AND relation.CATALOG_LEARNING_ITEM_ID=itm.LEARNING_ITEM_ID

AND relation.ACCESS_LEARNING_ITEM_ID=accessli.LEARNING_ITEM_ID

AND accessli.ACCESS_GROUP_TYPE='ORA_ACCESS_GROUP_ADHOC'

AND relation.EVENT_ASSIGNMENT_ID= asgRec.EVENT_ASSIGNMENT_ID

AND asgRec.EVENT_TYPE='ORA_LI_VIEWER'

AND asgRec.LEARNER_ID=HRC_SESSION_UTIL.GET_USER_PERSONID

AND asgRec.STATUS='ORA_ASSN_REC_ACTIVE'

AND TRUNC(SYSDATE) BETWEEN TRUNC(relation.EFFECTIVE_START_DATE) AND TRUNC(relation.EFFECTIVE_END_DATE)

AND TRUNC(SYSDATE) BETWEEN TRUNC(accessli.EFFECTIVE_START_DATE) AND TRUNC(accessli.EFFECTIVE_END_DATE)

AND TRUNC(SYSDATE) BETWEEN TRUNC(asgRec.EFFECTIVE_START_DATE) AND TRUNC(asgRec.EFFECTIVE_END_DATE)

)

OR

(

NOT EXISTS (SELECT 1 FROM WLF_LI_AG_RELATIONS_F relation,

WLF_LI_ACCESS_GROUPS_F accessli,

WLF_ASSIGNMENT_RECORDS_F asgRec

WHERE

relation.ACCESS_LEARNING_ITEM_ID= asgRec.LEARNING_ITEM_ID

AND relation.CATALOG_LEARNING_ITEM_ID=itm.LEARNING_ITEM_ID

AND relation.ACCESS_LEARNING_ITEM_ID=accessli.LEARNING_ITEM_ID

AND accessli.ACCESS_GROUP_TYPE='ORA_ACCESS_GROUP_ADHOC'

AND relation.EVENT_ASSIGNMENT_ID= asgRec.EVENT_ASSIGNMENT_ID

AND asgRec.EVENT_TYPE='ORA_LI_VIEWER'

AND asgRec.LEARNER_ID=HRC_SESSION_UTIL.GET_USER_PERSONID

AND asgRec.STATUS='ORA_ASSN_REC_DELETED'

AND TRUNC(SYSDATE) BETWEEN TRUNC(relation.EFFECTIVE_START_DATE) AND TRUNC(relation.EFFECTIVE_END_DATE)

AND TRUNC(SYSDATE) BETWEEN TRUNC(accessli.EFFECTIVE_START_DATE) AND TRUNC(accessli.EFFECTIVE_END_DATE)

AND TRUNC(SYSDATE) BETWEEN TRUNC(asgRec.EFFECTIVE_START_DATE) AND TRUNC(asgRec.EFFECTIVE_END_DATE)

)

AND

((itm.SS_VIEW_MODE IN ('ORA_DETAILS','ORA_SUMMARY')

AND

NOT EXISTS(select r3.priority from wlf_li_ag_relations_f r3,wlf_assignment_records_f a2,(

select * from (

select r.catalog_learning_item_id,r.access_learning_item_id,a.learner_id,a.event_type,r.priority,

ROW_NUMBER() OVER (partition by catalog_learning_item_id order by priority asc) minp from

wlf_li_ag_relations_f r,wlf_assignment_records_f a where

a.learner_id=HRC_SESSION_UTIL.GET_USER_PERSONID

and a.event_type='ORA_LI_VIEWER'

and r.catalog_learning_item_id=itm.learning_item_id

and a.learning_item_id=r.access_learning_item_id

and TRUNC(SYSDATE) BETWEEN TRUNC(a.EFFECTIVE_START_DATE) AND TRUNC(a.EFFECTIVE_END_DATE)) where minp=1) prior_rows

where r3.catalog_learning_item_id=prior_rows.catalog_learning_item_id and a2.learning_item_id=r3.access_learning_item_id

and a2.learner_id=HRC_SESSION_UTIL.GET_USER_PERSONID and r3.priority=prior_rows.priority and a2.status = 'ORA_ASSN_REC_DELETED' and a2.event_type='ORA_LI_VIEWER'

and TRUNC(SYSDATE) BETWEEN TRUNC(a2.EFFECTIVE_START_DATE) AND TRUNC(a2.EFFECTIVE_END_DATE)

))

OR

(itm.SS_VIEW_MODE IN ('ORA_NONE')

AND

EXISTS (

select r3.priority from wlf_li_ag_relations_f r3,wlf_assignment_records_f a2,(

select * from (

select r.catalog_learning_item_id,r.access_learning_item_id,a.learner_id,a.event_type,r.priority,

ROW_NUMBER() OVER (partition by catalog_learning_item_id order by priority asc) minp from

wlf_li_ag_relations_f r,wlf_assignment_records_f a where

a.learner_id=HRC_SESSION_UTIL.GET_USER_PERSONID

and a.event_type='ORA_LI_VIEWER'

and r.catalog_learning_item_id=itm.learning_item_id

and a.learning_item_id=r.access_learning_item_id and TRUNC(SYSDATE) BETWEEN TRUNC(a.EFFECTIVE_START_DATE) AND TRUNC(a.EFFECTIVE_END_DATE)) where minp=1) prior_rows

where r3.catalog_learning_item_id=prior_rows.catalog_learning_item_id and a2.learning_item_id=r3.access_learning_item_id

and a2.learner_id=HRC_SESSION_UTIL.GET_USER_PERSONID and r3.priority=prior_rows.priority and a2.status = 'ORA_ASSN_REC_ACTIVE' and a2.event_type='ORA_LI_VIEWER' and TRUNC(SYSDATE) BETWEEN TRUNC(a2.EFFECTIVE_START_DATE) AND TRUNC(a2.EFFECTIVE_END_DATE)

)

)

)

)

OR

itm.learning_item_type not in ('ORA_COURSE','ORA_CLASS','ORA_SPECIALIZATION')

AND (itm.attribution_id = HRC_SESSION_UTIL.GET_USER_PERSONID

AND itm.attribution_type = 'ORA_PERSON' )

OR

(itm.SS_VIEW_MODE = 'ORA_NONE'

AND itm.LEARNING_ITEM_TYPE = 'ORA_COURSE'

AND EXISTS

( WITH assigned_items1 AS

(SELECT asg.learning_item_id,

asg.event_type,

asg.learner_id,

asg.event_sub_type

FROM wlf_assignment_records_f asg

WHERE asg.event_type in ('ORA_REQUIRE_ASSIGNMENT','ORA_JOIN_ASSIGNMENT')

AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date

AND learner_id = HRC_SESSION_UTIL.GET_USER_PERSONID

)

SELECT 1

FROM wlf_li_hierarchies_f hier, assigned_items1

WHERE hier.LEARNING_ITEM_ID = itm.learning_item_id

AND hier.CHILD_LEARNING_ITEM_ID = assigned_items1.learning_item_id

)

)

)

AND (NOT EXISTS

(SELECT 1

FROM WLF_LEARNING_ITEMS_F

WHERE LEARNING_ITEM_ID = itm.ATTRIBUTION_ID

AND itm.ATTRIBUTION_TYPE = 'ORA_COMMUNITY'

AND SS_VIEW_MODE = 'ORA_NONE'

AND NOT EXISTS

(SELECT 1

FROM wlf_assignment_records_f member_record

WHERE TRUNC(SYSDATE) BETWEEN member_record.effective_start_date AND member_record.effective_end_date

AND member_record.learner_id = HRC_SESSION_UTIL.GET_USER_PERSONID

AND member_record.learning_item_id = itm.ATTRIBUTION_ID

)

) )