Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 23 Next »

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

Oracle Field/Calc

DW Field

Description (Sample)

Data Type

Field Length

sfbetrm.sfbetrm_term_code as term,

TERM

Fall terms: %10

Spring terms: %20

Summer terms: %30

int

6

stvterm.stvterm_desc as semester,

SEMESTER

Fall 2006

spriden.spriden_id as studentid,

STUDENTID

Banner ID (@12345678)

spbpers.spbpers_ssn as ssn,

SSN

123456789

spriden.spriden_pidm as pidm,

PIDM

Up to 10-digit unique Banner id for student

spriden_last_name as LastName,

LASTNAME

spriden_first_name as FirstName,

FIRSTNAME

spriden_mi as MidInit,

MIDINIT

nvl(spriden_last_name, '') || ', ' ||

nvl(spriden_first_name, '') || ' ' ||

nvl(spriden_mi, '') as studentname,

STUDENTNAME

Concatenation of Last, First Middle Initial (Smith, John A)

spraddr.spraddr_street_line1 as address,

ADDRESS

Address at the start of the record’s term

spraddr.spraddr_city as city,

CITY

City at the start of the record’s term

spraddr.spraddr_stat_code as state,

STATE

State at the start of the record’s term

spraddr.spraddr_zip as zip,

ZIP

Zip at the start of the record’s term

decode(sprtele_phone_number, null, '', '(' || sprtele_phone_area || ') ' || sprtele_phone_number) as phone,

PHONE

Current phone number

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,

STATUS

Description of Student Status. See the STVSTYP lookup table in the Appendix. This is the full description of the status, such as “Continuing Student”, “Returning Student”.

There is also the CAST code (shrttrm_cast_code) which is included at the end of the record.

sgbstdn.SGBSTDN_PROGRAM_1

PROGRAM

The Program of the Major

g.SGBSTDN_DEPT_CODE

PROG_DEPT

Department code for student’s program

g.SGBSTDN_MAJR_CODE_CONC_1

PROG_GE_PATTERN

Student’s GE pattern for programs

SGBSTDN.SGBSTDN_MAJR_CODE_1

MAJOR_CODE

Numeric code for the student’s major

substr(f_get_desc_fnc('STVMAJR',SGBSTDN.SGBSTDN_MAJR_CODE_1,30),1,30) as Major,

MAJOR

Description of the Major_Code

substr(f_get_desc_fnc('STVEGOL',SGBSTDN_EGOL_CODE,30),1,30) as Goal,

GOAL

Uninformed Educational Goal. 50-char description.

substr(f_get_serdtgl_eff_term(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,6)

GOAL_INFORMED_EFF_TERM

The term at which the Informed Goal codes became effective.

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)

FIN_AID_IND_BY_MJR

This is a financial aid indicator based off of the major code. Returns Y or N

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)

CTE_IND_BY_MJR

This is a CTE indicator based off of the major code. Returns Y or N.

substr(f_get_serdtgl_codes(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,10)

GOAL_INFORMED_CODES

Most recent Informed Goal codes. Up to 10 codes, no delimiter. See Goal Codes in Appendix.

SPBPERS.SPBPERS_SEX as gender,

SPBPERS_SEX fed from CCCApply initially and can be overridden by manual entry.

GENDER

M

F

N

Null

VARCHAR2

1

spbpers.spbpers_birth_date as birthdate,

BIRTHDATE

datetime

f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

AGE

float

f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

AGEATTERM

Age at term

substr(baninst1.cos_fw_census_age_categ(sfbetrm_term_code,spbpers_birth_date,spbpers_dead_date),1,18)

CENSUSAGEGROUP

18-char description of age group: “35-39 years”

SPBPERS.SPBPERS_MRTL_CODE

MARITAL_CODE

D: Divorced

M: Married

N: Not Known

P: Domestic Partner

R: Separated

S: Single

W: Widowed

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))

