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

DW Field

Oracle Field/Calc

 

Description (Sample)

TERM

sfbetrm.sfbetrm_term_code as term,

https://sequoias.atlassian.net/browse/GOVPUB-10

SEMESTER

stvterm.stvterm_desc as semester,

Semester YYYY is the typical format for how this description field is populated. (Spring 2022)

STUDENTID

spriden.spriden_id as 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

spbpers.spbpers_ssn as 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

spriden.spriden_pidm as 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

spriden_last_name as 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

spriden_first_name as 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

spriden_mi as 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

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

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

nvl(spriden_mi, '') as studentname,

Concatenation of LASTNAME, FIRSTNAME MIDINIT. (Bond, James Herbert)

ADDRESS

spraddr.spraddr_street_line1 as address,

Address at the start of the record’s term

CITY

spraddr.spraddr_city as city,

City at the start of the record’s term

STATE

spraddr.spraddr_stat_code as state,

State at the start of the record’s term

ZIP

spraddr.spraddr_zip as zip,

Zip at the start of the record’s term

PHONE

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

Current phone number. Concatenation of Area Code and Phone Number ((559) 8675309)

STATUS

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,

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

sgbstdn.SGBSTDN_PROGRAM_1

The program of the major

PROG_DEPT

g.SGBSTDN_DEPT_CODE

Department code for student’s program

Has snapshot data on SGASTDN instead of the maintained data on SOACURR.

PROG_GE_PATTERN

g.SGBSTDN_MAJR_CODE_CONC_1

Student’s GE pattern for programs

IN_STATE_IND

MAJOR_CODE

SGBSTDN.SGBSTDN_MAJR_CODE_1

Numeric code for the student’s major

MAJOR

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

Description of the MAJOR_CODE

GOAL

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

Student’s stated goal for applying. Happens during application process and overwhelmingly without counselor guidance.

GOAL_INFORMED_EFF_TERM

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

 

The term at which the Informed Goal codes became effective.

FIN_AID_IND_BY_MJR

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)

This is a Financial Aid indicator based on the major code. ('Y', 'N')

CTE_IND_BY_MJR

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)

This is a CTE indicator based on the major code. ('Y', 'N')

GOAL_INFORMED_CODES

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

 

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

SPBPERS.SPBPERS_SEX as 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

spbpers.spbpers_birth_date as birthdate,

Legal date of birth. Often populated off CCCApply Application or manual entry by Human Resources.

AGE

f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

Current age of person.

AGEATTERM

f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

Person’s age at term.

CENSUSAGEGROUP

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

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

SPBPERS.SPBPERS_MRTL_CODE

A person’s state of being married or not married.

Refer to STVMRTL for current codes.

ETHNICITY

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

No longer being maintained or useful. Likely superseded by another field or solution.

NEW_ETHNICITY

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

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

SUBSTR (f_get_races (spriden_pidm), 1, 60)

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.

Implementing SB38 (race_ethnic_full) a field length deficiency was discovered. AP-224 - Getting issue details... STATUS

This is PII:

Error rendering macro 'excerpt-include' : No location was provided.

RACES_DESC

SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)

Description of RACES code(s). Pipe '|' delimited.

RACE_ETHN_CDE

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

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.

  1. 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.

  2. 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.  

  3. 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.

  4. 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.

  5. 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

  6. 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.

RACE_ETHN_CDE_DESC

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

Race description according to RACE_ETHN_CDE andhttps://sequoias.atlassian.net/wiki/spaces/BANNER/pages/1173520436

DW Field

Oracle Field/Calc

Description (Sample)

HIGHSCHOOL

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

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

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

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

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

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

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

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

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

Highest LESL Placement score.

OVERALL_GPA

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

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

OVERALL_HRS_EARNED

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

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

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

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

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

Units transferred from outside institution

TRANSFER_IN_GPA

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

GPA of units transferred from outside institution

TERM_GPA

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

Float – GPA for this term. Includes transfer units.

TERM_HOURS

baninst1.cos_f_get_tenrl(spriden_pidm, sfbetrm_term_code)

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

[Note: used in StudentFundType view]

