Primary Records:
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.
| Record Name | Description |
|---|
| 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
- The academic career, program reference: Career, Program, Plans and Sub-Plans for Baruch
|
| 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.
- The academic plan reference: Career, Program, Plans and Sub-Plans for Baruch
|
| ACAD_SUBPLAN | - This is the record 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.
|
| 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).
- The service indicator reference: Service Indicators
|
| 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, academic level (freshman, sophomore etc.) academic load (full time, part time), enroll flag
|
| 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'.
|
| STDNT_GRPS_HIST | This table contains a student's historical relationship to one or more student groups. The keys are EMPLID, INSTITUTION and EFFDT. - The student groups reference: Student Groups
|
Reference Records:
There are set up tables below which reference to student records or using for prompts purpose
| Records | Description |
|---|
| ACAD_CAR_TBL | - To Prompt for Career. Institution prompt required.
|
| 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_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.
|
| 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_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.
|
| 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.
|
| TERM_VAL_TBL | - This table contains all the valid terms in the CUNY system.
- Use it for editing term prompts.
|
The records below use are used for Student Admission, financial Financial Aid, Transfer credits, degree audit
...