Versions Compared

Version Old Version 30 New Version 31
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

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

    • Oracle Field/Calc

     

    DW Field

    Description (Sample)

    sfbetrm.sfbetrm_term_code as term,

    TERM

    Insert excerpt
    Common Definitions
    Common Definitions
    nameTERM
    nopaneltrue

    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
    nameBANNERID
    nopaneltrue

    spbpers.spbpers_ssn as ssn,

    SSN

    Insert excerpt
    Common Definitions
    Common Definitions
    nameSSN
    nopaneltrue

    spriden.spriden_pidm as pidm,

    PIDM

    Insert excerpt
    Common Definitions
    Common Definitions
    namePIDM
    nopaneltrue

    spriden_last_name as LastName,

    LASTNAME

     

    Insert excerpt
    Common Definitions
    Common Definitions
    nameLASTNAME
    nopaneltrue

    spriden_first_name as FirstName,

    FIRSTNAME

     

    Insert excerpt
    Common Definitions
    Common Definitions
    nameFIRSTNAME
    nopaneltrue

    spriden_mi as MidInit,

    MIDINIT

    Insert excerpt
    Common Definitions
    Common Definitions
    nameMIDINIT
    nopaneltrue

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

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

    nvl(spriden_mi, '') as studentname,

    STUDENTNAME

    Insert excerpt
    Common Definitions
    Common Definitions
    nameSTUDENTNAME
    nopaneltrue

    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
    namePHONE
    nopaneltrue

    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.

    sgbstdn.SGBSTDN_PROGRAM_1

    PROGRAM

    The Program program of the Majormajor

    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 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 Financial Aid indicator based on the major code. Returns Y or N ('Y', '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 on the major code. Returns Y or N. ('Y', '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

    Insert excerpt
    Common Definitions
    Common Definitions
    nameLEGALSEX
    nopaneltrue

    spbpers.spbpers_birth_date as birthdate,

    BIRTHDATE

    Insert excerpt
    Common Definitions
    Common Definitions
    nameBIRTHDATE
    nopaneltrue

    f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

    AGE

    Insert excerpt
    Common Definitions
    Common Definitions
    nameAGE
    nopaneltrue

    f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

    AGEATTERM

    Insert excerpt
    Common Definitions
    Common Definitions
    nameAGEATTERM
    nopaneltrue

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

    CENSUSAGEGROUP

    Insert excerpt
    Common Definitions
    Common Definitions
    nameCENSUSAGEGROUP
    nopaneltrue

    SPBPERS.SPBPERS_MRTL_CODE

    MARITAL_CODE

    Insert excerpt
    Common Definitions
    Common Definitions
    nameMARITALSTATUS
    nopaneltrue

    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
    nameDEPRECATED
    nopaneltrue

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

    NEW_ETHNICITY‘Not Hispanic’, ‘Hispanic’, or blank

    Insert excerpt
    Common Definitions
    Common Definitions
    nameHISPANICLATINO
    nopaneltrue

    SUBSTR (f_get_races (spriden_pidm), 1, 60)

    RACES2-character Race codes. May contain multiple values separated by a pipe character (|). See Race Codes in Appendix.

    Insert excerpt
    Common Definitions
    Common Definitions
    nameRACEETHNICITY
    nopaneltrue

    SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)

    RACES_DESC

    Race descriptions, multiple values separated by a pipe character (|). See Race Codes in AppendixDescription of RACES code(s). Pipe '|' delimited.

    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
    Else Unknown

    Insert excerpt
    Common Definitions
    Common Definitions
    nameRACEETHNICITYDATAMART
    nopaneltrue

    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

    Insert excerpt
    Common Definitions
    Common Definitions
    nameRACEETHNICITYDATAMARTDESCRIPTION
    nopaneltrue