TERM_PASSED

baninst1.cos_f_get_tpass(spriden_pidm, sfbetrm_term_code)

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

baninst1.cos_f_get_tpass_abc(spriden_pidm, sfbetrm_term_code)

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

TERM_ACCUM_UNITS_ATT

F_GET_ACCUM_TUNITS_ATT (spriden_pidm,'000000',

sfbetrm_term_code, 'UG')

 

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

F_GET_ACCUM_TUNITS_PASSED (spriden_pidm,'000000',

sfbetrm_term_code, 'UG')

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

reportsnet.F_GET_ACCUM_TGPA (spriden_pidm,

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

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

COS_DEGREE_UNITS_ATT

reportsnet.f_get_univ_units (spriden_pidm,

'DA', sfbetrm_term_code)

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

COS_DEGREE_PTS

reportsnet.f_get_univ_qual_pts (spriden_pidm,

'DA', sfbetrm_term_code)

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

COS_DEGREE_UNITS_PASSED

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

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

COS_DEGREE_GPA

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

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

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

TERM_ACCUM_COS_DEGREE_GPA

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)

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

CSU_DEGREE_UNITS_ATT

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

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

CSU_DEGREE_PTS

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

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

CSU_DEGREE_UNITS_PASSED

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

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

TERM_ACCUM_CSU_DEGREE_

UNITS_PASSED

reportsnet.f_get_accum_univ_units (spriden_pidm, 'TCSU',

'000000', sfbetrm_term_code)

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

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

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

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

TERM_ACCUM_CSU_DEGREE_GPA

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)

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

UC_DEGREE_UNITS_ATT

reportsnet.f_get_univ_units (spriden_pidm, 'TUC',

sfbetrm_term_code)

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

UC_DEGREE_PTS

reportsnet.f_get_univ_qual_pts (spriden_pidm, 'TUC',

sfbetrm_term_code)

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

UC_DEGREE_UNITS_PASSED

reportsnet.f_get_univ_units_passed (spriden_pidm, 'TUC',

sfbetrm_term_code)

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

TERM_ACCUM_UC_DEGREE_UNITS_PASSED

reportsnet.f_get_accum_univ_units (spriden_pidm, 'TUC',

'000000', sfbetrm_term_code)

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

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

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

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

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

OVERALL_COS_DEGREE_

UNITS_PASSED

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

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

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

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

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

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

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

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

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

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

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

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

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

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

sgbeops.SGBEOPS_EOPS_CODE

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

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

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

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

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

VATEA

SGBUSER.SGBUSER_SUDA_CODE

Vocational And Technological Education Act (A/N)

A: student in vocational program

N: in vocational course, but not vocational major

PUENTE

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

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

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

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

f_get_campuses (spriden.spriden_pidm, sfbetrm.sfbetrm_term_code), 1, 100)

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

ACTIVITY_DATE

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

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

substr(f_get_cast_code(spriden.spriden_pidm),1,2) as 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

EMAIL

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

The Student’s email address

VISA_TYPE

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

see VISA type code in Appendix

CALWORKS

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

2: CALWORKS self referral participation

3: CALWORKS county referral participation

Null if not participating in CALWORKS

VETERAN

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

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

REGIS_APPT

cast(cosbwckrgtm.cos_f_calc_reg_appt(

SPRIDEN.SPRIDEN_PIDM, cos_f_get_next_term()) as date)

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

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

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

DEPENDENT

RCRAPP2_MODEL_CDE when RCRAPP2_INFC_CODE = 'EDE'

 

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

(BANINST1.COS_GENERAL.F_MAX_SIGNED_EDPLAN_DATE (s.SPRIDEN_PIDM, f.sfbetrm_term_code)) AS 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

SGBSTDN.SGBSTDN_EDLV_CODE

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

FIRST_GEN

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

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

ORIENTATION

REPORTSNET.F_GET_STUDENT_ORIENTATION_DATE

(SPRIDEN.SPRIDEN_PIDM)

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

Error rendering macro 'excerpt-include' : No link could be created for 'Student table (page 4)'.

  • No labels

0 Comments

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account.