STUDENT_TERM (COS) View

Proposed Architecture/Reference:

COS_DATALAKEHOUSE.COS_CURATED.STUDENT_TERM

Domain:

Tables Used (Document these in each element instead):
GOREMAL, GORPRAC, SFBETRM, SFRTHST, SGBSTDN, SHRLGPA, SHRTGPA, SMRPRLE, SPBPERS, SPRADDR, SPRHOLD, SPRIDEN, SPRTELE, STATTR, STVACYR, STVCAMP, STVCIPC, STVCITZ, STVCOLL, STVDEGC, STVESTS, STVETCT, STVETHN, STVLEVL, STVMAJR, STVNATN, STVSTST, STVSTYP, STVTERM, STVTMST, TBRACCD, TVTERM

This view is built on COS_DATALAKEHOUSE.CURATED.STUDENT_TERM. Invoke documentation defines this table as "One row per student per term." They source from more than just Banner for this curated table.

More information available here: COS_DATALAKEHOUSE.CURATED.COLLECTION_DEFINITION

COS Applications and Research decided to utilize the Invoke Curated tables as a starting point and build views off those for as long as feasible and accurate.

How Invoke decided to join tables, build exclusions/inclusions is unknown.

Update Frequency Notes:

COS_DATALAKEHOUSE.CURATED.STUDENT_TERM is built nightly. Functions execute on query.

