Load New Permission Numbers

Permission numbers are used extensively by CS advisors to help students get into courses where something (prerequisites, class size, etc) is blocking them. Ideally, OAT would be able to fetch a permission number directly from Quest via a web service (see Implement permission numbers in OAT3, created 9 years ago and still outstanding).

In the mean time, we ask the RO for a static list of permission numbers all with fixed expiry dates and a fixed set of permissions. Load them up into OAT where they get doled out by advisors.

This page is about getting and loading that list of permission numbers. The Lead CS Advisor typically emails a request in the middle of the term saying it’s time…

1. Request the permission numbers
   1. Log into the RO’s Student Systems Support ticketing system
   2. Click “Quest Support”
   3. Click “Records Systems Inquiries and Support”
   4. Fill out the form with something like “Permission numbers for Computer Science” as the summary and “We need to run permission numbers again for all CS courses for term XXXX to load into OAT.”
2. You should be emailed an Excel spreadsheet of permission numbers within a few days.
   This is typically done by Lorie Weinstein. Save it to a directory for the term. There may be further requests, so give it a _v1 suffix.
3. Clean up the Excel file. Open it and:
   1. Check if there is a column labeled Prmsn Seq between the section number and permission numbers. If so, remove it.
   2. Save it with format Comma Separated Values (.csv). Do NOT use CSV UTF-8 (Comma Delimited) (.csv) – Really!!!
4. Open the CSV in an editor and:
   1. Remove the first two lines.
   2. Remove the trailing commas from every line.
   3. Remove any blank lines (commas only) at the end of the file. Sometimes they exist; sometimes they don’t.
   4. Remove the space at the beginning of each course catalog number.
   5. Ensure that the last column is formatted YYYY-MM-DD. It usually is.
   6. The result of the previous operations should look like:

      1259,6691,CS,100,001,225163,2025-09-16
      1259,6691,CS,100,001,79499,2025-09-16
      1259,6691,CS,100,001,285741,2025-09-16
      1259,6691,CS,100,001,438700,2025-09-16
      1259,6691,CS,100,001,341668,2025-09-16
      1259,6691,CS,100,001,958581,2025-09-16

5. Load the permission numbers with the script, below. loadPermNums.sh oat-prod FILE.csv where oat-prod is your psql service for the production database. You may need a VPN if you’re off campus. It should tell you there are no missing permission numbers:

Loading permission numbers:
SET
COPY 4790
Missing permission numbers:
 term_id | subject_code | catalog | section_code 
---------+--------------+---------+--------------
(0 rows)

5. Let the advisors know they’ve been loaded.

Load Script

#!/bin/bash

# bwbecker	2/14/2011
#
#	Purpose:  Load permission numbers from a CSV file into the database.
#

if [ $# != 2 ]
then
	echo "Usage: $0 oat-prod permNumFile"
	exit -1
fi

echo "Loading permission numbers:"
psql service=$1 <<PSQL_Input
	set role to _oat_root;
	\copy _oat.std_permission_number \
	(term_id, class_id, subject_code, catalog, \
	section_code, permission_num, expiry_date) \
	from $2 (format csv)
PSQL_Input


echo "Missing permission numbers:"
psql service=$1 <<Missing
WITH offered_courses AS (
    SELECT DISTINCT term_id, subject_code, catalog, section_code
    FROM _quest.off_course_section_plus AS cs
             JOIN _quest.off_instruct_section USING (term_id, section_id)
    WHERE term_id >= _quest.uw_term_current() and term_id <= _quest.uw_term_next(_quest.uw_term_current()) AND subject_code = 'CS' AND cs.session_code <> 'PCS' AND
        is_status <> 'X' -- not cancelled
            AND component_code = 'LEC' AND catalog < '500'
)

SELECT term_id, subject_code, catalog, section_code
FROM offered_courses
         LEFT JOIN _oat.std_permission_number AS spn USING (term_id, subject_code, catalog, section_code)
WHERE spn IS NULL
ORDER BY term_id, subject_code, catalog, section_code
Missing