Student table (page 3)
- Jordan Lamb
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 Mar 18, 2015: Parent educational level code valid options based on MIS values. 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 |
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. Current Codes on related Validation Table GTVGNDR:
Codes - Definitions |
CCCID |
| CCC id that is assigned at the State level by CCCApply when a student applies to the school. It is stored in SZRCCAP. |
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 |
|
|