Column	Definition	Status
TERM_CODE	`STUDENT_TERM.TERM_CODE` Common alias for `%TERM_CODE`. The first 4 digits are determined by the Academic Calendar end year. The last 2 digits refer to the semester/intersession where "10" represents Fall, "20" - Spring, and "30" - Summer. Sequoias CCD has a preceding Summer Academic Calendar. 7% was used for Community Education 999999 is the `STVTERM_TERM_CODE` value used to represent “The End of Time” (The “End of Time” has an End Date of 05/15/2999. So, if you are reading this around that time plan accordingly. ⏲) 000000, conversely, represents “The Beginning of Time” (The beginning of time has a Start Date at 01/01/1900. Nothing existed prior to this date. 🙃 )	DONE
TERM	`STUDENT_TERM.TERM` Semester YYYY is the typical format for how this description field is populated. (Spring 2022)	DONE
BANNER_ID	`STUDENT_TERM.STUDENT_ALTERNATE_ID` 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.	DONE
SSN	`APPLICATION.APPLICATION_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.	DONE
PIDM	`STUDENT_TERM.STUDENT_ID` 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.	DONE
CCCID	`APPLICATION_CCC_ID`	DONE
LAST_NAME	`STUDENT_TERM.STUDENT_LAST_NAME` A person’s legal surname. Often populated off CCCApply Application or manual entry by Human Resources. Allowed null for both persons and non-persons.	DONE
FIRST_NAME	https://sequoias.atlassian.net/browse/GOVPUB-29
MIDDLE_NAME	`STUDENT_TERM.STUDENT_MIDDLE_NAME` 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.	DONE
STUDENT_NAME	Concatenation of `LASTNAME, FIRSTNAME MIDINIT`. (Bond, James Herbert)	DONE
ADDRESS	`STUDENT_TERM.STUDENT_ADDRESS_STREET_1`	DONE
CITY	`STUDENT_TERM.STUDENT_ADDRESS_CITY`	DONE
STATE	`STUDENT_TERM.STUDENT_ADDRESS_STATE`	DONE
ZIP	`STUDENT_TERM.STUDENT_ADDRESS_POSTAL_CODE`	DONE
BIRTH_DATE	`STUDENT_TERM.STUDENT_BIRTH_DATE`Legal date of birth. Often populated off CCCApply Application or manual entry by Human Resources.	DONE
AGE_AT_TERM_START	`STUDENT_TERM.AGE`	DONE
ETHNICITY	Common Definitions 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.	INVOKE IS CREATING THIS
GENDER	https://sequoias.atlassian.net/browse/GOVPUB-9
SEXUAL_ORIENTATION_CODE	https://sequoias.atlassian.net/browse/GOVPUB-4
SEXUAL_ORIENTATION	https://sequoias.atlassian.net/browse/GOVPUB-5
TRANSGENDER Previously TRANSGENDER_STATUS	https://sequoias.atlassian.net/browse/GOVPUB-8
CONFIDENTIAL_IND	`SPBPERS_CONFID_IND` Flag representing a person’s desire to retain information confidentially. Stored on `SPBPERS_CONFID_IND`	DONE
PHONE	`STUDENT_TERM.STUDENT_PHONE_NUMBER` or: https://sequoias.atlassian.net/browse/GOVPUB-19	REQUIRES RESEARCH VALIDATION
CELL_PHONE	https://sequoias.atlassian.net/browse/GOVPUB-12
EMAIL
PERSONAL_EMAIL
MARITAL_CODE	`APPLICATION.APPLICATION_MARITAL_STAT` A person’s state of being married or not married. Refer to `STVMRTL` for current codes.
DEPENDENT
VISA_TYPE	https://sequoias.atlassian.net/browse/GOVPUB-35
RESD_CODE	https://sequoias.atlassian.net/browse/GOVPUB-34
RESD_DESC	GOVPUB-34 - Getting issue details... STATUS
PARENT_EDLV_1_CODE	https://sequoias.atlassian.net/browse/GOVPUB-31
PARENT_EDLV_1_DESC	GOVPUB-31 - Getting issue details... STATUS
PARENT_EDLV_2_CODE	GOVPUB-31 - Getting issue details... STATUS
PARENT_EDLV_2_DESC	GOVPUB-31 - Getting issue details... STATUS
HIGHSCHOOL	`APPLICATION.APPLICATION_NAME_HSCH`Description of High School attended. Links to STVSBGI validation. Historical data do not seemingly link to a validation table and thus includes many duplicate spellings and college names.
ENGL_PLACE Previously named ENG251_ELIG	Most recent English (LE) placement score. Historically meant what test score they received and how that applied to ENGL 251 eligibility.
ENGL_TEST_DATE Previously named ENG_TEST_DATE	Date of `ENG251_ELIG`. Historically meant when a student took a test.
MATHPLACE
AM_MATHPLACE
MATH_TEST_DATE
COMPLETED_ELEVENTH
SR_HS_GPA
SR_HS_YEARS_ENGLISH
SR_HS_HIGHEST_ENGL_COURSE
SR_HS_HIGHEST_ENGL_GRADE
SR_HS_LOWEST_ENGL_GRADE
SR_HS_HIGHEST_MATH_COURSE
SR_HS_HIGHEST_MATH_TAKEN_GRADE
SR_HS_HIGHEST_MATH_PASS_GRADE
SR_HS_CURRENT_MATH
ORIENTATION
PROGRAM	`STUDENT_TERM.STUDENT_TERM_PROGRAM_CODE`
PROG_DEPT
PROG_GE_PATTERN
MAJOR_CODE	`STUDENT_TERM.STUDENT_TERM_MAJOR_CODE`
MAJOR	`STUDENT_TERM.STUDENT_TERM_MAJOR`An area of study that a student chooses; often times supported by an academic counselor.
CTE_IND_BY_MJR
FIN_AID_IND_BY_MJR
REGIS_APPT
ED_PLAN_DATE
ACADEMIC_STANDING	https://sequoias.atlassian.net/browse/GOVPUB-33
STUDENT_STATUS Previously STATUS	https://sequoias.atlassian.net/browse/GOVPUB-7
OVERALL_GPA	`STUDENT_TERM_OVERALL_GPA`	HOLDNEEDS VALIDATION Research assessing what STUDENT_TERM columns would be useful for GPA/HRS/Units
OVERALL_HRS_EARNED	`STUDENT_TERM_OVERALL_HOURS_EARNED`	HOLDNEEDS VALIDATION
OVERALL_HRS_PASSED	`STUDENT_TERM_OVERALL_HOURS_PASSED`	HOLDNEEDS VALIDATION
TRANSFER_IN_UNITS		HOLDNEEDS VALIDATION
TRANSFER_IN_GPA		HOLDNEEDS VALIDATION
TERM_GPA	`STUDENT_TERM_GPA`	HOLDNEEDS VALIDATION
TERM_HOURS	`STUDENT_TERM_HOURS_ATTEMPTED`	HOLDNEEDS VALIDATION
TERM_PASSED	`STUDENT_TERM_HOURS_PASSED`	HOLDNEEDS VALIDATION
TERM_PASSED_ABC		HOLDNEEDS VALIDATION
TERM_ACCUM_UNITS_ATT	`STUDENT_TERM_OVERALL_HOURS_ATTEMPTED`	HOLDNEEDS VALIDATION
TERM_ACUM_UNITS_PASSED		HOLDNEEDS VALIDATION
TERM_ACCUM_GPA		HOLDNEEDS VALIDATION
DEAN_PRES_CODE	https://sequoias.atlassian.net/browse/GOVPUB-30
DEAN_PRES_DESC	https://sequoias.atlassian.net/browse/GOVPUB-30
EOPS	https://sequoias.atlassian.net/browse/GOVPUB-15
DRC	https://sequoias.atlassian.net/browse/GOVPUB-18
DRC_CODES	https://sequoias.atlassian.net/browse/GOVPUB-20
VTEA	https://sequoias.atlassian.net/browse/GOVPUB-17
PUENTE	https://sequoias.atlassian.net/browse/GOVPUB-13
CALWORKS	https://sequoias.atlassian.net/browse/GOVPUB-23
ATHLETE	https://sequoias.atlassian.net/browse/GOVPUB-25
WIA	https://sequoias.atlassian.net/browse/GOVPUB-26
FOSTER_YOUTH	https://sequoias.atlassian.net/browse/GOVPUB-27
TRIO	https://sequoias.atlassian.net/browse/GOVPUB-28
PELL	https://sequoias.atlassian.net/browse/GOVPUB-14
HOMELESS_STATUS