Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 21 Next »

Proposed Architecture/Reference:

COS_DATALAKEHOUSE.COS_CURATED.STUDENT_TERM

Domain:

Tables Used (Document these in each element instead):
GOREMAL, GORPRAC, SFBETRM, SFRTHST, SGBSTDN, SHRLGPA, SHRTGPA, SMRPRLE, SPBPERS, SPRADDR, SPRHOLD, SPRIDEN, SPRTELE, STATTR, STVACYR, STVCAMP, STVCIPC, STVCITZ, STVCOLL, STVDEGC, STVESTS, STVETCT, STVETHN, STVLEVL, STVMAJR, STVNATN, STVSTST, STVSTYP, STVTERM, STVTMST, TBRACCD, TVTERM

This view is built on COS_DATALAKEHOUSE.CURATED.STUDENT_TERM. Invoke documentation defines this table as "One row per student per term." They source from more than just Banner for this curated table.

More information available here: COS_DATALAKEHOUSE.CURATED.COLLECTION_DEFINITION

  • COS Applications and Research decided to utilize the Invoke Curated tables as a starting point and build views off those for as long as feasible and accurate.

How Invoke decided to join tables, build exclusions/inclusions is unknown.

Update Frequency Notes:

COS_DATALAKEHOUSE.CURATED.STUDENT_TERM is built nightly. Functions execute on query.

Column

Definition

Lineage

Status

TERM_CODE

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.TERM_CODE

DONE

TERM

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.TERM

DONE

BANNER_ID

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_ALTERNATE_ID

DONE

SSN

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

APPLICATION.APPLICATION_SSN

DONE

PIDM

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_ID

DONE

CCCID

APPLICATION_CCC_ID

DONE

LAST_NAME

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_LAST_NAME

DONE

FIRST_NAME

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_FIRST_NAME

DONE

MIDDLE_NAME

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_MIDDLE_NAME

DONE

STUDENT_NAME

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

DONE

ADDRESS

STUDENT_TERM.STUDENT_ADDRESS_STREET_1

DONE

CITY

STUDENT_TERM.STUDENT_ADDRESS_CITY

DONE

STATE

STUDENT_TERM.STUDENT_ADDRESS_STATE

DONE

ZIP

STUDENT_TERM.STUDENT_ADDRESS_POSTAL_CODE

DONE

BIRTH_DATE

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_BIRTH_DATE

DONE

AGE_AT_TERM_START

STUDENT_TERM.AGE

DONE

ETHNICITY

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

INVOKE IS CREATING THIS

GENDER

Rework to match Gender field on CCCApply:

F = Female M = Male B = Non-binary X = Decline to state = No selection

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_GENDER

REWRITING FUNCTION NEEDS VALIDATION

SEXUAL_ORIENTATION_CODE

Sexual Orientation: Self-declared sexual orientation code value. Self-reported initially from the student’s application. This is stored in Banner in the table: SVBSXLO and the column: SVBSXLO_SXLO_CODE

Uses SB37 DED Definitions

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

AP-399 - Getting issue details... STATUS

DONE

SEXUAL_ORIENTATION

The description of the SEXUAL_ORIENTATION_CODE

AP-399 - Getting issue details... STATUS

DONE

TRANSGENDER

Previously TRANSGENDER_STATUS

Rework to match Transgender field on CCCApply:

1 = Yes 2 = No 3 = Decline to State 0 = No selection Null = not displayed to user

SPBPERS_GNDR_CODE is populated from CCCApply, but the Banner data architecture does not allow for a 1-to-1 relationship with CCCApply data.

IF c1_rec.gender = 'B' AND c1_rec.transgender_encrypted BETWEEN '1' and '3'
THEN
--ryanwh: 11.2.2021: using DECODE to map the CCCApply values to the appropriate MIS values.
v_gndr_code :=
DECODE (
SUBSTR (c1_rec.transgender_encrypted,
0,
1),
'2', '0',
'3', '2',
'0', 'X',
SUBSTR (c1_rec.transgender_encrypted,
0,
1))
|| c1_rec.gender;
ELSE
v_gndr_code :=
DECODE (
SUBSTR (c1_rec.transgender_encrypted, 0, 1),
'2', '0',
'3', '2',
'0', 'X',
SUBSTR (c1_rec.transgender_encrypted, 0, 1));
END IF;

Because of this, Technology Services staff chose not to split that data back out to separate gender/transgender fields like CCCApply.

