Financial_Aid_By_Term table

Owned by Jordan Lamb
Last updated: May 14, 2021
3 min read

There is a record for each type of financial aid the student has received (been paid) for a term. Columns include the type of financial aid, amount awarded for the academic year, amount paid for the academic year, and amount paid for the term. For example, a simple selection like ‘select * from Financial_Aid_By_Term where pidm = 99835 and acad_yr = 2005’ returns (not all columns shown):

 

TERMSEMESTERAIDYRSTUDENTNAME AWARD_AMT_ACAD_YRAWARD_PAID_ACAD_YRAWARD_PAID_TERMFUND_CODEBALANCE

200510Fall 20040405Ortiz, Ceasar 936.00936.00468.00BWCENULL

200510Fall 20040405Ortiz, Ceasar 576.00576.00288.00CALC0.00

200510Fall 20040405Ortiz, Ceasar 4050.004050.002025.00PELL0.00

200510Fall 20040405Ortiz, Ceasar 250.00250.00250.00SEOG0.00

200520Spring 20050405Ortiz, Ceasar 936.00936.00468.00BWCENULL

200520Spring 20050405Ortiz, Ceasar 576.00576.00288.00CALC0.00

200520Spring 20050405Ortiz, Ceasar 4050.004050.002025.00PELL0.00

200520Spring 20050405Ortiz, Ceasar 250.00250.000.00SEOG0.00

 

In that selection, the student did not receive aid for Summer 2005 so there are no records for term 200530. As you can see from the 2 yellow rows, the student was awarded $936 (see AWARD_AMT_ACAD_YR column) from the BWCE fund for the academic year – not just for the term. The student was paid all of this amount (see AWARD_PAID_ACAD_YR column). These academic year values exists for both term records (the Fall and Spring semesters). Of that amount, $468 was paid each term, as the 2 records indicate (see AWARD_PAID_TERM column). Note that funding from SEOG was paid only for the 200510 term.

 

Use the vStudent_Award_Summary view in SQL Server to see an academic year summary with totals per semester (records are by academic year and not term). For example, a simple selection like ‘select * from vStudent_Award_Summary where acad_yr = 2005 and pidm = 99835’ returns (not all columns shown):

 

ACAD_YRSTUDENTNAME FUND_CODEAWARD_AMT_ACAD_YRAWARD_PAID_ACAD_YRAIDYRFALL SPRINGSUMMER BALANCE

2005Ortiz, Ceasar BWCE936.00936.000405468.00 468.000.00 NULL

2005Ortiz, Ceasar CALC576.00576.000405288.00 288.000.00 0.00

2005Ortiz, Ceasar PELL4050.004050.0004052025.00 2025.000.00 0.00

2005Ortiz, Ceasar SEOG250.00250.000405250.00 0.000.00 0.00

 

Oracle source: ReportsNet. FINANCIAL_AID1_NON_BOGG, ReportsNet. FINANCIAL_AID2_BOGG views

Oracle objects: COS_SV_FIN_AID_SUMMARY1 (view of SFBETRM, STVTERM, SPRIDEN, SPBPERS, RPRAWRD) – non-BOGG

COS_SV_FIN_AID_SUMMARY2 (view of SFBETRM, STVTERM, SPRIDEN, SPBPERS) – BOGG

 

This table is a union of non-BOGG type financial aid records (from COS_SV_FIN_AID_SUMMARY1) and BOGG type financial aid records (from COS_SV_FIN_AID_SUMMARY2).

 

Using the Father_Hi_Grade and Mother_Hi_Grade fields, the StudentQuery report finds first generation students as where father_hi_grade < 3 and mother_hi_grade < 3

Updated: nightly

Oracle Field/Calc

DW Field

Description or Sample

sfbetrm.SFBETRM_TERM_CODE

TERM

200710 (Fall = 10, Spring = 20, Summer = 30)

stvterm.STVTERM_DESC

SEMESTER

Fall 2006

stvterm.STVTERM_ACYR_CODE

ACAD_YR

The academic year (yyyy) of the terms. Terms 200710, 200720, 200730 are academic year 2007.

rprawrd.RPRAWRD_AIDY_CODE

AIDYR

2 digit year of Fall term + 2 digit year of academic year (0607 for 2007 academic year)

sfbetrm.SFBETRM_PIDM

PIDM

Up to 10-digit unique Banner id for student

spriden.SPRIDEN_FIRST_NAME

FIRSTNAME

 

spriden.SPRIDEN_LAST_NAME

LASTNAME

 

spriden.SPRIDEN_MI

