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).
SGBSTDN (SGASTDN Front End Page) snapshots SORCMJR/SOACURR data and keeps that on the student’s record.
Oracle source: ReportsNet.Student view
Oracle objects: SFBETRM, SPRADDR, SPRIDEN, SPRTELE, SPBPERS, STVTERM, SGBSTDN
Updated: Nightly
DW Field | Oracle Field/Calc
| Description (Sample) |
---|---|---|
TERM |
| https://sequoias.atlassian.net/browse/GOVPUB-10 |
SEMESTER |
| Semester YYYY is the typical format for how this description field is populated. (Spring 2022) |
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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| Concatenation of |
ADDRESS |
| Address at the start of the record’s term |
CITY |
| City at the start of the record’s term |
STATE |
| State at the start of the record’s term |
ZIP |
| Zip at the start of the record’s term |
PHONE |
| Current phone number. Concatenation of Area Code and Phone Number ((559) 8675309) |
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 |
| The program of the major |
PROG_DEPT |
| Department code for student’s program Has snapshot data on SGASTDN instead of the maintained data on SOACURR. |
PROG_GE_PATTERN |
| Student’s GE pattern for programs |
IN_STATE_IND |
|
|
MAJOR_CODE |
| Numeric code for the student’s major |
MAJOR |
| Description of the |
GOAL |
| Student’s stated goal for applying. Happens during application process and overwhelmingly without counselor guidance. |
GOAL_INFORMED_EFF_TERM |
| The term at which the Informed Goal codes became effective. |
FIN_AID_IND_BY_MJR |
| This is a Financial Aid indicator based on the major code. ('Y', 'N') |
CTE_IND_BY_MJR |
| This is a CTE indicator based on the major code. ('Y', 'N') |
GOAL_INFORMED_CODES |
| 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 |
| 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 |
| Legal date of birth. Often populated off CCCApply Application or manual entry by Human Resources. |
AGE |
| Current age of person. |
AGEATTERM |
| Person’s age at term. |
CENSUSAGEGROUP |
| 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 |
| A person’s state of being married or not married. Refer to
|
ETHNICITY |
|
No longer being maintained or useful. Likely superseded by another field or solution.
|
NEW_ETHNICITY |
| 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 |
| 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. This is PII:
Error rendering macro 'excerpt-include' : No location was provided.
|
RACES_DESC |
| Description of |
RACE_ETHN_CDE
|
| 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.
This is distinct from IPEDS. There is no CCCCO Data Mart methodology documented. See - AP-231Getting issue details... STATUS for the work history.
|
RACE_ETHN_CDE_DESC
|
| Race description according to RACE_ETHN_CDE andhttps://sequoias.atlassian.net/wiki/spaces/BANNER/pages/1173520436 |
DW Field | Oracle Field/Calc | Description (Sample) |
---|---|---|
HIGHSCHOOL |
| 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.
|
ENG251_ELIG |
| Student’s highest LE score. While ENGL 251 Eligibility is no longer relevant, this field is still helpful. |
ENG_TEST_DATE | SELECT MAX (sortest_test_date) FROM sortest WHERE sortest_pidm = spriden.spriden_pidm AND sortest_tesc_code = 'LE') | Date of most recent LE score. Does not necessarily relate to ENG251_ELIG. Prior to AB 705, this represented the test date of most recent English 251 placement test. |
MATHPLACE |
| Student’s highest LM score. Math placement score that does not include AM (Any Math) placement scores. Prior to AB 705, Score on most recent Math placement test. 0 to 6.0. |
MATH_TEST_DATE | SELECT MAX (sortest_test_date) FROM sortest WHERE sortest_pidm = spriden.spriden_pidm AND sortest_tesc_code = 'LM') | Date of most recent LM score. Does not necessarily relate to MATHPLACE. Prior to AB 705, represented the test date that generated the most recent Math placement score. |
READ_LEVEL |
| Student’s highest LR score. Prior to AB 705, represented score on most recent Reading placement test. 0 to 6.0. |
READ_TEST_DATE | SELECT MAX (sortest_test_date) FROM sortest WHERE sortest_pidm = spriden.spriden_pidm AND sortest_tesc_code = 'LR') | Date of most recent LR score. Does not necessarily relate to READ_LEVEL. Prior to AB 705, represented the test date of most recent Reading placement test score. |
CELSA_ESL | f_test_score_fnc (spriden_pidm,'ESL1','HIGH',null) | Highest score on CELSA ESL test |
ESL_TEST_DATE | SELECT MAX (sortest_test_date) FROM sortest WHERE sortest_pidm = spriden.spriden_pidm AND sortest_tesc_code = 'ESL1') | Test date of most recent ESL test |
ESL_PLACEMENT |
| Highest LESL Placement score. |
OVERALL_GPA |
| Float – from all terms attended. Updated during semester Includes transfer courses. |
OVERALL_HRS_EARNED |
| Float – Units earned with grades A to D, P, or CR. This includes transferred units into COS and units earned at COS. |
OVERALL_HRS_PASSED |
| Float – Units earned with grades A to F. This includes transferred units into COS and units earned at COS. Used for GPA calculation. |
TRANSFER_IN_UNITS |
| Units transferred from outside institution |
TRANSFER_IN_GPA |
| GPA of units transferred from outside institution |
TERM_GPA |
| Float – GPA for this term. Includes transfer units. |
TERM_HOURS |
| Float – Hours enrolled for this term (R% registration). Same as units. [Note: used in StudentFundType view] |
TERM_PASSED |
| Float – Units passed for this term. Changed to look at current grade in Academic History instead of initial grade in registration. Includes but not limited to (has numbers too but excluded them here): A, A+, A-, B, B+, B-, C, C+, CR, D, D+, D-, P NOTE: There is no C-, we do not give out this grade at this time.
See following internal sql (oracle) – maybe mod term_passed_abc to use this instead:
SELECT DISTINCT shrgrde_code FROM shrgrde WHERE shrgrde_passed_ind = 'Y' and regexp_like(shrgrde_code, '\D') ORDER BY 1; |
TERM_PASSED_ABC |
| Float – Units passed for courses taken this term with grade of A, B, or C (including + and – grades) |
TERM_ACCUM_UNITS_ATT |
| Accumulative units attempted from the earliest term available in Banner to the term of this record. Same as Units Attempted on SWIASTD screen (except this is an accumulation for each term). This includes both Institutional and Transfer. |
TERM_ACCUM_UNITS_PASSED |
| Accumulative units passed from the earliest term available in Banner to the term of this record. Same as Units Attempted on SWIASTD screen (except this is an accumulation for each term). This includes both Institutional and Transfer. |
TERM_ACCUM_GPA |
| Accumulative GPA from the earliest term available in Banner to the term of this record. |
COS_DEGREE_UNITS_ATT |
| Units attempted for degree for the term on this record at COS. This does NOT include Credit/No Credit courses. |
COS_DEGREE_PTS |
| Grade points for degree for the term on this record at COS |
COS_DEGREE_UNITS_PASSED |
| Units passed for degree for the term on this record at COS. This does include Credit/No Credit courses. |
COS_DEGREE_GPA |
| Grade Point Average for degree for the term on this record at COS |
TERM_ACCUM_COS_DEGREE_GPA |
| Accumulative GPA for degree at COS from the earliest term available in Banner to the term of this record |
CSU_DEGREE_UNITS_ATT |
| Units attempted for degree for the term on this record transferable to a California State University |
CSU_DEGREE_PTS |
| Grade points for degree for the term on this record transferable to a California State University |
CSU_DEGREE_UNITS_PASSED |
| Units passed for degree for the term on this record transferable to a California State University |
TERM_ACCUM_CSU_DEGREE_ UNITS_PASSED |
| Accumulative units passed for degree from the earliest term available in Banner to the term of this record transferable to a California State University |
CSU_DEGREE_GPA |
| Grade Point Average for degree for the term on this record transferable to a California State University |
TERM_ACCUM_CSU_DEGREE_GPA |
| Accumulative GPA for a CSU degree from the earliest term available in Banner to the term of this record |
UC_DEGREE_UNITS_ATT |
| Units attempted for degree for the term on this record transferable to a University of California |
UC_DEGREE_PTS |
| Grade points for degree for the term on this record transferable to a University of California |
UC_DEGREE_UNITS_PASSED |
| Units passed for degree for the term on this record transferable to a University of California |
TERM_ACCUM_UC_DEGREE_UNITS_PASSED |
| Accumulative units passed for degree from the earliest term available in Banner to the term of this record transferable to a University of California |
UC_DEGREE_GPA | reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TUC', sfbetrm_term_code) / reportsnet.f_get_univ_units_passed (spriden_pidm, 'TUC', sfbetrm_term_code) | Grade Point Average for degree for the term on this record transferable to a University of California |
TERM_ACCUM_UC_DEGREE_GPA | reportsnet.f_get_accum_univ_qual_pts (spriden_pidm, 'TUC', '000000', sfbetrm_term_code) / reportsnet.f_get_accum_univ_units (spriden_pidm, 'TUC', '000000', sfbetrm_term_code) | Accumulative GPA for a UC degree from the earliest term available in Banner to the term of this record |
OVERALL_COS_DEGREE_UNITS_ATT |
| Total units attempted for degree (for all terms including terms older than those in the warehouse) at COS. This does NOT include Credit/No Credit courses. |
OVERALL_COS_DEGREE_PTS |
| Total grade points for degree (for all terms including terms older than those in the warehouse) at COS |
OVERALL_COS_DEGREE_ UNITS_PASSED |
| Total units passed for degree (for all terms including terms older than those in the warehouse) at COS. This does include Credit/No Credit courses. |
OVERALL_COS_DEGREE_ GPA | to_char(baninst1.cos_f_univ_qual_pts(spriden.spriden_pidm, 'DA') / nullif(baninst1.cos_f_univ_units(spriden.spriden_pidm, 'DA'),0),'99.99') | Grade Point Average for degree (for all terms including terms older than those in the warehouse)at COS |
OVERALL_CSU_DEGREE_ UNITS_ATT |
| Total units attempted for degree (for all terms including terms older than those in the warehouse) transferable to a California State University |
OVERALL_CSU_DEGREE_PTS |
| Total grade points for degree (for all terms including terms older than those in the warehouse)transferable to a California State University |
OVERALL_CSU_DEGREE_UNITS_PASSED |
| Total units passed for degree (for all terms including terms older than those in the warehouse) transferable to a California State University |
OVERALL_CSU_DEGREE_GPA | to_char(baninst1.cos_f_univ_qual_pts(spriden.spriden_pidm, 'TCSU') / nullif(baninst1.cos_f_univ_units(spriden.spriden_pidm, 'TCSU'),0),'99.99') | Grade Point Average for degree (for all terms including terms older than those in the warehouse) transferable to a California State University |
OVERALL_UC_DEGREE_UNITS_ATT |
| Total units attempted for degree (for all terms including terms older than those in the warehouse) transferable to a University of California |
OVERALL_UC_DEGREE_PTS |
| Total grade points for degree (for all terms including terms older than those in the warehouse) transferable to a University of California |
OVERALL_UC_DEGREE_UNITS_PASSED |
| Total units passed for degree (for all terms including terms older than those in the warehouse) transferable to a University of California |
OVERALL_UC_DEGREE_GPA | baninst1.cos_f_univ_qual_pts (spriden_pidm, 'TUC') / baninst1.cos_f_univ_units (spriden_pidm, 'TUC') | Grade Point Average for degree (for all terms including terms older than those in the warehouse) transferable to a University of California |
EOPS |
| Code From STVEOPS (EOPS program). The ‘E’ value means the Student is part of the EOPS program during the term on this record. At the end of the term, the code may change to something other than C. So, they are in EOPS if this code is not null and not ‘C%’. E: EOPS-Elig next term G: EOPS-Successfully Comp. Goal P: EOPS-failed Cont/CARE-New CARE C: EOPS-Chosen EOPS/CARE-w/oWaiv CE: Chose not to be in EOPS S: EOPS-Completed 6 Sem. U: EOPS-Comp 70 Units, end elig. X: EOPS-Unknown/Unreported B: CARE-waiver CalWORKS/TANF/AFDC N: CARE-not served by CARE pgm L: CARE-Obsolete Code/Do Not Use |
DRC Now referenced as AAC |
| Student has a disability (Y). This value can only be determined once AAC has entered this student in Banner. |
DRC_CODES Now referenced as AAC |
| List of disability codes (1 character each, no delimiter, up to 10 codes) |
VATEA |
| Vocational And Technological Education Act (A/N) A: student in vocational program N: in vocational course, but not vocational major |
PUENTE |
| Student is part of Puente program (Y or null). BANNER does not store this information per term. The nightly job updates this column for all rows in Student to the current Puente status. |
COHORT_CODES |
| Currently limited to codes A through E |
REGISTERED_CLASSES | select count(sfrstcr.SFRSTCR_CRN) from sfrstcr where sfrstcr.sfrstcr_term_code = sfbetrm.SFBETRM_TERM_CODE AND sfrstcr.sfrstcr_pidm = SFBETRM.SFBETRM_PIDM AND sfrstcr.SFRSTCR_RSTS_CODE like 'R%' GROUP BY sfrstcr.SFRSTCR_PIDM | Number of classes for which the student has registered |
CAMPUSES |
| Pipe delimited string of 3-char campus codes for all campuses at which the student is enrolled in courses. |
ACTIVITY_DATE |
| Datetime – most recent activity date from all tables involved (sfbterm, spraddr, spriden, sprtele, spbpers, sfbetrm). Essentially this is the last time that any information about the student was changed. |
CAST_CODE |
| Combined Academic Standing Code for the tern of this record. Note that this code will not be correct until grades have been entered at the end of the term: 00: Good Standing 03: Progress Probation 04: Academic Probation 05: Both Prog & Acad Probation 06: Progress Dismissal 07: Academic Dismissal 08: Both Acad & Prog Dismiss |
| The Student’s email address | |
VISA_TYPE |
| see VISA type code in Appendix |
CALWORKS |
| 2: CALWORKS self referral participation 3: CALWORKS county referral participation Null if not participating in CALWORKS |
VETERAN |
| ‘Y’ if the student was a veteran for the term of this record. Null otherwise. |
REGIS_APPT |
| The date of the student’s next registration appointment |
ATHLETE | select s.SGRSPRT_SPST_CODE from sgrsprt s where s.sgrsprt_term_code = term and s.SGRSPRT_PIDM = pidm and s.SGRSPRT_SPST_CODE = 'A'; | ‘Y’ if the student is an athlete for the term of this record (at least one record in SGRSPRT where SGRSPRT_SPST_CODE = ‘A’) |
FIRST_YR_EXPERIENCE | select 'Y' from sfrsrpo s where s.sfrsrpo_term_code = term and s.SFRSRPO_PIDM = pidm and s.SFRSRPO_ROVR_CODE = 'FYE' | ‘Y’ if the student is in First Year Experience for the term of this record. |
SECOND_YR_EXPERIENCE | select 'Y' from sfrsrpo s where s.sfrsrpo_term_code = term and s.SFRSRPO_PIDM = pidm and s.SFRSRPO_ROVR_CODE = 'SYE' | ‘Y’ if the student is in Second Year Experience for the term of this record. |
WIA | select decode (s.sgrsact_actc_code, 'wia', 'y', 'n') from saturn.sgrsact s where s.sgrsact_term_code = sfbetrm_term_code and s.sgrsact_actc_code = 'wia' and s.sgrsact_pidm = spriden.spriden_pidm | ‘Y’ if the student is WIA (Workforce Investment Act) for the term of this record. |
PELL |
| ‘Y” if the Student was awarded PELL for the term of this record. ‘N’ otherwise. |
DEPENDENT |
| Dependency status from the FAFSA, if the student submitted a FAFSA. This is the status for the aid year in which this record’s term falls (example: 200810 is in the 0708 aid year, so this value will be the same for 200810, 200820, 200830): I: Independent D: Dependent Null: student did not submit a FAFSA OR they did not supply a dependency status |
ED_PLAN_DATE |
| Date that the student met with a counselor to create an educational plan. If null, no meeting took place and no plan was created. NOTE: This does NOT show the most recent date just that a meeting took place at one time. This is also looking at an older table so the data is inaccurate. |
ED_LEVEL_CODE |
| A three position alphanumeric field which indicate the highest level of the education that the student completed |
FIRST_GEN |
| ‘Y’ / ‘N’ if Student is a first-generation college student (based on 2 Parent Education Level questions on CCCApply). |
ORIENTATION |
| The date the student attended orientation. If null, we have no record of the student attending orientation. |
DW Field | Oracle Field/Calc | Description (Sample) |
---|---|---|
AP_CALCAB_SCORE | SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'CLAB'),1,1) | AP test score on CALC AB Exam |
AP_CALCBC_SCORE | SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'CLBC'),1,1) | AP test score on CALC BC Exam |
AP_STATS_SCORE | SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'STAT'),1,1) | AP test score on STATS Exam |
AP_LANGCOMP_SCORE | SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'ELNG'),1,1) | AP test score on LANG & COMP Exam |
AP_LITCOMP_SCORE | SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'ELIT'),1,1) | AP test score on LIT & COMP Exam |
FOSTER_YOUTH | REPORTSNET.F_GET_STUDENT_IS_FOSTER_YOUTH ( SPRIDEN_PIDM, SFBETRM_TERM_CODE) | For Term < 201330, from SZRCCAP_FOSTER_YOUTH column, which is Yes/No for Foster Youth Residency: ‘Y’: student is foster youth ‘N’: student is not foster youth Null: unknown For Term >= 201330, from BANNER SVBSFYS table which is based on the Foster Youth Status question on the Application (value 0 to 5) (this can be updated in BANNER so is more ‘correct’ but we only started capturing this in Feb 2013): ‘Y’: value > 0 selected ‘N’: other or none selected |
BASIC_SKILLS | REPORTSNET.F_GET_STUDENT_IS_BASIC_SKILLS (in_pidm, in_term) | ‘Y’: Basic Skills ‘N’: Not Basic Skills A Non Basic Skills student is defined as Completed MATH 360 or higher OR has MATH placement score > 3.0 AND Completed ENGL 360 or higher OR has ENGL placement score > 3.0 All other students are considered “Basic Skills” |
CALC_RACE_ETHN |
| COMPUTED (Tim Garner rules for a single race/ethnicity given many factors): when [NEW_ETHNICITY] is null and [RACES] is null and[ETHNICITY] is null then 'Unknown' when [VISA_TYPE] = 'F1' then 'Non-res. alien' when [NEW_ETHNICITY] is null and first character of [RACES] is 'H' then 'H' when [NEW_ETHNICITY] is null and [RACES] contains a pipe then 'Two or more' when [NEW_ETHNICITY] is null and [RACES] is not null then first character of [RACES] when [NEW_ETHNICITY] is null and [RACES] is null and [ETHNICITY]='Other' then 'Unknown' when [NEW_ETHNICITY] is null and [RACES] is null and [ETHNICITY]='Am. Indian or Alaskan Native' then 'N' when [NEW_ETHNICITY] is null and [RACES] is null and [ETHNICITY]='Asian or Pacific Islander' then 'Unknown' when [NEW_ETHNICITY] is null and [RACES] is null then first character of [ETHNICITY] when [NEW_ETHNICITY]='Hispanic' then 'H' when [NEW_ETHNICITY]='Not Hispanic' and [RACES] is nul and [ETHNICITY]='Other' then 'Unknown' when [NEW_ETHNICITY]='Not Hispanic' and [RACES] is null and [ETHNICITY]='Am. Indian or Alaskan Native' then 'Unknown' when [NEW_ETHNICITY]='Not Hispanic' and [RACES] is null and [ETHNICITY]='Asian or Pacific Islander' then 'Unknown' when [NEW_ETHNICITY]='Not Hispanic' and [RACES] is null then first character of [ETHNICITY] when [NEW_ETHNICITY]='Not Hispanic' and [RACES] contains a pipe then 'Two or more' when [NEW_ETHNICITY]='Not Hispanic' then first character of [RACES] else 'Unknown' |
EOPS_CARE |
| Possible 3 characters. The first character is either a ‘Y’ or ‘N’. If ‘Y’, the student is in the EOPS CARE program for the term of the record. If ‘N’, the student is not in the EOPS CARE program for the term of the record and characters 2 and 3 will contain the exit code: CE: Chose not to be EOPS CL: Exit CARE - No longer EOPS CM: Exit CARE - Married or not SHH CR: Exit CARE - Did not Recertify CT: Exit CARE - Not TANF CX: Exit CARE - Termination |
HS_STATUS | SUBSTR( COS_PIN_REG.cos_is_hs(spriden_pidm, sfbetrm_term_code) , 1, 15) | Values returned: UPHS - UPHS student for that term VTEC - VTEC student for that term Y - other concurrent student Null - not a concurrent student |
CALC_RACE_ETHN_CDE |
| Defunct |
CAREER_ADVACAD | SUBSTR(baninst1.COS_GENERAL_RESTR.f_is_CareerAdvAcademyPart(SPRIDEN_PIDM, sfbetrm.sfbetrm_term_code), 1, 1) | Y/N – If Student is in CAA (Career Advancement Academy Participant) |
DEAN_PRES_CODE | SUBSTR(BANINST1.cos_general.f_is_on_deans_or_pres_list(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE), 1, 1) | D/P/NULL – if a student is on the President’s or Dean’s List for a term Refer to SHAINST page for data entry and maintenance. |
DEANS_PRES_DESC | SUBSTR(CASE BANINST1.cos_general.f_is_on_deans_or_pres_list(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE) --ryanwh 1.16.2015 added Dean's and President's list by term. WHEN NULL THEN NULL ELSE f_get_desc_fnc('STVASTD', BANINST1.cos_general.f_is_on_deans_or_pres_list(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE), 30) END, 1, 30) | If null, it doesn’t put a description. If P or D, it will return the description listed in Banner on the STVASTD table |
RESD_CODE | SGBSTDN.SGBSTDN_RESD_CODE | Residency code from the student’s SGBSTDN record by term Contains AB 540 information |
RESD_DESC | STVRESD.STVRESD_DESC | Residency description for the student’s residency code |
CAA | SVBSCAA.SVBSCAA_CAA_IND | Indicates if a student is a part of the CAA (Career Advancement Academy), which returns a Y or null. |
TRIO | ,SUBSTR(baninst1.cos_general.f_trio_code(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE), 1, 10) | Indicates if a student is a part of the TRIO program. One of two codes will be listed, TRIOSSS (TRIO Student Support Services) or TRIOUBMS (TRIO Upward Bound Math/Science) |
PERS_EMAIL |
|
|
CELL_PHONE | SUBSTR(cos_f_get_tele_plain(spriden_pidm, 'CL'), 1, 21) AS CELL_PHONE | The cell phone of the student added for SSO purposes. |
PARENT_EDLV_1_CODE | SUBSTR (SZRCCAP_PARENT_EDLV_1, 1, 1) PARENT_EDLV_1_CODE | Parent educational level based on CCCApply data. Research Office request : Parent educational level code valid options based on MIS values. The description from the CCCApply application do not explicitly match the description from MIS. The intent appears to be equivalency. CCCApply: 1 - Grade 9 or less 2 - Some high school; did not graduate 3 - High school graduate (diploma, GED, or equivalent) 4 - Some college credit; no degree 5 - Associate’s degree (for example: AA, AS) 6 - Bachelor’s degree (for example: BA, BS) 7 - Graduate degree (Master’s, Ph.D., or professional degree beyond Bachelor’s) X - Unknown Y - No parent or guardian raised me MIS Values: 1 - Grade 9 or less 2 - Grade 10, 11, or 12 but did not graduate 3 - High school graduate 4 - Some college but no degree 5 - AA/AS degree 6 - BA/BS degree 7 - Graduate or professional degree beyond a BA/BS X - Unknown / Unreported Y - Not applicable, no noted guardian/parent |
PARENT_EDLV_1_DESC | SUBSTR(BANINST1.COS_GENERAL_CROSSWALK.F_GET_PARENT_ED_LVL_DESC (ccap.SZRCCAP_PARENT_EDLV_1), 1, 60) PARENT_EDLV_1_DESC | Description above with code. |
PARENT_EDLV_2_CODE | SUBSTR (SZRCCAP_PARENT_EDLV_2, 1,1) PARENT_EDLV_2_CODE | Description above with code. |
PARENT_EDLV_2_DESC |
| Description above with code. |
Homeless_Status |
| Homeless status as determined by the student’s application |
Sexual_Orientation |
| Sexual Orientation: Self-declared sexual orientation code value. Self-reported initially from the Student’s application. This is stored in Banner in the table: SVBSXLO and the column: SVBSXLO_SXLO_CODE Uses SB37 DED Definitions |
Transgender_Status Known misnomer as it encompasses more than just Transgender Status.
|
|
IF c1_rec.gender = 'B' AND c1_rec.transgender_encrypted BETWEEN '1' and '3' THEN --ryanwh: 11.2.2021: using DECODE to map the CCCApply values to the appropriate MIS values. v_gndr_code := DECODE ( SUBSTR (c1_rec.transgender_encrypted, 0, 1), '2', '0', '3', '2', '0', 'X', SUBSTR (c1_rec.transgender_encrypted, 0, 1)) || c1_rec.gender; ELSE v_gndr_code := DECODE ( SUBSTR (c1_rec.transgender_encrypted, 0, 1), '2', '0', '3', '2', '0', 'X', SUBSTR (c1_rec.transgender_encrypted, 0, 1)); END IF; Because of this, Technology Services staff chose not to split that data back out to separate gender/transgender fields like CCCApply. We also crosswalk from CCCApply values to approved MIS values because MIS only allows certain codes and there is no known Banner process to do code conversions for SB reporting. Current Codes on related Validation Table GTVGNDR:
Codes - Definitions Codes 3 and 3B are no longer valid |
CCCID |
| CCC id that is assigned at the State level by CCCApply when a student applies to the school. It is stored in SZRCCAP. This field is not being properly populated by the CCCApply integration and shouldn’t be relied upon. Future CCCID data elements should pull from the most downstream data source |
Completed_Eleventh |
| Whether a Student has completed the eleventh grade. This is a self-reported column that is on the Student application. |
SR_HS_GPA |
| A Student’s self-reported HS GPA as reported on the Student’s application. |
SR_HS_Highest_Engl_Course |
| The highest English course a student has taken. Self-reported on the Student’s application. |
SR_HS_Highest_Engl_Grade |
| The highest English Grade a student has received. Self-reported on the Student’s application. |
SR_HS_Highest_Math_Course |
| The highest Math course a student has taken. Self-reported on the Student’s application. |
SR_HS_Highest_Math_Taken_Grade |
| The highest Math taken Grade. Self-reported on the Student’s application. |
SR_HS_Highest_Math_Pass_Grade |
| The highest passing Math Grade. Self-reported on the Student’s application. |
SR_HS_Highest_Math_Passed |
| The highest Math course a student has passed. Self-reported on the Student’s application |
SR_HS_Years_English |
| Years of English taken in High School. Self-reported on the Student’s application |
SR_HS_Lowest_Engl_Grade |
| Lowest English grade received in High School. Self-reported on the Student’s application. |
SR_HS_Current_Math |
| A Student’s current High School Math class. Self-reported on the Student’s application. |
Distance_Ed_Student | SUBSTR ( Baninst1.cos_general_restr.f_Is_Student_enrolled_Online ( f.sfbetrm_pidm,f.SFBETRM_TERM_CODE), 1, 1. | Checks whether a student is enrolled in any online classes and returns a ‘Y’ or an ‘N’ |
Confidential_ind |
|
|
AM_MATHPLACE |
|
|
0 Comments