We also crosswalk from CCCApply values to approved MIS values because MIS only allows certain codes and there is no known Banner process to do code conversions for SB reporting.

Current Codes on related Validation Table GTVGNDR:

Codes - Definitions
0 - Not transgender
0B - Non-Binary gender and Not transgender
1 - Transgender
1B - Non-Binary gender and transgender
2 - Decline to State
2B - Non-Binary gender and Decline to State
X - Uncollected
Null - Unknown
XB - Uncollected Non-Binary Gender

Codes 3 and 3B are no longer valid

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

SPBPERS_GNDR_CODE

REWRITING FUNCTION

CONFIDENTIAL_IND

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

SPBPERS_CONFID_IND

PHONE

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_PHONE_NUMBER

CELL_PHONE

EMAIL

PERSONAL_EMAIL

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

MARITAL_CODE

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

APPLICATION.APPLICATION_MARITAL_STAT

DEPENDENT

VISA_TYPE

RESD_CODE

RESD_DESC

PARENT_EDLV_1_CODE

PARENT_EDLV_1_DESC

PARENT_EDLV_2_CODE

PARENT_EDLV_2_DESC

HIGHSCHOOL

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

APPLICATION.APPLICATION_NAME_HSCH

ENGL_PLACE

Previously named ENG251_ELIG

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

ENGL_TEST_DATE

Previously named ENG_TEST_DATE

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

MATHPLACE

AM_MATHPLACE

MATH_TEST_DATE

COMPLETED_ELEVENTH

SR_HS_GPA

SR_HS_YEARS_ENGLISH

SR_HS_HIGHEST_ENGL_COURSE

SR_HS_HIGHEST_ENGL_GRADE

SR_HS_LOWEST_ENGL_GRADE

SR_HS_HIGHEST_MATH_COURSE

SR_HS_HIGHEST_MATH_TAKEN_GRADE

SR_HS_HIGHEST_MATH_PASS_GRADE

SR_HS_CURRENT_MATH

ORIENTATION

PROGRAM

STUDENT_TERM.STUDENT_TERM_PROGRAM_CODE

PROG_DEPT

PROG_GE_PATTERN

MAJOR_CODE

STUDENT_TERM.STUDENT_TERM_MAJOR_CODE

MAJOR

Error rendering macro 'excerpt-include' : No link could be created for 'DOC:Common Definitions'.

STUDENT_TERM.STUDENT_TERM_MAJOR

CTE_IND_BY_MJR

FIN_AID_IND_BY_MJR

REGIS_APPT

ED_PLAN_DATE

ACADEMIC_STANDING

CAST_CODE

STUDENT_STATUS

Previously STATUS

SGBSTDN_STYP_CODE

REWRITTEN. PENDING ACCESS TO WRITE FUNCTION.

OVERALL_GPA

STUDENT_TERM_OVERALL_GPA

HOLDNEEDS VALIDATION

Research assessing what STUDENT_TERM columns would be useful for GPA/HRS/Units

OVERALL_HRS_EARNED

STUDENT_TERM_OVERALL_HOURS_EARNED

HOLDNEEDS VALIDATION

OVERALL_HRS_PASSED

STUDENT_TERM_OVERALL_HOURS_PASSED

HOLDNEEDS VALIDATION

TRANSFER_IN_UNITS

HOLDNEEDS VALIDATION

TRANSFER_IN_GPA

HOLDNEEDS VALIDATION

TERM_GPA

STUDENT_TERM_GPA

HOLDNEEDS VALIDATION

TERM_HOURS

STUDENT_TERM_HOURS_ATTEMPTED

HOLDNEEDS VALIDATION

TERM_PASSED

STUDENT_TERM_HOURS_PASSED

HOLDNEEDS VALIDATION

TERM_PASSED_ABC

HOLDNEEDS VALIDATION

TERM_ACCUM_UNITS_ATT

STUDENT_TERM_OVERALL_HOURS_ATTEMPTED

HOLDNEEDS VALIDATION

TERM_ACUM_UNITS_PASSED

HOLDNEEDS VALIDATION

TERM_ACCUM_GPA

HOLDNEEDS VALIDATION

DEAN_PRES_CODE

DEAN_PRES_DESC

EOPS

DRC

DRC_CODES

VTEA

PUENTE

CALWORKS

ATHLETE

WIA

FOSTER_YOUTH

TRIO

PELL

HOMELESS_STATUS

COHORTS

Student Success,
COS Foundation

COMING SOON

https://sequoias.atlassian.net/browse/AP-357

  • No labels