Versions Compared

Version Old Version 30 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
DW Fieldhttps://sequoias.atlassian.net/browse/GOVPUB-10

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

    • Race description according to

    DW Field

    Oracle Field/Calc

     

    Description (Sample)

    TERM

    sfbetrm.sfbetrm_term_code as term,

    TERM

    Insert excerpt
    Common DefinitionsCommon Definitions
    nameTERM
    nopaneltrue

    SEMESTER

    stvterm.stvterm_desc as semester,SEMESTER

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameSEMESTERTERM
    nopaneltrue

    STUDENTID

    spriden.spriden_id as studentid,STUDENTID

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameBANNERIDSTUDENTID
    nopaneltrue

    SSN

    spbpers.spbpers_ssn as ssn,SSN

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameSSN
    nopaneltrue

    PIDM

    spriden.spriden_pidm as pidm,PIDM

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

    LASTNAME

    spriden_last_name as LastName,

    LASTNAME

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameLASTNAME
    nopaneltrue

    FIRSTNAME

    spriden_first_name as FirstName,

    FIRSTNAME

     

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameFIRSTNAME
    nopaneltrue

    MIDINIT

    spriden_mi as MidInit,

    MIDINIT

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameMIDINIT
    nopaneltrue

    STUDENTNAME

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

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

    nvl(spriden_mi, '') as studentname,

    STUDENTNAME

    Insert excerpt
    BF:Common Definitions
    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,PHONE

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    namePHONE
    nopaneltrue

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

    PROGRAM

    The Program program of the Majormajor

    PROG_DEPT

    g.SGBSTDN_DEPT_CODEPROG_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 MAJOR_CODE

    GOAL

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

    Uninformed Educational Goal description

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions

    GOAL

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

    GENDER

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameLEGALSEXGENDER
    nopaneltrue

    BIRTHDATE

    spbpers.spbpers_birth_date as birthdate,BIRTHDATE

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameBIRTHDATE
    nopaneltrue

    AGE

    f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

    AGE

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameAGE
    nopaneltrue

    AGEATTERM

    f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

    AGEATTERM

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameAGEATTERM
    nopaneltrue

    CENSUSAGEGROUP

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

    CENSUSAGEGROUP

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameCENSUSAGEGROUP
    nopaneltrue

    MARITAL_CODE

    SPBPERS.SPBPERS_MRTL_CODE

    MARITAL_CODE

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameMARITALSTATUSMARITAL_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

    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)

    Insert excerpt
    BF:Common Definitions
    BF:Common Definitions
    nameRACES
    2-character Race codes. May contain multiple values separated by a pipe character (|). See Race Codes in Appendix.
    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.

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

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

    RACE_ETHN_CDE

    Anchor
    race_ethn_cde
    race_ethn_cde

    Looks at the first character that is returned.

    Info
    Else Unknown

    SUBSTR(REPORTSNET.f_get_race_ethn(spriden_pidm), 1, 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