FAN_ENRLMNT_VW

(SQL View)

SELECT DISTINCT A.EMPLID ,A.INSTITUTION ,A.AID_YEAR ,A.STRM ,B.DESCR ,A.FIN_AID_FED_RES ,A.ACAD_LEVEL_BOT ,A.ACAD_LEVEL_PROJ ,A.FA_LOAD ,C.XLATSHORTNAME FROM PS_STDNT_TERM_BDGT A , PS_TERM_VAL_TBL B , PSXLATITEM C WHERE A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_STDNT_TERM_BDGT A2 WHERE A2.EMPLID = A.EMPLID AND A2.INSTITUTION = A.INSTITUTION AND A2.AID_YEAR = A.AID_YEAR AND A2.ACAD_CAREER = A.ACAD_CAREER AND A2.STRM = A.STRM AND A2.EFFDT <= %CurrentDateIn ) AND EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_STDNT_TERM_BDGT A3 WHERE A3.EMPLID = A.EMPLID AND A3.INSTITUTION = A.INSTITUTION AND A3.AID_YEAR = A.AID_YEAR AND A3.ACAD_CAREER = A.ACAD_CAREER AND A3.STRM = A.STRM AND A3.EFFDT = ( SELECT MAX(A3A.EFFDT) FROM PS_STDNT_TERM_BDGT A3A WHERE A3A.EMPLID = A.EMPLID AND A3A.INSTITUTION = A.INSTITUTION AND A3A.AID_YEAR = A.AID_YEAR AND A3A.ACAD_CAREER = A.ACAD_CAREER AND A3A.STRM = A.STRM AND A3A.EFFDT <= %CurrentDateIn ) ) AND A.ACAD_CAREER = ( SELECT MAX(A4.ACAD_CAREER) FROM PS_STDNT_TERM_BDGT A4 WHERE A4.EMPLID = A.EMPLID AND A4.INSTITUTION = A.INSTITUTION AND A4.AID_YEAR = A.AID_YEAR AND A4.STRM = A.STRM AND A4.EFFDT = ( SELECT MAX(A4A.EFFDT) FROM PS_STDNT_TERM_BDGT A4A WHERE A4A.EMPLID = A.EMPLID AND A4A.INSTITUTION = A.INSTITUTION AND A4A.AID_YEAR = A.AID_YEAR AND A4A.ACAD_CAREER = A.ACAD_CAREER AND A4A.STRM = A.STRM AND A4A.EFFDT <= %CurrentDateIn ) AND A4.EFFSEQ = ( SELECT MAX(A4B.EFFSEQ) FROM PS_STDNT_TERM_BDGT A4B WHERE A4B.EMPLID = A.EMPLID AND A4B.INSTITUTION = A.INSTITUTION AND A4B.AID_YEAR = A.AID_YEAR AND A4B.ACAD_CAREER = A.ACAD_CAREER AND A4B.STRM = A.STRM AND A4B.EFFDT = ( SELECT MAX(A4C.EFFDT) FROM PS_STDNT_TERM_BDGT A4C WHERE A4C.EMPLID = A.EMPLID AND A4C.INSTITUTION = A.INSTITUTION AND A4C.AID_YEAR = A.AID_YEAR AND A4C.ACAD_CAREER = A.ACAD_CAREER AND A4C.STRM = A.STRM AND A4C.EFFDT <= %CurrentDateIn ) ) ) AND B.STRM = A.STRM AND C.FIELDNAME = 'FA_LOAD' AND C.FIELDVALUE = A.FA_LOAD AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PSXLATITEM C2 WHERE C2.FIELDNAME = C.FIELDNAME AND C2.FIELDVALUE = C.FIELDVALUE)

# 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 STRM Character(4) VARCHAR2(4) NOT NULL Term

5 STRM_DESCR Character(30) VARCHAR2(30) NOT NULL Term Description

6 FIN_AID_FED_RES Character(5) VARCHAR2(5) NOT NULL Fin Aid Federal Residency

7 ACAD_LEVEL_BOT Character(3) VARCHAR2(3) NOT NULL Academic Level - Term Start

8 ACAD_LEVEL_PROJ Character(3) VARCHAR2(3) NOT NULL Academic Level - Projected

9 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

10 LOAD_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Load Descr is the xlatshortname from the FA_LOAD field.

#	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	STRM	Character(4)	VARCHAR2(4) NOT NULL	Term
5	STRM_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Term Description
6	FIN_AID_FED_RES	Character(5)	VARCHAR2(5) NOT NULL	Fin Aid Federal Residency
7	ACAD_LEVEL_BOT	Character(3)	VARCHAR2(3) NOT NULL	Academic Level - Term Start
8	ACAD_LEVEL_PROJ	Character(3)	VARCHAR2(3) NOT NULL	Academic Level - Projected
9	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
10	LOAD_DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Load Descr is the xlatshortname from the FA_LOAD field.