1. Tables and Views for HCM
  2. WLF_LEARNER_MULTI_COURSE_ARS_V

WLF_LEARNER_MULTI_COURSE_ARS_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

COURSE_LEARNING_ITEM_ID

LEARNING_ITEM_TYPE

CLASS_LEARNING_ITEM_ID

LEARNER_ID

EVENT_TYPE

ASSIGNMENT_RECORD_ID

ASSIGNMENT_EFF_START_DATE

ASSIGNMENT_EFF_END_DATE

STATUS

ASSIGNMENT_RECORD_COUNT

ASSIGNMENT_RECORD_RANK

Query

SQL_Statement

WITH multiple_assign_records AS (

SELECT

COUNT(offeringitem.course_learning_item_id) assignment_record_count,

offeringitem.course_learning_item_id,

assignmentrecords.learner_id

FROM

wlf_learning_items_f learningitem,

wlf_assignment_records_f assignmentrecords,

wlf_li_classes_f offeringitem

WHERE

learningitem.learning_item_type = 'ORA_CLASS'

AND assignmentrecords.status IN (

'ORA_ASSN_REC_REQUESTED',

'ORA_ASSN_REC_REQ_APPROVED',

'ORA_ASSN_REC_PENDING_PREREQ',

'ORA_ASSN_REC_WAITLISTED',

'ORA_ASSN_REC_PENDING_PAYMENT',

'ORA_ASSN_REC_ACTIVE',

'ORA_ASSN_REC_CONTENT_COMPLETE',

'ORA_ASSN_REC_PENDING_ACTIVE'

)

AND assignmentrecords.event_type IN (

'ORA_JOIN_ASSIGNMENT',

'ORA_REQUIRE_ASSIGNMENT'

)

AND learningitem.learning_item_id = assignmentrecords.learning_item_id

AND learningitem.learning_item_id = offeringitem.learning_item_id

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

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

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

AND trunc(SYSDATE) > assignmentrecords.assigned_on_date

AND assignmentrecords.learner_id IS NOT NULL

GROUP BY

offeringitem.course_learning_item_id,

assignmentrecords.learner_id

HAVING

COUNT(offeringitem.course_learning_item_id) > 1

),multi_course_ars AS (

SELECT

offeringitem.course_learning_item_id,

learningitem.learning_item_type,

offeringitem.learning_item_id class_learning_item_id,

assignmentrecords.learner_id,

assignmentrecords.event_type,

assignmentrecords.assignment_record_id,

assignmentrecords.effective_start_date assignment_eff_start_date,

assignmentrecords.effective_end_date assignment_eff_end_date,

assignmentrecords.last_update_date,

assignmentrecords.status,

DECODE(offeringitem.delivery_mode, 'ORA_ILT', 1, 'ORA_BLENDED', 2, 'ORA_SP', 3) delivery_mode,

assignment_record_count,

(

CASE

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQUESTED' THEN 8

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 7

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 6

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED' THEN 5

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_ACTIVE' THEN 4

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT' THEN 3

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE'

AND (assignmentrecords.sub_status IS NULL OR assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED') THEN (

SELECT

to_number(concat('2',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_INPROGRESS' THEN (

SELECT

to_number(concat('1',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_CONTENT_COMPLETE' THEN (

SELECT

to_number(concat('0',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQUESTED' THEN 28

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 27

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 26

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED' THEN 25

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_ACTIVE' THEN 24

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT' THEN 23

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE'

AND (assignmentrecords.sub_status IS NULL OR assignmentrecords.sub_status IS NULL OR assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED') THEN (

SELECT

to_number(concat('22',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_ACTIVE'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_INPROGRESS' THEN (

SELECT

to_number(concat('21',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_CONTENT_COMPLETE' THEN (

SELECT

to_number(concat('20',concat('.', (

SELECT

TO_CHAR( (assignmentrecords.calculated_due_date) - DATE '1970-01-01') * (24 * 60 * 60 * 1000)

FROM

dual

) ) ) )

FROM

dual

)

END

) AS assignment_record_rank

FROM

wlf_learning_items_f learningitem,

wlf_li_classes_f offeringitem,

wlf_assignment_records_f assignmentrecords,

multiple_assign_records

WHERE

multiple_assign_records.course_learning_item_id = offeringitem.course_learning_item_id

AND assignmentrecords.learner_id = multiple_assign_records.learner_id

AND learningitem.learning_item_type = 'ORA_CLASS'

AND assignmentrecords.status IN (

'ORA_ASSN_REC_REQUESTED',

'ORA_ASSN_REC_REQ_APPROVED',

'ORA_ASSN_REC_PENDING_PREREQ',

'ORA_ASSN_REC_WAITLISTED',

'ORA_ASSN_REC_PENDING_PAYMENT',

'ORA_ASSN_REC_ACTIVE',

'ORA_ASSN_REC_CONTENT_COMPLETE',

'ORA_ASSN_REC_PENDING_ACTIVE'

)

AND assignmentrecords.event_type IN (

'ORA_JOIN_ASSIGNMENT',

'ORA_REQUIRE_ASSIGNMENT'

)

AND learningitem.learning_item_id = assignmentrecords.learning_item_id

AND learningitem.learning_item_id = offeringitem.learning_item_id

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

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

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

AND trunc(SYSDATE) > assignmentrecords.assigned_on_date

AND assignmentrecords.learner_id IS NOT NULL

ORDER BY

offeringitem.course_learning_item_id,

assignmentrecords.learner_id,

assignment_record_rank,

delivery_mode,

assignmentrecords.last_update_date DESC

) SELECT

course_learning_item_id,

learning_item_type,

class_learning_item_id,

learner_id,

event_type,

assignment_record_id,

assignment_eff_start_date,

assignment_eff_end_date,

status,

assignment_record_count,

assignment_record_rank

FROM

multi_course_ars

WHERE

course_learning_item_id NOT IN (

SELECT

course_learning_item_id

FROM

multi_course_ars

WHERE

learner_id IS NOT NULL

GROUP BY

course_learning_item_id,

learner_id

HAVING (

MAX(event_type) = 'ORA_JOIN_ASSIGNMENT'

AND MAX(assignment_record_rank) > 23

)

OR (

MIN(event_type) = 'ORA_REQUIRE_ASSIGNMENT'

AND MAX(assignment_record_rank) > 3

)

)

ORDER BY

course_learning_item_id,

learner_id,

assignment_record_rank,

delivery_mode,

last_update_date DESC