SFA_LNPG_OVR_VW

(SQL View)
 Index Back

Prompt view std w/loan awards

Pulls emplids with awards of loan type and accepted status used in Origination

SELECT DISTINCT B.INSTITUTION , B.AID_YEAR , B.ACAD_CAREER , C.LOAN_PROGRAM , B.EMPLID , A.NAME FROM PS_SCC_PRI_NAME_VW A , PS_STDNT_AWARDS B , PS_ITEM_TYPE_FA C , PS_INSTALLATION_FA D WHERE A.EMPLID = B.EMPLID AND A.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PS_SCC_PRI_NAME_VW X1 WHERE X1.EMPLID = A.EMPLID) AND B.FIN_AID_TYPE = 'L' AND ( ( B.OFFER_ACTIVITY_IND IN( 'U','A') AND B.AWARD_STATUS = 'A') OR (B.OFFER_ACTIVITY_IND IN ('A', 'O') AND B.AWARD_STATUS IN ('C','D')) OR EXISTS ( SELECT 'X' FROM PS_LOAN_ORIG_DTL X WHERE X.EMPLID = B.EMPLID AND X.LOAN_PROC_STAT = 'S' AND X.LN_ORIG_TRANS = 'E' ) ) AND C.SETID = B.SETID AND C.ITEM_TYPE = B.ITEM_TYPE AND C.AID_YEAR = B.AID_YEAR AND C.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_ITEM_TYPE_FA X WHERE X.SETID = C.SETID AND X.ITEM_TYPE = C.ITEM_TYPE AND X.AID_YEAR = C.AID_YEAR)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year
3 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
4 LOAN_PROGRAM Character(1) VARCHAR2(1) NOT NULL Loan Program
A=Alternative
D=Direct Lending
F=FFELP
H=Health Professions
P=Perkins
S=State
U=University
5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
6 NAME Character(50) VARCHAR2(50) NOT NULL Name