StudentStem

Oracle Field/Calc

DW Field

Description or Sample

Date Added

SGBSTDN.SGBSTDN_TERM_CODE_EFF as term,

TERM

200710 (Fall = 10, Spring = 20, Summer = 30)

ryanwh - 2.25.2015

spriden.spriden_id as studentid,

STUDENTID

Banner Id (@12345678)

ryanwh - 2.25.2015

spriden.spriden_pidm as pidm,

PIDM

Up to 10-digit unique Banner id for student

ryanwh - 2.25.2015

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

STUDENTNAME

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

ryanwh - 2.25.2015

spraddr.spraddr_street_line1 as address,

ADDRESS

Address at the start of the record’s term

ryanwh - 2.25.2015

spraddr.spraddr_city as city,

CITY

City at the start of the record’s term

ryanwh - 2.25.2015

spraddr.spraddr_stat_code as state,

STATE

State at the start of the record’s term

ryanwh - 2.25.2015

spraddr.spraddr_zip as zip,

ZIP

Zip at the start of the record’s term

ryanwh - 2.25.2015

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

PHONE

Current phone number

ryanwh - 2.25.2015

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

EMAIL

The Student’s email address

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

SGBSTDN.SGBSTDN_RESD_CODE

RESD_CODE

Residency code from the student’s SGBSTDN record by term

ryanwh - 2.25.2015

STVRESD.STVRESD_DESC

RESD_DESC

Residency description for the student’s residency code

ryanwh - 2.25.2015

sgbstdn.SGBSTDN_PROGRAM_1

PROGRAM

The Program of the Major

ryanwh - 2.25.2015

SGBSTDN.SGBSTDN_MAJR_CODE_1

MAJOR_CODE

Numeric code for the student’s major

ryanwh - 2.25.2015

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

MAJOR

Description of the Major_Code

ryanwh - 2.25.2015

spbpers.spbpers_sex as gender,

GENDER

1-char

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

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

ENG251_ELIG

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

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

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

MATHPLACE

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

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

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

READ_LEVEL

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

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

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

CELSA_ESL

Score on CELSA ESL test

ryanwh - 2.25.2015

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

ryanwh - 2.25.2015

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

ESL_PLACEMENT

ESL Placement Level

ryanwh - 2.25.2015