1. Tables and Views for HCM
  2. WLF_CHECK_PMRY_OFFNG_ARS_V

WLF_CHECK_PMRY_OFFNG_ARS_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

COURSE_ASSIGNMENT_REC_ID

CURRENT_PRIMARY_REC_ID

CORRECT_PRIMARY_REC_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

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'

)

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) >= TRUNC(nvl(assignmentrecords.assigned_on_date, SYSDATE))

AND assignmentrecords.learner_id IS NOT NULL

GROUP BY

offeringitem.course_learning_item_id,

assignmentrecords.learner_id

),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.status,

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

assignmentrecords.last_update_date,

assignmentrecords.assigned_on_date,

assignment_record_count,

(

CASE

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQUESTED' THEN 9

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 8

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 7

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED' THEN 6

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_WAITLISTED' THEN 5

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED' THEN 4

WHEN assignmentrecords.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT'

AND assignmentrecords.sub_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 = '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 29

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_REQ_APPROVED' THEN 28

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PREREQ'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_PENDING_PREREQ' THEN 27

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED' THEN 26

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_WAITLISTED'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_WAITLISTED' THEN 25

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT'

AND assignmentrecords.sub_status = 'ORA_ASSN_REC_NOTSTARTED' THEN 24

WHEN assignmentrecords.event_type = 'ORA_JOIN_ASSIGNMENT'

AND assignmentrecords.status = 'ORA_ASSN_REC_PENDING_PAYMENT'

AND assignmentrecords.sub_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 = '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'

)

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) >= TRUNC(nvl(assignmentrecords.assigned_on_date, SYSDATE))

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

),

primary_offering_assignment AS (

SELECT

course_learning_item_id,

learning_item_type,

class_learning_item_id,

learner_id,

event_type,

assignment_record_id,

status,

assigned_on_date,

assignment_record_count,

assignment_record_rank,

MIN(rownum) OVER(

PARTITION BY course_learning_item_id,learner_id

ORDER BY

assignment_record_rank ASC,

delivery_mode ASC,

last_update_date DESC

) AS row_rank

FROM

multi_course_ars

), correct_primaries AS (

SELECT

course_assignment_rec_id,

current_primary_rec_id,

correct_primary_rec_id,

effective_start_date,

effective_end_date,

assigned_on_date,

assignment_record_rank,

row_number () OVER (

PARTITION BY course_assignment_rec_id

ORDER BY

assignment_record_rank,

assigned_on_date DESC NULLS LAST

) rnum

FROM

(

SELECT

courseofferingrelations.related_object_id AS course_assignment_rec_id,

(

CASE

WHEN courseofferingrelations.primary_flag = 'Y' THEN courseofferingrelations.assignment_record_id

WHEN NVL(courseofferingrelations.primary_flag, 'N') = 'N' THEN -1

END

) AS current_primary_rec_id,

(

CASE

WHEN (

(

primarycrsofferassignment.assignment_record_rank >= 0

AND primarycrsofferassignment.assignment_record_rank < 3

)

OR (

primarycrsofferassignment.assignment_record_rank >= 20

AND primarycrsofferassignment.assignment_record_rank < 23

)

) THEN nvl(primarycrsofferassignment.assignment_record_id,-1)

WHEN (

(

primarycrsofferassignment.assignment_record_rank >= 3

AND primarycrsofferassignment.assignment_record_rank < 20

)

OR ( primarycrsofferassignment.assignment_record_rank >= 23 )

) THEN -1

END

) AS correct_primary_rec_id,

courseofferingrelations.effective_start_date,

courseofferingrelations.effective_end_date,

primarycrsofferassignment.assigned_on_date,

primarycrsofferassignment.assignment_record_rank

FROM

primary_offering_assignment primarycrsofferassignment

INNER JOIN wlf_ar_relations_f courseofferingrelations ON primarycrsofferassignment.assignment_record_id = courseofferingrelations.assignment_record_id

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

)

WHERE

correct_primary_rec_id != current_primary_rec_id)

SELECT

course_assignment_rec_id,

current_primary_rec_id,

correct_primary_rec_id,

effective_start_date,

effective_end_date

FROM

correct_primaries

WHERE

rnum = 1