These are some of the more important records/tables which you may or may not use in your queries. Please note that the spelling of the records (tables) needs to be checked.
- ACAD_PROG - This is the core of the student system. For someone to be a student, there must be an ACAD_PROG record (and at least one ACAD_PLAN record) present. The key structure of this record is EMPLID, ACAD_CAREER, STDNT_CAR_NBR, EFFDT and EFF_SEQ. Even though INSTITUTION is not a key, it must also be considered because the CUNY Installation has twenty four institutions and without that added delimiter, the query would return too many rows. This record contains useful fields such as ADMIT_TERM, ACAD_PROG, PROG_STATUS, DEGR_CHKOUT_STAT and COMPLETION_TERM. PROG_STATUS will can have all these values shown in the ACAD_PROG-Prog_Status_codes.JPG. DC - More than 3 semesters absent. Effective Date - Date in effect; Effective_Seq (EFFSEQ) - Multiple changes occurred on same day; ACTION_DT - Date assigned by system; Admit_Term - Start Date; Req_Term - Has ACAD_PLAN as parent table
- ACAD_PROG_TBL - This is the set-up table where all academic programs are defined and stored. All programs must be added to this set-up table before they can be assigned to a student. Some examples programs are NDEGU, UGRD and CBUIS on the undergraduate career level and MBA, MPA, MS, etc. on the graduate level. The keys to this table are INSTITUTION, ACAD_PROG & EFFDT.
- ACAD_PLAN - This is where the major or minor is stored for the student. A student must have at least one of these record and it is a child of ACAD_PROG. It shares the same keys (EMPLID, ACAD_CAREER, STDNT_CAR_NBR, EFFDT and EFF_SEQ) as ACAD_PROG and has one more, ACAD_PLAN. The five keys of ACAD_PLAN must equal the first five keys of ACAD_PLAN.
- ACAD_PLAN_TBL - This is the set-up table where the academic plans are defined and stored. All plans must be added to this set-up table before they can be assigned to a student. The keys to this table are INSTITUTION, ACAD_PLAN & EFFDT. This table contains such fields as DEGREE (BS, MBA, etc.), CIP_CODE, HEGIS_CODE and the SSR_NSC_CRD_LVL (degree level - Associate's, Bachelor's, etc.).
- ACAD_SUBPLAN - This is where a student's concentration is kept. A student may or may not have a subplan but it is a child of ACAD_PLAN and shares the six keys of ACAD_PLAN (EMPLID, ACAD_CAREER, STDNT_CAR_NBR, EFFDT, EFF_SEQ & ACAD_PLAN). The field, ACAD_SUBPLAN, is also a key in this table. Because ACAD_SUBPLAN is optional for the student, be sure to do an outer join with ACAD_PLAN.
- ACAD_SUBPLN_TBL - This is the set-up table where the academic subplans are defined and stored. All subplans must be added to this set-up table before they can be assigned to a student. The keys to this table are INSTITUTION, ACAD_PLAN, ACAD_SUBPLAN & EFFDT.
- ADM_APPL_DATA - This record contains basic data for an applicant in Student Admissions. These are applicants, not students, and this record contains the ADMIT_TYPE.
- ADM_APPL_PROG - This record is similar to ACAD_PROG but on the Student Admissions side. It is a child of ADM_APPL_DATA.
- ADM_APPL_PLAN - This record is similar to ACAD_PLAN but on the Student Admissions side. It is a child of ADM_APPL_PROG.
- ADM_APPL_SBPLAN - This record is similar to ACAD_SUBPLAN but on the Student Admissions side. It is a child of ADM_APPL_PLAN.
- STDNT_CAR_TERM - This record is created for every term that a student is term-activated. It is related to ACAD_PROG - a student must be 'active' in ACAD_PROG before that student can be term-activated. Its keys are EMPLID, INSTITUTION, ACAD_CAREER and STRM (term) but STDNT_CAR_NBR, although not a key, should match that of ACAD_PROG. This record contains term and career-to-date statistics such as GPAs, credits taken and earned, transfer credits, etc.
- STDNT_ENRL - This records contains the actual class enrollment for a student. It is directly related to STDNT_CAR_TERM - a student cannot enroll in a class until that student is term-activated. Make sure that the STDNT_ENRL_STATUS is 'E' for enrolled. Other possibilities are 'D' for dropped or 'W' for 'Waitlisted'.
- CLASS_TBL - This record contains information about a class, such as SUBJECT, CATALOG_NBR, SECTION, SESSION_CODE, etc. Join it with STDNT_ENRL for a more complete picture of the student's class schedule.
- CLASS_TBL_SE_VW - This is a view which joins STDNT_ENRL & CLASS_TBL. You can use it instead of STDNT_ENRL and CLASS_TBL. Make sure that the STDNT_ENRL_STATUS is 'E' for enrolled, just like STDNT_ENRL.
- CU_BIO_VW - This is a CUNY-created view which contains the student's primary name, the preferred phone (if it exists) and the preferred email (if it exists). The key is EMPLID.
- RESIDENCY_OFF - This record contains the official residency of a student. At least one of these records is required per Institution for the tuition calculation process to run correct. Also, the term (a key) should be no later than the ADMIT_TERM on the ACAD_PROG record for that Institution. There may be multiple records per EMPLID and INSTITUTION, so you should select the latest STRM not greater than the current term.
- SRVC_IND_DATA - This record contains the service indicators and reason codes which have been added to a student. The keys are EMPLID and SRVC_IND_DTTM but you need to consider INSTITUTION as a delimiter. This record is either there when added, or not there when it is released. There is no history kept in this table (see AUDIT_SRVC_IND).
- SRVC_IN_CD_TBL - This is the set-up table where the service indicators are defined and stored. All service indicators must be added to this set-up table before they can be assigned to a student. The keys to this table are INSTITUTION, SRVC_IND_CD & EFFDT.
- SRVC_IND_RSN_TBL - This is the set-up table where the service indicator reason code are defined and stored. All service indicators must be added to this set-up table before they can be assigned to a student. The keys to this table are INSTITUTION, SRVC_IND_CD & EFFDT.
- STDNT_GRPS_HIST - This table contains a student's historical relationship to one or more student groups. The keys are EMPLID, INSTITUTION and EFFDT.
- SFA_SAP_STDNT - This record is the result of the SAP (student academic progress) process that is run for financial aid. It is stored by EMPLID for each AID_YEAR but the process can be run multiple times in the year, so you must use the latest process date for the year.
- TERM_VAL_TBL - This table contains all the valid terms in the CUNY system. Use it for editing term prompts.
- TERM_TBL - This table contains the calendars for the INSTITUTION and ACAD_CAREER, including the TERM_BEGIN_DT and the TERM_END_DT.
- ACCOUNT_SF - This is a student account summary by term. The keys are EMPLID, BUS_UNIT and STRM. BUS_UNIT is the same as INSTITUTION, but used in the student's financial records.
- ITEM_SF - This is the student account detail (which rolls up into ACCOUNT_SF). The keys are EMPLID, BUS_UNIT and STRM.
- ITEM_TYPE_TBL - This is the set-up table where the item types are defined and stored. All item types must be added to this set-up table before they can be assigned to a student. The keys to this table are INSTITUTION, ITEM_TYPE & EFFDT.
- ANTICIPATED_AID - This is the table which contains anticipated aid for the students. Because this data can change during the term, it is necessary to grab the latest record for the EMPLID, ITEM_TYPE and TERM.
- ACAD_DEGR - Degree from any CUNY campus.
- STDNT_TEST_COMP - SAT, Reading etc. test.
- ACAD_CAR_TBL - To Prompt for Career. Institution prompt required.
- STDNT_SRCH / PEOPLE_SEARCH - to check a person by Empl ID.
- TRNS_CRSE_DTL, TRNS_CRSE_SCH, TRNS_CRSE_TERM - Required for transfer credits.
Note:
- Optional Prompt Example: Opt_Promt_Example.JPG