Oracle Field/Calc | DW Field | Description (Sample) | Data Type | Field Length |
sfbetrm.sfbetrm_term_code as term,
| TERM | Fall terms: %10 Spring terms: %20 Summer terms: %30 | int | 6 |
stvterm.stvterm_desc as semester,
| SEMESTER | Fall 2006 | | |
spriden.spriden_id as studentid,
| STUDENTID | Banner ID (@12345678) | | |
spbpers.spbpers_ssn as ssn,
| SSN | 123456789 | | |
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 Null | VARCHAR2 | |
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 | | |
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 | 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 |
SUBSTR(REPORTSNET.f_get_race_ethn_desc(REPORTSNET.f_get_race_ethn(spriden_pidm)), 1, 30)
| 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 |
| | |