Page Comparison

Needed - JL Override

Excerpt

name	student_table_1

Needed

title

ZIP

Needed

Blue

title

Needed

Not Needed

You can use the following SQL code snippet in Snowflake to see the validation table as of yesterday.

DW Field

Oracle Field/Calc

Description (Sample)

Snowflake Import Status

TERM

sfbetrm.sfbetrm_term_code as term,

Insert excerpt

Common Definitions Common Definitions

name TERM

nopanel true

Status

colour	Blue
title	Needed

https://sequoias.atlassian.net/browse/GOVPUB-10

SEMESTER

stvterm.stvterm_desc as semester,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	SEMESTERTERM
nopanel	true

Status

colour	Blue
title	Needed

STUDENTID

spriden.spriden_id as studentid,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	STUDENTID
nopanel	true

Status

colour	Blue
title	Needed

SSN

spbpers.spbpers_ssn as ssn,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	SSN
nopanel	true

Status

colour

	Blue
title	Needed

PIDM

spriden.spriden_pidm as pidm,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	PIDM
nopanel	true

Status

colour	Blue
title	Needed

LASTNAME

spriden_last_name as LastName,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	LASTNAME
nopanel	true

Status

colour	Blue
title	Needed

FIRSTNAME

spriden_first_name as FirstName,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	FIRSTNAME
nopanel	true

Status

colour	Blue
title	Needed

MIDINIT

spriden_mi as MidInit,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	MIDINIT
nopanel	true

Status

colour	Blue
title	Needed

STUDENTNAME

nvl(spriden_last_name, '') || ', ' ||

nvl(spriden_first_name, '') || ' ' ||

nvl(spriden_mi, '') as studentname,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	STUDENTNAME
nopanel	true

Status

colour

	Blue
title	Needed

ADDRESS

spraddr.spraddr_street_line1 as address,

Address at the start of the record’s term

Status

colour	Blue
title	Needed

CITY

spraddr.spraddr_city as city,

City at the start of the record’s term

Status

colour Blue

title

STATE

spraddr.spraddr_stat_code as state,

State at the start of the record’s term

Status

colour Blue

Needed

spraddr.spraddr_zip as zip,

Zip at the start of the record’s term

Status

colour	Blue
title	Needed

PHONE

decode(sprtele_phone_number, null, '', '(' || sprtele_phone_area || ') ' || sprtele_phone_number) as phone,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	PHONE
nopanel	true

Status

colour	Blue
title	Needed

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,

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.

Status

colour Blue

title

PROGRAM

sgbstdn.SGBSTDN_PROGRAM_1

The program of the major

Status

colour	Blue
title	Needed

PROG_DEPT

g.SGBSTDN_DEPT_CODE

Department code for student’s program

Has snapshot data on SGASTDN instead of the maintained data on SOACURR.

Status

colour	Blue
title	Needed

PROG_GE_PATTERN

g.SGBSTDN_MAJR_CODE_CONC_1

Student’s GE pattern for programs

Status

colour

title Needed

IN_STATE_IND

Status

Not Needed

MAJOR_CODE

SGBSTDN.SGBSTDN_MAJR_CODE_1

Numeric code for the student’s major

Status

colour	Blue
title	Needed

MAJOR

substr(f_get_desc_fnc('STVMAJR',SGBSTDN.SGBSTDN_MAJR_CODE_1,30),1,30) as Major,

Description of the MAJOR_CODE

Status

colour	Blue
title	Needed

GOAL

substr(f_get_desc_fnc('STVEGOL',SGBSTDN_EGOL_CODE,30),1,30) as Goal,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	EDUCATIONAL_GOAL
nopanel	true

Status

title	Not Needed

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.status

title	Not Needed

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)

This is a Financial Aid indicator based on the major code. ('Y', 'N')status

colour	Blue
title	Needed

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)

This is a CTE indicator based on the major code. ('Y', 'N')

Status

colour Blue

title

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. See Goal Codes in Appendix.

Status

title

Code Block

language	sql

SELECT *
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVGOAL
WHERE INVOKE_DATA_DATE = CURRENT_DATE;

GENDER

SPBPERS.SPBPERS_SEX as gender,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	GENDER
nopanel	true

Status

colour

	Blue
title	Needed

BIRTHDATE

spbpers.spbpers_birth_date as birthdate,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	BIRTHDATE
nopanel	true

Status

colour	Blue
title	Needed

AGE

f_calculate_age(null,spbpers_birth_date, spbpers_dead_date) as age,

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	AGE
nopanel	true

Status

colour	Blue
title	Needed

AGEATTERM

f_calculate_age(stvterm_end_date,spbpers_birth_date, spbpers_dead_date)

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	AGEATTERM
nopanel	true

Status

colour	Blue
title	Needed

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
nopanel	true

Status

title	Not Needed

MARITAL_CODE

SPBPERS.SPBPERS_MRTL_CODE

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	MARITAL_CODE
nopanel	true

Status

title

colour	Blue
	Needed

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))

status

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	DEPRECATED
nopanel	true

title	Not Needed

NEW_ETHNICITY

decode(spbpers_ethn_cde, null, null, 1, 'Not Hispanic', 2, 'Hispanic')

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	NEW_ETHNICITY
nopanel	true

Status

colour	Purple
title	Needed - JL Override

RACES

SUBSTR (f_get_races (spriden_pidm), 1, 60)

Insert excerpt

	BF:Common Definitions
	BF:Common Definitions
name	RACES
nopanel	true

Status

title

colour	Purple
	Needed - JL Override

Needs rework to allow 3 character codes.

RACES_DESC

SUBSTR (f_get_races_desc (spriden_pidm), 1, 1200)

Description of RACES code(s). Pipe '|' delimited.

Status

colour Purple

title

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
nopanel	true

Status

colour	Purple
title	Needed - JL Override

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
nopanel	true

Status

colour	Purple
title	Needed - JL Override

Versions Compared

Old Version 39

New Version Current

Key