ETHNICITY

40-char description of spbpers_ethn_code, coming from STVETCT:

1 Black Non-Hispanic

2 Am. Indian or Alaskan Native

3 Asian or Pacific Islander

4 Hispanic

5 White Non-Hispanic

6 Other

decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')

NEW_ETHNICITY

‘Not Hispanic’, ‘Hispanic’, or blank

SUBSTR (f_get_races (spriden_pidm), 1, 60)

RACES

2-character Race codes. May contain multiple values separated by a pipe character (|). See Race Codes in Appendix.

SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)

RACES_DESC

Race descriptions, multiple values separated by a pipe character (|). See Race Codes in Appendix.

SUBSTR(REPORTSNET.f_get_race_ethn(spriden_pidm), 1, 1)

RACE_ETHN_CDE

1-character race codes according to the new methods

Looks at the first character that is returned. 

Grabs the ethnicity as mentioned above in the ethnicity field, then grabs the new_ethnicity, and race fields. 

If nothing is found it assigns unknown 

Else If ethnicity is null but races =  ‘Hispanic’ then it assigns Hispanic 

Else If ethnicity is null and races contains “|” character it assigns twoOrMore 

Else If new_Ethnicity is null and races is not null then it checks if races is Filipino if so it assigns Filipino  to stuEthnicity, else it grabs the first character of the race field. 

Else if new ethnicity is null and races is null and the ethnicity equals  ‘Am. Indian or Alaskan Native', Then Native American code. 

“” equals other or  Asian or Pacific Islander 

Then “Unknown” 

“” equals “Hispanic” then Hispanic 

Else if new ethnicity equals NotHispanic 

And races is null  

And (ethnicity is other or ethnicity is ‘Am. Indian or Alaskan Native' or ‘Asian or Pacific Islander’)  then unknown code 

Else if ethnicity is nothispanic and races is null then the first letter of their ethnicity code.  

Else if new_ethnicity is not Hispanic and races contains ‘|’ then ethnicity equals twoOrMore 

Else notHispanic 

Then ethnicity equals the first Letter of their race field.  

Else it assigns unknown. 

Returns the value. 

SUBSTR(REPORTSNET.f_get_race_ethn_desc(REPORTSNET.f_get_race_ethn(spriden_pidm)), 1, 30)

RACE_ETHN_CDE_DESC

30-character Race description according to RACE_ETHN_CDE

Calls the  f_get_race_ethn function and just follows through a series of case statements and returns the appropriate value for the description. 

‘2’ then ‘Multi-Ethnicity’ 

‘A’ then ‘Asian’ 

‘B’ then ‘African-American’ 

‘F’ then ‘Filipino’ 

‘H’ then Hispanic 

‘N’ then  American Indian/Alaskan Native 

‘P’ then Pacific Islander 

‘W’ then White Non-Hispanic 

‘X’ then Unknown 

Doesn’t match then Unknown

decode(f_get_hsch_code(sfbetrm.sfbetrm_pidm), null, null,substr(f_get_desc_fnc('STVSBGI',f_get_hsch_code(sfbetrm.sfbetrm_pidm),30),1,30) )

HIGHSCHOOL

f_test_score_fnc (spriden.spriden_pidm,'LE','HIGH',null)

ENG251_ELIG

Score on most recent English 251 placement test. 0 to 6.0.

SELECT MAX (sortest_test_date) FROM sortest

WHERE sortest_pidm = spriden.spriden_pidm

AND sortest_tesc_code = 'LE')

ENG_TEST_DATE

Test date of most recent English 251 placement test

f_test_score_fnc (spriden_pidm,'LM','HIGH',null)

MATHPLACE

Score on most recent Math placement test. 0 to 6.0.

SELECT MAX (sortest_test_date) FROM sortest

WHERE sortest_pidm = spriden.spriden_pidm

AND sortest_tesc_code = 'LM')

MATH_TEST_DATE

