SFA_PERK_OVR_VW

(SQL View)
 Index Back

Perkins Student Override View

View for Perkins MPN Student Override. Retrieves students who have a Perkins Item Type that has the MPN Required checkbox set. This view is used when the Career field is filled in with a value.

SELECT DISTINCT A.INSTITUTION , A.AID_YEAR , A.ACAD_CAREER , A.EMPLID , E.NAME FROM PS_STDNT_AWARDS A ,PS_ITEM_TYPE_FA B ,PS_SFA_PERKMPN_OPT C ,PS_SCC_PRI_NAME_VW E WHERE A.SETID = B.SETID AND A.ITEM_TYPE = B.ITEM_TYPE AND A.AID_YEAR = B.AID_YEAR AND B.FEDERAL_ID = 'PERK' AND B.SFA_MPN_REQUIRED = 'Y' AND B.EFF_STATUS ='A' AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_ITEM_TYPE_FA B2 WHERE B2.SETID = B.SETID AND B2.ITEM_TYPE = B.ITEM_TYPE AND B2.AID_YEAR = B.AID_YEAR AND B2.EFF_STATUS = B.EFF_STATUS AND B2.EFFDT <= %CurrentDateIn) AND A.INSTITUTION=C.INSTITUTION AND C.EFFDT=( SELECT MAX(C2.EFFDT) FROM PS_SFA_PERKMPN_OPT C2 WHERE C.INSTITUTION = C2.INSTITUTION AND C2.EFFDT<=%CurrentDateIn) AND ( (C.SFA_MPN_CREATE_OPT = 'A' AND A.AWARD_STATUS = 'A' ) OR (C.SFA_MPN_CREATE_OPT = 'O' AND (A.AWARD_STATUS = 'A' OR A.AWARD_STATUS = 'O')) ) AND A.EMPLID = E.EMPLID AND E.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PS_SCC_PRI_NAME_VW X1 WHERE X1.EMPLID = E.EMPLID)

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 NAME Character(50) VARCHAR2(50) NOT NULL Name