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 one or more 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.
ACAD_PROG
Every student must have a program record, ACAD_PROG, the layout of which appears below.
About Effective Date - 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 CUNYfirst 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 record are: ACAD_PROG (i.e., UGRD, MBA, etc.), ADMIT_TERM, DEGR_CHKOUT_STAT and PROG_STATUS.
ACAD_PROG Record/Table
| Field Name | Description/Notes |
|---|---|
| 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.) |
ACAD_PLAN
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.
A student may have multiple plans but one is required.
ACAD_PLAN Record/Table
| Field Name | Description/Notes |
|---|---|
| EMPLID | Empl ID (key; must be the same as the owning ACAD_PROG.) |
| ACAD_CAREER | Academic Career (key; must be the same as the owning ACAD_PROG.) |
| STDNT_CAR_NBR | Student Career Nbr (key; must be the same as the owning ACAD_PROG.) |
| EFFDT | Effective Date (key; must be the same as the owning ACAD_PROG.) |
| EFFSEQ | Effective Sequence (key; must be the same as the owning ACAD_PROG.) |
| ACAD_PLAN | Academic Plan (the student's major or minor.) |
| DECLARE_DT | Declare Date |
| PLAN_SEQUENCE | Plan Sequence |
| REQ_TERM | Requirement Term |
| COMPLETION_TERM | Completion Term |
| STDNT_DEGR | Student Degree Nbr |
| DEGR_CHKOUT_STAT | Degree Checkout Status |
| ADVIS_STATUS | Advisement Status |
| SSR_APT_INSTANCE | APT Instance |
| SSR_YR_OF_PROG | Year of Program |
| SCC_ROW_ADD_OPRID | Created By |
| SCC_ROW_ADD_DTTM | Created |
| SCC_ROW_UPD_OPRID | Updated By |
| SCC_ROW_UPD_DTTM | Last Update Date/Time |
STDNT_CAR_TERM
STDNT_CAR_TERM is created when the student is term activated. A student MUST be term activated in order to enroll in class and that student MUST be active in a program to be term activated.
STDNT_CAR_TERM contains a running total of the student's activity at the institution. The layout is below.
Basically, ACAD_PROG owns STDNT_CAR_TERM and that should be considered in any query writing.
STDNT_CAR_TERM Record/Table
| Field Name | Description/Notes |
|---|---|
| EMPLID | Empl ID (key; must equal that of ACAD_PROG.) |
| ACAD_CAREER | Academic Career (key; must equal that of ACAD_PROG.) |
| INSTITUTION | Academic Institution (key; must 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; must 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 |
STDNT_ENRL
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.
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 Record/Table
| Field Name | Description/Notes |
|---|---|
| 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 |
Other important Student related tables
The following tables include important student information are:
- ACAD_PROG_TBL – details about the program.
- ACAD_PLAN_TBL – details about the plan.
- ACAD_SUBPLAN - the subplan table, which gives the student's concentration. ACAD_SUBPLAN is owned by ACAD_PLAN. The six keys of ACAD_PLAN are the same as the first six of ACAD_SUBPLAN. Subplans are optional, so you need to join ACAD_SUBPLAN to ACAD_PLAN with an outer join.