| Excerpt |
|---|
|
DW Field | Oracle Field/Calc |
|---|
|
...
...
Data Type
...
TERM | sfbetrm.sfbetrm_term_code as term,
|
|
...
TERM
...
Fall terms: %10
Spring terms: %20
Summer terms: %30
...
int
...
6
...
SEMESTER
...
Fall 2006
...
...
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | TERM |
|---|
| nopanel | true |
|---|
|
| STUDENTID | spriden.spriden_id as studentid,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | STUDENTID |
|---|
|
|
|
...
Banner ID (@12345678)
...
| SSN | spbpers.spbpers_ssn as ssn,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | SSN |
|---|
|
|
|
...
...
...
PIDM | spriden.spriden_pidm as pidm,
| |
|
...
PIDM
...
Up to 10-digit unique Banner id for student
...
| BF:Common Definitions | | BF:Common Definitions |
|---|
| name | PIDM |
|---|
| nopanel | true |
|---|
|
| LASTNAME | spriden_last_name as LastName,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | LASTNAME |
|---|
|
|
|
...
...
...
FIRSTNAME | spriden_first_name as FirstName,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | FIRSTNAME |
|---|
|
|
|
...
...
...
MIDINIT | spriden_mi as MidInit,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | MIDINIT |
|---|
|
|
|
...
...
...
STUDENTNAME | nvl(spriden_last_name, '') || ', ' ||
nvl(spriden_first_name, '') || ' ' ||
nvl(spriden_mi, '') as studentname,
| |
|
...
STUDENTNAME
...
Concatenation of Last, First Middle Initial (Smith, John A)
...
| BF:Common Definitions | | name | STUDENTNAME |
|---|
| nopanel | true |
|---|
|
| ADDRESS | 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 at the start of the record’s term |
|
...
ZIP | 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,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | 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,
|
|
...
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
|
|
...
...
...
...
...
...
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 |
|
...
...
substr(f_get_desc_fnc('STVMAJR',SGBSTDN.SGBSTDN_MAJR_CODE_1,30),1,30) as Major,
|
|
...
MAJOR
...
...
...
GOAL | substr(f_get_desc_fnc('STVEGOL',SGBSTDN_EGOL_CODE,30),1,30) as Goal,
| |
|
...
GOAL
...
Uninformed Educational Goal. 50-char description.
...
| BF:Common Definitions | | BF:Common Definitions |
|---|
| name | EDUCATIONAL_GOAL |
|---|
| nopanel | true |
|---|
|
| 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)
|
|
...
...
Financial Aid indicator based |
|
...
...
...
('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 |
|
...
...
...
('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. |
|
...
...
GENDER
...
M
F
N
Null
...
You can use the following SQL code snippet in Snowflake to see the validation table as of yesterday. | Code Block |
|---|
| 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. |
| Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | GENDER |
|---|
| nopanel | true |
|---|
|
| BIRTHDATE | spbpers.spbpers_birth_date as birthdate,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | BIRTHDATE |
|---|
|
|
|
...
...
...
AGE | f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | AGE |
|---|
|
|
|
...
...
...
AGEATTERM | f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | AGEATTERM |
|---|
|
|
|
...
...
...
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 |
|---|
| name | CENSUSAGEGROUP |
|---|
|
|
|
...
18-char description of age group: “35-39 years”
...
...
| 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 |
|---|
| name | MARITAL_CODE |
|---|
| nopanel | true |
|---|
|
| 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 |
|---|
| name | DEPRECATED |
|---|
| nopanel | true |
|---|
|
| NEW_ETHNICITY | decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | NEW_ETHNICITY |
|---|
|
|
|
...
‘Not Hispanic’, ‘Hispanic’, or blank
...
| 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 |
|---|
| name | RACES |
|---|
| nopanel | true |
|---|
|
| 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.
...
...
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
...
VARCHAR2
...
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
...
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.
...
| Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | RACE_ETHN_CDE |
|---|
| nopanel | true |
|---|
|
| 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
...
| Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | RACE_ETHN_CDE |
|---|
|
|
|
...