| Excerpt |
|---|
|
DW Field | Oracle Field/Calc |
|---|
|
...
...
...
Field Length
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
...
| Insert excerpt |
|---|
| 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,
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | STUDENTNAME |
|---|
|
|
|
...
Concatenation of Last, First Middle Initial (Smith, John A)
...
| ADDRESS | spraddr.spraddr_street_line1 as address,
|
|
...
ADDRESS
Address at the start of the record’s term |
|
...
...
spraddr.spraddr_city as 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 |
|
...
...
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. |
|
...
...
sgbstdn.SGBSTDN_PROGRAM_1
|
|
...
PROGRAM
...
...
...
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
|
|
...
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 | | name | EDUCATIONAL_GOAL |
|---|
| nopanel | true |
|---|
|
| 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
...
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)
|
|
...
CENSUSAGEGROUP
...
18-char description of age group: “35-39 years”
...
| Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | CENSUSAGEGROUP |
|---|
| nopanel | true |
|---|
|
| 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.
...
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)
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | RACE_ETHN_CDE |
|---|
|
|
|
...
Looks at the first character that is returned.
| Info |
|---|
Grabs the ethnicity as mentioned above in the ethnicity field, then grabs the new_ethnicity, and race fields. If nothing is found it assigns unknown Else If ethnicity is null but races = ‘Hispanic’ then it assigns Hispanic Else If ethnicity is null and races contains “|” character it assigns twoOrMore Else If new_Ethnicity is null and races is not null then it checks if races is Filipino if so it assigns Filipino to stuEthnicity, else it grabs the first character of the race field. Else if new ethnicity is null and races is null and the ethnicity equals ‘Am. Indian or Alaskan Native', Then Native American code. “” equals other or Asian or Pacific Islander Then “Unknown” “” equals “Hispanic” then Hispanic Else if new ethnicity equals NotHispanic And races is null And (ethnicity is other or ethnicity is ‘Am. Indian or Alaskan Native' or ‘Asian or Pacific Islander’) then unknown code Else if ethnicity is nothispanic and races is null then the first letter of their ethnicity code. Else if new_ethnicity is not Hispanic and races contains ‘|’ then ethnicity equals twoOrMore Else notHispanic Then ethnicity equals the first Letter of their race field. Else it assigns unknown. Returns the value. |
...
VARCHAR2
...
1 CHAR
...
| 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)
| | Insert excerpt |
|---|
| BF:Common Definitions |
|---|
| BF:Common Definitions |
|---|
| name | RACE_ETHN_CDE_DESC |
|---|
|
|
|
...
30-character Race description according to RACE_ETHN_CDE and COS_GEN.CODATAMART_ETHN_VALIDATION_CODES
| Info |
|---|
Calls the f_get_race_ethn function and just follows through a series of case statements and returns the appropriate value for the description. ‘2’ then ‘Multi-Ethnicity’ ‘A’ then ‘Asian’ ‘B’ then ‘African-American’ ‘F’ then ‘Filipino’ ‘H’ then Hispanic ‘N’ then American Indian/Alaskan Native ‘P’ then Pacific Islander ‘W’ then White Non-Hispanic ‘X’ then Unknown Doesn’t match then Unknown |