Queries whose name begins with jgeol. Primary contact is Sandra Delawder delawdsa@jmu.edu Section last updated 3/13/2012.
Jgeol001 (TS): Course anal for geology. Returns data of whether or not a Geology major has taken a series of 27 different courses.
Graduate School
Queries whose name begins with jgs. Primary contact is Lynette Michael michaeld@jmu.eduand Liz Rose rose2ea@jmu.edu both of which have create query access and make changes to queries in this series. Also, some of the jgs queries may have originated as jad queries since the graduate school follows a similar data path as undergraduate admissions. Section last updated 12/1/2015
Jgs0004 (TS): GR/DR data for merged ltrs. Used daily to select data to merge with MS Word documents for those applying to the graduate school. Prompts on effective date and up to four different program actions.
Jgs0004a (TS): Used to return declining graduate school applications for a prompted for effective date.
Jgs0005 (TS): GR/DR used to place holds. Returns conditional admits with a program reason=TRAN who also have an active undergraduate career.
Jgs0006 (TS): GR/DR graduation data. Used to extract data from SA system and import in to a MS Access database which includes additional fields not found in SA for tracking graduation data about a student. The MS Access database has macros to import/update the fields and can break once in a while.
Jgs0007 (TS): GR427 apps by term. Returns graduate students who applied to the GR427 program for a given term.
Jgs0007a (TS): cloned from jgs0007 for Terri Roberts in the graduate OT program. All she ever needs are students active in this program, and wanted their GRE test scores.
Jgs0009 (TS): Discontinued by date for GR/DR. cloned from jhs_5006_discountinued for Sherry Wills in GR school. Needed a list of GR/DR students who have discontinued in the GR/DR career by action date range.
Jgs0010 (TS): 60 day to dept. email. Used with a BI PUBLISHER report by the same name. Uses bursting to send email to program coordinators about their student. See RTF template doc with same name.
Jgs0011 (TS): Email merge missing checklists. Cloned from jus0053 which should no longer be used when SA 9.0 goes live. Used as the data source for three different BI publisher docs: jgs0011, jgs0011a, jgs0011b. As of 3/14/2012, the BI publisher bursting cannot be used due to the fact the student population returned by the query will not have a JMU email address because they are not yet active in their academic program. The bursting needs the email address on the psrolexlatopr record to send email, no email, no bursting. Using a crystal report in the interim.
Jgs0012 (TS): Admissions approval form. Cloned from jgsadmissions_approval. Used this as a more standard naming convention as part of replacing crystal reports (and their underlying queries) with BI PUBLISHER reports during SA 9 upgrade. See also jgs0012a for test data and connected query named jgs0012 and rtf doc with same name. The BI publisher report with bursting IS in production for this query/process. The jgs0012 BI publisher report uses a connected query as its data source. That connected query is built over jgs0012 and jgs0012a which returns test scores.
Jgs0013 (TS): Applicant check list. replaces the two queries named jgsjoemanning and jgsapplicant_checklist and the crystal report named jgsapplicantstatus and checklist.rpt. There is also an BI publisher report with the same name which does not use bursting. Output is hard copy and placed in student file which constitutes their “application checklist”.
Jgs0014 (TS): GR/DR term activation data. cloned from jgs0004 for Lynette Michael. Needed a list of GR/DR students getting close to graduation so they can email the program coordinators to see if the student is going to graduate. This so the student is not incorrectly term activated under the wrong career_number the next term when term activation is run.
Jgs0015 (TS): Ready for review. cloned from jgs0010This query feeds an BI publisher report by the same name. Uses BI publisher bursting to send email to program coordinators that their students have completed the application process and are ready to review.
Jgs0016 (TS): GR/DR chklist cleanup. Created for Lynette Michael for her to be able to clean up inactive checklists.
Jgs0017 (TS): gr/dr queries with OTHR email. cloned from jgs001 for Lynette Bible. Needed a query to return all jgs queries using the othr email addresses
Jgs0018 (TS): GR/DR with no HOME email. cloned from jgs0001 for Lynette Bible. Needed a list of active gr/dr applicants who have a OTHR email address but no HOME email address. See also issue and op ITI06398
Jgs0019 (TS) Accepted email to coordinators. Query used by an BI PUBLISHER report by the same name. Built for Lynette Michael and scheduled to recur daily @ 4 AM via a scheduled BI PUBLISHER report. No prompts as there is hard coded reference to the program action date = system date -1 to return yesterdays GR/DR students who have accepted or declined admissions to the graduate school. Bursting active used to email the “recruiter” that a student assigned to them has accepted or declined our offer of admissions. Translation values on the recruiter eid provided by Lynette as some recruiters wanted this correspondence sent to their administrative assistant vs. directly to them. This BIP report is scheduled by Tracie Esmaili using a recurrence definition name
Jgs0020 (TS): GR/DR student responses. Prompts for an admit term. Returns the responses provided by GR/DR students regarding why the declined our offer of admissions. Grad schools runs the query periodically and emails data to the appropriate graduate director of the results. First use of the stdnt_response record.
Jgs0021 (TS): GL Transactions For Grad Schl. Includes no prompts. Cloned from jgs0019 per specs from Lynette Michael. The item types and logic came from Brandon Cline who says this is how you can tell if someone has paid the deposit the grad school is looking for. Needs to be a scheduled BI PUBLISHER report with bursting by the same name because grad school does not have access to the records where deposits are tracked. Scheduled report recurs daily after midnight selecting data with the accounting date=sysdate per Brandon Cline.
Jgs0023 (TS): GR/DR applied to graduate. We need a query that we can run on a regular basis to give us the names and local addresses of graduate students who have applied to graduate. You could pull the information from Checklist Management for those students with a status of Completed for Item GRAD05. The query would need to provide us with an option to enter a date range for the students for whom that checklist item was Completed from xx to xx.
Jgs0024 (NTS): GR/DR <> DENY. Returns GR/DR who took TOEFL or IELTS and program action does not equal deny.
Jgs0025 (TS): All GR/DR students. Prompts for a term, return all students active in their respective academic program whose career is either GR or DR.
Jgs0026 (TS): GR/DR bad grades by date. Includes no prompts as there is a hard coded date which must be changed manually each term. Query was created for a very specific purpose. The registrars office changes grades all the time, sometimes for previous terms. The grad school has an existing query which they run once a term after grades are posted which returns the three populations of GR/DR students who have received a grade of U or F OR who have received a total of three C grades in their career. This query was designed to be scheduled and run daily to catch changes made to grades AFTER the grading period is over, hence the reason for the hard coded date based upon the grade date on the enrollment records for the student. Grad school needs to know about these students as it violates their program policy, and the registrar cannot always remember to call the grad school to let them know a recent grade change may affect the given student.
Jgs0027 (TS) GR429/555 email to Directors. Query used by a BI PUBLISHER report by the same name. Built for Lynette Michael and scheduled to recur daily @ 4 AM via a scheduled BI PUBLISHER report. No prompts as there is hard coded reference to the program action date = system date -1 to return yesterdays GR/DR students who have paid their deposit for these two programs to the graduate school. Bursting active used to email the “director” logic provided by Lynette Michael. This BIP report is scheduled by Tracie Esmaili using a recurrence definition named jgs0027