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):
COMPUTED (Tim Garner rules for a single race/ethnicity given many factors):
Code Block
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:
D/P/NULL – if a student is on the President’s or Dean’s List for a term
Info
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.
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)
SUBSTRPERS_EMAIL
CELL_PHONE
SUBSTR(cos_f_get_tele_plain(spriden_pidm, 'CL'), 1, 21) AS CELL_PHONE
CELL_PHONE
The cell phone of the student added for SSO purposes.
COSPARENT_STUEDLV_EMAIL.STUDENT_PASSWORD AS ENCRYPTED_PASSWORD
Homeless status as determined by the student’s application
Sexual_Orientation
Jira Legacy
server
System JIRA
serverId
8b861ae3-ff87-316b-a17e-97c61a7e2fb4
key
AP-137
BANINST1.SV_MIS_SB_STU.f_get_sexual_orientation
Anchor
transgender_status
transgender_status
Sexual _Orientation
Jira Legacy
server
System JIRA
serverId
8b861ae3-ff87-316b-a17e-97c61a7e2fb4
key
AP-137
Sexual Orientation: SelfOrientation: 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
Info
Uses SB37 DED Definitions
SPBPERS_GNDR_CODE
Transgender_Status
Known misnomer as it encompasses more than just Transgender Status.
SPBPERS_GNDR_CODE
SPBPERS_GNDR_CODE is populated from CCCApply, but the Banner data architecture does not allow for a 1-to-1 relationship with CCCApply data.
Code Block
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 0 - Not transgender 0B - Non-Binary gender and Not transgender 1 - Transgender 1B - Non-Binary gender and transgender 2 - Decline to State 2B - Non-Binary gender and Decline to State X - Uncollected Null - Unknown XB - Uncollected Non-Binary Gender
Note
Codes 3 and 3B are no longer valid
CCCID
SZRCCAP_CCC_ID
CCCID
CCC id that is assigned at the State level by CCCApply when a student applies to the school. It is stored in SZRCCAP.CCC id that is assigned at the State level by CCCApply when a student applies to the school. It is stored in SZRCCAP.
Note
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 GORADID_ADDITIONAL_ID where ADID_CODE = 'CCC'. This is the field that MIS SB34 pulls from.
Completed_Eleventh
SZRCCAP_COMPLETED_ELEVENTH
Completed_Eleventh
Whether a Student has completed the eleventh grade. This is a self-reported column that is on the Student application.SZRCCAP_GPA
SR_HS_GPA
SZRCCAP_GPA
A Student’s self-reported HS GPA as reported on the Student’s application.SZRCCAP_HIGHEST_ENGL_COURSEStudent’s application.
SR_HS_Highest_Engl_Course
SZRCCAP_HIGHEST_ENGL_COURSE
The highest English course a student has taken. Self-reported on the Student’s application.
SZRCCAP_HIGHEST_ENGL_GRADE
SR_HS_Highest_Engl_Grade
SZRCCAP_HIGHEST_ENGL_GRADE
The highest English Grade a student has received. Self-reported on the Student’s application.SZRCCAP_HIGHEST_MATH_TAKEN
SR_HS_Highest_Math_Course
SZRCCAP_HIGHEST_MATH_TAKEN
The highest Math course a student has taken. Self-reported on the Student’s application.SZRCCAP_HIGH_MATH_TAKEN_GRADE Student’s application.
SR_HS_Highest_Math_Taken_Grade
SZRCCAP_HIGH_MATH_TAKEN_GRADE
The highest Math taken Grade. Self-reported on the Student’s application.SZRCCAP_HIGH_MATH_PASS_GRADE
SR_HS_Highest_Math_Pass_Grade
SZRCCAP_HIGH_MATH_PASS_GRADE
The highest passing Math Grade. Self-reported on the Student’s application.SZRCCAP_HIGHEST_MATH_PASSED
SR_HS_Highest_Math_Passed
SZRCCAP_HIGHEST_MATH_PASSED
The highest Math course a student has passed. Self-reported on the Student’s applicationSZRCCAP_YEARS_ENGLISH application
SR_HS_Years_English
SZRCCAP_YEARS_ENGLISH
Years of English taken in High School. Self-reported on the Student’s application
SZRCCAP_LOWEST_ENGL_GRADE
SR_HS_Lowest_Engl_Grade
SZRCCAP_LOWEST_ENGL_GRADE
Lowest English grade received in High School. Self-reported on the Student’s application.
SZRCCAP_CURRENT_MATH
SR_HS_Current_Math
SZRCCAP_CURRENT_MATH
A Student’s current High School Math class. Self-reported on the Student’s application.