This code is what can be used to interface enrollments from Banner to your Moodle instance. The code contained below is hereby released under the GNU General Public License. Minor customization of the code may be necessary for your environment. This will create a set of views that can be accessed using the Moodle enrollments external Database configuration settings.
The environment this was developed on was Banner 8 (Oracle 11g) and Moodle 2.2.x. There is no reason this will not work with other combinations. Once you grasp the concepts of how the integration works, Banner programmers should easily be able to adjust the views to customize to your individual needs.
Banner itself is a Higher Education Software and is licensed from Ellucian. For more information about Ellucian, it's software offerings and copyrights, please visit their website at http://www.ellucian.com/
Moodle is an open source Course Management System released under similar Public License. For more information about Moodle, please visit their website at http://moodle.org.
Oracle is a commercial database package and you can learn more information about their products by visiting their website at http://www.oracle.com.
This document is targeted to Moodle Administrators and Banner DBAs or programmers.
We created a dedicated owner for all the database objects, "MOODLEUSR". This user will own the views and will need to have access the tables necessary to compile that view within Banner. You will likely want to create a second user account that is just able to access the views from Moodle. For this we will keep a single user for simplicity of discussion and development. You should consult your Oracle DBA and organizations best practices for implementing security between.
Moodle uses categories for each course. In our instance we usually have term based categories like "Spring 2012" or "Fall 2013". The category table is just going to be the cross reference table we use. Your courses will show up under the default Miscellaneous category if you do not have a mapping in this table.
CREATE TABLE IF NOT EXISTS "MOODLEUSR"."CATEGORY" ( "TERM_CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, "ID" VARCHAR2(2 BYTE) NOT NULL ENABLE, CONSTRAINT "CATAGORY_PK" PRIMARY KEY ("TERM_CODE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DEVELOPMENT" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "DEVELOPMENT" ;
Category codes from the Moodle side can be found in table mdl_course_categories.There are two fields, ID and Name. We are going to want to map those to the appropriate term codes.
Note: The Moodle database table used in the example was created with the "mdl" pre-fix, your tables may vary in name.
Banner programmers will be familiar with the term code table STVTERM. We will be matching the stvterm.stvterm_code (in Banner) with mdl_course_categories.id (in Moodle).
The Moodle category IDs can also be identified from the URL for the category in Moodle. Navigate to a specific category and the URL will look something like this: "https://your_school.edu/course/category.php?id=19". The ID in this case is 19. The categories can be found under Site Administration > Courses > Add/edit courses.
So we construct a table of data we wish to insert into the table. Here is an example of what our term_codes mapped to for our category codes.
Now we would just insert those values into the table through a SQL editor.
insert into moodleusr.category Values ('201201','4'); insert into moodleusr.category Values ('201202','8'); insert into moodleusr.category Values ('201209','10'); commit;
Now that we've created a category id to term code mapping, our next step will be to create a view that contains the course data we wish to make available to Moodle for course creation. For the Moodle enrollment to work we will need to have these 5 pieces of data:
CREATE OR REPLACE FORCE VIEW "MOODLEUSR"."COURSE" ("COURSE_ID", "SHORTNAME", "LONGNAME", "CATEGORY", "START_DATE") AS SELECT ssbsect_crn ||ssbsect_term_code, ssbsect_subj_code ||'-' ||ssbsect_crse_numb ||'-' || ssbsect_crn ||'-' ||ssbsect_term_code, ssbsect_subj_code ||'-' ||ssbsect_crse_numb ||'-' || ssbsect_crn ||'-' ||NVL(ssbsect_crse_title,a.scbcrse_title) || ' (' ||stvterm_desc ||')', cat.id, ssbsect_ptrm_start_date FROM stvterm, ssbsect, scbcrse a, moodleusr.category cat WHERE stvterm_code = cat.term_code AND ssbsect_term_code = stvterm_code AND a.scbcrse_subj_code = ssbsect_subj_code AND a.scbcrse_crse_numb = ssbsect_crse_numb AND a.scbcrse_eff_term = (SELECT MAX(b.scbcrse_eff_term) FROM scbcrse b WHERE b.scbcrse_subj_code = ssbsect_subj_code AND b.scbcrse_crse_numb = ssbsect_crse_numb AND b.scbcrse_eff_term <= ssbsect_term_code );
Thus far we've created the foundation piece to supply courses to Moodle. We cannot create enrollments into Moodle until the courses exist. So our next step is to create the enrollment view which will provide us that information.
CREATE OR REPLACE FORCE VIEW "MOODLEUSR"."ENROLLMENT" ("COURSE_ID", "ID", "ROLE") AS SELECT ssbsect_crn ||ssbsect_term_code, spriden_id, 'student' FROM stvterm, ssbsect, sfrstcr, spriden WHERE (stvterm_start_date BETWEEN TRUNC(SYSDATE) AND TRUNC(ADD_MONTHS(SYSDATE,6)) OR TRUNC(SYSDATE) BETWEEN stvterm_start_date AND ADD_MONTHS(stvterm_end_date,12)) AND ssbsect_term_code = stvterm_code AND ssbsect_ssts_code = 'A' AND sfrstcr_term_code = ssbsect_term_code AND sfrstcr_crn = ssbsect_crn AND sfrstcr_rsts_code IN ('RE','RW') AND spriden_pidm = sfrstcr_pidm AND spriden_change_ind IS NULL UNION SELECT ssbsect_crn ||ssbsect_term_code, spriden_id, 'instructor' FROM stvterm, ssbsect, sirasgn, spriden WHERE (stvterm_start_date BETWEEN TRUNC(SYSDATE) AND TRUNC(ADD_MONTHS(SYSDATE,6)) OR TRUNC(SYSDATE) BETWEEN stvterm_start_date AND ADD_MONTHS(stvterm_end_date,12)) AND ssbsect_term_code = stvterm_code AND ssbsect_ssts_code = 'A' AND sirasgn_term_code = ssbsect_term_code AND sirasgn_crn = ssbsect_crn AND spriden_pidm = sirasgn_pidm AND spriden_change_ind IS NULL;
This is where it becomes difficult to provide specific instruction. However I will proved highlights and links to Moodle Documentation which should assist in completing the implementation.
From our moodle root folder on the moodle server you will find a folder ..\enrol\database\cli\sync.php. The first couple of times you run this launch it from an interactive command line interface so you can monitor the results. You may want to run it a couple times to make sure. When you are satisfied your process is completing as desired, set up a recurring task on the Moodle server to run the sync at your desired interval. We would recommend at least daily. In our environment we run a user sync, and then 10 minutes later our enrollment sync in separate jobs. We do this so that a failure of one task doesn't impact the other task from completing. Mostly personal preference, but we don't want a user sync failure to stop enrollment sync and vice versa. This job pair runs once hourly, 24/7/365.
Now that your Banner to Moodle integration is up and running it's time to take 5 minutes, reflect on your success and have a nice cold glass of water. Now get back to work, there's always more to be done!