In order to be a student in CUNYfirst, certain records must exist. These records are ACAD_PROG, ACAD_PLAN, STDNT_CAR_TERM and STDNT_ENRL.
Every student must have a program record, ACAD_PROG, the layout of which appears below.
A) ACAD_PROG
Field Description
EMPLID - Empl ID (key)
ACAD_CAREER - Academic Career (key)
STDNT_CAR_NBR - Student Career Nbr (key)
EFFDT - Effective Date (key; the current record is the maximum effective date, less than or equal to today, for the EMPLID, ACAD_CAREER and STDNT_CAR_NBR)
EFFSEQ - Effective Sequence (key; this field allows multiple transactions for the EFFDT. The current record is the maximum effective sequence for the EFFDT.)
INSTITUTION - Academic Institution (this is not a key but should be considered one because there are multiple institutions in CUNYfirst. Always delimit your selection by INSTITUTION.)
ACAD_PROG - Academic Program (the student's program - MPA, MA, MBA, UGRD, etc.)
PROG_STATUS - Academic Program Status ('AC' - active – a student MUST be active to be term activated; 'CM' - completed – the student has graduated; 'CN' - cancelled; 'DC' - discontinued; 'DM' - dismissed. This field is derived from PROG_ACTION.)
PROG_ACTION - Program Action (what is happening, such as MATR - matriculation, DATA - data change, DISC - discontinuation, etc.)
ACTION_DT - Action Date (the date of the action, always defaults to today's date)
PROG_REASON - Action Reason (why the action is taking place)
ADMIT_TERM - Admit Term (the term when the student started)
EXP_GRAD_TERM - Expected Graduation Term (the term when the student should graduate)
REQ_TERM - Requirement Term (the term which governs the rules for graduation.)
ACAD_LOAD_APPR - Approved Academic Load
CAMPUS - Campus
DEGR_CHKOUT_STAT - Degree Checkout Status (if a student has applied for graduation, or has graduated; 'AG' - applied, 'AW' - awarded, etc.)
COMPLETION_TERM - Completion Term (the term in which the student graduated)
ACAD_PROG_DUAL - Dual Academic Program
JOINT_PROG_APPR - Joint Program Approved
ADM_APPL_NBR - Application Nbr (if this student came through the Admissions process, this number can be used to retrieve the application.)
APPL_PROG_NBR - Application Program Nbr
DATA_FROM_ADM_APPL - Data from Admissions Appl
SSR_RS_CANDIT_NBR - Candidate Number
SSR_APT_INSTANCE - APT Instance
SSR_YR_OF_PROG - Year of Program
SSR_SHIFT - Academic Shift
SSR_COHORT_ID - Cohort Tag
SCC_ROW_ADD_OPRID - Created By (the user who added the record.)
SCC_ROW_ADD_DTTM - Created (when the record was added.)
SCC_ROW_UPD_OPRID - Updated By (the user who change the record last.)
SCC_ROW_UPD_DTTM - Last Update Date/Time (when the record was changed last.)
and one plan – a student may have multiple plans but one is required. A student must be active in a program in order to be term activated. That process creates the STDNT_CAR_TERM record and a student will have one of these records for each term he/she is term activated. Finally, for each term and each class in which a student is enrolled, a STDNT_ENRL record is created.
Key Description
A) Working backwards, the STDNT_ENRL contains the following keys:
- EMPLID
- ACAD_CAREER
- INSTITUTION
- STRM
- CLASS_NBR
- T
For each class, a student will have one of these records. This does not mean that the student is enrolled. For that, the field STDNT_ENRL_STATUS must be checked. If a student is enrolled, the status will be 'E'; if a student is waitlisted for a class, the status will be 'W'; if the student was enrolled and subsequently dropped the class, the status would be 'D'. Therefore, you must check the STDNT_ENRL_STATUS for 'E' to determine in which classes a student is enrolled or even if this is an enrolled student for the term.
STDNT_ENRL does not contain much class information, such as subject, catalog number or description. For this detail, you must join STDNT_ENRL to CLASS_TBL, which contains class information. Or, instead of using STDNT_ENRL, use the view CLASS_TBL_SE_VW, which joins STDNT_ENRL to CLASS_TBL for you.
B) Term activation creates STDNT_CAR_TERM. A student MUST be term activated in order to enroll in class. There is a one-to-many relationship between STDNT_CAR_TERM and STDNT_ENRL; for each term a student may have one or more classes. STDNT_CAR_TERM contains the following keys:
- EMPLID
- ACAD_CAREER
- INSTITUTION
- STRM
Another field which should be considered as a key is STDNT_CAR_NBR, which is a key in ACAD_PROG. In our environment, a student may have multiple careers at the same institution, so this field is important.
STDNT_CAR_TERM keeps a running total of term and career information: term and career GPAs; term and career credits taken and passed for GPA, non-GPA, financial aid; academic level, etc. One important field is UNT_TAKEN_PRGRSS. A positive value in this field means that the student is enrolled. If the student is enrolled (STDNT_ENRL) in a three-credit class, UNT_TAKEN_PRGRSS will be three. If the student is enrolled (STDNT_ENRL) in four three-credit class, UNT_TAKEN_PRGRSS will be twelve. Of course, this is not always a reliable test of enrollment. A student could be enrolled in non-credit classes, and therefore be considered an enrolled student but UNT_TAKEN_PRGRSS would be zero.
C) ACAD_PLAN contains the student's plan, which is the major or minor. A student MUST have one plan but there could be more than one. The keys to this table are;
- EMPLID
- ACAD_CAREER
- STDNT_CAR_NBR
- EFFDT
- EFFSEQ
- ACAD_PLAN
The first five keys are the same as ACAD_PROG, which owns ACAD_PLAN, and these values must be the same as the owning ACAD_PROG. There is a one-to-many relationship between ACAD_PROG and ACAD_PLAN – for each program, a student must have one or more plans. The field, ACAD_PLAN, indicates the major or minor to which the student is attached.
D) Every student must have an ACAD_PROG record. The keys to this table are;
- EMPLID
- ACAD_CAREER
- STDNT_CAR_NBR
- EFFDT
- EFFSEQ
The EFFDT allows history for the student to be stored and EFFSEQ allows multiple transactions for the same EFFDT. These are reserve words in PeopleSoft and have special functions. Query recognizes this special-ness and creates code accordingly. STDNT_CAR_NBR is important in the CUNYfirst environment because a student may have multiple careers. Another important field, but not a key, is INSTITUTION. Because CUNfirst has many institutions, this field MUST be considered in order to narrow down the selection and to join to other table.
Other important fields in this table are: ACAD_PROG (i.e., UGRD, MBA, etc.), ADMIT_TERM, DEGR_CHKOUT_STAT and PROG_STATUS. PROG_STATUS could be . Another field of interest is the ADM_APPL_NBR. If this ACAD_PROG record was created through the application process, this field will have a value. It allows the user to return to the application to find data captured there, such as the ADMIT_TYPE.
- Other related tables which contain important information are:
- ACAD_PROG_TBL – details about the program.
- ACAD_PLAN_TBL – details about the plan.