Test date of most recent Math placement test

f_test_score_fnc (spriden_pidm,'LR','HIGH',null)

READ_LEVEL

Score on most recent Reading placement test. 0 to 6.0.

SELECT MAX (sortest_test_date) FROM sortest

WHERE sortest_pidm = spriden.spriden_pidm

AND sortest_tesc_code = 'LR')

READ_TEST_DATE

Test date of most recent Reading placement test

f_test_score_fnc (spriden_pidm,'ESL1','HIGH',null)

CELSA_ESL

Score on CELSA ESL test

SELECT MAX (sortest_test_date) FROM sortest

WHERE sortest_pidm = spriden.spriden_pidm

AND sortest_tesc_code = 'ESL1')

ESL_TEST_DATE

Test date of most recent ESL test

f_test_score_fnc (spriden.spriden_pidm, 'LESL', 'HIGH', NULL)

ESL_PLACEMENT

ESL Placement Level

baninst1.cos_f_get_lgpa(spriden_pidm, 'UG','O')

OVERALL_GPA

Float – from all terms attended. Updated during semester Includes transfer courses.

baninst1.cos_f_get_lhrs_ern(spriden_pidm, 'UG','O')

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.

baninst1.cos_f_get_lhrs_gpa(spriden_pidm, 'UG','O')

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.

baninst1.cos_f_get_lhrs_ern(spriden_pidm, 'UG','T')

TRANSFER_IN_UNITS

Units transferred from outside institution

baninst1.cos_f_get_lgpa(spriden_pidm, 'UG','T')

TRANSFER_IN_GPA

GPA of units transferred from outside institution

baninst1.cos_f_get_tgpa(spriden_pidm, sfbetrm_term_code, 'UG','O') as term_gpa,

TERM_GPA

Float – GPA for this term. Includes transfer units.

baninst1.cos_f_get_tenrl(spriden_pidm, sfbetrm_term_code)

TERM_HOURS

Float – Hours enrolled for this term (R% registration). Same as units.

[Note: used in StudentFundType view]

baninst1.cos_f_get_tpass(spriden_pidm, sfbetrm_term_code)

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;

baninst1.cos_f_get_tpass_abc(spriden_pidm, sfbetrm_term_code)

TERM_PASSED_ABC

Float – Units passed for courses taken this term with grade of A, B, or C (including + and – grades)

F_GET_ACCUM_TUNITS_ATT (spriden_pidm,'000000',

sfbetrm_term_code, 'UG')

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.

F_GET_ACCUM_TUNITS_PASSED (spriden_pidm,'000000',

sfbetrm_term_code, 'UG')

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.

reportsnet.F_GET_ACCUM_TGPA (spriden_pidm,

'000000', sfbetrm_term_code, 'UG', 'I')

TERM_ACCUM_GPA

Accumulative GPA from the earliest term available in Banner to the term of this record.

reportsnet.f_get_univ_units (spriden_pidm,

'DA', sfbetrm_term_code)

COS_DEGREE_UNITS_ATT

Units attempted for degree for the term on this record at COS. This does NOT include Credit/No Credit courses.

reportsnet.f_get_univ_qual_pts (spriden_pidm,

'DA', sfbetrm_term_code)

COS_DEGREE_PTS

Grade points for degree for the term on this record at COS

reportsnet.f_get_univ_units_passed (spriden_pidm, 'DA', sfbetrm_term_code)

COS_DEGREE_UNITS_PASSED

Units passed for degree for the term on this record at COS. This does include Credit/No Credit courses.

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'DA', sfbetrm_term_code)

/ reportsnet.f_get_univ_units (spriden_pidm, 'DA',sfbetrm_term_code)

COS_DEGREE_GPA

Grade Point Average for degree for the term on this record at COS

reportsnet.f_get_accum_univ_qual_pts (spriden_pidm, 'DA', '000000', sfbetrm_term_code)

