Page Comparison

Excerpt

name	student_table_1

Oracle Field/Calc

DW Field

Description (Sample)

sfbetrm.sfbetrm_term_code as term,

TERM

Insert excerpt

	Common Definitions
	Common Definitions
name	TERM
nopanel	true

stvterm.stvterm_desc as semester,

SEMESTER

Insert excerpt

	Common Definitions
	Common Definitions
name	SEMESTER
nopanel	true

spriden.spriden_id as studentid,

STUDENTID

Insert excerpt

	Common Definitions
	Common Definitions
name	StudentIDBANNERID
nopanel	true

spbpers.spbpers_ssn as ssn,

SSN

Insert excerpt

	Common Data FieldsDefinitions
	Common Data FieldsDefinitions
name	SSN
nopanel	true

spriden.spriden_pidm as pidm,

PIDMUp to 10-digit unique Banner id for student

Insert excerpt

	Common Definitions
	Common Definitions
name	PIDM
nopanel	true

spriden_last_name as LastName,

LASTNAME

Insert excerpt

	Common Definitions
	Common Definitions
name	LASTNAME
nopanel	true

spriden_first_name as FirstName,

FIRSTNAME

Insert excerpt

	Common Definitions
	Common Definitions
name	FIRSTNAME
nopanel	true

spriden_mi as MidInit,

MIDINIT

Insert excerpt

	Common Definitions
	Common Definitions
name	MIDINIT
nopanel	true

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

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

nvl(spriden_mi, '') as studentname,

STUDENTNAME

Insert excerpt

	Common Definitions
	Common Definitions
name	STUDENTNAME

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

nopanel	true

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

Insert excerpt

	Common Definitions
	Common Definitions
name	PHONE

Current phone number

nopanel	true

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

. (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 MajorMAJOR_CodeCODE

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 on 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 on 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,

GENDER

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

GENDER

M

F

N

Null

Insert excerpt

	Common Definitions
	Common Definitions
name	LEGALSEX
nopanel	true

spbpers.spbpers_birth_date as birthdate,

BIRTHDATE

Insert excerpt

	Common Definitions
	Common Definitions
name	BIRTHDATE

datetime

nopanel	true

f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

AGE

Insert excerpt

nopanel
	Common Definitions
	Common Definitions
name	AGE

float

true

f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

AGEATTERM

Insert excerpt

nopanel
	Common Definitions
	Common Definitions
name	AGEATTERM

Age at term

true

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

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

Insert excerpt

	Common Definitions
	Common Definitions
name	CENSUSAGEGROUP
nopanel	true

SPBPERS.SPBPERS_MRTL_CODE

MARITAL_CODE

D: Divorced

M: Married

N: Not Known

P: Domestic Partner

R: Separated

S: Single

W: Widowed

Insert excerpt

	Common Definitions
	Common Definitions
name	MARITALSTATUS
nopanel	true

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

Insert excerpt

	Common Definitions
	Common Definitions
name	DEPRECATED
nopanel	true

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

Anchor

	race_ethn_cde
	race_ethn_cde

Looks at the first character that is returned.

Info

IS_HEADER on 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.

If a student has selected SPBPERS_ETHN_CDE = ‘2’ OR has select a number of races that equates ETHN_CODE to include ‘H’, then that student will be listed as Hispanic.
If a student has selected a number of races WHERE SPBPERS_ETHN_CDE <> ‘2’ AND ETHN_CODE equates to 1 – 2 codes including ‘F’ and potentially ‘A’, AND the races that equate to ETHN_CODE = ‘A’ have IS_HEADER = ‘Y’, the student will be returned Filipino.
If a student has selected a number of races WHERE SPBPERS_ETHN_CDE <> ‘2’ AND ETHN_CODE equates to 1 -2 codes including ‘A’ and potentially ‘F’, AND the races that equate to ETHN_CODE = ‘A’ have IS_HEADER <> ‘Y’
If a student has selected a number of races that equate to TWO or MORE unique ETHN_CODE values that DOES NOT include ‘H’ OR SPBPERS_ETHN_CODE <> ‘2’, the student will be returned as Multi-Ethnic.
If a student has selected a number of races that equate to ONE ETHN_CODE AND SPBPERS_ETHN_CDE <> ‘2’, the student will be returned as derived ETHN_CODE
Else Unknown

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

RACE_ETHN_CDE_DESC

Anchor

	race_ethn_cde_desc
	race_ethn_cde_desc

Race description according to RACE_ETHN_CDE and COS_GEN.CODATAMART_ETHN_VALIDATION_CODES

Version	Old Version 28	New Version 29
Changes made by	Jordan Lamb	Jordan Lamb
Saved on	Apr 07, 2022	Apr 07, 2022

Versions Compared

Key