| | |
|---|
TERM | sfbetrm.sfbetrm_term_code as term,
| |
SEMESTER | stvterm.stvterm_desc as semester,
|
|
STUDENTID | spriden.spriden_id as studentid,
|
|
SSN | spbpers.spbpers_ssn as ssn,
|
|
PIDM | spriden.spriden_pidm as pidm,
|
|
LASTNAME | spriden_last_name as LastName,
|
|
FIRSTNAME | spriden_first_name as FirstName,
|
|
MIDINIT | spriden_mi as MidInit,
|
|
STUDENTNAME | nvl(spriden_last_name, '') || ', ' ||
nvl(spriden_first_name, '') || ' ' ||
nvl(spriden_mi, '') as studentname,
|
|
ADDRESS | spraddr.spraddr_street_line1 as address,
| Address at the start of the record’s term |
CITY | spraddr.spraddr_city as city,
| City at the start of the record’s term |
STATE | spraddr.spraddr_stat_code as state,
| State at the start of the record’s term |
ZIP | spraddr.spraddr_zip as zip,
| Zip at the start of the record’s term |
PHONE | decode(sprtele_phone_number, null, '', '(' || sprtele_phone_area || ') ' || sprtele_phone_number) as phone,
|
|
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,
| 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
| The program of the major |
PROG_DEPT | g.SGBSTDN_DEPT_CODE
| 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
| Student’s GE pattern for programs |
IN_STATE_IND | Â | Â |
MAJOR_CODE | SGBSTDN.SGBSTDN_MAJR_CODE_1
| Numeric code for the student’s major |
MAJOR | substr(f_get_desc_fnc('STVMAJR',SGBSTDN.SGBSTDN_MAJR_CODE_1,30),1,30) as Major,
| Description of the MAJOR_CODE |
GOAL | substr(f_get_desc_fnc('STVEGOL',SGBSTDN_EGOL_CODE,30),1,30) as Goal,
|
|
GOAL_INFORMED_EFF_TERM | substr(f_get_serdtgl_eff_term(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,6)
 | 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)
| This is a Financial Aid indicator based on the major code. ('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)
| This is a CTE indicator based on the major code. ('Y', 'N') |
GOAL_INFORMED_CODES | substr(f_get_serdtgl_codes(spriden.SPRIDEN_PIDM, sfbetrm.SFBETRM_TERM_CODE),1,10)
 | Most recent Informed Goal codes. Up to 10 codes, no delimiter. You can use the following SQL code snippet in Snowflake to see the validation table as of yesterday. SELECT *
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVGOAL
WHERE INVOKE_DATA_DATE = CURRENT_DATE; |
GENDER | SPBPERS.SPBPERS_SEX as gender,
|
|
BIRTHDATE | spbpers.spbpers_birth_date as birthdate,
|
|
AGE | f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,
|
|
AGEATTERM | f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)
|
|
CENSUSAGEGROUP | substr(baninst1.cos_fw_census_age_categ(sfbetrm_term_code,spbpers_birth_date,spbpers_dead_date),1,18)
|
|
MARITAL_CODE | SPBPERS.SPBPERS_MRTL_CODE
|
|
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))
|
|
NEW_ETHNICITY | decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')
|
|
RACES | SUBSTR (f_get_races (spriden_pidm), 1, 60)
|
|
RACES_DESC | SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)
| Description of RACES code(s). Pipe '|' delimited. |
RACE_ETHN_CDE
| SUBSTR(REPORTSNET.f_get_race_ethn(spriden_pidm), 1, 1)
|
|
RACE_ETHN_CDE_DESC
| SUBSTR(REPORTSNET.f_get_race_ethn_desc(REPORTSNET.f_get_race_ethn(spriden_pidm)), 1, 30)
|
|