/ reportsnet.f_get_accum_univ_units (spriden_pidm, 'DA', '000000', sfbetrm_term_code)

TERM_ACCUM_COS_DEGREE_GPA

Accumulative GPA for degree at COS from the earliest term available in Banner to the term of this record

reportsnet.f_get_univ_units (spriden_pidm, 'TCSU', sfbetrm_term_code)

CSU_DEGREE_UNITS_ATT

Units attempted for degree for the term on this record transferable to a California State University

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TCSU', sfbetrm_term_code)

CSU_DEGREE_PTS

Grade points for degree for the term on this record transferable to a California State University

reportsnet.f_get_univ_units_passed (spriden_pidm,'TCSU',sfbetrm_term_code)

CSU_DEGREE_UNITS_PASSED

Units passed for degree for the term on this record transferable to a California State University

reportsnet.f_get_accum_univ_units (spriden_pidm, 'TCSU',

'000000', sfbetrm_term_code)

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

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TCSU', sfbetrm_term_code)

/ reportsnet.f_get_univ_units (spriden_pidm, 'TCSU', sfbetrm_term_code)

CSU_DEGREE_GPA

Grade Point Average for degree for the term on this record transferable to a California State University

reportsnet.f_get_accum_univ_qual_pts (spriden_pidm,'TCSU', '000000', sfbetrm_term_code)

/ reportsnet.f_get_accum_univ_units (spriden_pidm, 'TCSU', '000000', sfbetrm_term_code)

TERM_ACCUM_CSU_DEGREE_GPA

Accumulative GPA for a CSU degree from the earliest term available in Banner to the term of this record

reportsnet.f_get_univ_units (spriden_pidm, 'TUC',

sfbetrm_term_code)

UC_DEGREE_UNITS_ATT

Units attempted for degree for the term on this record transferable to a University of California

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TUC',

sfbetrm_term_code)

UC_DEGREE_PTS

Grade points for degree for the term on this record transferable to a University of California

reportsnet.f_get_univ_units_passed (spriden_pidm, 'TUC',

sfbetrm_term_code)

UC_DEGREE_UNITS_PASSED

Units passed for degree for the term on this record transferable to a University of California

reportsnet.f_get_accum_univ_units (spriden_pidm, 'TUC',

'000000', sfbetrm_term_code)

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

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TUC',

sfbetrm_term_code)

/ reportsnet.f_get_univ_units_passed (spriden_pidm,

'TUC', sfbetrm_term_code)

UC_DEGREE_GPA

Grade Point Average for degree for the term on this record transferable to a University of California

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)

TERM_ACCUM_UC_DEGREE_GPA

Accumulative GPA for a UC degree from the earliest term available in Banner to the term of this record

baninst1.cos_f_univ_units(spriden.spriden_pidm, 'DA')

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.

baninst1.cos_f_univ_qual_pts(spriden.spriden_pidm, 'DA')

OVERALL_COS_DEGREE_PTS

Total grade points for degree (for all terms including terms older than those in the warehouse) at COS

baninst1.cos_f_univ_passed(spriden.spriden_pidm, 'DA')

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.

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')

OVERALL_COS_DEGREE_

GPA

Grade Point Average for degree (for all terms including terms older than those in the warehouse)at COS

baninst1.cos_f_univ_units(spriden.spriden_pidm, 'TCSU')

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

baninst1.cos_f_univ_qual_pts(spriden.spriden_pidm, 'TCSU')

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

baninst1.cos_f_univ_passed(spriden.spriden_pidm, 'TCSU')

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

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')

OVERALL_CSU_DEGREE_GPA

Grade Point Average for degree (for all terms including terms older than those in the warehouse) transferable to a California State University

baninst1.cos_f_univ_units(spriden.spriden_pidm, 'TUC')

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

baninst1.cos_f_univ_qual_pts(spriden.spriden_pidm, 'TUC')

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

