1. Tables and Views for HCM
  2. WLF_LEARNER_MULTI_ARS_V

WLF_LEARNER_MULTI_ARS_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

LEARNING_ITEM_ID

LEARNER_ID

ASSIGNMENT_RECORD_RANK

ASSIGNED_ON_DATE

LEARNING_ITEM_NUMBER

LEARNING_ITEM_TYPE

ASSIGNMENT_RECORD_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

EVENT_TYPE

STATUS

ASSIGNMENT_RECORD_COUNT

Query

SQL_Statement

WITH multiple_assign_records AS (

SELECT

learningitem.learning_item_id,

learningitem.learning_item_number,

learningitem.learning_item_type,

assignmentrecords.learner_id,

COUNT(learningitem.learning_item_id) assignment_record_count

FROM

wlf_learning_items_f learningitem

INNER JOIN wlf_assignment_records_f assignmentrecords ON learningitem.learning_item_id = assignmentrecords.learning_item_id

AND trunc(SYSDATE) BETWEEN assignmentrecords.effective_start_date AND assignmentrecords.effective_end_date

AND assignmentrecords.assigned_on_date <= trunc(SYSDATE)

AND assignmentrecords.learner_id IS NOT NULL

AND assignmentrecords.status IN (

'ORA_ASSN_REC_ACTIVE',

'ORA_ASSN_REC_CONTENT_COMPLETE',

'ORA_ASSN_REC_REQUESTED',

'ORA_ASSN_REC_PENDING_ACTIVE',

'ORA_ASSN_REC_PENDING_PAYMENT',

'ORA_ASSN_REC_PENDING_PREREQ',

'ORA_ASSN_REC_REQ_APPROVED',

'ORA_ASSN_REC_WAITLISTED',

'ORA_ASSN_REC_WITHDRAW_PENDING'

)

AND assignmentrecords.event_type IN (

'ORA_JOIN_ASSIGNMENT',

'ORA_REQUIRE_ASSIGNMENT'

)

WHERE

trunc(SYSDATE) BETWEEN learningitem.effective_start_date AND learningitem.effective_end_date

AND learningitem.learning_item_type = 'ORA_COURSE'

GROUP BY

learningitem.learning_item_id,

learningitem.learning_item_number,

learningitem.learning_item_type,

assignmentrecords.learner_id

HAVING

COUNT(learningitem.learning_item_id) > 1

), multi_assns_rank AS (

SELECT

multi_arec.*,

assignmentrecords.assignment_record_id,

assignmentrecords.effective_start_date,

assignmentrecords.effective_end_date,

assignmentrecords.event_type,

assignmentrecords.status,

trunc(assignmentrecords.assigned_on_date) assigned_on_date,

(

CASE

WHEN multi_arec.learning_item_type = 'ORA_COURSE' THEN

(SELECT 100 FROM wlf_ar_relations_f ar WHERE ar.related_object_id = assignmentrecords.assignment_record_id

AND ar.relation_type = 'ORA_OFFERING_COURSE'

AND ar.related_object_type = 'ORA_COURSE'

AND trunc(SYSDATE) BETWEEN ar.effective_start_date AND ar.effective_end_date

AND rownum = 1)

WHEN multi_arec.learning_item_type = 'ORA_CLASS' THEN

(SELECT 100 FROM wlf_ar_relations_f ar WHERE ar.assignment_record_id = assignmentrecords.assignment_record_id

AND ar.relation_type = 'ORA_OFFERING_COURSE'

AND ar.related_object_type = 'ORA_COURSE'

AND trunc(SYSDATE) BETWEEN ar.effective_start_date AND ar.effective_end_date

AND rownum = 1)

ELSE

0

END

) ar_relation_rank,

(

CASE

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQUESTED' THEN 7

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 6

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 5

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED' THEN 4

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_ACTIVE' THEN 3

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT' THEN 2

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE' THEN 1

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_CONTENT_COMPLETE' THEN 0

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WITHDRAW_PENDING' THEN 18

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQUESTED' THEN 17

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 16

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 15

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED' THEN 14

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_ACTIVE' THEN 13

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT' THEN 12

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE' THEN 11

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_CONTENT_COMPLETE' THEN 10

END

) AS status_rank

FROM

multiple_assign_records multi_arec

INNER JOIN wlf_assignment_records_f assignmentrecords ON assignmentrecords.learning_item_id = multi_arec.learning_item_id

AND assignmentrecords.learner_id = multi_arec.learner_id

AND assignmentrecords.event_type IN (

'ORA_JOIN_ASSIGNMENT',

'ORA_REQUIRE_ASSIGNMENT'

)

AND trunc(SYSDATE) BETWEEN assignmentrecords.effective_start_date AND assignmentrecords.effective_end_date

)

SELECT

learning_item_id,

learner_id,

(status_rank - NVL(ar_relation_rank, 0)) assignment_record_rank,

assigned_on_date,

learning_item_number,

learning_item_type,

assignment_record_id,

effective_start_date,

effective_end_date,

event_type,

status,

assignment_record_count

FROM

multi_assns_rank

ORDER BY

1, 2, 3