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.
Table of Contents maxLevel 3
...
ACAD_PROG
Every student must have a program record, ACAD_PROG, the layout of which appears below.
A) ACAD_PROG
Field Description
EMPLID - Empl ID
ACAD_CAREER - Academic Career
STDNT_CAR_NBR - Student Career Nbr
EFFDT - Effective Date
EFFSEQ - Effective Sequence
INSTITUTION - Academic Institution
ACAD_PROG - Academic Program
PROG_STATUS - Academic Program Status
PROG_ACTION - Program Action
ACTION_DT - Action Date
PROG_REASON - Action Reason
ADMIT_TERM - Admit Term
EXP_GRAD_TERM - Expected Graduation Term
REQ_TERM - Requirement Term
ACAD_LOAD_APPR - Approved Academic Load
CAMPUS - Campus
DEGR_CHKOUT_STAT - Degree Checkout Status
COMPLETION_TERM - Completion Term
ACAD_PROG_DUAL - Dual Academic Program
JOINT_PROG_APPR - Joint Program Approved
ADM_APPL_NBR - Application Nbr
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
SCC_ROW_ADD_DTTM - Created
SCC_ROW_UPD_OPRID - Updated By
SCC_ROW_UPD_DTTM - Last Update Date/Time
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:
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.
Info |
---|
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 an user to return to the application to find data captured there, such as the ADMIT_TYPE. |
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.
Info |
---|
STDNT_ENRL |
...
is missing some class information, such as subject, catalog number |
...
and description. For this detail, you must join STDNT_ENRL to CLASS_TBL, which contains that 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 'AC' (active – a student MUST be active to be term activated), 'CM' (completed – the student has graduated), 'CN' (cancelled), 'DC' (discontinued) or 'DM' (dismissed). 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.
...
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.