Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Excerpt
namestudent_table_1

Oracle Field/Calc

 

DW Field

Description (Sample)

Data Type

Field Length

sfbetrm.sfbetrm_term_code as term,

TERM

Insert excerpt
Common Definitions
Common Definitions
nameTERM
nopaneltrue
nvarchar(6)

6

stvterm.stvterm_desc as semester,

SEMESTER

Insert excerpt
Common Definitions
Common Definitions
nameSEMESTER
nopaneltrue

 

 

spriden.spriden_id as studentid,

STUDENTID

Insert excerpt
Common Definitions
Common Definitions
nameStudentID
nopaneltrue

 

 

spbpers.spbpers_ssn as ssn,

SSN

123456789

 

 

Insert excerpt
Common Data Fields
Common Data Fields
nameSSN
nopaneltrue

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,

Info

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

GENDER

M

F

N

NullVARCHAR2

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

 

 

Insert excerpt
Common Data Fields
Common Data Fields
nameDEPRECATED
nopaneltrue

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.

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

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

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

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

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

  6. Else Unknown

VARCHAR2

1

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