Oracle Field/Calc | DW Field | Description (Sample) | Data Type | Field Length |
sfbetrm.sfbetrm_term_code as term,
| TERM | Insert excerpt |
---|
| Common Definitions |
---|
| Common Definitions |
---|
name | TERM |
---|
nopanel | true |
---|
| nvarchar(6) | 6 |
stvterm.stvterm_desc as semester,
| SEMESTER | Insert excerpt |
---|
| Common Definitions |
---|
| Common Definitions |
---|
name | SEMESTER |
---|
nopanel | true |
---|
|
| | |
spriden.spriden_id as studentid,
| STUDENTID | Insert excerpt |
---|
| Common Definitions |
---|
| Common Definitions |
---|
name | StudentID |
---|
nopanel | true |
---|
|
| | |
spbpers.spbpers_ssn as ssn,
| SSN | 123456789 | | | Insert excerpt |
---|
| Common Data Fields |
---|
| Common Data Fields |
---|
name | SSN |
---|
nopanel | true |
---|
|
|
spriden.spriden_pidm as pidm,
| PIDM | Up to 10-digit unique Banner id for student | | |
spriden_last_name as LastName,
| LASTNAME | | | |
spriden_first_name as FirstName,
| FIRSTNAME | | | |
spriden_mi as MidInit,
| MIDINIT | | | |
nvl(spriden_last_name, '') || ', ' ||
nvl(spriden_first_name, '') || ' ' ||
nvl(spriden_mi, '') as studentname,
| STUDENTNAME | Concatenation of Last, First Middle Initial (Smith, John A) | |
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 | Current phone number | |
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, such as “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 of the Major | |
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. 50-char 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 indicator based off of the major code. Returns Y or 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 off of the major code. Returns Y or 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,
Info |
---|
SPBPERS_SEX fed from CCCApply initially and can be overridden by manual entry. |
| GENDER | M F N NullVARCHAR2 |
spbpers.spbpers_birth_date as birthdate,
| BIRTHDATE | datetime | |
f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,
| AGE | float | | |
f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)
| AGEATTERM | Age at term | |
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” | | |
SPBPERS.SPBPERS_MRTL_CODE
| MARITAL_CODE | D: Divorced M: Married N: Not Known P: Domestic Partner R: Separated S: Single W: Widowed | | |
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 |
---|
| Common Data Fields |
---|
| Common Data Fields |
---|
name | DEPRECATED |
---|
nopanel | true |
---|
|
|
decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')
| NEW_ETHNICITY | ‘Not Hispanic’, ‘Hispanic’, or blank | |
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. | | |
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)
| RACE_ETHN_CDE Anchor |
---|
| race_ethn_cde |
---|
| race_ethn_cde |
---|
|
| Looks at the first character that is returned. Info |
---|
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 Else Unknown
|
| VARCHAR2 | 1 |
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 |