baninst1.cos_f_univ_passed(spriden.spriden_pidm, 'TUC')

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

baninst1.cos_f_univ_qual_pts (spriden_pidm, 'TUC')

/ baninst1.cos_f_univ_units (spriden_pidm, 'TUC')

OVERALL_UC_DEGREE_GPA

Grade Point Average for degree (for all terms including terms older than those in the warehouse) transferable to a University of California

sgbeops.SGBEOPS_EOPS_CODE

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

substr(f_get_student_is_disa(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,1)

DRC

Student is disabled (Y). This value can only be determined once Sandy Gaither has entered this student as a DRC student in Banner.

substr(f_get_disa_codes(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,10)

DRC_CODES

List of disability codes (1 character each, no delimiter, up to 10 codes)

SGBUSER.SGBUSER_SUDA_CODE

VATEA

Vocational And Technological Education Act (A/N)

A: student in vocational program

N: in vocational course, but not vocational major

SUBSTR(COS_GENERAL_RESTR.F_IS_PUENTE_BY_PIDM(spriden.SPRIDEN_PIDM), 1, 1)

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.

substr(f_get_cohort_codes(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,10)

COHORT_CODES

Currently limited to codes A through E

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

REGISTERED_CLASSES

Number of classes for which the student has registered

f_get_campuses (spriden.spriden_pidm,

sfbetrm.sfbetrm_term_code), 1, 100)

CAMPUSES

Pipe delimited string of 3-char campus codes for all campuses at which the student is enrolled in courses.

nvl(greatest(sfbetrm.sfbetrm_activity_date, spraddr.spraddr_activity_date, spriden.spriden_activity_date, sprtele.sprtele_activity_date, spbpers.spbpers_activity_date), sfbetrm.sfbetrm_activity_date) as activity_date

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.

substr(f_get_cast_code(spriden.spriden_pidm),1,2) as CAST_CODE,_cast_code

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

substr(BANINST1.cos_f_get_email(spriden.SPRIDEN_PIDM),1,90) as EMAIL

EMAIL

The Student’s email address

substr(f_get_visa_type(spriden.SPRIDEN_PIDM, stvterm.STVTERM_START_DATE, stvterm.STVTERM_END_DATE),1,2)

VISA_TYPE

see VISA type code in Appendix

substr(f_get_calworks_code(spriden.SPRIDEN_ID, sfbetrm.sfbetrm_term_code),1,1)

CALWORKS

2: CALWORKS self referral participation

3: CALWORKS county referral participation

Null if not participating in CALWORKS

substr(f_get_veteran_status(spriden.SPRIDEN_PIDM, sfbetrm.sfbetrm_term_code),1,1)

VETERAN

‘Y’ if the student was a veteran for the term of this record. Null otherwise.

cast(cosbwckrgtm.cos_f_calc_reg_appt(

SPRIDEN.SPRIDEN_PIDM, cos_f_get_next_term()) as date)

REGIS_APPT

The date of the student’s next registration appointment

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';

ATHLETE

‘Y’ if the student is an athlete for the term of this record (at least one record in SGRSPRT where SGRSPRT_SPST_CODE = ‘A’)

select 'Y' from sfrsrpo s where s.sfrsrpo_term_code = term

and s.SFRSRPO_PIDM = pidm

and s.SFRSRPO_ROVR_CODE = 'FYE'

FIRST_YR_EXPERIENCE

‘Y’ if the student is in First Year Experience for the term of this record.

select 'Y' from sfrsrpo s where s.sfrsrpo_term_code = term

and s.SFRSRPO_PIDM = pidm

and s.SFRSRPO_ROVR_CODE = 'SYE'

SECOND_YR_EXPERIENCE

‘Y’ if the student is in Second Year Experience for the term of this record.

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

WIA

‘Y’ if the student is WIA (Workforce Investment Act) for the term of this record.

decode(FINANCIAL_AID1_NON_BOGG.AWARD_PAID_TERM, null, 'N', 0, 'N', 'Y')

PELL

‘Y” if the Student was awarded PELL for the term of this record. ‘N’ otherwise.

RCRAPP2_MODEL_CDE when RCRAPP2_INFC_CODE = 'EDE'

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

(BANINST1.COS_GENERAL.F_MAX_SIGNED_EDPLAN_DATE (s.SPRIDEN_PIDM, f.sfbetrm_term_code)) AS Ed_Plan_Date

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.

SGBSTDN.SGBSTDN_EDLV_CODE

ED_LEVEL_CODE

A three position alphanumeric field which indicate the highest level of the education that the student completed

SUBSTR (REPORTSNET.F_GET_STUDENT_IS_FIRST_GEN (SPRIDEN.SPRIDEN_PIDM, sfbetrm.sfbetrm_term_code), 1, 1 )

FIRST_GEN

‘Y’ / ‘N’ if Student is a first-generation college student (based on 2 Parent Education Level questions on CCCApply).

REPORTSNET.F_GET_STUDENT_ORIENTATION_DATE

(SPRIDEN.SPRIDEN_PIDM)

ORIENTATION

The date the student attended orientation. If null, we have no record of the student attending orientation.

SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'CLAB'),1,1)

