1 / 28

Some LSU Banner Group Projects

Some LSU Banner Group Projects. By Denny Lin. About Me. Born in Taiwan, grew up in Singapore and Bolivia. Double-majored in Computer Science and Music from La Sierra University Masters in Computer Science from CSU Fullerton Worked in IT since 1989 Part time Faculty at LSU since 2001

gladys
Download Presentation

Some LSU Banner Group Projects

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Some LSU Banner Group Projects By Denny Lin

  2. About Me • Born in Taiwan, grew up in Singapore and Bolivia. • Double-majored in Computer Science and Music from La Sierra University • Masters in Computer Science from CSU Fullerton • Worked in IT since 1989 • Part time Faculty at LSU since 2001 • Sings in Loma Linda University Church Sanctuary Choir • Writing an opera based on Esther • Married to Eunice, has two kids Fiona (7) and Gared (3)

  3. Recent Banner Projects • Reports: • SWWAPPL: Lists all applicant details, including personal essay. Part of the online application process. • SWWCSAS: Tracks academic progress of first year students. • FWWCHDP: Information about manufacturers for chemicals stored in different departments. • Updates: • Campus code change for students in the Criminal Justice program. • Schedule conflict error fixes for Winter 2011

  4. Old online application process: Student completes an application to LSU on website. Application is submitted as an e-mail to the Registrar, resulting in piles of printed e-mail applications Registrar office staff manually retypesinformation into Banner, resulting in delays and typing mistakes Staff monitors whether application is complete Student essays may be received separately through snail-mail Documents are easily misplaced, leading to delays in decision to admit applicants Anecdotal reports of lost applications and applicants going to competing universities New online application process Student logs into Self-Service and enters application on website, including student essay Website automatically tracks the progress of application All Self-Service website information can be electronically “pushed” into Banner SWWAPPL is a printed report used to help the Admissions committee decide whether to admit applicants Record-breaking enrollment in Fall 2010 SWWAPPL

  5. SWWAPPL Data Model

  6. SWWAPPL PDF File Output

  7. SWWCSAS • Center for Student Academic Success (CSAS) uses SWWCSAS to track the academic progress of Freshmen • The report lists for each Freshman their admissions and placement test results (there are 12 possible tests), quarterly GPA, and the grades of classes (there are 40 possible classes) taken every quarter • Statistical analysis (using SPSS) are performed to help determine key factors that contribute to the academic success or failure of applicants

  8. SWWCSAS Data Model

  9. SWWCSAS Excel File Output

  10. FWWCHDP • LSU is required to have an inventory listing of chemicals stored by each department (Chemistry, Custodial, Biology, Physics, Physical Plant, etc.) • The Risk Management office provides a listing of these chemicals upon request by any state or federal regulating agencies • FWWCHDP lists chemicals, contact information of the chemical manufacturers, by the department that houses them • Listing was not displaying correctly. See if you can determine the cause of this error!

  11. FWWCHDP Before

  12. FWWCHDP After

  13. FWWCHDP Data Model

  14. Criminal Justice Campus Code Change • LSU launched a new Criminal Justice program in Fall 2010 • All academic programs have a location. Most programs are on campus; on-campus programs have a campus code of 6. The Criminal Justice program (BS-CRJUST) is located off-campus • Before mid-October 2010, most applicants and students in the Criminal Justice program were tagged as belonging to a program located in an “LSU Off-Campus” program, with a campus code of 7 • By mid-October 2010, it was decided that the Criminal Justice program must have a campus code of 9, specifically for the Criminal Justice location in Corona, CA • There are over a hundred of these records that need to be updated, so it is more efficient to use SQL scripts rather than manual labor • The scripts modify records created by the Admissions office (tagged as ‘Admissions’) and the Registrar’s office (tagged as ‘Learner’); we ignore records created by Recruitment (tagged as ‘Recruit’). Any one of these offices may have created a student record with campus code ‘6’ or ‘7’ • This change mustonly affect Criminal Justice program students, from the 201102 term (current quarter) and up to the 201204 term

  15. column LMOD_CODE head 'LMOD CODE' for a15 select sorlcur_pidm PIDM, sorlcur_lmod_code LMOD_CODE, sorlcur_camp_code CAMP_CODE from sorlcur where sorlcur_term_code = '201202' and sorlcur_program = 'BS-CRJUST' and sorlcur_lmod_code IN ('LEARNER','ADMISSIONS') and sorlcur_camp_code IN ('6','7') order by 1 PIDM LMOD CODE CAMP_CODE ---------- --------------- ------------ 10185531 ADMISSIONS 7 10185605 ADMISSIONS 7 10185605 LEARNER 7 10185621 ADMISSIONS 7 10185723 ADMISSIONS 7 10186162 ADMISSIONS 7 10186427 ADMISSIONS 7 10186480 ADMISSIONS 7 10186527 ADMISSIONS 7 10186560 ADMISSIONS 7 10186561 ADMISSIONS 7 10186562 ADMISSIONS 7 10186567 ADMISSIONS 7 10186631 ADMISSIONS 7 10186631 LEARNER 7 10186641 ADMISSIONS 7 10186667 ADMISSIONS 7 10186677 ADMISSIONS 7 10186701 ADMISSIONS 7 10186715 ADMISSIONS 7 10186716 ADMISSIONS 7 10186720 ADMISSIONS 7 10186722 ADMISSIONS 7 10186755 ADMISSIONS 7 10186788 ADMISSIONS 7 10186789 ADMISSIONS 7 10186790 ADMISSIONS 7 10186803 ADMISSIONS 7 10186809 ADMISSIONS 7 29 rows selected. BS-CRJUST students in codes 6 or 7 for 201202 (Fall 2011)

  16. column LMOD_CODE head 'LMOD CODE' for a15 select sorlcur_pidm PIDM, sorlcur_lmod_code LMOD_CODE, sorlcur_camp_code CAMP_CODE from sorlcur where sorlcur_term_code = '201202' and sorlcur_program = 'BS-CRJUST' and sorlcur_lmod_code IN ('LEARNER','ADMISSIONS') and sorlcur_camp_code = '9' order by 1; PIDM LMOD CODE CAMP_CODE ---------- --------------- ------------ 10186816 ADMISSIONS 9 10186817 ADMISSIONS 9 10186831 ADMISSIONS 9 10186835 ADMISSIONS 9 10186836 ADMISSIONS 9 10186837 ADMISSIONS 9 10186993 ADMISSIONS 9 10186995 ADMISSIONS 9 10187024 ADMISSIONS 9 10187062 ADMISSIONS 9 10187063 ADMISSIONS 9 10187081 ADMISSIONS 9 10187082 ADMISSIONS 9 10187083 ADMISSIONS 9 14 rows selected. BS-CRJUST students in code 9 for 201202 (Fall 2011)

  17. update sorlcur set sorlcur_camp_code = '9' where sorlcur_lmod_code IN ('LEARNER','ADMISSIONS') and sorlcur_camp_code IN ('6','7') and sorlcur_term_code = '201202' and sorlcur_program = 'BS-CRJUST'; [System response]: 29 rows updated. Update BS-CRJUST students in Fall 2011 from codes 6 or 7 to code 9

  18. PIDM LMOD CODE CAMP_CODE ---------- --------------- ------------ 10185531 ADMISSIONS 9 10185605 ADMISSIONS 9 10185605 LEARNER 9 10185621 ADMISSIONS 9 10185723 ADMISSIONS 9 10186162 ADMISSIONS 9 10186427 ADMISSIONS 9 10186480 ADMISSIONS 9 10186527 ADMISSIONS 9 10186560 ADMISSIONS 9 10186561 ADMISSIONS 9 10186562 ADMISSIONS 9 10186567 ADMISSIONS 9 10186631 LEARNER 9 10186631 ADMISSIONS 9 10186641 ADMISSIONS 9 10186667 ADMISSIONS 9 10186677 ADMISSIONS 9 10186701 ADMISSIONS 9 10186715 ADMISSIONS 9 10186716 ADMISSIONS 9 10186720 ADMISSIONS 9 10186722 ADMISSIONS 9 10186755 ADMISSIONS 9 10186788 ADMISSIONS 9 10186789 ADMISSIONS 9 10186790 ADMISSIONS 9 10186803 ADMISSIONS 9 10186809 ADMISSIONS 9 10186816 ADMISSIONS 9 10186817 ADMISSIONS 9 10186831 ADMISSIONS 9 10186835 ADMISSIONS 9 10186836 ADMISSIONS 9 10186837 ADMISSIONS 9 10186993 ADMISSIONS 9 10186995 ADMISSIONS 9 10187024 ADMISSIONS 9 10187062 ADMISSIONS 9 10187063 ADMISSIONS 9 10187081 ADMISSIONS 9 10187082 ADMISSIONS 9 10187083 ADMISSIONS 9 43 rows selected. Checking Update Results We expect to see 43 rows containing campus code 9 after this query: column LMOD_CODE head 'LMOD CODE' for a15 select sorlcur_pidm PIDM, sorlcur_lmod_code LMOD_CODE, sorlcur_camp_code CAMP_CODE from sorlcur where sorlcur_term_code = '201204' and sorlcur_program = 'BS-CRJUST' and sorlcur_lmod_code IN ('LEARNER','ADMISSIONS') and sorlcur_camp_code = '9' order by 1;

  19. Post Update • Only after confirming that we have the same results as expected, the changes are committed to the database using the “commit” command • Conflicting numbers of changed records might mean that there was a logical error in the SQL code. In that case, changes are rolled back using the “rollback” command

  20. Schedule Conflict Errors • The registrar’s office had been unable to allocate a classroom for a class in Winter 2011 using Banner: • ARTS395A meets Tuesdays and Wednesdays from 15:00 – 16:50 in VAC301 • SSAMATX shows only one class meets in VAC301: ARTS194B Mon and Wed from 17:00 – 18:50 • SSASECT insists that there is a schedule conflict • This problem stopped registration for Winter 2011, and would have significant academic impact for students and financial implications for the university

  21. SSASECT display for Winter 2011 ARTS395A

  22. SSAMATX view of room assignments for VAC301

  23. Cause of the Schedule Conflict • SSASECT shows that ARTS395A for Winter 2011 meets from 10-JAN-2010 to 24-MAR-2010. These are Winter 2010 dates • The future class was meeting in a time slot that conflicted with a class that had already taken place in VAC301 • A batch program was used to transfer (“roll-over”) meeting times of all classes that were taught in Winter of 2010. This batch operation did not complete with the correct start and end year information

  24. Fixing the error • Two database tables are involved in storing the start and end date information • SSBSECT: 1230 rows (courses) with erroneous dates • SSRMEET: 1185 rows (courses) with erroneous dates • Two scripts were created to add 12 months to the starting and ending dates • The scripts do not modify records that have: • A term other than 201103 • Starting and ending dates >= January 1, 2011

  25. SSRMEET and SSBSECT Update Scripts update ssrmeet set ssrmeet_start_date = add_months(ssrmeet_start_date,12), ssrmeet_end_date = add_months(ssrmeet_end_date,12) where ssrmeet_term_code = '201103' and ssrmeet_start_date < '01-JAN-11' and ssrmeet_end_date < '01-JAN-11'; update ssbsect set ssbsect_ptrm_start_date = add_months(ssbsect_ptrm_start_date,12), ssbsect_ptrm_end_date = add_months(ssbsect_ptrm_end_date,12) where ssbsect_term_code = '201103' and ssbsect_ptrm_start_date < '01-JAN-11' and ssbsect_ptrm_end_date < '01-JAN-11';

  26. Post Update Issues • Received new request to create a room schedule conflict detector • At least 144 CRNs have some kind of conflicting room assignment for Winter 2011; there are 138 CRNs that have conflicts in Fall 2010 • Because of cross-listing, the same course may have different CRNs. • For example, this class has two CRNs: MATH485 (CRN: 4078) and CPTG485 (CRN: 4074), but meets in the same location and time PSC146, Mondays 11:00 – 11:50. The room schedule conflict detector would list 4078 and 4074 as conflicting classes

  27. Room Schedule Conflict Detector (201102: Fall 2010) column room head 'Room' for a8 column crn head 'CRN' for a5 column mon head 'M' for a1 column tue head 'T' for a1 column wed head 'W' for a1 column thu head 'R' for a1 column fri head 'F' for a1 column begtime head 'From' for a4 column endtime head '--To' for a4 column course head 'Course' for a9 SELECT a.ssrmeet_bldg_code || a.ssrmeet_room_code room, a.ssrmeet_crn crn, ssbsect_subj_code || ssbsect_crse_numb course, a.ssrmeet_mon_day mon, a.ssrmeet_tue_day tue, a.ssrmeet_wed_day wed, a.ssrmeet_thu_day thu, a.ssrmeet_fri_day fri, a.ssrmeet_begin_time begtime, a.ssrmeet_end_time endtime FROM ssrmeet a, ssbsect where a.ssrmeet_term_code = '201102' and ssbsect_term_code = '201102' and ssbsect_crn = a.ssrmeet_crn and a.ssrmeet_bldg_code || a.ssrmeet_room_code in (select b.ssrmeet_bldg_code || b.ssrmeet_room_code from ssrmeet b where b.ssrmeet_term_code = '201102' and b.ssrmeet_crn <> a.ssrmeet_crn and b.ssrmeet_bldg_code = a.ssrmeet_bldg_code and b.ssrmeet_room_code = a.ssrmeet_room_code and (b.ssrmeet_mon_day = a.ssrmeet_mon_day or b.ssrmeet_tue_day = a.ssrmeet_tue_day or b.ssrmeet_wed_day = a.ssrmeet_wed_day or b.ssrmeet_thu_day = a.ssrmeet_thu_day or b.ssrmeet_fri_day = a.ssrmeet_fri_day) and b.ssrmeet_begin_time = a.ssrmeet_begin_time and b.ssrmeet_end_time = a.ssrmeet_end_time) order by 1, 2;

  28. Conflict list for PSC146, Fall 2010 Room CRN Course M T W R F From --To -------- ----- --------- - - - - - ---- ---- PSC146 3851 MATH006 M T R F 0800 0850 PSC146 3852 MATH006 M T R F 0800 0850 PSC146 3853 MATH006 M T R F 0900 0950 PSC146 3854 MATH006 M T R F 0900 0950 PSC146 3855 MATH006 M T W R 1300 1350 PSC146 3856 MATH006 M T W R 1300 1350 PSC146 3857 MATH006 M T W R 1400 1450 PSC146 3858 MATH006 M T W R 1400 1450 PSC146 3859 MATH006 M T R F 1000 1050 PSC146 3860 MATH006 M T R F 1000 1050 PSC146 4074 CPTG485 M 1100 1150 PSC146 4078 MATH485 M 1100 1150 PSC146 4796 MATH006 M T R F 1200 1250 PSC146 4801 MATH006 M T R F 1200 1250

More Related