1 of x

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,` 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. 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` 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