StudentPuente table

Oracle Field/Calc

DW Field

Description or Sample

Date added

spriden.SPRIDEN_ID

Student_Id

Banner Id (@12345678)

spbpers.SPBPERS_SSN

SSN

123456789

szrpstu_pidm

PIDM

Up to 10-digit unique Banner id for student

spriden_last_name

Last_Name

spriden_first_name

First_Name

spriden_mi

Middle_Name

nvl(spriden_last_name, '') || ', ' || nvl(spriden_first_name, '') || ' ' || nvl(spriden_mi, '')

Student_Name

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

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

Phone

SZRPSTU_CAST_CODE

Academic_Status_Code

Combined Academic Standing Code:

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

f_get_desc_fnc('STVCAST', SZRPSTU_CAST_CODE, 30)

Academic_Status

(see Academic_Status_Code)

spbpers.spbpers_sex

Gender

f_get_ethn_category(spbpers_ethn_code,'ETCT')

Ethnicity_Code

See Ethnicity Code in Appendix

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

(see Ethnicity_Code)

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

Major

(select stvterm_desc from stvterm where stvterm_code = szrpstu_first_term_code)

First_Semester

First Semester at COS (Fall 2003)

SZRPSTU_ENGL_251_CRN

Eng251_CRN

CRN of English 251 course in which the student enrolled

SZRPSTU_ENGL_251_TERM_CODE

Eng251_Term

Term of English 251 course in which the student enrolled (200710)

(select stvterm_desc from stvterm where stvterm_code = szrpstu_engl_251_term_code)

Eng251_Semester

Semester of English 251 course in which the student enrolled (Fall 2006)

SZRPSTU_ENGL_251_GRADE

Eng251_Grade

Grade achieved in English 251

SZRPSTU_ENGL_1_CRN

Eng1_CRN

CRN of English 1 course in which the student enrolled

SZRPSTU_ENGL_1_TERM_CODE

Eng1_Term

Term of English1 course in which the student enrolled (200710)

(select stvterm_desc from stvterm where stvterm_code = szrpstu_engl_1_term_code)

Eng1_Semester

Semester of English 1 course in which the student enrolled (Fall 2006)

SZRPSTU_ENGL_1_GRADE

Eng1_Grade

Grade achieved in English 1

SZRPSTU_LAST_TERM_CODE

Last_Term

Last Term the student attended COS (200710)

(select stvterm_desc from stvterm where stvterm_code = szrpstu_last_term_code)

Last_Semester

Last Semester the student attended COS (Fall 2006)

SZRPSTU_UC_UNITS

UC_Units

Number of units passed that are transferable to University of California

SZRPSTU_UC_GPA

UC_GPA

GPA for UC units

SZRPSTU_CSU_UNITS

CSU_Units

Number of units passed that are transferable to California State University

SZRPSTU_CSU_GPA

CSU_GPA

GPA for CSU units

SZRPSTU_COS_UNITS

COS_Units

Number of units passed that are transferable to COS

SZRPSTU_COS_GPA

COS_GPA

GPA for COS units

SZRPSTU_DEGC_CODE

Degree_Earned

AA, AS, etc

(select stvterm_desc from stvterm where stvterm_code = szrpstu_degree_term)

Degree_Semester

Semester in which the degree was achieved

SZRPSTU_TRANS_DATE

Trans_Semester

Semester at which the student transferred out of COS

SZRPSTU_TRANS_INST

Trans_Inst

Type of Institution to which the student transferred (UC, CSU, IND, etc)

SZRPSTU_TRANS_CAMPUS

Trans_Campus

Name of the transfer institution (Santa Barbara, San Diego, Chico, etc)

spraddr.spraddr_street_line1

Address

spraddr.spraddr_city

City

spraddr.spraddr_stat_code

State

spraddr.spraddr_zip

Zip

decode(f_sgbstdn_fields (spriden.spriden_pidm, szrpstu.SZRPSTU_LAST_TERM_CODE , 'STU_TYPE'), null, null, substr(f_get_desc_fnc('STVSTYP',f_sgbstdn_fields (spriden.spriden_pidm, szrpstu.SZRPSTU_LAST_TERM_CODE,'STU_TYPE'),30),1,30) )

Status

See Student Type Codes in Appendix

cos_f_get_tenrl(spriden.spriden_pidm, szrpstu.SZRPSTU_LAST_TERM_CODE

Term_Hours

Float – Hours enrolled for this term. Same as units.

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

Overall_GPA

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

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

Overall_Hrs_Earned

Float – earned D > and credits

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

Overall_Hrs_Passed

Float – hours used for gpa

case when substr(f_get_desc_fnc('STVGOAL',SGBSTDN_EGOL_CODE,30),1,1) = '*' then ''

else substr(f_get_desc_fnc('STVGOAL',SGBSTDN_EGOL_CODE,30),1,30)

end

Goal

50-char desc of Goal

sgbeops.SGBEOPS_EOPS_CODE

EOPS

Code From STVEOPS (EOPS program). The ‘E’ value for means the Student is part of the EOPS program.  If it’s anything other than ‘E’, they are not:

E: EOPS-Elig next term

G: EOPS-Successfully Comp. Goal

P: EOPS-failed Cont/CARE-New CARE

C: EOPS-Chose nEOPS/CARE-w/oWaiv

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

sgrdisa.SGRDISA_PRIMARY_IND

DRC

Disabled Student (Y/N)

substr(SGBUSER.SGBUSER_SUDA_CODE,1,1)

VATEA

Vocational And Technological Education Act (A/N)

A: student in vocational program, N: in vocational course, but not vocational major

SZRPSTU_ACTIVITY_DATE

Activity_Date

Date when this record was last updated

CAST(BANINST1.cos_general.f_get_puente_status_code(szrpstu_pidm) AS VARCHAR2(2))

PUENTE_CODE

A function that returns a Puente student status code

1.15.2015 - ryanwh

SVVPNTE.SVVPNTE_DESC

PUENTE_CODE_DESC

A description of the Puente_code

1.15.2015 - ryanwh