Versions Compared

Version Old Version 22 New Version Current
Changes made by

Jordan Lamb

Jordan Lamb

Saved on

Key

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

Looks at the first character that is returned.

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

    • Race description according to

    DW Field

    Oracle Field/Calc

     

    DW Field

    Description (Sample)

    Data TypeTERM

    Field Length

    sfbetrm.sfbetrm_term_code as term,

    TERM

    Fall terms: %10

    Spring terms: %20

    Summer terms: %30

    int

    6

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

    SEMESTER

    stvterm.stvterm_desc as semester,

    SEMESTER

    Fall 2006

     

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameTERM
    nopaneltrue

    STUDENTID

    spriden.spriden_id as studentid,

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameSTUDENTID

    Banner ID (@12345678)

     

    nopaneltrue

    SSN

    spbpers.spbpers_ssn as ssn,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameSSN
    123456789
    nopanel
     
    true
     

    PIDM

    spriden.spriden_pidm as pidm,

    PIDM

    Up to 10-digit unique Banner id for student

     

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    namePIDM
    nopaneltrue

    LASTNAME

    spriden_last_name as LastName,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameLASTNAME
     
    nopanel
     
    true
     

    FIRSTNAME

    spriden_first_name as FirstName,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameFIRSTNAME
     
    nopanel
     
    true
     

    MIDINIT

    spriden_mi as MidInit,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameMIDINIT
     
    nopanel
     
    true
     

    STUDENTNAME

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

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

    nvl(spriden_mi, '') as studentname,

     

    Insert excerpt
    BF:Common Definitions

    STUDENTNAME

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

     

    BF:Common Definitions
    nameSTUDENTNAME
    nopaneltrue

    ADDRESS

    spraddr.spraddr_street_line1 as address,

    ADDRESS

    Address at the start of the record’s term

     CITY

     

    spraddr.spraddr_city as city,

    CITY

    City at the start of the record’s term

     

     STATE

    spraddr.spraddr_stat_code as state,

    STATE

    State at the start of the record’s term

     

     ZIP

    spraddr.spraddr_zip as zip,ZIP

    Zip at the start of the record’s term

     PHONE

     

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

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    namePHONE
    Current phone number
    nopanel
     
    true
     

    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,

    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.

     PROGRAM

     

    sgbstdn.SGBSTDN_PROGRAM_1PROGRAM

    The Program program of the Majormajor

     PROG_DEPT

     

    g.SGBSTDN_DEPT_CODE

    PROG_DEPT

    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

    PROG_GE_PATTERN

    Student’s GE pattern for programs

     

     IN_STATE_IND

    MAJOR_CODE

    SGBSTDN.SGBSTDN_MAJR_CODE_1

    MAJOR_CODE

    Numeric code for the student’s major

     MAJOR

     

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

    MAJOR

    Description of the MajorMAJOR_CodeCODE

     GOAL

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

     

    Insert excerpt

    GOAL

    Uninformed Educational Goal. 50-char description.

     

    BF:Common Definitions
    BF:Common Definitions
    nameEDUCATIONAL_GOAL
    nopaneltrue

    GOAL_INFORMED_EFF_TERM

    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.

     

     

    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)

    FIN_AID_IND_BY_MJR

    This is a financial aid Financial Aid indicator based off of on the major code. Returns Y or N

     

     

    ('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)CTE_IND_BY_MJR

    This is a CTE indicator based off of on the major code. Returns Y or N.

     

     ('Y', 'N')

    GOAL_INFORMED_CODES

    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.

     

     

    You can use the following SQL code snippet in Snowflake to see the validation table as of yesterday.

    Code Block
    languagesql
    SELECT *
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVGOAL
WHERE INVOKE_DATA_DATE = CURRENT_DATE;

    GENDER

    SPBPERS.SPBPERS_SEX as gender,

    Info

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

    GENDER

    M

    F

    N

    Null

    VARCHAR2

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameGENDER
    nopaneltrue

    BIRTHDATE

    spbpers.spbpers_birth_date as birthdate,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameBIRTHDATE
    datetime
    nopanel
     
    true
     

    AGE

    f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameAGE
    float
    nopanel
     
    true
     

    AGEATTERM

    f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameAGEATTERM
    Age at term
    nopanel
     
    true
     

    CENSUSAGEGROUP

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

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameCENSUSAGEGROUP

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

     

    nopaneltrue

    MARITAL_CODE

    SPBPERS.SPBPERS_MRTL_CODE

    MARITAL_CODE

    D: Divorced

    M: Married

    N: Not Known

    P: Domestic Partner

    R: Separated

    S: Single

    W: Widowed

     

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameMARITAL_CODE
    nopaneltrue

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

    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
    BF:Common Definitions
    BF:Common Definitions
    nameDEPRECATED
    nopaneltrue

    NEW_ETHNICITY

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

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameNEW_ETHNICITY

    ‘Not Hispanic’, ‘Hispanic’, or blank

     

    nopaneltrue

    RACES

    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.

     

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameRACES
    nopaneltrue

    RACES_DESC

    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.

     

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

    RACE_ETHN_CDE

    Anchor
    race_ethn_cde
    race_ethn_cde

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

    RACE_ETHN_CDE

    Anchor
    race_ethn_cderace_ethn_cde
    Info

    VARCHAR2

    1

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameRACE_ETHN_CDE
    nopaneltrue

    RACE_ETHN_CDE_DESC

    Anchor
    race_ethn_cde_desc
    race_ethn_cde_desc

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

    RACE_ETHN_CDE_DESC

    Anchor
    race_ethn_cde_descrace_ethn_cde_desc
    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameRACE_ETHN_CDE
    and COS_GEN.CODATAMART_ETHN_VALIDATION_CODES 
    _DESC
    nopaneltrue