AP_CALCAB_SCORE

AP test score on CALC AB Exam

SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'CLBC'),1,1)

AP_CALCBC_SCORE

AP test score on CALC BC Exam

SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'STAT'),1,1)

AP_STATS_SCORE

AP test score on STATS Exam

SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'ELNG'),1,1)

AP_LANGCOMP_SCORE

AP test score on LANG & COMP Exam

SUBSTR (REPORTSNET.F_GET_TEST_SCORE_AP (spriden_pidm, 'ELIT'),1,1)

AP_LITCOMP_SCORE

AP test score on LIT & COMP Exam

REPORTSNET.F_GET_STUDENT_IS_FOSTER_YOUTH (

SPRIDEN_PIDM, SFBETRM_TERM_CODE)

FOSTER_YOUTH

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

REPORTSNET.F_GET_STUDENT_IS_BASIC_SKILLS (in_pidm, in_term)

BASIC_SKILLS

‘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

SUBSTR(

COS_PIN_REG.cos_is_hs(spriden_pidm, sfbetrm_term_code)

, 1, 15)

HS_STATUS

Values returned:

UPHS - UPHS student for that term

VTEC - VTEC student for that term

Y - other concurrent student

Null - not a concurrent student

SUBSTR(baninst1.COS_GENERAL_RESTR.f_is_CareerAdvAcademyPart(SPRIDEN_PIDM, sfbetrm.sfbetrm_term_code), 1, 1)

CAREER_ADVACAD

Y/N – If Student is in CAA (Career Advancement Academy Participant)

SUBSTR(BANINST1.cos_general.f_is_on_deans_or_pres_list(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE), 1, 1)

DEAN_PRES_CODE

D/P/NULL – if a student is on the President’s or Dean’s List for a term

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)

DEANS_PRES_DESC

If null, it doesn’t put a description. If P or D, it will return the description listed in Banner on the STVASTD table

SGBSTDN.SGBSTDN_RESD_CODE

RESD_CODE

Residency code from the student’s SGBSTDN record by term

STVRESD.STVRESD_DESC

RESD_DESC

Residency description for the student’s residency code

SVBSCAA.SVBSCAA_CAA_IND

CAA

Indicates if a student is a part of the CAA (Career Advancement Academy), which returns a Y or null.

,SUBSTR(baninst1.cos_general.f_trio_code(spriden.spriden_pidm, SFBETRM.SFBETRM_TERM_CODE), 1, 10)

TRIO_CODE

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)

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.

COS_STU_EMAIL.STUDENT_PASSWORD AS ENCRYPTED_PASSWORD

