Queries whose name begins with jcvpa. Primary contacts are Ruben Graciani graciarg@jmu.edu and Sheena Ramirez ramiresn@jmu.edu Section last updated 5/9/2018. In the Spring of 2017, CVPA added a new business process in which all of their undergraduate applications would require an additional application process. As a result, several records were added and this series of queries/reports were necessary. The list of records are below. See also the query series jtad and jmusic.
JAD_ SPECIALTY: Setup table (not managed by any pages) associating valid specialty codes and descriptions with the appropriate plans
JAD_APPL_DATA: Specialty data (up to 2 codes per plan) for a CVPA applicant. Keys are emplid, acad_career, stdnt_car_nbr, Adm_appl_nbr, appl_prog_nbr and acad_plan
JAD_PRSPCT_DATA: Specialty data (up to 2 codes per plan) for a CVPA prospect. Keys are emplid, acad_career, institution, acad_prog and acad_plan
JAD_CVPA_TRCKNG – Table: One row per CVPA acad plan per applicant – contains application status and decision data for CVPA applicants. The JMU CVPA Applicant Tracking page under Application Evaluation, used by CVPA staff, modifies the data in this table. The batch process for updating the portfolio/audition flags also inserts or updates rows in this table.
JAD_CVPA_PLANVW: primary purpose is to prevent users with access to this record from accessing all rows of data in the underlying adm_appl_plan record. Same for the records JAD_CVPA_PROGVW and JAD_CVPA_SBPLVW.
JAD_CVPATRK_SET – Table: Setup table (not managed by any pages) containing the acad_plan IDs that should be considered CVPA plans. Used to limit which plans are seen by the tracking pages. Also contains message catalog numbers that show which “letter” is displayed if an applicant is admitted to a specific school.
JAD_CVPAHDR_VW: Used to display scroll level 0 data on the CVPA Applicant Tracking page, regarding university admission status. Contains data from adm_appl_data and adm_appl_prog. jad
JAD_SS_CVPA_VW: One row per CVPA acad plan per applicant - Used on the self-service App Center page to display the CVPA tracking status of each applicant plan (if exists in the JAD_CVPATRK_SET setup table). Outer joins to the CVPA tracking table, so even if a tracking row doesn’t yet exist, a row should exist for every CVPA applicant in this view. Also shows most recent prog_status and prog_action from UG admissions.
JAD_CVPATK_SRCH: Search record for the CVPA Applicant Tracking component. One row per applicant per CVPA plan, but not as much data as in the other views.
jcvpa001 (NTS): is a private query used to keep expressions to render the specialties only. The design of jad_appl_data includes two specialty fields, so each has to be included to render/determine if a student is a specific specialty.
jcvpa002(TS): CVPA apps by term. Prompts for an admit term. Returns application, program, plan and specialty data for all plans associated with CVPA.
jcvpa002a (TS): Music apps by term. Similar to jcvpa002, hard coded reference that the degree be one of these values: ('BM','MM','DMA')
jcvpa002b (TS): Art apps by term. Similar to jcvpa002, hard coded plan numbers: ('0000006600','0000063150','0000005000','0000005180','0000005150','0000000700','0000001000','0000051200','0000051500','0000051800')
jcvpa002d (TS): CVPA summary by prog action. Prompts for an admit term. Returns summary data based upon each program action.
jcvpa003 (TS): CVPA summ app data specialty. Prompts for an admit term. Has summary expressions for each of the 37 specialties .
jcvpa004 (TS): CVPA apps wrong college. Prompts for an admit term. Should return zero rows. Includes a hard coded reference that the data returned be for the college (via the study_field on acad_plan_tbl) = CVPA.
jcvpa005 (TS): Admitted/accepted no AUDTN scr. Prompts for an admit term, returns those CVPA students who have been admitted or accepted but have not loaded an audition test score. The data returned by the query needs to be in a specific format to allow them to export this data to a CSV, then use the mass test scores loader to load the data returned by this query into sprd to the stdnt_test_comp table. The testid will always be AUDTN, the component will depend upon the acad_plan value in this query.
jcvpa006 (TS): Grace street CVPA. Prompts for a housing period. Returns those students active in their program whose major belongs to CVPA. Is used by the housing office, not the CVPA.
jcvpa007 (TS): Deposited or withdrawn student. IS meant to be run by Sheena as a recurring daily query. Contains hard coded references to the effective or action date from adm_appl_prog be yesterdays date and the program action be one of these values: ('MATR','WAPP','WADM').
Engineering
Queries whose name begins with jengr. Primary contact is Jamie Claytor claytojp@jmu.edu Section last updated 6/5/2014.
Jengr001 (TS): Coreq check for ENGR112. Returns students enrolled in ENGR112 who never passed nor transferred in PHYS240 nor MATH235.
Jengr002 (TS): Test scores for engineering. Returns engineering students and two test scores. JMU math placement and SAT1 math.
Jengr003 (TS). Test scores for engr courses. Union query; first select returns all courses an engineering major is enrolled in for a given term. Second select returns test scores for all AP tests.
Jengr004 (TS): Progression data for engr. Progression data for engineering students as defined by Tiffany Newbold. 5/17/2011, modified to not return original course now being repeated. Basically, returns any course an engineering major received a D/F in or if their cumulative GPA for the prompted term is LT 2.5
Jengr005 (TS): cloned from jusds024b for Tiffany Newbold in school of engineering. Wanted a version of all courses (at JMU or transferred) for JMU students and their grades.
Jengr006 (TS): Calculated GPA for engr. cloned from jengr004 for Tiffany Newbold. Needed a version of major GPA that used just engr subject courses, and did not want to include repeats (reason for the does not exist logic).
Jengr007 (TS): similar to jengr006, but contains hard coded course list whereas jengr006 uses all courses.
Jengr008 (NTS): Engr plan changes: summary. Cloned from jusds025d for Tiffany Newbold in engineering. Needed some other counter fields added and engineering has 2 plan numbers to be treated as one.
Jengr009 (NTS) Similar to jengr008, but this version returns student identifiable data vs. aggregate data returned by jengr008.
Jengr010 (NTS): Engr prog data test scores. Defines progression data. Need two queries. This version returns the "master" data such as test scores, race and gender. The A version returns course data.
Jengr010a (NTS): Returns all active engineering students and all courses they have taken in the STEM subjects or transfer courses.
Jengr011 (NTS): Bio summary for ENGR. Prompts for a term range. Returns one row of biographical and demographic aggregate data for engineering students active for the given FALL ONLY term(s).
Jengr012 (NTS): Retention data for ENGR. Prompts for two terms. Returns one row of data for the first term entered for two different populations as follows.
10-Freshman retained for term xxxx. This row of data is defined as the student being active in the engineering academic plan as of the census date for the first prompted term.
10-Freshman NOT retained for term xxxx. Is the inverse of a; these are students who were active as engineering students as of the census date of the first prompted term, but were NOT an active engineering student as of the census date of the second prompted for term. Refer to column d for an explanation of the various returned columns.
Similar rows are returned for the three remaining academic levels; sophomores, juniors and seniors.
Counts are provided for the number of students who meet specific criteria.
The total students, female and male columns are obvious.
Underprep contains the total number of students whose ethnicity is Black, Hispanic or American Indian.
The last three columns of data are really only meaningful for the row of data returned for NOT retained. The number in the active column is the total number of students who are active in their program as of the second prompted for term census date. Likewise for completed. The “other” column contains ALL program statuses other than active and complete, which include: Leave of Absence, Suspended, Discontinued, Dismissed and Deceased. Of special note again for the row of data which contains the word NOT. Pay special attention to the values in these three fields, as they indicate WHAT has happened to your students who you’ve NOT retained. The active column students are still @ JMU active in SOMEONE ELSES MAJOR, they left your major. The completed count can only include those who have completed their program of study, aka they graduated. And the other count was previously explained to mean all other program statuses.
