PowerSchool Extract Tool: Changes needed to exclude sites from extract.

Updating the PowerSchool extract to exclude sites (schools) requires changes to the scripts listed below.
- ie_export_script.sql
- enrollment_students.sql
- enrollment_reenrollments.sql
- enrollment_cc.sql
- mastschd.sql
- roster.sql
- studemo.sql

The scripts are located in the c:\ps_illuminateed_exporter folder on the computer running the extract process.

Using a text editor like Notepad++ or an editor that displays the current line number would be helpful when editing the scripts.

Before starting, make a backup copy of the file listed below.

- ie_export_script.sql
- enrollment_students.sql
- enrollment_reenrollments.sql
- enrollment_cc.sql
- mastschd.sql
- roster.sql
- studemo.sql

The updates needed are shown in BOLD below.

1. Edit the file ie_export_script.sql

Insert 2 new lines before the line that reads 'VARIABLE TAB CHAR;' which is typically line 19.

In these new lines, paste the following:
# NOTE: Change 123, 456, 789 below to the sites to be INCLUDED in the nightly extract
DEFINE IE_SITE_ID = '123, 456, 789'

Change the 123, 456, 789 and so on to the sites that you want INCLUDED in the nightly extract. Do not include sites that are not to be extracted.

The updated lines should look similar to what is shown below.
DEFINE IE_GRADE_LEVEL_ID = 'WHEN -2 THEN 15 WHEN -1 THEN 15 WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 4 WHEN 4 THEN 5 WHEN 5 THEN 6 WHEN 6 THEN 7 WHEN 7 THEN 8 WHEN 8 THEN 9 WHEN 9 THEN 10 WHEN 10 THEN 11 WHEN 11 THEN 12 WHEN 12 THEN 13 WHEN 99 THEN 14'

# NOTE: Change 123, 456, 789 below to the sites to be INCLUDED in the nightly extract
DEFINE IE_SITE_ID = '123, 456, 789'

VARIABLE TAB CHAR;
EXEC :TAB := CHR(9);

@@attendance.sql

Save the file.

2. Edit the file studemo.sql

Update the WHERE clause near the end of the file (typically around line 153) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Students table. Table alias name is 's'. Custom fields can be pulled using: ps_customfields.getStudentsCF(s.id, 'FIELDNAME')
--END additional data columns
FROM
(SELECT * FROM ps.students) s
WHERE s.entrydate >= '&IE_FIRST_DAY' AND s.exitdate >= sysdate AND s.schoolid IN (&IE_SITE_ID)
/
SPOOL OFF

Save the file.

3. Edit the file enrollment_cc.sql

Update the WHERE clause near the end of the file (typically around line 60) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Students table(s) Custom student fields: ps_customfields.getStudentsCF(s.id, 'FIELDNAME')
--CC table(cc).
--END additional data columns
FROM
(SELECT * FROM ps.students) s
JOIN ps.cc ON cc.studentid = s.id
JOIN schools sc ON sc.school_number = cc.schoolid
JOIN ps.terms trm ON trm.id = ABS(cc.termid) AND trm.schoolid = cc.schoolid
WHERE
((trm.YearID = &IE_YEAR_ID) AND (s.schoolid <> cc.schoolid) AND (cc.DATELEFT > s.ENTRYDATE) AND cc.schoolid IN (&IE_SITE_ID))
/
SPOOL OFF

Save the file.

4. Edit the file enrollment_reenrollments.sql

Update the WHERE clause near the end of the file (typically around line 59) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Students table. Table alias name is 's'. Custom fields can be pulled using: ps_customfields.getStudentsCF(s.id, 'FIELDNAME')
--Enrollment All view. View alias name is 'e'
--END additional data columns
FROM
(SELECT * FROM ps.students) s
JOIN ps.reenrollments e ON e.studentid = s.id
JOIN schools sc ON sc.school_number = e.schoolid
WHERE e.entrydate Between '&IE_FIRST_DAY' AND '&IE_LAST_DAY' AND e.schoolid IN (&IE_SITE_ID)
/
SPOOL OFF

Save the file.

5. Edit the file enrollment_students.sql

Update the WHERE clause near the end of the file (typically around line 58) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Students table. Table alias name is 's'. Custom fields can be pulled using: ps_customfields.getStudentsCF(s.id, 'FIELDNAME')
--Enrollment All view. View alias name is 'e'
--END additional data columns
FROM
(SELECT * FROM ps.students) s
JOIN schools sc ON sc.school_number = s.schoolid
WHERE s.entrydate Between '&IE_FIRST_DAY' AND '&IE_LAST_DAY' AND s.schoolid IN (&IE_SITE_ID)
/
SPOOL OFF

Save the file.

5. Edit the file mastschd.sql

Update the WHERE clause near the end of the file (typically around line 61) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Terms table. Table alias name is 'trm'.
--Section table. Table alias name is 's'.
--Teachers table. able alias name is 't'. Custom section fields: ps_customfields.getTeacherCF(t.id, 'FIELDNAME')
FROM (SELECT * FROM ps.terms) trm
INNER JOIN sections s ON (trm.ID = s.TERMID and trm.schoolid = s.schoolid)
INNER JOIN sectionteacher st ON (s.id = st.sectionid)
INNER JOIN ps.teachers t ON t.id = st.teacherid
JOIN schools sc ON sc.school_number = s.schoolid
WHERE trm.yearid = &IE_YEAR_ID AND s.schoolid IN (&IE_SITE_ID)
/
SPOOL OFF

Save the file.

6. Edit the file roster.sql

Update the WHERE clause near the end of the file (typically around line 60) to reference the IE_SITE_ID variable created in step 1.

--BEGIN additional data columns
--Acceptable fields:
--Students table(s) Custom student fields: ps_customfields.getStudentsCF(s.id, 'FIELDNAME')
--CC table(cc).
--END additional data columns
FROM
(SELECT * FROM ps.students) s
JOIN ps.cc ON cc.studentid = s.id
JOIN ps.courses c ON c.course_number = cc.course_number
JOIN ps.teachers t ON t.id = cc.teacherid
JOIN schools sc ON sc.school_number = cc.schoolid
JOIN ps.terms trm ON trm.id = ABS(cc.termid) AND trm.schoolid = cc.schoolid
WHERE
trm.YearID = &IE_YEAR_ID AND cc.schoolid IN (&IE_SITE_ID)
/
SPOOL OFF

Save the file.

These updates should then only extract the sites (schools) referenced by the IE_SITE_ID variable.