Change the PK1 of the Course Enrollment DSK

Problem: Students enrolled using the web interface (GUI) have a different Data Source Key (DSK) than ones entered through the Snapshot command.  When the snapshot enrollment is ran, a large number of errors are returned due to the wrong DSK. Workflow:

1. Determine “correct” DSK PK1
2. Determine the PK1 of the problematic course
3. Change DSK from SYSTEM (PK1 = 2) to be the “correct” DSK

Determine the PK1 of the enrollment DSK. In this example, the correct DSK is “1109” (Fall 2010), and the PK1 = 103.

SQL> select * from data_source where batch_uid LIKE '1109';
DTMODIFIE        PK1 ROW_STATUS
--------- ---------- ----------
BATCH_UID
-------------------------------------
DESCRIPTION
-------------------------------------
17-AUG-10        103          0
1109
Fall 2010 (1109) Courses

Determine the PK1 of the problematic course. SQL Command:  select pk1 from course_main where course_id LIKE ‘[COURSEID]’;

SQL> select pk1 from course_main where course_id LIKE '1109-THEUA-MBAD-5602-SEC901-46791';
 PK1
----------
       207

You can also find the number of students with the “incorrect” DSK PK1:

SQL> select COUNT(*) from course_users INNER JOIN course_main ON course_users.crsmain_pk1 = course_main.pk1 where course_users.data_src_pk1 = 2 AND course_users.role LIKE 'S' AND course_main.pk1 = 207;

COUNT(*)
----------
60

Change DSK from SYSTEM (PK1 = 2) to be the “correct” DSK Take the PK1s from Steps #1 and #2 and insert them into:  update course_users SET data_src_pk1 = [Step1 PK1] where data_src_pk1 = 2 AND role LIKE ‘S’ AND crsmain_pk1 = [Step 2 PK1];

SQL> update course_users SET data_src_pk1 = 103 where data_src_pk1 = 2 AND role LIKE 'S' AND crsmain_pk1 = 207;

60 rows updated.

]]>