MIDINIT

 

nvl(t.name_last, '') || ', ' || nvl(t.name_first, '') || ' ' || nvl(t.MI, '')

STUDENTNAME

Concatenation of Last, First Middle init (Smith, John A)

spriden.SPRIDEN_ID

STUDENTID

Banner Id (@12345678)

spbpers.SPBPERS_SSN

SSN

123456789

Non-BOGG: rprawrd.RPRAWRD_ACCEPT_AMT

 

BOGG: sum of all BOGG amounts for the student for the academic year:

(select sum(x.BOGG_AMT) FROM COS_SV_FIN_AID_SUMMARY2 x

WHERE x.PIDM = t.PIDM

and x.BOGG_CODE = t.BOGG_CODE

and x.Term >= (substr(t.TERM,1,4) || '10') and x.Term <= (substr(t.TERM,1,4) || '30'))

AWARD_AMT_ACAD_YR

Aid awarded to student for academic year (3 terms)

Non-BOGG: case when t.fund_code In ('CHAFEE','ARNETT','CHILDG','CRAIG','LEPD','STAF') then t.RPRAWRD_ACCEPT_AMT

else t.RPRAWRD_PAID_AMT

 

BOGG: sum of all BOGG amounts for the student for the academic year:

(select sum(x.BOGG_AMT) FROM COS_SV_FIN_AID_SUMMARY2 x

WHERE x.PIDM = t.PIDM

and x.BOGG_CODE = t.BOGG_CODE

and x.Term >= (substr(t.TERM,1,4) || '10') and x.Term <= (substr(t.TERM,1,4) || '30'))

AWARD_PAID_ACAD_YR

Aid paid to student for academic year

Non-BOGG: f_term_detc_payments (SFBETRM_PIDM, SFBETRM_TERM_CODE, RPRAWRD_FUND_CODE)

 

BOGG: ABS(f_term_BOGG_payments (SFBETRM_PIDM,SFBETRM_TERM_CODE,

f_bogg_code (SFBETRM_PIDM, SFBETRM_TERM_CODE, 'BOGW')))

AWARD_PAID_TERM

Aid paid for term

Non-BOGG: RPRAWRD_FUND_CODE

 

BOGG: NVL(f_bogg_code(SFBETRM_PIDM, SFBETRM_TERM_CODE, 'BOGW'),null)

FUND_CODE

Type of Aid:

Non-BOGG (from RFRBASE):

ACG1: Academic Competitive Grant 1

ACG2: Academic Competitive Grant 2

ARNETT: Arnett Loan Program

BOGW: BOG Waiver

CALB: Cal Grant B

CALC: Cal Grant C

CARE: EOPS CARE

CHAFEE: Chafee Grant

CHILDG: Child Developmnt Grant

CRAIG:Craig Loan Program

CWWS: Cal Works Work Study

CWWSN: CalWorks Wk-Study NonNeedBased

EOPS: Extended Opportunity Grant

EOPSWS: Extended Oppor. WorkStudy

FWS: Federal Work Study

LEPD: Law Enforcement Pers Gnt

PELL: Federal Pell Grant

SEOG: Supplemental Opp Grant

STAF: Subsidized Stafford Loan

 

BOGG (from TBRACCD):

BWAE: Bog Waiver A Enrollment

BWBE: Bog Waiver B Enrollment Fee

BWCE: Bog Waiver C Enrollment

BGVE: BOG VA Enrollment Fee

Non-BOGG:

case when (fund_code Like 'B%') OR fund_code In ('CHAFEE','ARNETT','CHILDG','CRAIG','LEPD','STAF') then null

else RPRAWRD_ACCEPT_AMT - RPRAWRD_PAID_AMT

 

BOGG: null (to match Access queries). There is never a balance for BOGG.

BALANCE

 

Non-BOGG:

COS_SV_FIN_AID_SUMMARY1.Father_Hi_Grade

 

BOGG:

COS_SV_FIN_AID_SUMMARY2.Father_Hi_Grade

FATHER_HI_GRADE

The highest educational level the father attained:

 

0: None, 1: Middle School, 2: High School,

3: College or Beyond, 4: Other/Unknown

Non-BOGG:

COS_SV_FIN_AID_SUMMARY1.Father_Hi_Grade

 

BOGG:

COS_SV_FIN_AID_SUMMARY2.Father_Hi_Grade

MOTHER_HI_GRADE

The highest educational level the mother attained:

 

0: None, 1: Middle School, 2: High School,

3: College or Beyond, 4: Other/Unknown