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.)
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
ACAD_CAREER - Academic Career
INSTITUTION - Academic Institution
STRM - Term
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
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
UNT_PASSD_PRGRSS - Units Passed for Progress
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
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
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
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.
- 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.