Query for Enrolment details

Discussion created by jdiamant on Sep 28, 2016

Recently I've been helping a system admin investigate enrollment problems being experienced during SIS Complete Refresh processing.  The SIS error logs for Complete Refresh will have a log message at the end telling an admin only the Enrollment UID affected.  This is not useful to figure out which course and user the enrollment associates with.  Therefore to help with this problem I made a SELECT query which should help others out with any database querying they are doing related to enrollments:



    e.DTMODIFIED as Last_Enrolment_Update,

    cuid.BATCH_UID as Enrolment_BatchUID,

    e.row_status as Enrolment_STATUS,

    e.available_ind as Enrolment_Availability,

    d.batch_uid as DSK,

    cm.course_id as COURSE_ID,

    cm.pk1 as CourseMain_PK,

    e.CRSMAIN_PK1 as Course_PK_fromCourseUSERS,

    e.CHILD_CRSMAIN_PK1 as Child_Course_PK,

    u.user_id as USER_ID,

    u.BATCH_UID as User_BatchUID,

    u.email as EMAIL

from course_main cm

    join course_users e

    on cm.PK1 = e.CRSMAIN_PK1

    join DATA_SOURCE d

    on d.PK1 = e.DATA_SRC_PK1

    join users u

    on u.PK1 = e.USERS_PK1

    join COURSE_USERS_UID cuid

    on e.PK1 = cuid.COURSE_USERS_PK1


    --u.BATCH_UID = 'provide the external person key/batchuid of a user here'    --enable checking against a user external person key by removing -- from the start of this line

    cuid.BATCH_UID in (provide the enrolment UIDs here)   -- disable this check against enrolment ID's by adding -- comment syntax at the start of this line


    d.BATCH_UID = 'provide your dsk here'

order by e.row_status asc, cm.PK1 asc, u.BATCH_UID asc;