WLF_COURSE_VOL_CLASS_REQ

Details

Schema: FUSION
Object owner: WLF
Object type: VIEW

Columns

Name
COURSE_LEARNING_ITEM_ID COURSE_LEARNER_ID COURSE_ASSIGNMENT_ID COURSE_STATUS COURSE_EVENT_TYPE CLASS_EVENT_TYPE COURSE_EFFECTIVE_START_DATE COURSE_EFFECTIVE_END_DATE CLASS_LEARNING_ITEM_ID CLASS_ASSIGNMENT_ID AR_CREATION_DATE AR_EFFECTIVE_START_DATE AR_EFFECTIVE_END_DATE

Name

COURSE_LEARNING_ITEM_ID

COURSE_LEARNER_ID

COURSE_ASSIGNMENT_ID

COURSE_STATUS

COURSE_EVENT_TYPE

CLASS_EVENT_TYPE

COURSE_EFFECTIVE_START_DATE

COURSE_EFFECTIVE_END_DATE

CLASS_LEARNING_ITEM_ID

CLASS_ASSIGNMENT_ID

AR_CREATION_DATE

AR_EFFECTIVE_START_DATE

AR_EFFECTIVE_END_DATE

Query

SQL_Statement
SELECT course_assignments.learning_item_id AS course_learning_item_id, course_assignments.learner_id AS course_learner_id, course_assignments.assignment_record_id AS course_assignment_id, course_assignments.status AS course_status, course_assignments.event_type AS course_event_type, class_assignments.event_type AS class_event_type, course_assignments.effective_start_date course_effective_start_date, course_assignments.effective_end_date course_effective_end_date, class_assignments.learning_item_id AS class_learning_item_id, class_assignments.assignment_record_id AS class_assignment_id, arrelations.creation_date ar_creation_date, arrelations.effective_start_date ar_effective_start_date, arrelations.effective_end_date ar_effective_end_date FROM wlf_assignment_records_f course_assignments, wlf_ar_relations_f arrelations, wlf_assignment_records_f class_assignments WHERE course_assignments.assignment_record_id = arrelations.related_object_id AND class_assignments.assignment_record_id = arrelations.assignment_record_id AND course_assignments.event_type = 'ORA_JOIN_ASSIGNMENT' AND class_assignments.event_type = 'ORA_REQUIRE_ASSIGNMENT' AND arrelations.relation_type = 'ORA_OFFERING_COURSE' AND arrelations.related_object_type = 'ORA_COURSE' AND class_assignments.status NOT IN ( 'ORA_ASSN_REC_DELETED', 'ORA_ASSN_REC_REQ_REJECTED', 'ORA_ASSN_REC_REJECTED', 'ORA_ASSN_REC_COMPLETE', 'ORA_ASSN_REC_PLANNING' ) AND course_assignments.status NOT IN ( 'ORA_ASSN_REC_DELETED', 'ORA_ASSN_REC_REQ_REJECTED', 'ORA_ASSN_REC_REJECTED', 'ORA_ASSN_REC_COMPLETE', 'ORA_ASSN_REC_PLANNING' ) AND trunc(SYSDATE) BETWEEN course_assignments.effective_start_date AND course_assignments.effective_end_date AND trunc(SYSDATE) BETWEEN class_assignments.effective_start_date AND class_assignments.effective_end_date AND trunc(SYSDATE) BETWEEN arrelations.effective_start_date AND arrelations.effective_end_date AND course_assignments.learner_id IS NOT NULL ORDER BY course_assignments.learning_item_id, course_assignments.learner_id

SQL_Statement

SELECT

course_assignments.learning_item_id AS course_learning_item_id,

course_assignments.learner_id AS course_learner_id,

course_assignments.assignment_record_id AS course_assignment_id,

course_assignments.status AS course_status,

course_assignments.event_type AS course_event_type,

class_assignments.event_type AS class_event_type,

course_assignments.effective_start_date course_effective_start_date,

course_assignments.effective_end_date course_effective_end_date,

class_assignments.learning_item_id AS class_learning_item_id,

class_assignments.assignment_record_id AS class_assignment_id,

arrelations.creation_date ar_creation_date,

arrelations.effective_start_date ar_effective_start_date,

arrelations.effective_end_date ar_effective_end_date

FROM

wlf_assignment_records_f course_assignments,

wlf_ar_relations_f arrelations,

wlf_assignment_records_f class_assignments

WHERE

course_assignments.assignment_record_id = arrelations.related_object_id

AND class_assignments.assignment_record_id = arrelations.assignment_record_id

AND course_assignments.event_type = 'ORA_JOIN_ASSIGNMENT'

AND class_assignments.event_type = 'ORA_REQUIRE_ASSIGNMENT'

AND arrelations.relation_type = 'ORA_OFFERING_COURSE'

AND arrelations.related_object_type = 'ORA_COURSE'

AND class_assignments.status NOT IN (

'ORA_ASSN_REC_DELETED',

'ORA_ASSN_REC_REQ_REJECTED',

'ORA_ASSN_REC_REJECTED',

'ORA_ASSN_REC_COMPLETE',

'ORA_ASSN_REC_PLANNING'

)

AND course_assignments.status NOT IN (

'ORA_ASSN_REC_DELETED',

'ORA_ASSN_REC_REQ_REJECTED',

'ORA_ASSN_REC_REJECTED',

'ORA_ASSN_REC_COMPLETE',

'ORA_ASSN_REC_PLANNING'

)

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

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

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

AND course_assignments.learner_id IS NOT NULL

ORDER BY

course_assignments.learning_item_id, course_assignments.learner_id