Student table

Each record describes a student and their information for each term. In order to be in this table, the student has to be registered for classes for the term on the record AND have shown up to class. The vStudent view in the Data Warehouse’s StudentNet database contains a subset of the fields from this table as well as a CALC_RACE_ETHN field which is an ethnicity code derived from Ethnicity, New Ethnicity, Visa Type, and Race information of the student. This field is a calculation provided by Tim Garner and Ryan Barry-Souza of the Research department (this also is included in the Applicant table).

Oracle source: ReportsNet.Student view

Oracle objects: SFBETRM, SPRADDR, SPRIDEN, SPRTELE, SPBPERS, STVTERM, SGBSTDN

Updated: Nightly

DW Field	Oracle Field/Calc	Description (Sample)
TERM	`sfbetrm.sfbetrm_term_code as term,`	https://sequoias.atlassian.net/browse/GOVPUB-10
SEMESTER	`stvterm.stvterm_desc as semester,`	Semester YYYY is the typical format for how this description field is populated. (Spring 2022)
STUDENTID	`spriden.spriden_id as studentid,`	Commonly Referred to as Banner ID. This is a unique ID per person/non-person that begins with the “@” symbol. This is PII, but widely used as the least bad option where COS Employees have “legitimate educational interest.” Error rendering macro 'excerpt-include' : No location was provided.
SSN	`spbpers.spbpers_ssn as ssn,`	Social Security numbers are issued by the Social Security Administration to U.S. citizens and other residents. SPBPERS_SSN This field is allowed to be null. This is PII: Error rendering macro 'excerpt-include' : No location was provided.
PIDM	`spriden.spriden_pidm as pidm,`	Personal Identification Master Key is a unique ID number per person/non-person in Banner. This is PII. Each person/entity should have only one PIDM. Duplicate PIDMs are resolved in Applications and documented in technical resources. Is not display on Banner forms and is not normally visible to users.
LASTNAME	`spriden_last_name as LastName,`	A person’s legal surname. Often populated off CCCApply Application or manual entry by Human Resources. Allowed null for both persons and non-persons.
FIRSTNAME	`spriden_first_name as FirstName,`	A person’s legal first name. Often populated off CCCApply Application or manual entry by Human Resources. Allowed null for both persons and non-persons.
MIDINIT	`spriden_mi as MidInit,`	A person’s middle name (not just their middle initial). Often populated off CCCApply Application or manual entry by Human Resources. Allowed null for both persons and non-persons.
STUDENTNAME	`nvl(spriden_last_name, '') \|\| ', ' \|\|` `nvl(spriden_first_name, '') \|\| ' ' \|\|` `nvl(spriden_mi, '') as studentname,`	Concatenation of `LASTNAME, FIRSTNAME MIDINIT`. (Bond, James Herbert)
ADDRESS	`spraddr.spraddr_street_line1 as address,`	Address at the start of the record’s term
CITY	`spraddr.spraddr_city as city,`	City at the start of the record’s term
STATE	`spraddr.spraddr_stat_code as state,`	State at the start of the record’s term
ZIP	`spraddr.spraddr_zip as zip,`	Zip at the start of the record’s term
PHONE	`decode(sprtele_phone_number, null, '', '(' \|\| sprtele_phone_area \|\| ') ' \|\| sprtele_phone_number) as phone,`	Current phone number. Concatenation of Area Code and Phone Number ((559) 8675309)
STATUS	`decode(f_sgbstdn_fields (spriden.spriden_pidm, sfbetrm.sfbetrm_term_code, 'STU_TYPE'), null, null, substr(f_get_desc_fnc('STVSTYP',f_sgbstdn_fields (spriden.spriden_pidm, sfbetrm.sfbetrm_term_code,'STU_TYPE'),30),1,30) ) as status,`	Description of Student Status. See the STVSTYP lookup table in the Appendix. This is the full description of the status. (Continuing Student, Returning Student) There is also the CAST code (shrttrm_cast_code) which is included at the end of the record.
PROGRAM	`sgbstdn.SGBSTDN_PROGRAM_1`	The program of the major
PROG_DEPT	`g.SGBSTDN_DEPT_CODE`	Department code for student’s program Has snapshot data on SGASTDN instead of the maintained data on SOACURR.
PROG_GE_PATTERN	`g.SGBSTDN_MAJR_CODE_CONC_1`	Student’s GE pattern for programs
IN_STATE_IND
MAJOR_CODE	`SGBSTDN.SGBSTDN_MAJR_CODE_1`	Numeric code for the student’s major
MAJOR	`substr(f_get_desc_fnc('STVMAJR',SGBSTDN.SGBSTDN_MAJR_CODE_1,30),1,30) as Major,`	Description of the `MAJOR_CODE`
GOAL	`substr(f_get_desc_fnc('STVEGOL',SGBSTDN_EGOL_CODE,30),1,30) as Goal,`	Student’s stated goal for applying. Happens during application process and overwhelmingly without counselor guidance.
GOAL_INFORMED_EFF_TERM	`substr(f_get_serdtgl_eff_term(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,6)`	The term at which the Informed Goal codes became effective.
FIN_AID_IND_BY_MJR	`substr( baninst1.cos_general_restr.f_aid_ind_from_stvmajor_code( sgbstdn.sgbstdn_majr_code_1, f_get_serdtgl_eff_term(spriden.spriden_pidm, sfbetrm.sfbetrm_term_code)), 1, 1)`	This is a Financial Aid indicator based on the major code. ('Y', 'N')
CTE_IND_BY_MJR	`SUBSTR( baninst1.cos_general_restr.f_cte_ind_from_stvmajor_code( sgbstdn.sgbstdn_majr_code_1 ,f_get_serdtgl_eff_term(spriden.spriden_pidm, sfbetrm.sfbetrm_term_code)) ,1 ,1)`	This is a CTE indicator based on the major code. ('Y', 'N')
GOAL_INFORMED_CODES	`substr(f_get_serdtgl_codes(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,10)`	Most recent Informed Goal codes. Up to 10 codes, no delimiter. You can use the following SQL code snippet in Snowflake to see the validation table as of yesterday. SELECT * FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVGOAL WHERE INVOKE_DATA_DATE = CURRENT_DATE;
GENDER	`SPBPERS.SPBPERS_SEX as gender,`	Allowed values of Male, Female, and Not Available (defaults to null). Often populated off CCCApply Application or manual entry by Student Services (or Human Resources for employees). CCCApply nulls can equal Non-binary, Decline to state, No selection
BIRTHDATE	`spbpers.spbpers_birth_date as birthdate,`	Legal date of birth. Often populated off CCCApply Application or manual entry by Human Resources.
AGE	`f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,`	Current age of person.
AGEATTERM	`f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)`	Person’s age at term.
CENSUSAGEGROUP	`substr(baninst1.cos_fw_census_age_categ(sfbetrm_term_code,spbpers_birth_date,spbpers_dead_date),1,18)`	Description of age group person was in at term census. (Less than 20 years, 20-24 years, 25-29 years, 30-34 years, 35-39 years, 40-49 years, 50-59 years, 60 or more years)
MARITAL_CODE	`SPBPERS.SPBPERS_MRTL_CODE`	A person’s state of being married or not married. Refer to `STVMRTL` for current codes.
ETHNICITY	`decode(spbpers_ethn_code,null,null, substr(f_get_desc_fnc('STVETCT',(substr(f_get_ethn_category(spbpers_ethn_code,'ETCT'),1,1)),30),1,30))`	No longer being maintained or useful. Likely superseded by another field or solution.
NEW_ETHNICITY	`decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')`	User’s ethnicity as it relates to Hispanic or Latino. Banner codes are converted to text for ease of use. Per U.S. Department of Education guidelines, colleges are required to collect this racial and ethnic data.
RACES	`SUBSTR (f_get_races (spriden_pidm), 1, 60)`	Ethnicity and ancestry groups that the user identified with. 2 character codes, pipe '\|' delimited. Often populated off CCCApply Application or manual entry by Human Resources. Implementing SB38 (race_ethnic_full) a field length deficiency was discovered. AP-224 - Getting issue details... STATUS This is PII: Error rendering macro 'excerpt-include' : No location was provided.
RACES_DESC	`SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)`	Description of `RACES` code(s). Pipe '\|' delimited.
RACE_ETHN_CDE	`SUBSTR(REPORTSNET.f_get_race_ethn(spriden_pidm), 1, 1)`	Intended to locally replicate the Race/Ethnicity groups shown on the CCCCO Data Mart system. IS_HEADER on https://sequoias.atlassian.net/wiki/spaces/BANNER/pages/1173946389/COS+GEN.CODATAMART+RACE+ETHN+CROSSWALK+table is an additional column to tell if GORPRAC race code is a header value. On CCCApply, a header value checkbox must be selected to be able to select values underneath that header. Values underneath headers do not have to be selected. If a student has selected SPBPERS_ETHN_CDE = ‘2’ OR has select a number of races that equates CODATAMART_ETHN_CODE to include ‘H’, then that student will be listed as Hispanic. If a student has selected a number of races WHERE SPBPERS_ETHN_CDE <> ‘2’ AND CODATAMART_ETHN_CODE equates to 1 – 2 codes including ‘F’ and potentially ‘A’, AND the races that equate to CODATAMART_ETHN_CODE = ‘A’ have IS_HEADER = ‘Y’, the student will be returned Filipino. If a student has selected a number of races WHERE SPBPERS_ETHN_CDE <> ‘2’ AND CODATAMART_ETHN_CODE equates to 1 -2 codes including ‘A’ and potentially ‘F’, AND the races that equate to CODATAMART_ETHN_CODE = ‘A’ have IS_HEADER <> ‘Y’, the student will be returned Asian. If a student has selected a number of races that equate to TWO or MORE unique CODATAMART_ETHN_CODE values that DOES NOT include ‘H’ OR SPBPERS_ETHN_CODE <> ‘2’, the student will be returned as Multi-Ethnic. If a student has selected a number of races that equate to ONE CODATAMART_ETHN_CODE AND SPBPERS_ETHN_CDE <> ‘2’, the student will be returned as derived ETHN_CODE Else Unknown This is distinct from IPEDS. There is no CCCCO Data Mart methodology documented. See AP-231 - Getting issue details... STATUS for the work history.
RACE_ETHN_CDE_DESC	`SUBSTR(REPORTSNET.f_get_race_ethn_desc(REPORTSNET.f_get_race_ethn(spriden_pidm)), 1, 30)`	Race description according to RACE_ETHN_CDE andhttps://sequoias.atlassian.net/wiki/spaces/BANNER/pages/1173520436

Student table

0 Comments