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. An enrolled student means that the student is taking classes for a certain term. An active student means that the student is active in a program. An enrolled student is always active but an active student is not always enrolled.
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.)
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 tables.
Other important fields in this table are: ACAD_PROG (i.e., UGRD, MBA, etc.), ADMIT_TERM, DEGR_CHKOUT_STAT and PROG_STATUS. Another field of interest is the ADM_APPL_NBR. If this ACAD_PROG record was created through the application process (Admissions), 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.
B) 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. ACAD_PLAN is owned by ACAD_PROG, so the five keys of ACAD_PROG MUST equal the first five keys of ACAD_PLAN. The table layout is below.
Field Description
Field Description
EMPLID - Empl ID (key; should equal that of ACAD_PROG.)
ACAD_CAREER - Academic Career (key; should equal that of ACAD_PROG.)
INSTITUTION - Academic Institution (key; should equal that of ACAD_PROG.)
STRM - Term (key)
REG_CARD_DATE - Registration Card Date
WITHDRAW_CODE - Withdrawal \ Cancel
WITHDRAW_REASON - Withdrawal \ Cancel Reason
WITHDRAW_DATE - Withdrawal \ Cancel Date
LAST_DATE_ATTENDED - Last Date of Attendance
STDNT_CAR_NBR - Student Career Nbr (NOT a key; should equal that of ACAD_PROG.)
ACAD_PROG_PRIMARY - Primary Academic Program
ACAD_LOAD_APPR - Approved Academic Load
ACADEMIC_LOAD - Academic Load
FA_LOAD - Financial Aid Load
ACAD_LEVEL_PROJ - Academic Level - Projected
ACAD_LEVEL_BOT - Academic Level - Term Start
ACAD_LEVEL_EOT - Academic Level - Term End
NSLDS_LOAN_YEAR - NSLDS Loan Year
OVRD_ACAD_LVL_PROJ - Override Projected Level
OVRD_ACAD_LVL_ALL - Override All Academic Levels
ELIG_TO_ENROLL - Eligible to Enroll
OVRD_MAX_UNITS - Override Maximum Units
MAX_TOTAL_UNIT - Max Total Units
MAX_NOGPA_UNIT - Max No GPA Units
MAX_AUDIT_UNIT - Max Audit Units
MAX_WAIT_UNIT - Max Wait List Units
MIN_TOTAL_UNIT - Min Total Units
OVRD_BILL_UNITS - Override Billing Units
PROJ_BILL_UNT - Projected Bill Units
UNT_TAKEN_PRGRSS - Units Taken for Progress (if the student is enrolled in credit-bearing courses, this number will be greater than zero.)
UNT_PASSD_PRGRSS - Units Passed for Progress (*** any field starting with UNT is a term total.)
UNT_TAKEN_GPA - Units Taken Toward GPA
UNT_PASSD_GPA - Units Passed Toward GPA
UNT_TAKEN_NOGPA - Units Taken Not Toward GPA
UNT_PASSD_NOGPA - Units Passed Not Toward GPA
UNT_INPROG_GPA - Units In Progress - GPA
UNT_INPROG_NOGPA - Unit In Progress - Not for GPA
GRADE_POINTS - Grade Points
UNT_AUDIT - Units Audited
UNT_TRNSFR - Units Transferred
TRF_TAKEN_GPA - Transfer Taken for GPA
TRF_TAKEN_NOGPA - Transfer Taken Not for GPA
TRF_PASSED_GPA - Transfer Passed for GPA
TRF_PASSED_NOGPA - Transfer Passed Not for GPA
TRF_GRADE_POINTS - Transfer Grade Points
UNT_TEST_CREDIT - Units from Test Credit
UNT_OTHER - Units from Other Credit
UNT_TAKEN_FA - Fin Aid Progress Units Taken
UNT_PASSD_FA - Fin Aid Progress Units Passed
UNT_TAKEN_FA_GPA - FA Units Taken Toward GPA
GRADE_POINTS_FA - Financial Aid Grade Points
UNT_TERM_TOT - Total Term Units
RESET_CUM_STATS - Reset Cum Stats at Term Start
TOT_TAKEN_PRGRSS - Total Taken for Progress (*** any field starting with TOT is a career total.)
TOT_PASSD_PRGRSS - Total Passed for Progress
TOT_TAKEN_GPA - Total Taken Toward GPA
TOT_PASSD_GPA - Total Passed Toward GPA
TOT_TAKEN_NOGPA - Total Taken Not Toward GPA
TOT_PASSD_NOGPA - Total Passed Not Toward GPA
TOT_INPROG_GPA - Total In Progress - GPA
TOT_INPROG_NOGPA - Total In Progress -Not for GPA
TOT_AUDIT - Total Audited
TOT_TRNSFR - Total Transferred
TOT_TEST_CREDIT - Total From Test Credit
TOT_OTHER - Total from Other Credit
TOT_CUMULATIVE - Total Cumulative Units
TOT_GRADE_POINTS - Total Grade Points
TOT_TAKEN_FA - Total Fin Aid Units Taken
TOT_PASSD_FA - Total Fin Aid Units Passed
TOT_TAKEN_FA_GPA - Total Fin Aid Taken Toward GPA
TOT_GRD_POINTS_FA - Total Fin Aid Grade Points
FORM_OF_STUDY - Form of Study
TERM_TYPE - Term Unit Type
CLASS_RANK_NBR - Class Rank Nbr
CLASS_RANK_TOT - Class Rank Total
SEL_GROUP - Tuition Group
TUIT_CALC_REQ - Tuition Calc Required
TUIT_CALC_DTTM - Tuit Calc Date Time
FA_STATS_CALC_REQ - FA Stats Calculation Required
FA_STATS_CALC_DTTM - FA Stats Calc Date Time
FA_ELIGIBILITY - Program Eligibility Flag
BILLING_CAREER - Billing Career
UNIT_MULTIPLIER - Unit Multiplier
ACAD_YEAR - Academic Year
ACAD_GROUP_ADVIS - Academic Group of Advisor
CUR_RESIDENT_TERMS - Current In Residence Terms
TRF_RESIDENT_TERMS - Transfer In Residence Terms
CUM_RESIDENT_TERMS - Cumulative In Residence Terms
REFUND_PCT - Refund Percentage
REFUND_SCHEME - Refund Scheme
PRO_RATA_ELIGIBLE - Pro-Rata Eligible
FULLY_ENRL_DT - Fully Enrolled Date
ENRL_ON_TRANS_DT - Show Enrollment on Transcript
STATS_ON_TRANS_DT - Show Statistics on Transcript
FULLY_GRADED_DT - Fully Graded Date
EXT_ORG_ID - External Org ID
COUNTRY - Country
STUDY_AGREEMENT - Study Agreement
START_DATE - Start Date for Gen Standing PO
END_DATE - End Date
MAX_CRSE_COUNT - Max Total Courses
CUR_GPA - Current GPA (term GPA, after grades are posted.)
CUM_GPA - Cumulative GPA
REGISTERED - Registered
OVRD_TUIT_GROUP - Override Tuition Group
OVRD_WDRW_SCHED - Override Withdrawal Schedule
TUITION_RES_TERMS - Tuition Residency
OVRD_INIT_ADD_FEE - Override Initial Add Fees
OVRD_INIT_ENR_FEE - Override Initial Enroll Fee
TC_UNITS_ADJUST - TC Units Adjustment
LOCK_IN_AMT - Lock In Amount
LOCK_IN_DT - Lock In Date
ACAD_CAREER_FIRST - First Time in Career
ACADEMIC_LOAD_DT - Academic Load Change Date
UNTPRG_CHG_NSLC_DT - Unit Progrss Changed Date NSC
SSR_ACTIVATION_DT - Term Activation Date
SSR_TRF_CUR_GPA - Transfer Credit Current GPA
SSR_COMB_CUR_GPA - Combined Current GPA
SSR_CUM_EN_GPA - Enrollment Cumulative GPA
SSR_TOT_EN_GRDPTS - Enrollment Grade Points
SSR_TOT_EN_TKNGPA - Units Taken Toward GPA
SSR_CUM_TR_GPA - Transfer Credit Cumulative GPA
SSR_TOT_TR_GRDPTS - Transfer Credit Grade Points
SSR_TOT_TR_TKNGPA - Units Taken Toward GPA
D) STDNT_ENRL is created when a student enrolls in a class or is waitlisted for that class. Even if the student subsequently drops the class, the record remains, but the status changes. STDNT_ENRL is owned by STDNT_CAR_TERM. The layout is below.
Field Description
EMPLID - Empl ID (key)
ACAD_CAREER - Academic Career (key)
INSTITUTION - Academic Institution (key)
STRM - Term (key)
CLASS_NBR - Class Nbr (key)
CRSE_CAREER - Course Career
SESSION_CODE - Session
SESSN_ENRL_CNTL - Enrollment Control Session
STDNT_ENRL_STATUS - Student Enrollment Status
ENRL_STATUS_REASON - Enrollment Status Reason ('E' for enrolled, 'W' for waitlisted, 'D' for dropped.)
ENRL_ACTION_LAST - Last Enrollment Action
ENRL_ACTN_RSN_LAST - Last Enrl Action Reason
ENRL_ACTN_PRC_LAST - Last Enrollment Action Process
STATUS_DT - Status Date
ENRL_ADD_DT - Enrollment Add Date
ENRL_DROP_DT - Enrollment Drop Date
UNT_TAKEN - Units Taken
UNT_PRGRSS - Units Taken-Academic Progress
UNT_PRGRSS_FA - Units Taken-Fin Aid Progress
UNT_BILLING - Billing Units
CRSE_COUNT - Course Count
GRADING_BASIS_ENRL - Grading Basis
GRADING_BASIS_DT - Grading Basis Date
OVRD_GRADING_BASIS - Override Grading Basis
CRSE_GRADE_OFF - Official Grade
CRSE_GRADE_INPUT - Grade Input
GRADE_DT - Grade Date
REPEAT_CODE - Repeat Code
REPEAT_DT - Repeat Date
CLASS_PRMSN_NBR - Class Permission Nbr
ASSOCIATED_CLASS - Associated Class
STDNT_POSITIN - Student Position
AUDIT_GRADE_BASIS - Audit Grading Basis
EARN_CREDIT - Earn Credit
INCLUDE_IN_GPA - Include in GPA
UNITS_ATTEMPTED - Units Attempted
GRADE_POINTS - Grade Points
GRADE_POINTS_FA - Financial Aid Grade Points
GRD_PTS_PER_UNIT - Grade Points Per Unit
MANDATORY_GRD_BAS - Mandatory Grading Basis
RSRV_CAP_NBR - Reserve Capacity Sequence
RQMNT_DESIGNTN - Requirement Designation
RQMNT_DESIGNTN_OPT - Requirement Designation Option
RQMNT_DESIGNTN_GRD - Requirement Designation Grade
INSTRUCTOR_ID - Instructor ID
DROP_CLASS_IF_ENRL - Drop This Class if Enrolled
ASSOCIATION_99 - Association 99
OPRID - User ID
TSCRPT_NOTE_ID - Transcript Note ID
TSCRPT_NOTE_EXISTS - Transcript Note Exists Flag
NOTIFY_STDNT_CHNG - Notify Student of Change
REPEAT_CANDIDATE - Repeat Candidate Flag
VALID_ATTEMPT - Valid Attempted Grade
GRADE_CATEGORY - Grade Category
SEL_GROUP - Tuition Group
DYN_CLASS_NBR - Dynamic Class Nbr
UNT_EARNED - Units Earned
LAST_UPD_DT_STMP - Last Update Date Stamp
LAST_UPD_TM_STMP - Last Update Time Stamp
LAST_ENRL_DT_STMP - Last Enrollment Date Stamp
LAST_ENRL_TM_STMP - Last Enrollment Time Stamp
LAST_DROP_DT_STMP - Last Drop Date Stamp
LAST_DROP_TM_STMP - Last Drop Time Stamp
ENRL_REQ_SOURCE - Enrollment Request Source
LAST_UPD_ENREQ_SRC - Enrollment Request Source
GRADING_SCHEME_ENR - Field is not active yet
RELATE_CLASS_NBR_1 - Field is not active yet
RELATE_CLASS_NBR_2 - Field is not active yet
ACAD_PROG - Academic Program
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.
- 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.
- Other related tables which contain important information are:
- ACAD_PROG_TBL – details about the program.
- ACAD_PLAN_TBL – details about the plan.