ENCRYPTED_PASSWORD

Students encrypted password for SSO purposes.

SUBSTR (SZRCCAP_PARENT_EDLV_1, 1, 1) PARENT_EDLV_1_CODE

PARENT_EDLV_1_CODE

Parent educational level code valid options based on MIS values as of 3/18/2015 per Research Office request.

NOTE: The description from the CCCApply application do not match the description from MIS.

MIS Values and description:

Code

Description

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

CCCApply application:

Code

Description

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

SUBSTR(BANINST1.COS_GENERAL_CROSSWALK.F_GET_PARENT_ED_LVL_DESC (ccap.SZRCCAP_PARENT_EDLV_1), 1, 60) PARENT_EDLV_1_DESC

PARENT_EDLV_1_DESC

Description above with code.

SUBSTR (SZRCCAP_PARENT_EDLV_2, 1,1) PARENT_EDLV_2_CODE

PARENT_EDLV_2_CODE

Description above with code.

SUBSTR (BANINST1.COS_GENERAL_CROSSWALK.F_GET_PARENT_ED_LVL_DESC (ccap.SZRCCAP_PARENT_EDLV_2), 1, 60) PARENT_EDLV_2_DESC,

PARENT_EDLV_2_DESC

Description above with code.

SVBSFYS_HOME_CODE

Homeless_Status

Homeless status as determined by the student’s application

key summary type created updated due assignee reporter priority status resolution
Refresh

BANINST1.SV_MIS_SB_STU.f_get_sexual_orientation

 The logic should work as follows: 

If record exists in SVBSXLO and the student orientation didn't change, do nothing. 

If record exists in SVBSXLO and the student orientation did change, update SVBSXLO_SXLO_CODE, SVBSXLO_ACTIVITY_DATE, and  SVBSXLO_DATA_ORIGIN from CCCApply data 

If no record exists in SVBSXLO, insert new record from CCCApply data. 

Sexual_Orientation

https://sequoias.atlassian.net/browse/AP-137

Sexual Orientation: Self-declared sexual orientation code value. Self-reported and ultimately comes from the Student’s application. This is stored in Banner in the table:  SVBSXLO  and the column: SVBSXLO_SXLO_CODE

Uses SB37 DED Definitions

SPBPERS_GNDR_CODE

Transgender_Status

Known misnomer as it encompasses more than just Transgender Status.

SPBPERS_GNDR_CODE is populated from CCCApply, but the Banner data architecture does not allow for a 1-to-1 relationship with CCCApply data.

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:

Banner & MIS 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

VARCHAR2

4

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.

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

A Student’s self-reported HS GPA as reported on the Student’s application.

SZRCCAP_HIGHEST_ENGL_COURSE

SR_HS_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

The highest English Grade a student has received. Self-reported on the Student’s application.

SZRCCAP_HIGHEST_MATH_TAKEN    

SR_HS_Highest_Math_Course

The highest Math course a student has taken. Self-reported on the Student’s application.

SZRCCAP_HIGH_MATH_TAKEN_GRADE 

SR_HS_Highest_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

The highest passing Math Grade. Self-reported on the Student’s application.

SZRCCAP_HIGHEST_MATH_PASSED

SR_HS_Highest_Math_Passed

The highest Math course a student has passed. Self-reported on the Student’s application

SZRCCAP_YEARS_ENGLISH         

SR_HS_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

Lowest English grade received in High School. Self-reported on the Student’s application.

SZRCCAP_CURRENT_MATH

SR_HS_Current_Math

A Student’s current High School Math class. Self-reported on the Student’s application.

SUBSTR (
Baninst1.cos_general_restr.f_Is_Student_enrolled_Online
(
  f.sfbetrm_pidm,f.SFBETRM_TERM_CODE), 1, 1.

Distance_Ed_Student

Checks whether a student is enrolled in any online classes and returns a ‘Y’ or an ‘N’

  • No labels