FISAP_STDNT_VW (SQL View)	Index Back
FISAP Student Selection View Used in Queries FISAP_NO_DISB_IN_GRAD_MAX_TERM and FISAP_NO_DISB_NOT_FT_MAX_TERM.

FISAP_STDNT_VW

(SQL View)

FISAP Student Selection View

Used in Queries FISAP_NO_DISB_IN_GRAD_MAX_TERM and FISAP_NO_DISB_NOT_FT_MAX_TERM.

SELECT A.EMPLID , A.INSTITUTION , A.AID_YEAR , C.ACAD_CAREER , J.NAME , E.FEDERAL_ID , D.DISBURSEMENT_ID , D.DISBURSED_BALANCE , A.FISAP_TOT_INC , A.FIRST_BACH_DEGREE , A.DEPNDNCY_STAT , G.FA_LOAD , B.AUTO_ZERO_EFC , G.NSLDS_LOAN_YEAR , K.TERM_END_DT , G.STRM , V.EFC_STATUS FROM PS_ISIR_STUDENT A , PS_ISIR_COMPUTED B , PS_STDNT_AWARDS C , PS_STDNT_AWRD_DISB D , PS_ITEM_TYPE_FA E , PS_STDNT_FA_TERM G , PS_PERSONAL_DTSAVW J , PS_TERM_TBL K , PS_DISB_ID_TBL L , PS_ISIR_CONTROL V WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_ISIR_STUDENT A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.INSTITUTION = A_ED.INSTITUTION AND A.AID_YEAR = A_ED.AID_YEAR AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_ISIR_STUDENT A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.INSTITUTION = A_ES.INSTITUTION AND A.AID_YEAR = A_ES.AID_YEAR AND A.EFFDT = A_ES.EFFDT) AND A.EMPLID = B.EMPLID AND A.EMPLID = J.EMPLID AND A.INSTITUTION = B.INSTITUTION AND B.EFFDT = A.EFFDT AND B.EFFSEQ = A.EFFSEQ AND A.AID_YEAR = B.AID_YEAR AND A.EMPLID = V.EMPLID AND A.INSTITUTION = V.INSTITUTION AND A.AID_YEAR = V.AID_YEAR AND A.EFFDT = V.EFFDT AND A.EFFSEQ = V.EFFSEQ AND B.EMPLID = C.EMPLID AND B.INSTITUTION = C.INSTITUTION AND B.AID_YEAR = C.AID_YEAR AND C.EMPLID = D.EMPLID AND C.INSTITUTION = D.INSTITUTION AND C.AID_YEAR = D.AID_YEAR AND C.ITEM_TYPE = D.ITEM_TYPE AND C.ACAD_CAREER = D.ACAD_CAREER AND D.AID_YEAR = E.AID_YEAR AND D.ITEM_TYPE = E.ITEM_TYPE AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_ITEM_TYPE_FA E_ED WHERE E.SETID = E_ED.SETID AND E.ITEM_TYPE = E_ED.ITEM_TYPE AND E.AID_YEAR = E_ED.AID_YEAR AND E_ED.EFFDT <= L.DISBURSEMENT_DATE) AND E.SETID = C.SETID AND E.ITEM_TYPE = C.ITEM_TYPE AND E.AID_YEAR = A.AID_YEAR AND E.EFF_STATUS = 'A' AND E.AID_YEAR = G.AID_YEAR AND G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.AID_YEAR = A.AID_YEAR AND G.EFFDT = ( SELECT MAX( X.EFFDT) FROM PS_STDNT_FA_TERM X WHERE X.EMPLID = G.EMPLID AND X.INSTITUTION = G.INSTITUTION AND X.STRM = G.STRM AND X.EFFDT <= %CurrentDateIn) AND G.EFFSEQ = ( SELECT MAX( Y.EFFSEQ) FROM PS_STDNT_FA_TERM Y WHERE Y.EMPLID = G.EMPLID AND Y.INSTITUTION = G.INSTITUTION AND Y.STRM = G.STRM AND Y.EFFDT = G.EFFDT) AND G.EFF_STATUS = 'A' AND E.FEDERAL_ID IN ('PERK','SEOG','FWSP') AND D.DISBURSED_BALANCE > 0 AND K.INSTITUTION = A.INSTITUTION AND K.ACAD_CAREER = G.ACAD_CAREER AND K.STRM = G.STRM AND K.TERM_END_DT = ( SELECT MAX(M.TERM_END_DT) FROM PS_TERM_TBL M , PS_STDNT_FA_TERM N WHERE M.INSTITUTION = N.INSTITUTION AND M.ACAD_CAREER = N.ACAD_CAREER AND M.STRM = N.STRM AND N.EMPLID = A.EMPLID AND N.INSTITUTION = A.INSTITUTION AND N.AID_YEAR = A.AID_YEAR AND N.EFFDT = ( SELECT MAX (O.EFFDT) FROM PS_STDNT_FA_TERM O WHERE O.EMPLID = N.EMPLID AND O.INSTITUTION = N.INSTITUTION AND O.STRM = N.STRM AND O.EFFDT <= %CurrentDateIn) AND N.EFFSEQ = ( SELECT MAX(P.EFFSEQ) FROM PS_STDNT_FA_TERM P WHERE P.EMPLID = N.EMPLID AND P.INSTITUTION = N.INSTITUTION AND P.STRM = N.STRM AND P.EFFDT = N.EFFDT) AND N.EFF_STATUS = 'A' ) AND L.INSTITUTION = C.INSTITUTION AND L.AID_YEAR = C.AID_YEAR AND L.ACAD_CAREER = C.ACAD_CAREER AND L.DISBURSEMENT_PLAN = C.DISBURSEMENT_PLAN AND L.DISBURSEMENT_ID = D.DISBURSEMENT_ID AND EXISTS ( SELECT 'X' FROM PS_ISIR_CONTROL Q , PS_STDNT_AID_ATRBT R WHERE Q.EFC_STATUS = 'O' AND (Q.SSA_CITIZENSHP_IND IN ('A',' ') OR Q.INS_MATCH = 'Y' OR Q.ISIR_SEC_INS_MATCH = 'Y' OR R.INS_MATCH_OVRD IN ('Y', 'N', ' ') OR R.SSA_CITIZEN_OVRD IN ('Y', 'N', '')) AND Q.EMPLID = R.EMPLID AND Q.INSTITUTION = R.INSTITUTION AND Q.AID_YEAR = R.AID_YEAR AND Q.EMPLID = A.EMPLID AND Q.INSTITUTION = A.INSTITUTION AND Q.AID_YEAR = A.AID_YEAR) AND EXISTS ( SELECT 'X' FROM PS_STDNT_FA_TERM S WHERE S.TERM_SRC IN ('T','M') AND S.EMPLID = A.EMPLID AND S.INSTITUTION = A.INSTITUTION AND S.AID_YEAR = A.AID_YEAR AND S.EFFDT = ( SELECT MAX (T.EFFDT) FROM PS_STDNT_FA_TERM T WHERE T.EMPLID = S.EMPLID AND T.INSTITUTION = S.INSTITUTION AND T.STRM = S.STRM AND T.EFFDT <= %CurrentDateIn ) AND S.EFFSEQ = ( SELECT MAX (U.EFFSEQ) FROM PS_STDNT_FA_TERM U WHERE U.EMPLID = S.EMPLID AND U.INSTITUTION = S.INSTITUTION AND U.STRM = S.STRM AND U.EFFDT = S.EFFDT ) AND S.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

3 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year

4 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine

5 NAME Character(50) VARCHAR2(50) NOT NULL Name

6 FEDERAL_ID Character(4) VARCHAR2(4) NOT NULL Federal ID
ACG=Academic Competitiveness Grant
FWSP=Federal Work Study Program
GAAN=Grad Assistance in Natl Need
GPLS=Graduate PLUS Loan
HEAL=Health Education Assistance Ln
HPSL=Health Profession Student Loan
IASA=Iraq/Afghan Service Award
JVTS=Jacob K Javits Fellowship
LDS=Loans for Disadvantaged Stdnts
NIH=National Institute of Health
NSF=National Science Foundation
NURS=Nursing Loan
OTHR=Other Federal Fund
PCL=Primary Care Loan
PELL=Federal PELL Grant
PERK=Federal Perkins Loan Program
PLUS=Federal Stafford Loan, PLUS
SEOG=Federal SEOG
SMRT=National Smart Grant
STFS=Fed Stafford Loan - Subsidized
STFU=Fed Stafford Loan-Unsubsidized
TCH=TEACH Grant

7 DISBURSEMENT_ID Character(2) VARCHAR2(2) NOT NULL Disbursement ID

8 DISBURSED_BALANCE Signed Number(13,2) DECIMAL(11,2) NOT NULL Disbursed Balance

9 FISAP_TOT_INC Signed Number(16,0) DECIMAL(15) NOT NULL FISAP Total Income

10 FIRST_BACH_DEGREE Character(1) VARCHAR2(1) NOT NULL Received First Bachelor Degree
1=Yes
2=No

11 DEPNDNCY_STAT Character(1) VARCHAR2(1) NOT NULL Dependency Status
1=Independent
2=Dependent
3=Indepenedent/Pro Judgmnt
D=Dependent
I=Independent
X=Dependent Reject
Y=Independent Reject
Z=Provisional Independent

12 FA_LOAD Character(1) VARCHAR2(1) NOT NULL Financial Aid Load
F=Enrolled Full-Time
H=Enrolled Half-Time
L=Less than Half-Time
N=No Unit Load
T=Three Quarter Time

13 AUTO_ZERO_EFC Character(1) VARCHAR2(1) NOT NULL Auto Zero EFC
Y=EFC Set To Zero

14 NSLDS_LOAN_YEAR Character(1) VARCHAR2(1) NOT NULL NSLDS Loan Year
0=1st Year - Never Attended
1=1st Year - Previously Attended
2=2nd Year
3=3rd Year
4=4th Year
5=5th Year+
A=1st Year Graduate
B=2nd Year Graduate
C=3rd Year Graduate
D=4th or more Graduate
G=Graduate/Professional

15 TERM_END_DT Date(10) DATE Term Ending Date

16 STRM Character(4) VARCHAR2(4) NOT NULL Term

17 EFC_STATUS Character(1) VARCHAR2(1) NOT NULL EFC Status Indicator
O=Official
R=Rejected
U=Unofficial
Z=Provisional

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution
3	AID_YEAR	Character(4)	VARCHAR2(4) NOT NULL	Aid Year
4	ACAD_CAREER	Character(4)	VARCHAR2(4) NOT NULL	Academic Career BAC=Bachelor (NLD) BBL=Vocational Coaching (NLD) BOL=Vocational Training (NLD) BUSN=Graduate Business CNED=Continuing Education CRED=Semester Credit EDU=Education (NLD) EXED=Extended Education GRAD=Graduate LAW=Law MEDS=Medical School NONA=Non Award PGRD=Postgraduate RSCH=Research TECH=Technical UENG=Undergraduate Engineering UGRD=Undergraduate VAVO=Advanced General Educ. (NLD) VETM=Veterinary Medicine
5	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
6	FEDERAL_ID	Character(4)	VARCHAR2(4) NOT NULL	Federal ID ACG=Academic Competitiveness Grant FWSP=Federal Work Study Program GAAN=Grad Assistance in Natl Need GPLS=Graduate PLUS Loan HEAL=Health Education Assistance Ln HPSL=Health Profession Student Loan IASA=Iraq/Afghan Service Award JVTS=Jacob K Javits Fellowship LDS=Loans for Disadvantaged Stdnts NIH=National Institute of Health NSF=National Science Foundation NURS=Nursing Loan OTHR=Other Federal Fund PCL=Primary Care Loan PELL=Federal PELL Grant PERK=Federal Perkins Loan Program PLUS=Federal Stafford Loan, PLUS SEOG=Federal SEOG SMRT=National Smart Grant STFS=Fed Stafford Loan - Subsidized STFU=Fed Stafford Loan-Unsubsidized TCH=TEACH Grant
7	DISBURSEMENT_ID	Character(2)	VARCHAR2(2) NOT NULL	Disbursement ID
8	DISBURSED_BALANCE	Signed Number(13,2)	DECIMAL(11,2) NOT NULL	Disbursed Balance
9	FISAP_TOT_INC	Signed Number(16,0)	DECIMAL(15) NOT NULL	FISAP Total Income
10	FIRST_BACH_DEGREE	Character(1)	VARCHAR2(1) NOT NULL	Received First Bachelor Degree 1=Yes 2=No
11	DEPNDNCY_STAT	Character(1)	VARCHAR2(1) NOT NULL	Dependency Status 1=Independent 2=Dependent 3=Indepenedent/Pro Judgmnt D=Dependent I=Independent X=Dependent Reject Y=Independent Reject Z=Provisional Independent
12	FA_LOAD	Character(1)	VARCHAR2(1) NOT NULL	Financial Aid Load F=Enrolled Full-Time H=Enrolled Half-Time L=Less than Half-Time N=No Unit Load T=Three Quarter Time
13	AUTO_ZERO_EFC	Character(1)	VARCHAR2(1) NOT NULL	Auto Zero EFC Y=EFC Set To Zero
14	NSLDS_LOAN_YEAR	Character(1)	VARCHAR2(1) NOT NULL	NSLDS Loan Year 0=1st Year - Never Attended 1=1st Year - Previously Attended 2=2nd Year 3=3rd Year 4=4th Year 5=5th Year+ A=1st Year Graduate B=2nd Year Graduate C=3rd Year Graduate D=4th or more Graduate G=Graduate/Professional
15	TERM_END_DT	Date(10)	DATE	Term Ending Date
16	STRM	Character(4)	VARCHAR2(4) NOT NULL	Term
17	EFC_STATUS	Character(1)	VARCHAR2(1) NOT NULL	EFC Status Indicator O=Official R=Rejected U=Unofficial Z=Provisional